The author selected the Free Software Foundation to receive a donation as part of the Write for DOnations program.

作者选择了自由软件基金会作为Write for DOnations计划的一部分接受捐赠。

介绍 (Introduction)

ProxySQL is a SQL-aware proxy server that can be positioned between your application and your database. It offers many features, such as load-balancing between multiple MySQL servers and serving as a caching layer for queries. This tutorial will focus on ProxySQL’s caching feature, and how it can optimize queries for your MySQL database.

ProxySQL是可识别 SQL的代理服务器，可以位于应用程序和数据库之间。 它提供了许多功能，例如多个MySQL服务器之间的负载平衡以及充当查询的缓存层 。 本教程将重点介绍ProxySQL的缓存功能，以及如何优化MySQL数据库的查询。

MySQL caching occurs when the result of a query is stored so that, when that query is repeated, the result can be returned without needing to sort through the database. This can significantly increase the speed of common queries. But in many caching methods, developers must modify the code of their application, which could introduce a bug into the codebase. To avoid this error-prone practice, ProxySQL allows you to set up transparent caching.

MySQL缓存是在存储查询结果时发生的，因此，当重复查询时，可以返回结果而无需对数据库进行排序。 这可以大大提高常见查询的速度。 但是在许多缓存方法中，开发人员必须修改其应用程序的代码，这可能会在代码库中引入错误。 为了避免这种容易出错的做法，ProxySQL允许您设置透明缓存 。

In transparent caching, only database administrators need to change the ProxySQL configuration to enable caching for the most common queries, and these changes can be done through the ProxySQL admin interface. All the developer needs to do is connect to the protocol-aware proxy, and the proxy will decide if the query can be served from the cache without hitting the back-end server.

在透明缓存中，只有数据库管理员需要更改ProxySQL配置才能为最常见的查询启用缓存，并且这些更改可以通过ProxySQL管理界面完成。 开发人员所需要做的就是连接到支持协议的代理，代理将决定是否可以从缓存中提供查询而不访问后端服务器。

In this tutorial, you will use ProxySQL to set up transparent caching for a MySQL server on Ubuntu 16.04. You will then test its performance using mysqlslap with and without caching to demonstrate the effect of caching and how much time it can save when executing many similar queries.

在本教程中，您将使用ProxySQL在Ubuntu 16.04上为MySQL服务器设置透明缓存。 然后，您将使用带有和不带有缓存的mysqlslap来测试其性能，以演示缓存的效果以及执行许多类似查询时可以节省多少时间。

先决条件 (Prerequisites)

Before you begin this guide you’ll need the following:

在开始本指南之前，您需要满足以下条件：

• One Ubuntu 16.04 server with at least 2 GB of RAM, set up with a non-root user with sudo privileges and a firewall, as instructed in our Ubuntu 16.04 Initial Server Setup guide.

  一台至少具有2 GB RAM的Ubuntu 16.04服务器，由具有sudo特权的非root用户和防火墙设置，如《 Ubuntu 16.04初始服务器设置指南》中所述 。

第1步-安装和设置MySQL服务器 (Step 1 — Installing and Setting Up the MySQL Server)

First, you will install MySQL server and configure it to be used by ProxySQL as a back-end server for serving client queries.

首先，您将安装MySQL服务器并将其配置为由ProxySQL用作后端服务器以服务于客户端查询。

On Ubuntu 16.04, mysql-server can be installed using this command:

在Ubuntu 16.04上，可以使用以下命令安装mysql-server ：

Press Y to confirm the installation.

按Y确认安装。

You will then be prompted for your MySQL root user password. Enter a strong password and save it for later use.

然后将提示您输入MySQL root用户密码。 输入一个强密码并保存以备后用。

Now that you have your MySQL server ready, you will configure it for ProxySQL to work correctly. You need to add a monitor user for ProxySQL to monitor the MySQL server, since ProxySQL listens to the back-end server via the SQL protocol, rather than using a TCP connection or HTTP GET requests to make sure that the backend is running. monitor will use a dummy SQL connection to determine if the server is alive or not.

