如何在Ubuntu 18.04上使用查询缓存优化MySQL

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

作者选择了Apache Software Foundation作为Write for DOnations计划的一部分来接受捐赠。

介绍 (Introduction)

Query cache is a prominent MySQL feature that speeds up data retrieval from a database. It achieves this by storing MySQL SELECT statements together with the retrieved record set in memory, then if a client requests identical queries it can serve the data faster without executing commands again from the database.

查询缓存是MySQL的一项重要功能，可加快从数据库中检索数据的速度。 它通过将MySQL SELECT语句与检索到的记录集一起存储在内存中来实现此目的，然后，如果客户端请求相同的查询，则它可以更快地提供数据，而无需再次执行数据库中的命令。

Compared to data read from disk, cached data from RAM (Random Access Memory) has a shorter access time, which reduces latency and improves input/output (I/O) operations. As an example, for a WordPress site or an e-commerce portal with high read calls and infrequent data changes, query cache can drastically boost the performance of the database server and make it more scalable.

与从磁盘读取的数据相比，从RAM(随机存取存储器)缓存的数据具有更短的访问时间，从而减少了延迟并改善了输入/输出(I / O)操作。 例如，对于具有高读取调用和不频繁数据更改的WordPress网站或电子商务门户，查询缓存可以极大地提高数据库服务器的性能并使其更具可伸缩性。

In this tutorial, you will first configure MySQL without query cache and run queries to see how quickly they are executed. Then you’ll set up query cache and test your MySQL server with it enabled to show the difference in performance.

在本教程中，您将首先配置不带查询缓存MySQL，然后运行查询以查看执行速度。 然后，您将设置查询缓存并在启用了该功能的情况下测试MySQL服务器，以显示性能差异。

Note: Although query cache is deprecated as of MySQL 5.7.20, and removed in MySQL 8.0, it is still a powerful tool if you’re using supported versions of MySQL. However, if you are using newer versions of MySQL, you may adopt alternative third-party tools like ProxySQL to optimize performance on your MySQL database.

注意：虽然自MySQL 5.7.20起已弃用查询缓存，但在MySQL 8.0中已将其删除，但如果使用受支持MySQL版本，它仍然是功能强大的工具。 但是，如果使用的是MySQL的较新版本，则可以采用替代的第三方工具，例如ProxySQL，以优化MySQL数据库的性能。

先决条件 (Prerequisites)

Before you begin, you will need the following:

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

• One Ubuntu 18.04 server configured with a firewall and a non-root user. You can refer to the Initial Server Setup with Ubuntu 18.04 guide to configure your server.

  一台配置了防火墙和非root用户的Ubuntu 18.04服务器。 您可以参考《 Ubuntu 18.04的初始服务器设置》指南来配置服务器。

• A MySQL server set up as detailed in this How To Install MySQL on Ubuntu 18.04 tutorial. Ensure you set a root password for the MySQL server.

  MySQL服务器的设置如本“ 如何在Ubuntu 18.04上安装MySQL”教程中所述。 确保为MySQL服务器设置了root密码。

第1步-检查查询缓存的可用性 (Step 1 — Checking the Availability of Query Cache)

Before you set up query cache, you’ll check whether your version of MySQL supports this feature. First, ssh into your Ubuntu 18.04 server:

在设置查询缓存之前，您将检查您MySQL版本是否支持此功能。 首先，将ssh插入您的Ubuntu 18.04服务器：

• ssh user_name@your_server_ip

  ssh用户名@ your_server_ip

Then, run the following command to log in to the MySQL server as the root user:

然后，运行以下命令以root用户身份登录到MySQL服务器：

• sudo mysql -u root -p
  须藤mysql -u root -p

Enter your MySQL server root password when prompted and then press ENTER to continue.

在出现提示时输入您MySQL服务器root密码，然后按ENTER继续。

Use the following command to check if query cache is supported:

使用以下命令检查是否支持查询缓存：

• show variables like 'have_query_cache';
  显示像'have_query_cache'这样的变量；

You should get an output similar to the following:

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

   
   

    
    Output
   
   
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.01 sec)

You can see the value of have_query_cache is set to YES and this means query cache is supported. If you receive an output showing that your version does not support query cache, please see the note in the Introduction section for more information.

您可以看到have_query_cache的值设置为YES ，这意味着支持查询缓存。 如果收到显示您的版本不支持查询缓存的输出，请参阅“简介”部分中的注释以获取更多信息。

Now that you have checked and confirmed that your version of MySQL supports query cache, you will move on to examining the variables that control this feature on your database server.

