一、什么是慢查询日志
1.1 定义
记录执行时间超过阈值的SQL语句。
1.2 作用
- 定位慢SQL
- 分析性能瓶颈
- 优化数据库性能
二、配置慢查询日志
2.1 查看当前配置
-- 是否启用
SHOW VARIABLES LIKE 'slow_query_log';
-- OFF（未启用）
-- 慢查询阈值
SHOW VARIABLES LIKE 'long_query_time';
-- 10（默认10秒）
-- 日志文件路径
SHOW VARIABLES LIKE 'slow_query_log_file';
2.2 启用慢查询日志
-- 方式1：临时启用（重启失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- 2秒
-- 方式2：配置文件永久启用
-- my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1 -- 记录未使用索引的查询
2.3 重启生效
systemctl restart mysqld
三、慢查询日志格式
3.1 日志示例
# Time: 2024-11-21T22:30:00.123456Z
# User@Host: root[root] @ localhost []
# Query_time: 5.123456 Lock_time: 0.000123 Rows_sent: 100 Rows_examined: 1000000
SET timestamp=1700601000;
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at;
3.2 关键字段
- Query_time：查询执行时间（秒）
- Lock_time：锁等待时间
- Rows_sent：返回行数
- Rows_examined：扫描行数
四、分析慢查询日志
4.1 查看慢查询日志
tail -f /var/log/mysql/slow.log
4.2 mysqldumpslow工具
# 查看出现次数最多的10条慢SQL
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
# 查看执行时间最长的10条慢SQL
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# 查看返回记录最多的10条慢SQL
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log
# 查看锁等待时间最长的10条慢SQL
mysqldumpslow -s l -t 10 /var/log/mysql/slow.log
参数说明：
-s：排序方式（c:次数, t:时间, r:返回行, l:锁时间）-t：显示前N条-g：正则匹配
4.3 pt-query-digest工具
# 安装
yum install percona-toolkit
# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log > slow_report.txt
报告内容：
- 查询统计（次数、时间、占比）
- 查询详情（示例SQL、EXPLAIN）
- 优化建议
五、慢查询优化流程
5.1 定位慢SQL
# 1. 查看慢查询日志
mysqldumpslow -s t -t 10 slow.log
# 2. 找到最慢的SQL
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at;
5.2 分析执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at;
检查：
- type是否ALL（全表扫描）
- key是否NULL（无索引）
- Extra是否Using filesort
5.3 优化索引
-- 创建索引
CREATE INDEX idx_user_created ON orders(user_id, created_at);
-- 验证
EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at;
-- type: ref
-- key: idx_user_created
-- Extra: Using index（无filesort）
5.4 再次测试
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at;
-- 执行时间从5秒降到0.01秒
六、常见慢查询场景
6.1 全表扫描
-- 慢SQL
SELECT * FROM users WHERE age = 20;
-- Rows_examined: 1000000
-- 优化
CREATE INDEX idx_age ON users(age);
6.2 大量数据排序
-- 慢SQL
SELECT * FROM orders ORDER BY created_at LIMIT 10;
-- Query_time: 3.5秒，Using filesort
-- 优化
CREATE INDEX idx_created_at ON orders(created_at);
6.3 未优化的JOIN
-- 慢SQL
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- Query_time: 5秒
-- 优化
CREATE INDEX idx_user_id ON orders(user_id);
6.4 大数据量LIMIT深分页
-- 慢SQL
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- Query_time: 10秒
-- 优化：延迟关联
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) AS t
ON o.id = t.id;
七、慢查询监控
7.1 查看慢查询数量
SHOW GLOBAL STATUS LIKE 'Slow_queries';
-- Slow_queries: 156（累计慢查询数）
7.2 定期分析
# 每天分析慢查询日志
0 1 * * * /usr/bin/pt-query-digest /var/log/mysql/slow.log > /tmp/slow_$(date +\%Y\%m\%d).txt
7.3 监控告警
# 慢查询数量超过阈值告警
if [ $(mysql -e "SHOW GLOBAL STATUS LIKE 'Slow_queries'\G" | grep Value | awk '{print $2}') -gt 1000 ]; then
echo "慢查询过多，需要优化" | mail -s "MySQL慢查询告警" admin@example.com
fi
八、最佳实践
8.1 阈值设置
-- 开发环境：1秒
long_query_time = 1
-- 生产环境：根据业务
-- 交互式查询：0.5-1秒
-- 报表查询：5-10秒
8.2 日志轮转
# 避免日志文件过大
# /etc/logrotate.d/mysql
/var/log/mysql/slow.log {
daily
rotate 7
missingok
compress
delaycompress
notifempty
}
8.3 定期清理
# 清理30天前的慢查询日志
find /var/log/mysql/ -name "slow.log.*" -mtime +30 -delete
九、总结
核心要点
- 配置慢查询：slow_query_log、long_query_time
- 日志字段：Query_time、Rows_examined
- 分析工具：mysqldumpslow、pt-query-digest
- 优化流程：定位 → EXPLAIN → 优化索引 → 验证
- 监控告警：定期分析、超阈值告警
慢查询优化检查清单
□ 是否启用慢查询日志
□ 阈值设置是否合理
□ 是否定期分析日志
□ 慢SQL是否已优化
□ 是否有监控告警
记忆口诀
慢查询日志要开启，long_query_time设阈值，
mysqldumpslow来分析，pt-query-digest更详细。
定位慢SQL用EXPLAIN，索引优化是关键，
定期分析做监控，性能瓶颈早发现。
本文字数：约2,300字 难度等级：⭐⭐⭐⭐（性能分析）