现在您已经准备好了MySQL服务器，您将对其进行配置，以使ProxySQL正常工作。 您需要为ProxySQL添加一个监视用户来监视MySQL服务器，因为ProxySQL通过SQL协议(而不是使用TCP连接或HTTP GET请求来确保后端正在运行)侦听后端服务器。 Monitor将使用虚拟SQL连接来确定服务器是否处于活动状态。

First, log in to the MySQL shell:

首先，登录到MySQL shell：

-uroot logs you in using the MySQL root user, and -p prompts for the root user’s password. This root user is different from your server’s root user, and the password is the one you entered when installing the mysql-server package.

-uroot记录您在使用MySQL root用户，并-p提示输入root用户的密码。 该root用户不同于服务器的root用户，密码是您在安装mysql-server软件包时输入的密码。

Enter the root password and press ENTER.

输入root密码，然后按ENTER 。

Now you will create two users, one named monitor for ProxySQL and another that you will use to execute client queries and grant them the right privileges. This tutorial will name this user sammy.

现在，您将创建两个用户，一个用户名为ProxySQL的监视器 ，另一个用户将用于执行客户端查询并为其授予正确的权限。 本教程将将此用户命名为sammy 。

Create the monitor user:

创建监视器用户：

The CREATE USER query is used to create a new user that can connect from specific IPs. Using % denotes that the user can connect from any IP address. IDENTIFIED BY sets the password for the new user; enter whatever password you like, but make sure to remember it for later use.

CREATE USER查询用于创建可以从特定IP连接的新用户。 使用%表示用户可以从任何IP地址进行连接。 IDENTIFIED BY设置新用户的密码； 输入您喜欢的任何密码，但请务必记住该密码以备后用。

With the user monitor created, next make the sammy user:

创建了用户监视器后 ，接下来使该用户变得笨拙 ：

Next, grant privileges to your new users. Run the following command to configure monitor:

接下来，向您的新用户授予特权。 运行以下命令以配置监视器 ：

The GRANT query is used to give privileges to users. Here you granted only SELECT on all tables in the sys database to the monitor user; it only needs this privilege to listen to the back-end server.

GRANT查询用于向用户授予特权。 在这里，您仅将sys数据库中所有表上的SELECT授予了监视用户。 它只需要此特权即可侦听后端服务器。

Now grant all privileges to all databases to the user sammy:

现在，将所有数据库的所有特权授予用户sammy ：

This will allow sammy to make the necessary queries to test your database later.

这将使sammy进行必要的查询，以便以后测试数据库。

Apply the privilege changes by running the following:

通过运行以下命令来应用特权更改：

Finally, exit the mysql shell:

最后，退出mysql shell：

You’ve now installed mysql-server and created a user to be used by ProxySQL to monitor your MySQL server, and another one to execute client queries. Next you will install and configure ProxySQL.

现在，您已经安装了mysql-server并创建了一个供ProxySQL用来监视您MySQL服务器的用户，以及一个用于执行客户端查询的用户。 接下来，您将安装和配置ProxySQL。

第2步-安装和配置ProxySQL Server (Step 2 — Installing and Configuring ProxySQL Server)

Now you can install ProxySQL server, which will be used as a caching layer for your queries. A caching layer exists as a stop between your application servers and database back-end servers; it is used to connect to the database and to save the results of some queries in its memory for fast access later.

现在，您可以安装ProxySQL服务器，该服务器将用作查询的缓存层 。 缓存层是您的应用程序服务器和数据库后端服务器之间的站点。 它用于连接数据库，并将某些查询的结果保存在其内存中，以便以后快速访问。

The ProxySQL releases Github page offers installation files for common Linux distributions. For this tutorial, you will use wget to download the ProxySQL version 2.0.4 Debian installation file:

ProxySQL发行的Github页提供了常见Linux发行版的安装文件。 对于本教程，您将使用wget下载ProxySQL版本2.0.4 Debian安装文件：

