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

查询缓存是一项突出的MySQL功能,可加速从数据库中检索数据。在本教程中,您将首先配置没有查询缓存的MySQL并运行查询以查看持续时间结果。然后,您将设置查询缓存并测试您的MySQL服务器,并启用它以显示性能差异。

作者选择Apache Software Foundation作为Write for DOnations计划的一部分进行捐赠。

介绍

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

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

在本教程中,您将首先配置没有查询缓存的MySQL并运行查询以查看它们的执行速度。 然后,您将设置查询缓存并测试您的MySQL服务器,并启用它以显示性能差异。

注意:虽然从MySQL 5.7.20开始不推荐使用查询缓存,并且在MySQL 8.0中删除了查询缓存,但如果您使用受支持的MySQL版本,它仍然是一个强大的工具。 但是,如果您使用的是较新版本的MySQL,则可以采用其他第三方工具(如ProxySQL)来优化MySQL数据库的性能。

先决条件

在开始之前,您需要以下内容:

第1步 - 检查查询缓存的可用性

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

ssh user_name@your_server_ip

然后,运行以下命令以root用户身份登录MySQL服务器:

sudo mysql -u root -p

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

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

show variables like 'have_query_cache';

您应该得到类似于以下内容的输出:

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

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

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

第2步 - 检查默认查询缓存变量

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

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

show variables like 'query_cache_%' ;

您将看到输出中列出的变量:

+------------------------------+----------+
| 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)

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

MySQL不会在一个大块中处理缓存数据; 相反,它是以块为单位处理的。 分配给每个块的最小内存量由query_cache_min_res_unit变量确定。 默认值为4096字节或4KB。

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

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

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

注意:自MySQL版本5.7.20起,不推荐使用query_cache_wlock_invalidate变量。 因此,您可能无法在输出中看到此内容,具体取决于您使用的MySQL版本。

在查看了控制MySQL查询缓存的系统变量之后,您现在将测试MySQL如何在不首先启用该功能的情况下执行。

第3步 - 在没有查询缓存的情况下测试MySQL服务器

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

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

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

Create database sample_db;
Query OK, 1 row affected (0.00 sec)

然后切换到数据库:

Use sample_db;
Database changed

创建一个包含两个字段( customer_idcustomer_name )的表,并将其命名为customers

Create table customers (customer_id INT PRIMARY KEY, customer_name VARCHAR(50) NOT NULL) Engine = InnoDB;
Query OK, 0 rows affected (0.01 sec)

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

Insert into customers(customer_id, customer_name) values ('1', 'JANE DOE');
Insert into customers(customer_id, customer_name) values ('2', 'JANIE DOE');
Insert into customers(customer_id, customer_name) values ('3', 'JOHN ROE');
Insert into customers(customer_id, customer_name) values ('4', 'MARY ROE');
Insert into customers(customer_id, customer_name) values ('5', 'RICHARD ROE');
Insert into customers(customer_id, customer_name) values ('6', 'JOHNNY DOE');
Insert into customers(customer_id, customer_name) values ('7', 'JOHN SMITH');
Insert into customers(customer_id, customer_name) values ('8', 'JOE BLOGGS');
Insert into customers(customer_id, customer_name) values ('9', 'JANE POE');
Insert into customers(customer_id, customer_name) values ('10', 'MARK MOE');
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
...

下一步是启动MySQL分析器 ,它是一种用于监视MySQL查询性能的分析服务。 要打开当前会话的配置文件,请运行以下命令,将其设置为1 ,该命令处于打开状态:

SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

然后,运行以下查询以检索所有客户:

Select * from customers;

您将收到以下输出:

+-------------+---------------+
| 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)

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

SHOW PROFILES;

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

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

输出显示MySQL从数据库中检索记录所花费的总时间。 在启用查询缓存时,您将在后续步骤中比较此数据,因此请记下您的Duration 您可以忽略输出中的警告,因为这只是表示将在未来的MySQL版本中删除SHOW PROFILES命令并替换为Performance Schema

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

quit;

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

第4步 - 设置查询缓存

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

使用nano编辑文件:

sudo nano /etc/mysql/my.cnf

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

/etc/mysql/my.cnf
...
[mysqld]
query_cache_type=1
query_cache_size = 10M
query_cache_limit=256K

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

CTRL + XY ,然后按ENTER保存并关闭文件。 然后,重新启动MySQL服务器以实现更改:

sudo systemctl restart mysql

您现在已启用查询缓存。

一旦配置了查询缓存并重新启动MySQL以应用更改,您将继续并在启用该功能的情况下测试MySQL的性能。

第5步 - 在启用查询缓存的情况下测试MySQL服务器

在此步骤中,您将再次运行在第3步中运行的相同查询,以检查查询缓存如何优化MySQL服务器的性能。

首先,以root用户身份连接到MySQL服务器:

sudo mysql -u root -p

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

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

show variables like 'query_cache_%' ;

您将看到以下输出:

+------------------------------+----------+
| 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)

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

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

Use sample_db;

启动MySQL探查器:

SET profiling = 1;

然后,运行查询以至少两次检索所有客户,以便生成足够的分析信息。

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

Select * from customers;
Select * from customers;

然后,列出配置文件信息:

SHOW PROFILES;

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

+----------+------------+-------------------------+
| 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)

如您所见,在此步骤中,运行查询所花费的时间从0.00044075 (第3步中没有查询缓存)大幅减少到0.00026000 (第二个查询)。

您可以通过详细分析第一个查询来查看启用查询缓存功能的优化:

SHOW PROFILE FOR QUERY 1;
+--------------------------------+----------+
| 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)

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

SHOW PROFILE FOR QUERY 2;
+--------------------------------+----------+
| 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)

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

通过在服务器上启用MySQL查询缓存功能,您现在可以获得更高的读取速度。

结论

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