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

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

介绍

Query Cache是一个突出的 MySQL功能,可通过存储 MySQL SELECT 陈述以及检索的记录在内存中,然后如果客户端请求相同的查询,它可以更快地服务数据,而不会从数据库中再次执行命令。

与从磁盘读取的数据相比,从RAM(随机访问内存)存储的缓存数据具有较短的访问时间,这减少了延迟并改善了输入/输出(I/O)操作,例如,对于一个WordPress网站或电子商务门户,高读数呼叫和罕见的数据变化,查询缓存可以大大提高数据库服务器的性能,使其更可扩展。

在本教程中,您将首先配置MySQL没有查询缓存并运行查询,以查看它们的执行速度。

<$>[注] 注: 虽然查询缓存在 MySQL 5.7.20 开始时已经过时,并且在 MySQL 8.0 中被删除,但如果您正在使用受支持的 MySQL 版本,它仍然是一个强大的工具。

前提条件

在你开始之前,你需要以下几点:

一个 Ubuntu 18.04 服务器配置了防火墙和非根用户. 您可以参阅 Ubuntu 18.04 初始服务器设置指南来配置您的服务器。
一个 MySQL 服务器设置如本如何在 Ubuntu 18.04 上安装 MySQL教程详细说明。确保您为 MySQL 服务器设置了根密码。

步骤 1 – 检查 Query 缓存的可用性

在设置查询缓存之前,您将检查您的MySQL版本是否支持此功能。

1[environment local]
2ssh user_name@your_server_ip

然后,运行以下命令来登录 MySQL 服务器作为 root 用户:

1sudo mysql -u root -p

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

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

1show variables like 'have_query_cache';

你应该得到一个类似于以下的输出:

1[secondary_label Output]
2+------------------+-------+
3| Variable_name    | Value |
4+------------------+-------+
5| have_query_cache | YES   |
6+------------------+-------+
71 row in set (0.01 sec)

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

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

步骤 2 — 检查默认查询缓存变量

在MySQL中,一系列变量控制查询缓存. 在此步骤中,您将检查与MySQL一起发送的默认值,并了解每个变量控制的内容。

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

1show variables like 'query_cache_%' ;

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

 1[secondary_label Output]
 2+------------------------------+----------+
 3| Variable_name                | Value    |
 4+------------------------------+----------+
 5| query_cache_limit            | 1048576  |
 6| query_cache_min_res_unit     | 4096     |
 7| query_cache_size             | 16777216 |
 8| query_cache_type             | OFF      |
 9| query_cache_wlock_invalidate | OFF      |
10+------------------------------+----------+
115 rows in set (0.00 sec)

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

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

「query_cache_size」控制了向查询缓存分配的总内存量。如果该值设置为零,则意味着查询缓存被禁用。在大多数情况下,默认值可能设置为1677216(约16MB)。另外,请记住,query_cache_size至少需要40KB来分配其结构。在这里分配的值与最接近的1024字节块保持一致。

MySQL 通过检查query_cache_type变量来确定要缓存的查询。将此值设置为0或OFF将防止缓存或检索缓存查询。您还可以将其设置为1,以允许缓存所有查询,除了那些以SELECT SQL_NO_CACHE(https://dev.mysql.com/doc/refman/5.7/en/query-cache-in-select.html)声明开始的查询之外。一个值为2告诉 MySQL 只有以SELECT SQL_CACHE命令开始的缓存查询。

变量 query_cache_wlock_invalidate 控制 MySQL 是否应该从缓存中获取结果,如果查询中使用的表被锁定。

<$>[注] **注:**从 MySQL 版本 5.7.20 开始, query_cache_wlock_invalidate 变量被贬值。

在审查控制MySQL查询缓存的系统变量后,您现在将测试MySQL如何运行,而不先启用该功能。

步骤 3 — 测试您的MySQL服务器没有查询缓存

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

在此步骤中,您将创建一个样本数据库,并插入一些数据,以查看MySQL如何运行而无需查询缓存。

当您仍然登录到您的MySQL服务器时,请创建一个数据库,并通过执行以下命令命名它为sample_db:

1Create database sample_db;

1[secondary_label Output]
2Query OK, 1 row affected (0.00 sec)

然后转到数据库:

1Use sample_db;

1[secondary_label Output]
2Database changed

创建一个包含两个字段的表(‘customer_id’和‘customer_name’),并命名它为‘客户’:

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

1[secondary_label Output]
2Query OK, 0 rows affected (0.01 sec)

然后,运行以下命令来插入一些样本数据:

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

1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)
3Query OK, 1 row affected (0.00 sec)
4...

下一步是启动 MySQL 配置文件,这是一个用于监控 MySQL 查询性能的分析服务. 要启用当前会话的配置文件,请运行以下命令,将其设置为 1,该命令是:

1SET profiling = 1;

1[secondary_label Output]
2Query OK, 0 rows affected, 1 warning (0.00 sec)

然后运行以下查询以获取所有客户:

1Select * from customers;

您将收到以下输出:

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

然后,运行显示配置文件命令以获取您刚刚运行的选择查询的性能信息:

1SHOW PROFILES;

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