Next, install the package using dpkg:

接下来，使用dpkg安装软件包：

Once it is installed, start ProxySQL with this command:

安装完成后，使用以下命令启动ProxySQL：

You can check if ProxySQL started correctly with this command:

您可以使用以下命令检查ProxySQL是否正确启动：

You will get an output similar to this:

您将获得类似于以下的输出：

   

    Output
   
root@ubuntu-s-1vcpu-2gb-sgp1-01:~# systemctl status proxysql
● proxysql.service - LSB: High Performance Advanced Proxy for MySQL
   Loaded: loaded (/etc/init.d/proxysql; bad; vendor preset: enabled)
   Active: active (exited) since Wed 2019-06-12 21:32:50 UTC; 6 months 7 days ago
     Docs: man:systemd-sysv-generator(8)
    Tasks: 0
   Memory: 0B
      CPU: 0

Now it is time to connect your ProxySQL server to the MySQL server. For this purpose, use the ProxySQL admin SQL interface, which by default listens to port 6032 on localhost and has admin as its username and password.

现在是时候将ProxySQL服务器连接到MySQL服务器了。 为此，请使用ProxySQL admin SQL接口，默认情况下，该接口侦听localhost上的端口6032 ，并将admin作为用户名和密码。

Connect to the interface by running the following:

通过运行以下命令连接到界面：

Enter admin when prompted for the password.

当提示您输入密码时，输入admin 。

-uadmin sets the username as admin, and the -h flag specifies the host as localhost. The port is 6032, specified using the -P flag.

-uadmin将用户名设置为admin ， -h标志将主机指定为localhost 。 端口是6032 ，使用-P标志指定。

Here you had to specify the host and port explicitly because, by default, the MySQL client connects using a local sockets file and port 3306.

在这里，您必须明确指定主机和端口，因为默认情况下，MySQL客户端使用本地套接字文件和端口3306 。

Now that you are logged into the mysql shell as admin, configure the monitor user so that ProxySQL can use it. First, use standard SQL queries to set the values of two global variables:

现在，您以admin身份登录到mysql shell，配置监视器用户，以便ProxySQL可以使用它。 首先，使用标准SQL查询来设置两个全局变量的值：

The variable mysql-monitor_username specifies the MySQL username that will be used to check if the back-end server is alive or not. The variable mysql-monitor_password points to the password that will be used when connecting to the back-end server. Use the password you created for the monitor username.

变量mysql-monitor_username指定MySQL用户名，该用户名将用于检查后端服务器是否处于活动状态。 变量mysql-monitor_password指向连接到后端服务器时将使用的密码。 使用您为监视器用户名创建的密码。

Every time you create a change in the ProxySQL admin interface, you need to use the right LOAD command to apply changes to the running ProxySQL instance. You changed MySQL global variables, so load them to RUNTIME to apply changes:

每次在ProxySQL管理界面中创建更改时，都需要使用正确的LOAD命令将更改应用于正在运行的ProxySQL实例。 您更改了MySQL全局变量，因此将它们加载到RUNTIME以应用更改：

Next, SAVE the changes to the on-disk database to persist changes between restarts. ProxySQL uses its own SQLite local database to store its own tables and variables:

接下来，将更改SAVE到磁盘数据库，以在两次重新启动之间保留更改。 ProxySQL使用其自己的SQLite本地数据库来存储其自己的表和变量：

Now, you will tell ProxySQL about the back-end server. The table mysql_servers holds information about each back-end server where ProxySQL can connect and execute queries, so add a new record using a standard SQL INSERT statement with the following values for hostgroup_id, hostname, and port:

现在，您将告诉ProxySQL有关后端服务器。 mysql_servers表保存有关ProxySQL可以连接和执行查询的每个后端服务器的信息，因此，使用标准SQL INSERT语句添加新记录，并为hostgroup_id ， hostname和port设置以下值：

