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

介绍

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

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

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

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

先决条件

在开始之前，您需要以下内容：一个配置了防火墙和非root用户的Ubuntu 18.04服务器。

确保为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 。

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

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

第3步 - 在没有查询缓存的情况下测试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分析器 ，它是一种用于监视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 。 您可以忽略输出中的警告，因为这只是表示将在未来的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 + 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语句的不必要执行，是一种强烈推荐和流行的优化数据库的方法。