现在，您已经检查并确认您MySQL版本支持查询缓存，接下来将继续检查控制数据库服务器上此功能的变量。

步骤2 —检查默认查询缓存变量 (Step 2 — Checking the Default Query Cache Variables)

In MySQL, a number of variables control query cache. In this step, you’ll check the default values that ship with MySQL and understand what each variable controls.

在MySQL中，许多变量控制查询缓存。 在此步骤中，您将检查MySQL附带的默认值，并了解每个变量控制什么。

You can examine these variables using the following command:

您可以使用以下命令检查这些变量：

• show variables like 'query_cache_%' ;
  显示类似'query_cache_％'的变量；

You will see the variables listed in your output:

您将在输出中看到列出的变量：

   
   

    
    Output
   
   
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | OFF      |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.00 sec)

The query_cache_limit value determines the maximum size of individual query results that can be cached. The default value is 1,048,576 bytes and this is equivalent to 1MB.

query_cache_limit值确定可以缓存的单个查询结果的最大大小。 默认值为1,048,576字节，相当于1MB。

MySQL does not handle cached data in one big chunk; instead it is handled in blocks. The minimum amount of memory allocated to each block is determined by the query_cache_min_res_unit variable. The default value is 4096 bytes or 4KB.

MySQL不会大量处理缓存的数据。 而是按块处理。 分配给每个块的最小内存量由query_cache_min_res_unit变量确定。 默认值为4096字节或4KB。

query_cache_size controls the total amount of memory allocated to the query cache. If the value is set to zero, it means query cache is disabled. In most cases, the default value may be set to 16,777,216 (around 16MB). Also, keep in mind that query_cache_size needs at least 40KB to allocate its structures. The value allocated here is aligned to the nearest 1024 byte block. This means the reported value may be slightly different from what you set.

query_cache_size控制分配给查询缓存的内存总量。 如果该值设置为零，则表示查询缓存已禁用。 在大多数情况下，默认值可能设置为16,777,216(约16MB)。 另外，请记住， query_cache_size至少需要40KB才能分配其结构。 此处分配的值与最接近的1024字节块对齐。 这意味着报告的值可能与您设置的值略有不同。

MySQL determines the queries to cache by examining the query_cache_type variable. Setting this value to 0 or OFF prevents caching or retrieval of cached queries. You can also set it to 1 to enable caching for all queries except for ones beginning with the SELECT SQL_NO_CACHE statement. A value of 2 tells MySQL to only cache queries that begin with SELECT SQL_CACHE command.

MySQL通过检查query_cache_type变量来确定要缓存的查询。 将此值设置为0或OFF可以防止缓存或检索缓存的查询。 您也可以将其设置为1以启用所有查询的缓存，但以SELECT SQL_NO_CACHE语句开头的查询除外。 值2指示MySQL仅缓存以SELECT SQL_CACHE命令开头的查询。

The variable query_cache_wlock_invalidate controls whether MySQL should retrieve results from the cache if the table used on the query is locked. The default value is OFF.

变量query_cache_wlock_invalidate控制如果查询中使用的表被锁定，MySQL是否应从缓存中检索结果。 默认值为OFF 。

Note: The query_cache_wlock_invalidate variable is deprecated as of MySQL version 5.7.20. As a result, you may not see this in your output depending on the MySQL version you’re using.

注： query_cache_wlock_invalidate变量被弃用MySQL版本20年7月5日。 结果，根据所使用MySQL版本，您可能不会在输出中看到此内容。

Having reviewed the system variables that control the MySQL query cache, you’ll now test how MySQL performs without first enabling the feature.

回顾了控制MySQL查询缓存的系统变量之后，现在您将测试MySQL的性能，而无需先启用该功能。

第3步-在没有查询缓存的情况下测试MySQL服务器 (Step 3 — Testing Your MySQL Server Without Query Cache)

The goal of this tutorial is to optimize your MySQL server by using the query cache feature. To see the difference in speed, you’re going to run queries and see their performance before and after implementing the feature.

本教程的目的是通过使用查询缓存功能来优化MySQL服务器。 要了解速度的差异，您将在实现功能之前和之后运行查询并查看其性能。

In this step you’re going to create a sample database and insert some data to see how MySQL performs without query cache.

在这一步中，您将创建一个示例数据库并插入一些数据，以查看MySQL在没有查询缓存的情况下的性能。

While still logged in to your MySQL server, create a database and name it sample_db by running the following command:

在仍然登录到MySQL服务器的同时，创建数据库并通过运行以下命令将其命名为sample_db ：

• Create database sample_db;

  创建数据库sample_db ;

   
   

    
    Output
   
   