1[secondary_label Output]
2+----------+------------+-------------------------+
3| Query_ID | Duration   | Query                   |
4+----------+------------+-------------------------+
5|        1 | 0.00044075 | Select * from customers |
6+----------+------------+-------------------------+
71 row in set, 1 warning (0.00 sec)

输出显示了MySQL在从数据库中获取记录时所花费的总时间。当启用查询缓存时,您将在下一个步骤中比较这些数据,所以请注意您的持续时间。

接下来,离开MySQL命令行界面。

1quit;

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

步骤 4 – 设置 Query Cache

在之前的步骤中,您创建了样本数据并在启用查询缓存之前运行了SELECT声明。

使用「nano」來編輯檔案:

1sudo nano /etc/mysql/my.cnf

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

1[label /etc/mysql/my.cnf]
2...
3[mysqld]
4query_cache_type=1
5query_cache_size = 10M
6query_cache_limit=256K

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

保存并关闭文件,按CTRL +X,Y,然后ENTER。

1sudo systemctl restart mysql

您已经启用了 Query Cache。

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

步骤 5 — 测试您的MySQL服务器与查询缓存启用

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

首先,连接到您的MySQL服务器作为 root用户:

1sudo mysql -u root -p

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

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

1show variables like 'query_cache_%' ;

您将看到以下输出:

 1[secondary_label Output]
 2+------------------------------+----------+
 3| Variable_name                | Value    |
 4+------------------------------+----------+
 5| query_cache_limit            | 262144   |
 6| query_cache_min_res_unit     | 4096     |
 7| query_cache_size             | 10485760 |
 8| query_cache_type             | ON       |
 9| query_cache_wlock_invalidate | OFF      |
10+------------------------------+----------+
115 rows in set (0.01 sec)

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

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

1Use sample_db;

启动 MySQL 配置文件:

1SET profiling = 1;

然后,运行查询以至少两次查找所有客户,以生成足够的配置信息。

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

1Select * from customers;
2Select * from customers;

然后列出配置文件信息:

1SHOW PROFILES;

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

1[secondary_label Output]
2+----------+------------+-------------------------+
3| Query_ID | Duration   | Query                   |
4+----------+------------+-------------------------+
5|        1 | 0.00049250 | Select * from customers |
6|        2 | 0.00026000 | Select * from customers |
7+----------+------------+-------------------------+
82 rows in set, 1 warning (0.00 sec)

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

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

1SHOW PROFILE FOR QUERY 1;

 1[secondary_label Output]
 2+--------------------------------+----------+
 3| Status                         | Duration |
 4+--------------------------------+----------+
 5| starting                       | 0.000025 |
 6| Waiting for query cache lock   | 0.000004 |
 7| starting                       | 0.000003 |
 8| checking query cache for query | 0.000045 |
 9| checking permissions           | 0.000008 |
10| Opening tables                 | 0.000014 |
11| init                           | 0.000018 |
12| System lock                    | 0.000008 |
13| Waiting for query cache lock   | 0.000002 |
14| System lock                    | 0.000018 |
15| optimizing                     | 0.000003 |
16| statistics                     | 0.000013 |
17| preparing                      | 0.000010 |
18| executing                      | 0.000003 |
19| Sending data                   | 0.000048 |
20| end                            | 0.000004 |
21| query end                      | 0.000006 |
22| closing tables                 | 0.000006 |
23| freeing items                  | 0.000006 |
24| Waiting for query cache lock   | 0.000003 |
25| freeing items                  | 0.000213 |
26| Waiting for query cache lock   | 0.000019 |
27| freeing items                  | 0.000002 |
28| storing result in query cache  | 0.000003 |
29| cleaning up                    | 0.000012 |
30+--------------------------------+----------+
3125 rows in set, 1 warning (0.00 sec)

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

1SHOW PROFILE FOR QUERY 2;

 1[secondary_label Output]
 2+--------------------------------+----------+
 3| Status                         | Duration |
 4+--------------------------------+----------+
 5| starting                       | 0.000024 |
 6| Waiting for query cache lock   | 0.000003 |
 7| starting                       | 0.000002 |
 8| checking query cache for query | 0.000006 |
 9| checking privileges on cached  | 0.000003 |
10| checking permissions           | 0.000027 |
11| sending cached result to clien | 0.000187 |
12| cleaning up                    | 0.000008 |
13+--------------------------------+----------+
148 rows in set, 1 warning (0.00 sec)

来自配置文件的输出显示,MySQL在第二个查询中花费了更少的时间,因为它能够从查询缓存中获取数据,而不是从磁盘中读取数据。您可以比较每个查询的两个输出集。

有了在您的服务器上启用的MySQL查询缓存功能,您现在将体验到改进的读取速度。

结论

你已经设置了查询缓存来加速你的MySQL服务器在Ubuntu 18.04. 使用功能,如MySQL的查询缓存可以提高你的网站或Web应用程序的速度。缓存减少了不必要的执行SQL陈述,这是一个高度推荐和流行的方法来优化你的数据库。有关加速你的MySQL服务器的更多信息,请尝试如何设置远程数据库以优化网站性能与MySQL在Ubuntu 18.04的教程。