MySQL 慢查询日志：配置、分析与性能优化实战
什么是慢查询日志
慢查询日志（Slow Query Log）是 MySQL 内置的一种诊断工具，专门用于记录执行时间超过指定阈值的 SQL 语句。它是排查数据库性能瓶颈、优化查询效率的重要手段。
核心作用
- 性能诊断：快速定位执行效率低下的 SQL 语句。
- 瓶颈定位：分析查询变慢的原因，如全表扫描、索引缺失或锁等待。
- 优化依据：为后续的 SQL 重构和索引调整提供真实数据支持。
配置参数详解
在调整之前，先了解关键参数：
-- 查看所有慢查询相关参数
SHOW VARIABLES LIKE '%slow%';
SHOW VARIABLES LIKE '%long_query_time%';
主要配置项说明：
slow_query_log：控制是否开启（ON/OFF）。slow_query_log_file：指定日志文件路径。long_query_time：阈值（秒），默认 10 秒，超过此时间的 SQL 才会被记录。min_examined_row_limit：最少检查行数阈值，低于此值不记录。log_queries_not_using_indexes：是否记录未使用索引的查询（即使很快）。log_output：输出方式，支持 FILE（文件）、TABLE（表）或 NONE。
开启和配置
临时开启（重启失效）
适用于测试环境或临时排查，无需修改配置文件：
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- 设为 2 秒
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL log_queries_not_using_indexes = 'ON';
永久开启（修改配置文件）
生产环境建议修改 my.cnf 或 my.ini 并重启服务：
[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-3% 的性能开销，生产环境需谨慎评估。
- 磁盘空间：日志增长较快，务必配置轮转策略，避免磁盘爆满。
- 敏感信息：日志可能包含明文密码或用户隐私，需妥善保管权限。
- 生产环境：建议设置合理的阈值，避免记录过多无关查询导致日志泛滥。
- 版本差异：MySQL 5.7+ 支持微秒级精度，旧版本仅精确到秒。
排查思路与优化经验
在实际工作中，找到慢 SQL 只是第一步，关键在于如何优化。以下是常见的排查流程：
- 获取执行计划：拿到慢 SQL 后，先用
EXPLAIN命令查看执行计划。重点关注key字段（是否用到索引）、rows字段（扫描行数）以及type字段（如果是ALL代表全表扫描，需警惕）。 - 针对性优化：
- 加索引：这是最直接的手段，检查
WHERE、ORDER BY、JOIN涉及的字段是否缺乏索引。 - 改写 SQL：避免
SELECT *，检查是否有复杂的子查询可改为JOIN，优化大偏移量的分页查询。 - 参数调优：确认缓冲池大小、连接数等参数是否合理。
- 加索引：这是最直接的手段，检查
- 长期维护：慢查询日志不仅是排障工具，也是容量规划的依据。建议在测试环境提前开启以发现潜在问题，在生产环境长期开启但配合合理的阈值和归档策略。
注意：开启日志期间请密切观察系统负载，确保不影响正常业务运行。