Query OK, 1 row affected (0.00 sec)

Then switch to the database:

然后切换到数据库：

• Use sample_db;

  使用sample_db ;

   
   

    
    Output
   
   
Database changed

Create a table with two fields (customer_id and customer_name) and name it customers:

创建一个包含两个字段的表( customer_id和customer_name )并将其命名为customers ：

• Create table customers (customer_id INT PRIMARY KEY, customer_name VARCHAR(50) NOT NULL) Engine = InnoDB;

  创建表客户 ( customer_id INT PRIMARY KEY， customer_name VARCHAR(50)NOT NULL)引擎= InnoDB；

   
   

    
    Output
   
   
Query OK, 0 rows affected (0.01 sec)

Then, run the following commands to insert some sample data:

然后，运行以下命令以插入一些示例数据：

• Insert into customers(customer_id, customer_name) values ('1', 'JANE DOE');

  将值('1'，'JANE DOE')插入客户 ( customer_id ， customer_name );

• Insert into customers(customer_id, customer_name) values ('2', 'JANIE DOE');

  将值('2'，'JANIE DOE')插入客户 ( customer_id ， customer_name );

• Insert into customers(customer_id, customer_name) values ('3', 'JOHN ROE');

  将值('3'，'JOHN ROE')插入客户 ( customer_id ， customer_name );

• Insert into customers(customer_id, customer_name) values ('4', 'MARY ROE');

  将值('4'，'MARY ROE')插入客户 ( customer_id ， customer_name );

• Insert into customers(customer_id, customer_name) values ('5', 'RICHARD ROE');

  将值(“ 5”，“ RICHARD ROE”)插入客户 ( customer_id ， customer_name )；

• Insert into customers(customer_id, customer_name) values ('6', 'JOHNNY DOE');

  将值('6'，'JOHNNY DOE')插入客户 ( customer_id ， customer_name );

• Insert into customers(customer_id, customer_name) values ('7', 'JOHN SMITH');

  将值('7'，'JOHN SMITH')插入客户 ( customer_id ， customer_name );

• Insert into customers(customer_id, customer_name) values ('8', 'JOE BLOGGS');

  将值(“ 8”，“ JOE BLOGGS”)插入到客户 ( customer_id ， customer_name )中；

• Insert into customers(customer_id, customer_name) values ('9', 'JANE POE');

  将值('9'，'JANE POE')插入客户 ( customer_id ， customer_name );

• Insert into customers(customer_id, customer_name) values ('10', 'MARK MOE');

  将值('10'，'MARK MOE')插入客户 ( customer_id ， customer_name );

   
   

    
    Output
   
   
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
...

The next step is starting the MySQL profiler, which is an analysis service for monitoring the performance of MySQL queries. To turn the profile on for the current session, run the following command, setting it to 1, which is on:

下一步是启动MySQL事件探查器 ，这是一个用于监视MySQL查询性能的分析服务。 要打开当前会话的概要文件，请运行以下命令，将其设置为1 ，即已打开：

• SET profiling = 1;
  SET配置文件= 1;

   
   

    
    Output
   
   
Query OK, 0 rows affected, 1 warning (0.00 sec)

Then, run the following query to retrieve all customers:

然后，运行以下查询以检索所有客户：

• Select * from customers;

  从客户中选择*;

You’ll receive the following output:

您将收到以下输出：

   
   

    
    Output
   
   
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
|           1 | JANE DOE      |
|           2 | JANIE DOE     |
|           3 | JOHN ROE      |
|           4 | MARY ROE      |
|           5 | RICHARD ROE   |
|           6 | JOHNNY DOE    |
|           7 | JOHN SMITH    |
|           8 | JOE BLOGGS    |
|           9 | JANE POE      |
|          10 | MARK MOE      |
+-------------+---------------+
10 rows in set (0.00 sec)

Then, run the SHOW PROFILES command to retrieve performance information about the SELECT query you just ran:

然后，运行SHOW PROFILES命令以检索有关您刚运行的SELECT查询的性能信息：

• SHOW PROFILES;
  显示个人资料；

You will get output similar to the following:

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

   
   

    
    Output
   
   
+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        1 | 0.00044075 | Select * from customers |
+----------+------------+-------------------------+
1 row in set, 1 warning (0.00 sec)

The output shows the total time spent by MySQL when retrieving records from the database. You are going to compare this data in the next steps when query cache is enabled, so keep note of your Duration. You can ignore the warning within the output since this simply indicates that SHOW PROFILES command will be removed in a future MySQL release and replaced with Performance Schema.

