迹忆客 专注技术分享

异常缓慢的查询是最常见的 MySQL 性能问题之一。 在生产工作负载压力下，在开发中执行可接受的查询可能会动摇。

每次命中端点时，大型应用程序可能会运行数百个唯一的数据库查询。 这使得很难挑出导致服务器响应延迟的查询。 MySQL 慢查询日志是一个调试选项，可以帮助我们识别可疑的 SQL 语句，为我们的调查提供一个出发点。

启用慢查询日志

日志是一种用于记录长时间运行的 SQL 查询的内置机制。 未在配置时间内完成的查询将写入日志。 阅读日志的内容会显示执行的 SQL 和所用时间。

默认情况下，慢速查询日志记录是关闭的。 我们可以通过从管理 MySQL shell 运行以下命令在我们的服务器上激活它：

SET GLOBAL slow_query_log_file='/var/log/mysql/mysql-slow.log';
SET GLOBAL slow_query_log=1;

更改立即生效。 慢速查询现在将记录到 /var/log/mysql/mysql-slow.log。 我们可以定期查看此文件以确定性能不佳的查询。

如果需要超过 10 秒才能完成，MySQL 会将查询计为“slow”。 对于需要近乎即时响应的面向用户的 Web 应用程序，此限制通常过于宽松。 我们可以通过设置 long_query_time 变量来更改限制：

SET GLOBAL long_query_time=1;

该值设置慢速查询的最短持续时间。 找到适合我们自己应用的天平很重要。 过高的阈值将排除实际影响性能的查询。 相反，非常低的值会导致捕获过多的查询，从而产生过于嘈杂的日志。

使用 MySQL 的配置文件

如果你打算长期使用它，你应该在你的 MySQL 配置文件中启用慢查询日志。 这将确保日志记录在 MySQL 服务器重新启动后自动恢复。

配置文件的位置可能因平台分布而异。 它通常位于 /etc/mysql/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf。 添加以下行以复制上面动态启用的设置：

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1

重新启动 MySQL 使我们的更改生效：

$ sudo service mysql restart

每次 MySQL 服务器启动时，慢速查询日志都将处于活动状态。

自定义日志内容

日志通常只包括排除“slow”阈值并由客户端应用程序提交的 SQL 查询。 这不包括可能发生的任何缓慢的管理操作，例如索引创建和表优化，以及将来可能变慢的查询。

我们可以通过对配置文件进行以下更改来扩展日志以包含此信息：

• log_slow_admin_statements = 1 – 包括管理 SQL 语句，例如 ALTER TABLE、CREATE INDEX、DROP INDEX 和 OPTIMIZE TABLE。 这很少是可取的，因为这些操作通常在维护和迁移脚本期间运行。 尽管如此，如果我们的应用程序也动态执行这些任务，此设置还是很有用的。
• log_slow_replica_statements = 1 – 此设置为副本服务器上的复制查询启用慢速查询日志记录。 默认情况下禁用。 对于 MySQL 8.0.26 及更早版本，请改用 log_slow_slave_statements。
• log_queries_not_using_indexes = 1 – 启用此设置时，将记录预期从目标表或视图中检索所有记录的查询，即使它们没有排除慢查询阈值。 这有助于确定查询何时缺少索引或无法使用它。 如果缺少限制获取行数的约束，则仍会记录具有可用索引的查询。

不使用索引的日志记录查询会显着增加冗长程度。 在某些情况下，可能需要或需要进行完整的索引扫描。 这些查询将继续显示在日志中，即使它们无法解决。

我们可以通过设置 log_throttle_queries_not_using_indexes 变量来限制没有索引的查询。 这定义了将在 60 秒内写入的最大日志数。 值 10 表示每分钟最多记录 10 个查询。 在第十个事件之后，在下一个 60 秒窗口打开之前不会记录更多查询。

解释慢查询日志

进入慢速查询日志的每个查询都将显示一组类似于以下内容的行：

# Time: 2022-07-12T19:00:00.000000Z
# User@Host: demo[demo] @ mysql [] Id: 51
# Query_time: 3.514223  Lock_time: 0.000010  Rows_sent: 5143  Rows_examined: 322216
SELECT * FROM slow_table LEFT JOIN another_table ...

查询上方的注释行包含执行时间、客户端连接的 MySQL 用户以及提供持续时间和发送行数的统计信息。 上面的示例用了 3.5 秒完成并查看了超过 320,000 行，然后仅向客户端发送了 5,143 行。 这可能表明缺少索引导致 MySQL 检查过多的记录。

我们可以选择通过在配置文件中设置 log_slow_extra = 1 系统变量来在日志中包含更多信息。 这将添加线程 ID、接收和发送的字节数、考虑排序的行数，以及提供 MySQL 如何处理查询的可见性的特定于语句的请求计数。

必须小心处理日志文件，因为它的内容很敏感。 查询完整显示，没有任何参数值的屏蔽。 这意味着如果我们在生产服务器上使用慢查询日志，用户数据将会存在。 访问权限应仅限于调整 SQL 语句的开发人员和数据库管理员。

慢查询日志记录和备份

当我们还使用 MySQLDump 创建数据库备份时，会出现对慢速查询日志的一种常见挫败感。 长时间运行的 SELECT * FROM ... 查询将被执行以从我们的表中获取数据并将它们提供给我们的备份。 它们将像任何其他 SQL 语句一样包含在慢速查询日志中。 如果我们定期进行备份，这可能会污染日志。

我们可以通过在运行 mysqldump 之前暂时禁用慢查询日志来解决此问题。 我们可以在备份完成后重新激活日志。 调整我们的备份脚本，使其看起来类似于以下内容：

#!/bin/bash

# Temporarily disable slow query logging
mysql -uUser -pPassword -e "SET GLOBAL slow_query_log=0";

# Run mysqldump
mysqldump -uUser -pPassword --single-transaction databaseName | gzip > backup.bak

# Enable the slow query log again
mysql -uUser -pPassword -e "SET GLOBAL slow_query_log=1"

这将使 MySQLDump 活动远离慢速查询日志，从而更容易专注于应用程序执行的 SQL。

总结

MySQL 慢查询日志是查明性能问题原因的最有效方法之一。 首先估计我们遇到的延迟并将此值用作 long_query_time。 如果重现问题后日志中没有任何内容，请减小该值。

慢速查询日志不会告诉我们确切的解决方法。 但是，查看服务器接收到的确切 SQL 的能力使我们可以重复执行性能不佳的语句，然后衡量优化的效果。 添加索引或缺少约束可能是涉及数千行的查询与处理少数行的查询之间的区别。