To apply the changes, run LOAD and SAVE again:

要应用更改，请再次运行LOAD和SAVE ：

Finally, you will tell ProxySQL which user will connect to the back-end server; set sammy as the user, and replace sammy_password with the password you created earlier:

最后，您将告诉ProxySQL哪个用户将连接到后端服务器。 将sammy设置为用户，并将sammy_password替换为您先前创建的密码：

The table mysql_users holds information about users used to connect to the back-end servers; you specified the username, password, and default_hostgroup.

mysql_users表保存有关用于连接到后端服务器的用户的信息。 您指定了username ， password和default_hostgroup 。

LOAD and SAVE the changes:

LOAD并SAVE更改：

Then exit the mysql shell:

然后退出mysql shell：

To test that you can connect to your back-end server using ProxySQL, execute the following test query:

要测试您是否可以使用ProxySQL连接到后端服务器，请执行以下测试查询：

In this command, you used the -e flag to execute a query and close the connection. The query prints the hostname of the back-end server.

在此命令中，您使用了-e标志来执行查询并关闭连接。 该查询将打印后端服务器的主机名。

Note: ProxySQL uses port 6033 by default for listening to incoming connections.

注意：默认情况下，ProxySQL使用端口6033侦听传入的连接。

The output will look like this, with your_hostname replaced by your hostname:

输出将如下所示，将your_hostname替换为您的主机名：

   

    Output
   
+----------------------------+
| hostname                   |
+----------------------------+
| your_hostname        |
+----------------------------+

To learn more about ProxySQL configuration, see Step 3 of How To Use ProxySQL as a Load Balancer for MySQL on Ubuntu 16.04.

要了解有关ProxySQL配置的更多信息，请参阅在Ubuntu 16.04上如何将ProxySQL用作MySQL的负载均衡器的步骤3 。

So far, you configured ProxySQL to use your MySQL server as a backend and connected to the backend using ProxySQL. Now, you are ready to use mysqlslap to benchmark the query performance without caching.

到目前为止，您已将ProxySQL配置为使用MySQL服务器作为后端，并使用ProxySQL连接到后端。 现在，您可以使用mysqlslap来对查询性能进行基准测试，而无需进行缓存。

第3步-使用不带缓存的mysqlslap测试 (Step 3 — Testing Using mysqlslap Without Caching)

In this step, you will download a test database so you can execute queries against it with mysqlslap to test the latency without caching, setting a benchmark for the speed of your queries. You will also explore how ProxySQL keeps records of queries in the stats_mysql_query_digest table.

在此步骤中，您将下载一个测试数据库，以便您可以使用mysqlslap执行查询以测试延迟而无需进行缓存，从而设置查询速度的基准。 您还将探索ProxySQL如何将查询记录保存在stats_mysql_query_digest表中。

mysqlslap is a load emulation client that is used as a load testing tool for MySQL. It can test a MySQL server with auto-generated queries or with some custom queries executed on a database. It comes installed with the MySQL client package, so you do not need to install it; instead, you will download a database for testing purposes only, on which you can use mysqlslap.

mysqlslap是一个负载仿真客户端，用作MySQL的负载测试工具。 它可以使用自动生成的查询或在数据库上执行的某些自定义查询来测试MySQL服务器。 它是随MySQL客户端软件包一起安装的，因此您无需安装它。 取而代之的是，您将下载一个仅用于测试目的的数据库，您可以在该数据库上使用mysqlslap 。

In this tutorial, you will use a sample employee database. You will be using this employee database because it features a large data set that can illustrate differences in query optimization. The database has six tables, but the data it contains has more than 300,000 employee records. This will help you emulate a large-scale production workload.

在本教程中，您将使用示例雇员数据库 。 您将使用此员工数据库，因为它具有大型数据集，可以说明查询优化中的差异。 该数据库有六个表，但其中包含的数据具有300,000多条员工记录。 这将帮助您模拟大规模生产工作负载。

To download the database, first clone the Github repository using this command:

