如何在Ubuntu18.04上使用查询缓存优化MySQL
作为 Write for DOnations 计划的一部分，作者选择了 Apache Software Foundation 来接受捐赠。
介绍
查询缓存 是一个突出的 MySQL 功能，可加快从数据库中检索数据的速度。 它通过将 MySQL SELECT 语句与检索到的记录集一起存储在内存中来实现这一点，然后如果客户端请求相同的查询，它可以更快地提供数据，而无需再次从数据库执行命令。
与从磁盘读取的数据相比，从 RAM（随机存取存储器）中缓存的数据具有更短的访问时间，从而减少了延迟并改善了输入/输出 (I/O) 操作。 例如，对于 WordPress 站点或具有高读取调用和不频繁数据更改的电子商务门户，查询缓存可以显着提高数据库服务器的性能并使其更具可扩展性。
在本教程中，您将首先配置没有查询缓存的 MySQL 并运行查询以查看它们的执行速度。 然后，您将设置查询缓存并在启用它的情况下测试您的 MySQL 服务器以显示性能差异。
注意： 虽然查询缓存从 MySQL 5.7.20 开始被弃用，并在 MySQL 8.0 中被删除，但如果您使用受支持的 MySQL 版本，它仍然是一个强大的工具。 但是，如果您使用较新版本的 MySQL，您可以采用替代的第三方工具，例如 ProxySQL 来优化 MySQL 数据库的性能。
先决条件
在开始之前，您将需要以下内容：
- 一台配置有防火墙和非 root 用户的 Ubuntu 18.04 服务器。 您可以参考 Initial Server Setup with Ubuntu 18.04 指南来配置您的服务器。
- 如本 How To Install MySQL on Ubuntu 18.04 教程中详细说明的 MySQL 服务器设置。 确保为 MySQL 服务器设置了 root 密码。
第 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';
您应该得到类似于以下内容的输出：
Output+------------------+-------+ | 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_%' ;
您将看到输出中列出的变量：
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)
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 变量来确定要缓存的查询。 将此值设置为 0 或 OFF 可防止缓存或检索缓存的查询。 您还可以将其设置为 1 以启用对所有查询的缓存，但以 SELECT SQL_NO_CACHE 语句开头的查询除外。 2 的值告诉 MySQL 只缓存以 SELECT SQL_CACHE 命令开头的查询。
变量 query_cache_wlock_invalidate 控制如果查询中使用的表被锁定，MySQL 是否应该从缓存中检索结果。 默认值为 OFF。
注意： query_cache_wlock_invalidate 变量自 MySQL 版本 5.7.20 起已弃用。 因此，根据您使用的 MySQL 版本，您可能不会在输出中看到这一点。
在查看了控制 MySQL 查询缓存的系统变量之后，您现在将测试 MySQL 在不先启用该功能的情况下如何执行。
第三步——在没有查询缓存的情况下测试你的 MySQL 服务器
本教程的目标是使用查询缓存功能优化您的 MySQL 服务器。 要查看速度差异，您将运行查询并查看它们在实现该功能之前和之后的性能。
在这一步中，您将创建一个示例数据库并插入一些数据，以查看 MySQL 在没有查询缓存的情况下如何执行。
在仍然登录到您的 MySQL 服务器时，通过运行以下命令创建一个数据库并将其命名为 sample_db：
Create database sample_db;
OutputQuery OK, 1 row affected (0.00 sec)
然后切换到数据库：
Use sample_db;
OutputDatabase changed
创建一个包含两个字段（customer_id 和 customer_name）的表并将其命名为 customers：
Create table customers (customer_id INT PRIMARY KEY, customer_name VARCHAR(50) NOT NULL) Engine = InnoDB;
OutputQuery 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');
OutputQuery OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec) ...
下一步是启动 MySQL profiler，这是一个用于监控 MySQL 查询性能的分析服务。 要为当前会话打开配置文件，请运行以下命令，将其设置为打开的 1：
SET profiling = 1;
OutputQuery OK, 0 rows affected, 1 warning (0.00 sec)
然后，运行以下查询以检索所有客户：
Select * from customers;
您将收到以下输出：
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)
然后，运行 SHOW PROFILES 命令来检索您刚刚运行的 SELECT 查询的性能信息：
SHOW PROFILES;
您将获得类似于以下内容的输出：
Output+----------+------------+-------------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------------+ | 1 | 0.00044075 | Select * from customers | +----------+------------+-------------------------+ 1 row in set, 1 warning (0.00 sec)
输出显示 MySQL 从数据库中检索记录所花费的总时间。 启用查询缓存后，您将在接下来的步骤中比较此数据，因此请记下您的 Duration。 您可以忽略输出中的警告，因为这只是表明 SHOW PROFILES 命令将在未来的 MySQL 版本中删除并替换为 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 的值设置为 [X173X 来分配 10 兆字节到查询缓存]。
按 CTRL + X、Y，然后按 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_%' ;
您将看到以下输出：
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)
变量query_cache_type设置为ON； 这确认您使用上一步中定义的参数启用了查询缓存。
切换到您之前创建的 sample_db 数据库。
Use sample_db;
启动 MySQL 分析器：
SET profiling = 1;
然后，运行查询以检索所有客户至少两次，以便生成足够的分析信息。
请记住，一旦您运行了第一个查询，MySQL 将创建结果缓存，因此，您必须运行两次查询才能触发缓存：
Select * from customers; Select * from customers;
然后，列出配置文件信息：
SHOW PROFILES;
您将收到类似于以下内容的输出：
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)
如您所见，在此步骤中，运行查询所需的时间已从 0.00044075（步骤 3 中没有查询缓存）大幅减少到 0.00026000（第二个查询）。
您可以通过详细分析第一个查询来查看启用查询缓存功能的优化：
SHOW PROFILE FOR QUERY 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)
运行以下命令以显示缓存的第二个查询的配置文件信息：
SHOW PROFILE FOR QUERY 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)
分析器的输出显示 MySQL 在第二个查询上花费的时间更少，因为它能够从查询缓存中检索数据，而不是从磁盘中读取数据。 您可以比较每个查询的两组输出。 如果查看 QUERY 2 上的配置文件信息，sending cached result to client 的状态显示数据已从缓存中读取，并且由于缺少 Opening tables 状态，因此没有打开任何表。
在您的服务器上启用 MySQL 查询缓存功能后，您现在将体验到更高的读取速度。
结论
您已设置查询缓存以加速 Ubuntu 18.04 上的 MySQL 服务器。 使用 MySQL 的查询缓存等功能可以提高您的网站或 Web 应用程序的速度。 缓存减少了不必要的 SQL 语句执行，是一种高度推荐和流行的优化数据库的方法。 有关加速 MySQL 服务器的更多信息，请尝试 如何设置远程数据库以在 Ubuntu 18.04 上使用 MySQL 优化站点性能教程。
