一文掌握MySQL慢查询日志：配置、分析与实战技巧

一文掌握MySQL慢查询日志：配置、分析与实战技巧

在高性能数据库管理中，MySQL慢查询日志是识别和优化性能瓶颈的关键工具。通过记录执行时间过长的SQL语句，慢查询日志帮助数据库管理员发现低效查询，进而提升系统整体性能。本文将深入探讨MySQL慢查询日志的配置、分析方法及实战应用，助力读者掌握这一性能优化利器。

慢查询日志记录了所有执行时间超过预设阈值的SQL语句，包括查询时间、锁定时间、返回行数等关键信息。通过分析这些数据，DBA可以识别出性能瓶颈，进而采取针对性的优化措施。慢查询日志不仅适用于生产环境的性能调优，也是开发阶段不可或缺的调试工具。

要启用慢查询日志，首先需要在MySQL配置文件（通常是my.cnf或my.ini）中进行相应设置。以下是最基本的配置参数：

• slow_query_log：设置为1或ON以启用慢查询日志功能。
• slow_query_log_file：指定慢查询日志文件的存储路径。
• long_query_time：设定查询时间的阈值，超过此时间的查询将被记录。
• log_queries_not_using_indexes：如果设置为1或ON，未使用索引的查询也会被记录。
• min_examined_row_limit：设定查询必须检查的最小行数才能被记录到慢查询日志中。

例如，以下是一个典型的配置示例：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
min_examined_row_limit = 0

除了通过配置文件静态设置外，还可以使用SQL语句动态调整慢查询日志的配置，无需重启服务：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL min_examined_row_limit = 0;

要查看当前的慢查询日志配置，可以使用以下SQL语句：

SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
SHOW GLOBAL VARIABLES LIKE 'slow_query_log_file';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';
SHOW GLOBAL VARIABLES LIKE 'log_queries_not_using_indexes';
SHOW GLOBAL VARIABLES LIKE 'min_examined_row_limit';

基本查看方法

慢查询日志文件通常是一个纯文本文件，可以直接使用文本编辑器（如less、cat等）打开查看。每条记录包含了查询时间、锁定时间、返回行数等关键信息，以及具体的SQL语句。

使用mysqldumpslow工具

MySQL自带的mysqldumpslow工具是分析慢查询日志的利器。它可以解析日志文件并汇总信息，支持多种排序和过滤选项。常用的命令格式如下：

mysqldumpslow [options] [log_file ...]

其中，sort_type参数用于指定排序方式，可选值包括：

• t 或 at：按查询时间或平均查询时间排序
• l 或 al：按锁占用时间或平均锁占用时间排序
• r 或 ar：按发送的行数或平均发送的行数排序
• c：按计数排序

例如，要获取执行时间最长的前10条慢查询，可以使用以下命令：

mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

要获取使用最多的10条慢查询，可以使用：

mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log

此外，还可以使用正则表达式过滤特定的SQL语句：

mysqldumpslow -s t -t 10 -g "left join" /var/log/mysql/mysql-slow.log

第三方工具

除了mysqldumpslow，还有许多优秀的第三方工具可以帮助分析慢查询日志，例如pt-query-digest。这些工具通常提供更强大的分析功能和更友好的输出格式，能够帮助DBA更快速地定位问题。

假设我们正在管理一个在线交易系统，用户反馈在高峰期系统响应变慢。通过启用慢查询日志并设置合理的阈值，我们收集到了以下慢查询记录：

# Time: 2024-01-25T14:30:00.000000Z
# User@Host: user@localhost []  Query_time: 3.500000  Lock_time: 0.000000 Rows_sent: 1000  Rows_examined: 10000
SET timestamp=1674649800;
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2024-01-25 14:00:00';

通过分析这条记录，我们可以发现以下问题：

1. 查询时间长达3.5秒，明显过长
2. 返回了1000行数据，说明数据量较大
3. 检查了10000行数据，可能存在全表扫描

进一步使用EXPLAIN工具分析SQL执行计划：

EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND created_at > '2024-01-25 14:00:00';

结果显示该查询没有使用任何索引，导致全表扫描。针对这一问题，我们可以采取以下优化措施：

1. 为status和created_at字段创建复合索引
2. 优化SQL语句，避免选择过多的列
3. 调整查询时间窗口，减少数据量

经过优化后，再次观察慢查询日志，发现该查询的执行时间显著降低，系统性能得到明显提升。

尽管慢查询日志功能强大，但在实际使用中可能会遇到一些问题。以下是一些常见的失效场景及解决方案：

1. 动态设置long_query_time后未生效：检查是否在新的会话中查询，旧会话可能仍使用旧值。
2. 未使用索引的SQL未记录：默认情况下，未使用索引的SQL不会被记录。如果需要记录，需设置log_queries_not_using_indexes=ON。但要注意，这可能会导致日志文件快速增长。
3. 慢SQL中有锁等待：慢查询日志默认不记录锁等待时间，需要通过其他工具（如performance_schema）进行监控。
4. 管理类命令未记录：默认情况下，ALTER TABLE等管理类命令不会被记录。如果需要记录，需设置log_slow_admin_statements=ON。
5. min_examined_row_limit设置不当：如果该值设置过高，一些慢查询可能不会被记录。建议保持默认值0。
6. 慢日志文件句柄变化：如果在运行期间使用编辑器打开并保存日志文件，可能导致句柄变化。需要执行FLUSH SLOW LOGS命令。
7. 从库复制语句未记录：默认情况下，从库上的复制语句不会被记录。如果需要记录，需设置log_slow_slave_statements=ON，但要注意binlog格式的影响。

通过合理配置和有效利用慢查询日志，DBA可以及时发现并解决数据库性能问题，确保系统稳定运行。掌握慢查询日志的使用方法，是每个数据库管理者不可或缺的技能之一。