要下载数据库，请首先使用以下命令克隆Github存储库：

Then enter the test_db directory and load the database into the MySQL server using these commands:

然后进入test_db目录，并使用以下命令将数据库加载到MySQL服务器中：

This command uses shell redirection to read the SQL queries in employees.sql file and execute them on the MySQL server to create the database structure.

此命令使用外壳重定向来读取employees.sql文件中SQL查询，并在MySQL服务器上执行它们以创建数据库结构。

You will see output like this:

您将看到如下输出：

   

    Output
   
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:00:32

Once the database is loaded into your MySQL server, test that mysqlslap is working with the following query:

将数据库加载到您MySQL服务器后，测试mysqlslap是否可以使用以下查询：

mysqlslap has similar flags to the mysql client; here are the ones used in this command:

mysqlslap具有与mysql客户端相似的标志； 这是此命令中使用的那些：

• -u specifies the user used to connect to the server.

  -u指定用于连接服务器的用户。

• -p prompts for the user’s password.

  -p提示输入用户密码。

• -P connects using the specified port.

  -P使用指定的端口连接。

• -h connects to the specified host.

  -h连接到指定的主机。

• --auto-generate-sql lets MySQL perform load testing using its own generated queries.

  --auto-generate-sql允许MySQL使用其自身生成的查询执行负载测试。

• --verbose makes the output show more information.

  --verbose使输出显示更多信息。

You will get output similar to the following:

您将获得类似于以下内容的输出：

   

    Output
   
Benchmark
    Average number of seconds to run all queries: 0.015 seconds
    Minimum number of seconds to run all queries: 0.015 seconds
    Maximum number of seconds to run all queries: 0.015 seconds
    Number of clients running queries: 1
    Average number of queries per client: 0

In this output, you can see the average, minimum, and maximum number of seconds spent to execute all queries. This gives you an indication about the amount of time needed to execute the queries by a number of clients. In this output, only one client was used to execute queries.

在此输出中，您可以看到执行所有查询所花费的平均，最小和最大秒数。 这为您指示了许多客户端执行查询所需的时间。 在此输出中，仅使用一个客户端来执行查询。

Next, find out what queries mysqlslap executed in the last command by looking at ProxySQL’s stats_mysql_query_digest. This will give us information like the digest of the queries, which is a normalized form of the SQL statement that can be referenced later to enable caching.

接下来，通过查看ProxySQL的stats_mysql_query_digest上一条命令中对mysqlslap执行的stats_mysql_query_digest 。 这将为我们提供诸如查询摘要之类的信息，这是SQL语句的规范化形式，以后可以引用该形式以启用缓存。

Enter the ProxySQL admin interface with this command:

使用以下命令输入ProxySQL管理界面：

Then execute this query to find information in the stats_mysql_query_digest table:

然后执行此查询以在stats_mysql_query_digest表中查找信息：

You will see output similar to the following:

您将看到类似于以下内容的输出：

+------------+----------+-----------+--------------------+----------------------------------+
| count_star | sum_time | hostgroup | digest             | digest_text                      |
+------------+----------+-----------+--------------------+----------------------------------+
| 1          | 598      | 1         | 0xF8F780C47A8D1D82 | SELECT @@HOSTNAME as hostname    |
| 1          | 0        | 1         | 0x226CD90D52A2BA0B | select @@version_comment limit ? |
+------------+----------+-----------+--------------------+----------------------------------+
2 rows in set (0.01 sec)

The previous query selects data from the stats_mysql_query_digest table, which contains information about all executed queries in ProxySQL. Here you have five columns selected:

上一个查询从stats_mysql_query_digest表中选择数据，该表包含有关ProxySQL中所有已执行查询的信息。 在这里，您选择了五列：

• count_star: The number of times this query was executed.

  count_star ：执行此查询的次数。

• sum_time: Total time in milliseconds that this query took to execute.

  sum_time ：此查询执行的总时间(以毫秒为单位)。

