MySQL 慢查询日志完全梳理与实战优化

怎么查看慢查询日志？

方式一：直接看文件

tail -f /data/mysql/slow/slow.log

典型日志样子如下：

# Time: 2025-02-07T12:34:56.123456Z 
# User@Host: app_user[app_user] @ [192.168.1.100] Id: 12345 
# Query_time: 2.345 Lock_time: 0.000123 Rows_sent: 100 Rows_examined: 1000000 
# Rows_affected: 0 Bytes_received: 0 
SET timestamp=1738922096; 
SELECT * FROM orders WHERE user_id = 123456 ORDER BY create_time DESC LIMIT 10;

关键字段解读：

• Query_time：执行总耗时
• Lock_time：等待锁的时间
• Rows_examined：扫描了多少行（越大越危险）
• Rows_sent：返回给客户端多少行

方式二：写入 mysql.slow_log 表

SET GLOBAL log_output = 'TABLE';

然后就能用 SQL 查了：

SELECT sql_text, query_time, lock_time, rows_examined, rows_sent, user_host, db, last_seen 
FROM mysql.slow_log 
ORDER BY start_time DESC 
LIMIT 100;

生产环境中如何高效分析慢查询？

常用流程：

1. 打开慢查询 + 设置合理阈值（1s 或 0.5s）。
2. 运行一段时间后，使用 mysqldumpslow 工具汇总分析。

# 汇总显示前 20 条最慢的 SQL（按执行次数、耗时排序）
mysqldumpslow -s t -t 20 /data/mysql/slow/slow.log
mysqldumpslow -s c -t 20 /data/mysql/slow/slow.log # 按执行次数排序

3. 用 pt-query-digest（Percona Toolkit）做更强大的分析。

pt-query-digest /data/mysql/slow/slow.log > digest.txt

它会给你：

• 总耗时占比
• 执行次数
• 平均/最大/最小耗时
• 95% 响应时间
• 指纹化的 SQL（自动参数化）

4. 快速定位 SQL 问题：
   • Rows_examined 很大 → 全表扫描、缺少索引
   • Lock_time 很高 → 行锁、表锁、事务长时间未提交
   • Using filesort / Using temporary → 排序或临时表开销大
   • Query_time 远大于 Lock_time → 可能是 IO 慢、数据量大

常见误区 & 注意事项

• long_query_time = 0 会记录所有查询（慎用，日志会爆炸）
• 慢查询日志默认不记录 SELECT SLEEP(10) 这种主动慢查询
• log_queries_not_using_indexes = ON 非常有用，但会增加少量开销
• 慢查询日志是异步写的，日志里时间可能比实际执行晚一点
• 高并发下日志文件可能非常大，建议设置 expire_logs_days 或定期清理
• 5.7 和 8.0 之后，慢查询日志对复制线程的 SQL 也可以记录（log_slow_replica_statements）

终极建议

• 开发阶段：本地 / 测试环境把 long_query_time 设成 0.1～0.5 秒，养成看慢查询的习惯
• 上线前：用 pt-query-digest 过一遍主要接口的 SQL
• 线上：每天定时分析慢查询（可以结合 ELK、Prometheus + Grafana 做监控）
• 最常见的优化方向（优先级）：
  1. 加索引（最有效）
  2. 避免 select *
  3. 优化 order by / group by
  4. 拆分大事务
  5. 减少扫描行数

一句话总结：慢查询日志不是用来'看日志'的，而是用来'找到最该优化的 SQL'的。