利用MySQL慢查询日志解决查询性能问题
注意：这篇文章发布已超过两年，因此其中包含的信息可能已过时。如果您发现了问题，请留下评论，我公司将尽力更正。
2024年1月21日 - 阅读时长26分钟
MySQL的慢查询日志是MySQL管理设置中的关键一部分。常规日志能帮助您追踪数据库系统的问题，而慢查询日志则能让您在数据库设置出现问题之前，就把问题发现并解决掉。
正确设置慢查询日志，能让您在数据库慢查询问题变得更严重之前，成功找出并解决它们。大部分慢查询在数据行较少时能正常工作，但随着数据量增多，查询数据所需的时间也会增加。设置慢查询日志就能显示出这些查询，方便您采取相应措施。
在进行Drupal开发，尤其是Drupal模块开发以及Drupal升级到Drupal11的过程中，数据库性能的优化十分关键。在本文中，我们将了解如何设置慢查询日志、如何分析其结果，以及如何使用真实数据触发慢查询日志并进行实践。所有这些信息同样适用于MariaDB。
一、慢查询日志选项
与慢查询日志相关的有几个选项。
- slow_query_log - 一个布尔标志，用于开启或关闭慢查询日志。
- slow_query_log_file - 慢查询日志的存储位置。
- long_query_time - 触发慢查询日志所需的秒数。
- min_examined_row_limit - 触发慢查询日志所需检查的最小行数。
- log_slow_admin_statements - 一个布尔标志，可用于防止管理查询被记录到慢查询日志中。
- log_queries_not_using_indexes - 一个布尔标志，如果查询未使用索引，则可防止触发慢查询日志。
这些选项可以在MySQL或MariaDB的配置文件中进行设置。以下是一个典型的配置设置，如果查询执行时间超过2秒，将触发慢查询日志，并将数据记录到文件“/var/log/mysql - slow.log”中。
; 激活慢查询日志。 slow_query_log = ON ; 设置慢查询日志的位置。 slow_query_log_file = /var/log/mysqld-slow.log ; 设置触发慢查询日志的超时时间（以秒为单位）。 long_query_time = 2 ; 触发慢查询日志所需检查的最小行数。 min_examined_row_limit = 2 ; 标记是否记录可能污染慢查询日志的管理语句。 log_slow_admin_statements = OFF ; 标记是否记录未使用索引的查询 log_queries_not_using_indexes = ON
最好在配置文件中设置这些参数，然后根据需要在数据库中使用SET命令进行调整。使用GLOBAL关键字将为服务器上的所有新会话设置这些配置项。
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL slow_query_log_file = '/var/log/mysqld-slow.log'; SET GLOBAL long_query_time = 2;
如果您想查看系统中这些参数的当前值，可以使用SHOW命令，并传入您要查找的值。
SHOW GLOBAL VARIABLES LIKE 'slow_query%';
这将输出以下内容。
+---------------------+--------------------------+ | Variable_name | Value | +---------------------+--------------------------+ | slow_query_log | ON | | slow_query_log_file | /var/log/mysqld-slow.log | +---------------------+--------------------------+
设置好这些后，让我们来看看慢查询日志本身。
二、分析慢查询日志
当慢查询日志记录数据后，您可以直接打印出来查看其中的内容。
虽然这样能看到触发日志的查询，但这不是最好的办法。检查慢查询日志更好的方法是使用mysqldumpslow工具。
要使用mysqldumpslow工具，只需传入慢查询日志文件的路径。
mysqldumpslow /var/log/mysqld-slow.log
这将读取慢查询日志，并显示触发日志记录的查询的概要信息。
例如，以下是慢查询日志中的一条记录，该查询使服务器运行了7.9秒，并检查了10000行数据。
Reading mysql slow query log from /var/log/mysql/mysql-slow.log Count: 1 Time=7.90s (7s) Lock=0.00s (0s) Rows_sent=6042.0 (6042), Rows_examined=10000.0 (10000), Rows_affected=0.0 (0), root[root]@localhost SELECT * FROM users AS u LEFT JOIN club_members AS cm ON u.id = cm.user_id LEFT JOIN clubs AS c ON cm.club_id = c.id WHERE cm.user_id IS NULL
要使此查询被记录在此日志中，long_query_time选项必须设置为小于7秒，min_examined_row_limit选项必须设置为小于10000行。这里的“Count”是该查询的执行次数。日志还会显示生成该查询的用户，但不会显示查询所执行的数据库。
您可以使用 -t 标志让该工具生成一个性能最差的查询的“top”列表。这将根据平均查询时间显示前5个最慢的查询。
mysqldumpslow -t 5 /var/log/mysqld-slow.log
您还可以使用 -s 标志按不同的值对最慢的查询进行排序，这里我们告诉工具按原始查询时间进行排序。
mysqldumpslow -t 5 -s t /var/log/mysqld-slow.log
可以与 -s 标志一起使用的值有：
- t - 查询时间。
- l - 锁定时间（小写字母l）。
- r - 返回的行数。
- at - 平均查询时间（这是默认值）。
- al - 平均锁定时间。
- ar - 平均返回的行数。
使用这个工具可以让我们快速检查查询日志，找出问题最严重的查询。
三、触发慢查询日志
现在我们已经设置好了慢查询日志，我想找到一种可靠的方法来触发它。我曾看到有人询问如何触发慢查询日志，但没有看到合适的答案。实际上，常用的方法是使用sleep函数让数据库服务器休眠几秒，如下所示。
SELECT SLEEP(10);
虽然这个查询确实可以触发慢查询日志，但它不能让我们看到有关查询的任何有用信息，也无法看到解决问题如何能显著加快查询速度。
为此，我公司创建了一个慢查询练习项目，该项目会在数据库（涉及三个表）中生成25000条没有索引的数据记录。运行这个练习只需要一个MySQL/MariaDB服务器和PHP（用于生成数据）。使用了Faker项目来生成所有数据，这样数据看起来不会太随机，也更便于使用。
数据库本身是一个规范化的俱乐部管理系统，包含三个表。
- users - 用户列表。
- clubs - 俱乐部列表。
- club_members - 存储用户和俱乐部之间的关系。
要为表创建数据，您需要在项目中运行“composer install”来安装Faker包，然后运行“php faker.php”来生成包含数据的所需CSV文件。
这将生成10000条用户和俱乐部记录，以及用户数量一半的俱乐部成员记录，这样一些用户将不属于任何俱乐部。这样设置可以让我们执行一些不同的查询，以查找属于俱乐部和不属于俱乐部的用户。
生成CSV文件后，我们可以创建数据库并将数据导入到创建的表中。有两个辅助脚本来协助导入过程。
- slow_setup.sql - 将创建一个没有任何索引的数据库和表，然后将数据导入其中。
- optimized_setup.sql - 将创建一个带有必要索引的数据库和表，然后将数据导入其中。
思路是先运行slow_setup.sql脚本执行一些慢查询，以触发慢查询日志。之后可以运行optimized_setup.sql脚本来导入带有正确索引的表，并查看这如何提高查询速度。
要运行这些脚本，只需使用必要的认证信息运行“mysql”命令，并传入相应的脚本文件。用户必须有权限创建数据库和表才能执行所需的步骤。
要导入慢查询设置，请运行以下命令。
mysql -u root -p < slow_setup.sql
这将创建的表结构如下。
CREATE TABLE `users` (
id INT,
forename VARCHAR(255) NOT NULL,
surname VARCHAR(255) NOT NULL
);
CREATE TABLE `clubs` (
id INT,
name VARCHAR(255) NOT NULL
);
CREATE TABLE `club_members` (
user_id INT,
club_id INT
);
如果您对MySQL数据库比较了解，就会发现这里存在的问题，但慢查询设置的目的是让慢查询日志能够被触发。
然后使用以下SQL语句将数据导入到表中。
LOAD DATA LOCAL INFILE 'users.csv' INTO TABLE `users` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'; LOAD DATA LOCAL INFILE 'clubs.csv' INTO TABLE `clubs` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'; LOAD DATA LOCAL INFILE 'club_members.csv' INTO TABLE `club_members` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
数据库设置好后，我们可以看看执行查询的情况。以下查询将生成所有俱乐部成员用户的列表。
SELECT * FROM users AS u INNER JOIN club_members AS cm ON u.id = cm.user_id INNER JOIN clubs AS c ON cm.club_id = c.id;
这个查询虽然简单，但运行时间超过5秒。我公司使用100000条记录进行了测试，在这种数据量下，这个查询至少需要10分钟才能完成。
如果这是一个实际的例子，数据库最初可能运行良好，但随着数据的增加，速度会越来越慢。在遇到性能问题之前设置慢查询日志很有用，因为当数据量成为问题时，它会发出警告。
当我们执行这样一个慢查询时，应该能够在慢查询日志中看到它，确实可以看到，以下是慢查询日志中的相关部分。
> mysqldumpslow /var/log/mysqld-slow.log Count: 2 Time=5.22s (10s) Lock=0.00s (0s) Rows_sent=5001.0 (10002), Rows_examined=5001.0 (10002), Rows_affected=0.0 (0), root[root]@localhost SELECT * FROM users AS u INNER JOIN club_members AS cm ON u.id = cm.user_id INNER JOIN clubs AS c ON cm.club_id = c.id
要了解查询运行缓慢的原因，我们需要使用EXPLAIN命令。将该命令添加到查询的开头，它将显示查询执行时的情况，并给出以下输出。
> EXPLAIN SELECT * FROM users AS u INNER JOIN club_members AS cm ON u.id = cm.user_id INNER JOIN clubs AS c ON cm.club_id = c.id; +------+-------------+-------+------+---------------+------+---------+------+-------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+--------------------------------------------------------+ | 1 | SIMPLE | cm | ALL | NULL | NULL | NULL | NULL | 5001 | | | 1 | SIMPLE | c | ALL | NULL | NULL | NULL | NULL | 10029 | Using where; Using join buffer (flat, BNL join) | | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 10072 | Using where; Using join buffer (incremental, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+-------+--------------------------------------------------------+ 3 rows in set (0.011 sec)
了解EXPLAIN命令显示的所有内容是很有价值的，但这里很明显，由于我们没有任何索引，数据库在执行这个查询时需要加载所有表中的所有行。数据库服务器必须经过多个步骤才能实时分析这些数据，这在生成结果时会导致严重的性能下降。
为了解决这个问题，我们需要创建带有索引的表。我们通过将每个ID设为主键（主键本身就是一种索引），并为名字和姓氏字段添加全文索引来实现。以下是通过optimized_setup.sql脚本导入的表结构。
CREATE TABLE `users` (
id INT PRIMARY KEY,
forename VARCHAR(255) NOT NULL,
surname VARCHAR(255) NOT NULL,
FULLTEXT (forename, surname)
) ENGINE=InnoDB;
CREATE TABLE `clubs` (
id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
FULLTEXT (name)
) ENGINE=InnoDB;
CREATE TABLE `club_members` (
user_id INT,
club_id INT,
INDEX (user_id, club_id)
) ENGINE=InnoDB;
现在，当我们运行获取用户和俱乐部列表的查询时，时间不到一秒。将该查询通过EXPLAIN命令运行，会显示在生成结果时对表中的索引和数据的使用更为高效。
> EXPLAIN SELECT * FROM users AS u INNER JOIN club_members AS cm ON u.id = cm.user_id INNER JOIN clubs AS c ON cm.club_id = c.id; +------+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------+ | 1 | SIMPLE | cm | index | user_id | user_id | 10 | NULL | 5001 | Using where; Using index | | 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | clubs.cm.club_id | 1 | | | 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | clubs.cm.user_id | 1 | | +------+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------+ 3 rows in set (0.001 sec)
创建优化后的表并导入数据后，数据库现在可以高效使用了。即使有100000条记录，结果也能在一秒内返回。这清楚地显示了索引的重要性，并演示了如何使用慢查询日志和EXPLAIN命令来发现数据库中的性能问题。
四、总结
慢查询日志是发现数据库性能问题的强大工具，但在遇到任何问题之前正确设置它非常重要。否则，您的整个应用程序可能会变得极其缓慢，而您却毫无察觉。慢查询日志只会显示问题查询所在，您还需要进行一些调查工作来找出查询缓慢的原因以及如何解决这些问题。
同时要注意误报情况。有些查询可能因为执行复杂操作而变慢，这些查询应该在分析中被排除。
我希望通过运行这个慢查询练习项目，您能对慢查询日志有更深入的了解，并在未来更好地利用它。在Drupal开发、Drupal模块开发以及Drupal升级到Drupal11的过程中，合理利用慢查询日志优化数据库性能至关重要。
慢查询日志的配置可以比这里描述的更复杂。MySQL和MariaDB都有不同的属性可以设置，以改变慢查询日志的使用方式。