• hostgroup: The hostgroup used to execute the query.

  hostgroup ：用于执行查询的主机组。

• digest: A digest of the executed query.

  digest ：已执行查询的摘要。

• digest_text: The actual query. In this tutorial’s example, the second query is parameterized using ? marks in place of variable parameters. select @@version_comment limit 1 and select @@version_comment limit 2, therefore, are grouped together as the same query with the same digest.

  digest_text ：实际查询。 在本教程的示例中，第二个查询使用?参数化? 标记代替可变参数。 select @@version_comment limit 1和select @@version_comment limit 2 ，因此将它们组合为具有相同摘要的同一查询。

Now that you know how to check query data in the stats_mysql_query_digest table, exit the mysql shell:

现在，您知道如何检查stats_mysql_query_digest表中的查询数据，退出mysql shell：

The database you downloaded contains some tables with demo data. You will now test queries on the dept_emp table by selecting any records whose from_date is greater than 2000-04-20 and recording the average execution time.

您下载的数据库包含一些带有演示数据的表。 现在，通过选择from_date大于2000-04-20任何记录并记录平均执行时间，来测试dept_emp表上的查询。

Use this command to run the test:

使用以下命令运行测试：

Here you are using some new flags:

在这里，您使用了一些新的标志：

• --concurrency=100: This sets the number of users to simulate, in this case 100.

  --concurrency=100 ：设置要模拟的用户数，在这种情况下为100 。

• --iterations=20: This causes the test to run 20 times and calculate results from all of them.

  --iterations=20 ：这将导致测试运行20次并从所有计算结果。

• --create-schema=employees: Here you selected the employees database.

  --create-schema=employees ：在这里您选择了employees数据库。

• --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'": Here you specified the query executed in the test.

  --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'" ：在此处，您指定了在测试中执行的查询。

The test will take a few minutes. After it is done, you will get results similar to the following:

测试将需要几分钟。 完成后，您将获得类似于以下内容的结果：

   

    Output
   
Benchmark
        Average number of seconds to run all queries: 18.117 seconds
        Minimum number of seconds to run all queries: 8.726 seconds
        Maximum number of seconds to run all queries: 22.697 seconds
        Number of clients running queries: 100
        Average number of queries per client: 1

Your numbers could be a little different. Keep these numbers somewhere in order to compare them with the results from after you enable caching.

您的电话号码可能会有所不同。 将这些数字保留在某处，以便将它们与启用缓存后的结果进行比较。

After testing ProxySQL without caching, it is time to run the same test again, but this time with caching enabled.

在不缓存的情况下测试ProxySQL之后，是时候再次运行相同的测试了，但是这次启用了缓存。

第4步-使用mysqlslap和缓存进行测试 (Step 4 — Testing Using mysqlslap With Caching)

In this step, caching will help us to decrease latency when executing similar queries. Here, you will identify the queries executed, take their digests from ProxySQL’s stats_mysql_query_digest table, and use them to enable caching. Then, you will test again to check the difference.

在这一步中，缓存将帮助我们减少执行类似查询时的延迟。 在这里，您将确定执行的查询，从ProxySQL的stats_mysql_query_digest表中提取其摘要，并使用它们来启用缓存。 然后，您将再次测试以检查差异。

To enable caching, you need to know the digests of the queries that will be cached. Log in to the ProxySQL admin interface using this command:

要启用缓存，您需要知道将要缓存的查询的摘要。 使用以下命令登录到ProxySQL管理界面：

Then execute this query again to get a list of queries executed and their digests:

然后再次执行此查询以获取已执行查询及其摘要的列表：

You will get a result similar to this:

您将得到类似于以下结果：

   

    Output
   