输出显示MySQL从数据库检索记录时花费的总时间。 启用查询缓存后，您将在接下来的步骤中比较这些数据，因此请注意您的Duration 。 您可以忽略输出中的警告，因为这仅表明SHOW PROFILES命令将在将来MySQL版本中删除，并被Performance Schema取代。

Next, exit from the MySQL Command Line Interface.

接下来，退出MySQL命令行界面。

• quit;
  退出;

You have ran a query with MySQL before enabling query cache and noted down the Duration or time spent to retrieve records. Next, you will enable query cache and see if there is a performance boost when running the same query.

在启用查询缓存之前，您已经使用MySQL运行了查询，并记下了Duration或检索记录所花费的Duration 。 接下来，您将启用查询缓存，并查看运行相同查询时性能是否得到提高。

步骤4 —设置查询缓存 (Step 4 — Setting Up Query Cache)

In the previous step, you created sample data and ran a SELECT statement before you enabled query cache. In this step, you’ll enable query cache by editing the MySQL configuration file.

在上一步中，您创建了示例数据并运行了SELECT语句，然后启用了查询缓存。 在此步骤中，您将通过编辑MySQL配置文件来启用查询缓存。

Use nano to edit the file:

使用nano编辑文件：

• sudo nano /etc/mysql/my.cnf
  须藤nano /etc/mysql/my.cnf

Add the following information to the end of your file:

将以下信息添加到文件末尾：

/etc/mysql/my.cnf

/etc/mysql/my.cnf

...
[mysqld]
query_cache_type=1
query_cache_size = 10M
query_cache_limit=256K

Here you’ve enabled query cache by setting the query_cache_type to 1. You’ve also set up the individual query limit size to 256K and instructed MySQL to allocate 10 megabytes to query cache by setting the value of query_cache_size to 10M.

在这里，通过将query_cache_type设置为1启用了查询缓存。 您还已经将单个查询限制大小设置为256K并通过将query_cache_size的值设置为10M来指示MySQL为查询缓存分配10 MB。

Save and close the file by pressing CTRL + X, Y, then ENTER. Then, restart your MySQL server to implement the changes:

通过按CTRL + X ， Y ，然后按ENTER保存并关闭文件。 然后，重新启动MySQL服务器以实施更改：

• sudo systemctl restart mysql
  sudo systemctl重新启动mysql

You have now enabled query cache.

现在，您已启用查询缓存。

Once you have configured query cache and restarted MySQL to apply the changes, you will go ahead and test the performance of MySQL with the feature enabled.

配置查询缓存并重新启动MySQL以应用更改后，您将继续并启用该功能来测试MySQL的性能。

第5步—在启用查询缓存的情况下测试MySQL服务器 (Step 5 — Testing Your MySQL Server with Query Cache Enabled)

In this step, you’ll run the same query you ran in Step 3 one more time to check how query cache has optimized the performance of your MySQL server.

在这一步中，您将再次运行在第3步中运行的同一查询，以检查查询缓存如何优化了MySQL服务器的性能。

First, connect to your MySQL server as the root user:

首先，以root用户身份连接到MySQL服务器：

• sudo mysql -u root -p
  须藤mysql -u root -p

Enter your root password for the database server and hit ENTER to continue.

输入数据库服务器的根密码，然后按ENTER继续。

Now confirm your configuration set in the previous step to ensure you enabled query cache:

现在，确认上一步中的配置集，以确保启用了查询缓存：

• show variables like 'query_cache_%' ;
  显示类似'query_cache_％'的变量；

You’ll see the following output:

您将看到以下输出：

   
   

    
    Output
   
   
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 262144   |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 10485760 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.01 sec)

The variable query_cache_type is set to ON; this confirms that you enabled query cache with the parameters defined in the previous step.

变量query_cache_type设置为ON ； 这确认您已使用上一步中定义的参数启用了查询缓存。

Switch to the sample_db database that you created earlier.

切换到您先前创建的sample_db数据库。

• Use sample_db;

  使用sample_db ;

Start the MySQL profiler:

启动MySQL事件探查器：

• SET profiling = 1;
  SET配置文件= 1;

Then, run the query to retrieve all customers at least two times in order to generate enough profiling information.

然后，运行查询以至少两次检索所有客户，以生成足够的概要信息。

Remember, once you’ve run the first query, MySQL will create a cache of the results and therefore, you must run the query twice to trigger the cache:

请记住，运行第一个查询后，MySQL将创建结果的缓存，因此，您必须运行两次查询才能触发缓存：

• Select * from customers;

  从客户中选择*;

