MySQL 慢查询日志：配置、分析与性能优化实战

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
log_output = FILE

日志格式分析

典型日志条目

# Time: 2024-01-01T10:00:00.123456Z
# User@Host: root[root] @ localhost [] Id: 5
# Query_time: 5.123456 Lock_time: 0.001000 Rows_sent: 10 Rows_examined: 1000000
SET timestamp=1672560000;
SELECT * FROM users WHERE last_name LIKE '%smith%' ORDER BY create_time DESC;

关键字段解释

• Query_time：查询执行的总耗时。
• Lock_time：等待锁的时间。
• Rows_sent：返回给客户端的行数。
• Rows_examined：实际扫描的行数（通常越大越差）。
• Rows_affected：受影响的行数（针对 UPDATE/DELETE/INSERT）。

常用分析工具

mysqldumpslow（MySQL 自带）

适合快速汇总统计：

# 按查询时间排序
mysqldumpslow -s t /var/log/mysql/slow.log

# 按锁时间排序
mysqldumpslow -s l /var/log/mysql/slow.log

# 按执行次数排序
mysqldumpslow -s c /var/log/mysql/slow.log

# 显示前 10 条最慢的查询
mysqldumpslow -t 10 /var/log/mysql/slow.log

# 分析特定用户的慢查询
mysqldumpslow -a -g "root" /var/log/mysql/slow.log

pt-query-digest（Percona Toolkit）

功能更强大，适合深度分析：

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log

# 分析最近 12 小时的慢查询
pt-query-digest --since=12h /var/log/mysql/slow.log

# 输出到文件
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

mysqlslow（第三方工具）

部分场景下可用作补充：

mysqlslow /var/log/mysql/slow.log

表模式存储

除了写入文件，MySQL 5.7+ 支持将慢查询日志直接存入 mysql.slow_log 表，方便通过 SQL 查询：

SET GLOBAL log_output = 'TABLE'; 
SET GLOBAL slow_query_log = 'ON'; 

-- 查询慢查询日志
SELECT * FROM mysql.slow_log;

表结构包含 start_time、query_time、lock_time、rows_sent、rows_examined、sql_text 等字段，便于集成监控脚本。

最佳实践与优化建议

阈值设置建议

阈值设置需根据业务负载动态调整：

-- 生产环境建议
SET GLOBAL long_query_time = 2; -- 2 秒阈值

-- 开发/测试环境可以更严格
SET GLOBAL long_query_time = 0.5; -- 500 毫秒

-- 微秒级精度（MySQL 5.7+）
SET GLOBAL long_query_time = 0.1; -- 100 毫秒

日志轮转配置

防止日志文件过大占用磁盘空间，建议使用 logrotate：

/var/log/mysql/slow.log {
    daily
    rotate 30
    missingok
    compress
    delaycompress
    notifempty
    create 640 mysql mysql
    postrotate
        mysqladmin flush-logs
    endscript
}

定期分析计划

建立自动化脚本每日分析并归档：

#!/bin/bash
DATE=$(date +%Y%m%d)
pt-query-digest /var/log/mysql/slow.log > /var/log/mysql/slow_report_${DATE}.txt

# 清空日志文件（先备份）
cp /var/log/mysql/slow.log /var/log/mysql/slow.log.${DATE}
echo "" > /var/log/mysql/slow.log

性能监控和告警

监控慢查询数量

通过状态变量实时监控：

-- 监控每分钟的慢查询数量
SHOW GLOBAL STATUS LIKE 'Slow_queries'; 

-- 查看当前正在运行的查询
SHOW PROCESSLIST;

慢查询告警脚本

当慢查询数量异常时触发通知：

#!/bin/bash
SLOW_COUNT=$(mysql -e "SHOW GLOBAL STATUS LIKE 'Slow_queries'" | grep Slow_queries | awk '{print $2}')
THRESHOLD=100

if [ $SLOW_COUNT -gt $THRESHOLD ]; then
    echo "警告：慢查询数量异常！当前数量：$SLOW_COUNT" | mail -s "MySQL 慢查询告警" [email protected]
fi

注意事项

1. 性能影响：开启慢查询日志会有约 1-3% 的性能开销，生产环境需谨慎评估。
2. 磁盘空间：日志增长较快，务必配置轮转策略，避免磁盘爆满。
3. 敏感信息：日志可能包含明文密码或用户隐私，需妥善保管权限。
4. 生产环境：建议设置合理的阈值，避免记录过多无关查询导致日志泛滥。
5. 版本差异：MySQL 5.7+ 支持微秒级精度，旧版本仅精确到秒。

排查思路与优化经验

在实际工作中，找到慢 SQL 只是第一步，关键在于如何优化。以下是常见的排查流程：

1. 获取执行计划：拿到慢 SQL 后，先用 EXPLAIN 命令查看执行计划。重点关注 key 字段（是否用到索引）、rows 字段（扫描行数）以及 type 字段（如果是 ALL 代表全表扫描，需警惕）。
2. 针对性优化：
   • 加索引：这是最直接的手段，检查 WHERE、ORDER BY、JOIN 涉及的字段是否缺乏索引。
   • 改写 SQL：避免 SELECT *，检查是否有复杂的子查询可改为 JOIN，优化大偏移量的分页查询。
   • 参数调优：确认缓冲池大小、连接数等参数是否合理。
3. 长期维护：慢查询日志不仅是排障工具，也是容量规划的依据。建议在测试环境提前开启以发现潜在问题，在生产环境长期开启但配合合理的阈值和归档策略。

注意：开启日志期间请密切观察系统负载，确保不影响正常业务运行。