+------------+-------------+-----------+--------------------+------------------------------------------+
| count_star | sum_time    | hostgroup | digest             | digest_text                              |
+------------+-------------+-----------+--------------------+------------------------------------------+
| 2000       | 33727110501 | 1         | 0xC5DDECD7E966A6C4 | SELECT * from dept_emp WHERE from_date>? |
| 1          | 601         | 1         | 0xF8F780C47A8D1D82 | SELECT @@HOSTNAME as hostname            |
| 1          | 0           | 1         | 0x226CD90D52A2BA0B | select @@version_comment limit ?         |
+------------+-------------+-----------+--------------------+------------------------------------------+
3 rows in set (0.00 sec)

Look at the first row. It is about a query that was executed 2000 times. This is the benchmarked query executed previously. Take its digest and save it to be used in adding a query rule for caching.

看第一行。 它是关于一个已执行2000次的查询。 这是先前执行的基准查询。 提取摘要并保存，以添加用于缓存的查询规则。

The next few queries will add a new query rule to ProxySQL that will match the digest of the previous query and put a cache_ttl value for it. cache_ttl is the number of milliseconds that the result will be cached in memory:

接下来的几个查询将向ProxySQL添加一个新查询规则，该规则将与先前查询的摘要匹配，并为其添加一个cache_ttl值。 cache_ttl是结果将被缓存在内存中的毫秒数：

In this command you are adding a new record to the mysql_query_rules table; this table holds all the rules applied before executing a query. In this example, you are adding a value for the cache_ttl column that will cause the matched query by the given digest to be cached for a number of milliseconds specified in this column. You put 1 in the apply column to make sure that the rule is applied to queries.

在此命令中，您将新记录添加到mysql_query_rules表中； 该表包含执行查询之前应用的所有规则。 在此示例中，您要为cache_ttl列添加一个值，该值将使给定摘要的匹配查询被缓存此列中指定的毫秒数。 您在“应用”列中输入1以确保将规则应用于查询。

LOAD and SAVE these changes, then exit the mysql shell:

LOAD并SAVE这些更改，然后退出mysql shell：

Now that caching is enabled, re-run the test again to check the result:

现在已启用缓存，请再次重新运行测试以检查结果：

This will give output similar to the following:

这将产生类似于以下内容的输出：

   

    Output
   
Benchmark
        Average number of seconds to run all queries: 7.020 seconds
        Minimum number of seconds to run all queries: 0.274 seconds
        Maximum number of seconds to run all queries: 23.014 seconds
        Number of clients running queries: 100
        Average number of queries per client: 1

Here you can see the big difference in average execution time: it dropped from 18.117 seconds to 7.020.

在这里，您可以看到平均执行时间的巨大差异：它从18.117秒降低到7.020 。

结论 (Conclusion)

In this article, you set up transparent caching with ProxySQL to cache database query results. You also tested the query speed with and without caching to see the difference that caching can make.

在本文中，您将使用ProxySQL设置透明缓存来缓存数据库查询结果。 您还测试了使用和不使用缓存的查询速度，以了解缓存可以带来的不同。

You’ve used one level of caching in this tutorial. You could also try, web caching, which sits in front of a web server and caches the responses to similar requests, sending the response back to the client without hitting the back-end servers. This is very similar to ProxySQL caching but at a different level. To learn more about web caching, check out our Web Caching Basics: Terminology, HTTP Headers, and Caching Strategies primer.

在本教程中，您已经使用了一级缓存。 您还可以尝试Web缓存 ，它位于Web服务器的前面，并缓存对类似请求的响应，将响应发送回客户端而不打到后端服务器。 这与ProxySQL缓存非常相似，但级别不同。 要了解有关Web缓存的更多信息，请查看我们的Web缓存基础知识：术语，HTTP标头和缓存策略入门 。

MySQL server also has its own query cache; you can learn more about it in our How To Optimize MySQL with Query Cache on Ubuntu 18.04 tutorial.

MySQL服务器也有自己的查询缓存； 您可以在《 如何在Ubuntu 18.04上使用查询缓存优化MySQL》中了解有关它的更多信息。

翻译自: https://www.digitalocean.com/community/tutorials/how-to-optimize-mysql-queries-with-proxysql-caching-on-ubuntu-16-04