• Select * from customers;

  从客户中选择*;

Then, list the profiles information:

然后，列出配置文件信息：

• SHOW PROFILES;
  显示个人资料；

You’ll receive an output similar to the following:

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

   
   

    
    Output
   
   
+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        1 | 0.00049250 | Select * from customers |
|        2 | 0.00026000 | Select * from customers |
+----------+------------+-------------------------+
2 rows in set, 1 warning (0.00 sec)

As you can see the time taken to run the query has drastically reduced from 0.00044075 (without query cache in Step 3) to 0.00026000 (the second query) in this step.

如您所见，运行查询所花费的时间已从0.00044075 (步骤3中没有查询缓存)急剧减少0.00044075步骤中的0.00026000 (第二个查询)。

You can see the optimization from enabling the query cache feature by profiling the first query in detail:

通过详细分析第一个查询，可以从启用查询缓存功能中看到优化：

• SHOW PROFILE FOR QUERY 1;
  显示查询1的个人资料；

   
   

    
    Output
   
   
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000025 |
| Waiting for query cache lock   | 0.000004 |
| starting                       | 0.000003 |
| checking query cache for query | 0.000045 |
| checking permissions           | 0.000008 |
| Opening tables                 | 0.000014 |
| init                           | 0.000018 |
| System lock                    | 0.000008 |
| Waiting for query cache lock   | 0.000002 |
| System lock                    | 0.000018 |
| optimizing                     | 0.000003 |
| statistics                     | 0.000013 |
| preparing                      | 0.000010 |
| executing                      | 0.000003 |
| Sending data                   | 0.000048 |
| end                            | 0.000004 |
| query end                      | 0.000006 |
| closing tables                 | 0.000006 |
| freeing items                  | 0.000006 |
| Waiting for query cache lock   | 0.000003 |
| freeing items                  | 0.000213 |
| Waiting for query cache lock   | 0.000019 |
| freeing items                  | 0.000002 |
| storing result in query cache  | 0.000003 |
| cleaning up                    | 0.000012 |
+--------------------------------+----------+
25 rows in set, 1 warning (0.00 sec)

Run the following command to show profile information for the second query, which is cached:

运行以下命令以显示第二个查询的配置文件信息，该信息已缓存：

• SHOW PROFILE FOR QUERY 2;
  显示查询2的个人资料；

   
   

    
    Output
   
   
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000024 |
| Waiting for query cache lock   | 0.000003 |
| starting                       | 0.000002 |
| checking query cache for query | 0.000006 |
| checking privileges on cached  | 0.000003 |
| checking permissions           | 0.000027 |
| sending cached result to clien | 0.000187 |
| cleaning up                    | 0.000008 |
+--------------------------------+----------+
8 rows in set, 1 warning (0.00 sec)

The outputs from the profiler show that MySQL took less time on the second query because it was able to retrieve data from the query cache instead of reading it from the disk. You can compare the two sets of output for each of the queries. If you look at the profile information on QUERY 2, the status of sending cached result to client shows that data was read from the cache and no tables were opened since the Opening tables status is missing.

分析器的输出表明，MySQL在第二次查询上花费的时间更少，因为它能够从查询缓存中检索数据，而不是从磁盘读取数据。 您可以为每个查询比较两组输出。 如果您查看QUERY 2上的配置文件信息，则将sending cached result to client的状态显示从缓存中读取了数据，并且由于缺少“ Opening tables状态而没有打开任何表。

With the MySQL query cache feature enabled on your server, you’ll now experience improved read speeds.

在服务器上启用MySQL查询缓存功能后，您现在将获得提高的读取速度。

结论 (Conclusion)

You have set up query cache to speed up your MySQL server on Ubuntu 18.04. Using features like MySQL’s query cache can enhance the speed of your website or web application. Caching reduces unnecessary execution for SQL statements and is a highly recommended and popular method for optimizing your database. For more on speeding up your MySQL server, try the How To Set Up a Remote Database to Optimize Site Performance with MySQL on Ubuntu 18.04 tutorial.

您已经设置了查询缓存，以在Ubuntu 18.04上加快MySQL服务器的速度。 使用MySQL的查询缓存等功能可以提高您的网站或Web应用程序的速度。 缓存减少了SQL语句的不必要执行，是一种强烈推荐和流行的用于优化数据库的方法。 有关加快MySQL服务器速度的更多信息，请尝试如何在Ubuntu 18.04上使用MySQL设置远程数据库以优化站点性能 。

翻译自: https://www.digitalocean.com/community/tutorials/how-to-optimize-mysql-with-query-cache-on-ubuntu-18-04