MySQL 慢查询治理闭环实战：从慢日志到防复发
业务系统跑了一段时间后，总会冒出几条越来越慢的 SQL：平时不声不响，一到高峰就把 CPU 和连接数吃满。很多团队的处理方式是捞一条改一条，改完当场见效，过两个月同样的戏码再演一遍。问题不在手法，而在没有闭环——慢查询治理应该是一条固定流水线：持续发现、按总代价归因、针对性优化、验证收益、防止复发。本文以 MySQL 8.0 为例（工具以 Percona Toolkit 为主，实例主机记为 db-01），把这条流水线完整走一遍。
前置条件
- 有一个能登录目标实例的运维账号，具备查看全局变量的权限，动态改参数需要 SYSTEM_VARIABLES_ADMIN 或 SUPER 权限；
- 已安装 Percona Toolkit 3.x，pt-query-digest 可用；
- 明确本次治理的观察窗口，建议至少覆盖一个完整业务高峰（如工作日上午高峰），窗口太短会漏掉周期性慢查询；
- 慢日志准备放在独立目录或独立磁盘上，并已规划好轮转方式，避免把系统盘写满；
- 动手优化前，先留一份治理前的 pt-query-digest 报告存档，作为后面对比效果的基线。
第一步：把慢日志开对
慢日志是整个闭环的数据来源，开错了后面全是歪的。核心口径有三条：阈值不要贪低，生产环境 long_query_time 从 1 秒起步，先看清主要矛盾，不要一上来就设 0.1 秒把日志量放大十倍；输出到文件而不是表，log_output 设为 FILE，写表本身会给实例添负担；加上 min_examined_row_limit 过滤掉扫描行数极少的噪音，小表全表扫描再慢也慢不到哪去。设置用动态方式立即生效，同时别忘了同步写进配置文件，否则下次重启就丢了。
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/data/mysql-log/slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_output = 'FILE';
SET GLOBAL min_examined_row_limit = 1000;
几个变量的读法再交代一句：slow_query_log_file 的目录要提前建好并给 mysql 用户写权限，否则开启会直接报错；long_query_time 单位是秒、支持小数，老版本改完对新连接才生效，8.0 同样要注意已建立的会话不感知；log_queries_not_using_indexes 先不要开，它会把所有未走索引的查询都记下来，噪音问题在常见问题一节专门讲。
第二步：用 pt-query-digest 做汇总归因
慢日志的原始文件不能直接看：同一条 SQL 会出现成千上万次，肉眼翻日志只会盯住偶发的那条 30 秒。治理的第一原则是按总代价排序——一条平均 0.3 秒、每天执行五万次的 SQL，总耗时远超一条偶发的 30 秒。pt-query-digest 的作用就是把慢日志按指纹（去掉具体参数后的 SQL 骨架）聚合，给出每类查询的总耗时、占比、调用次数和平均耗时。分析时先用时间窗口对齐业务高峰，再看整体汇总和 Profile 排名表。
# 全量汇总，生成基线报告
pt-query-digest /data/mysql-log/slow.log > /tmp/digest-before.txt
# 只看某个高峰窗口
pt-query-digest --since '2026-11-01 09:00:00' --until '2026-11-01 12:00:00' /data/mysql-log/slow.log > /tmp/digest-peak.txt
读报告抓四个数：Response time 总耗时决定治理的天花板；每条指纹的占比决定优先级，占比过半的那条就是本次治理的主角；R/Call 是单次平均耗时，配合 Calls 看是「慢而多」还是「少而慢」；Rows_examined 与 Rows_sent 的比值最能暴露问题，比值几百上千倍的，基本就是缺索引或索引失效。报告里每条指纹下面还给了一条样本 SQL，拿着样本去下一步做针对性分析即可。
第三步：三类典型慢查询的优化手法
1. 缺索引：先确认，再下手
特征是 EXPLAIN 里 type 为 ALL、rows 大得离谱、Extra 里出现 Using filesort 或 Using temporary。处理顺序是先看 WHERE 与 JOIN 条件涉及哪些列、排序和分组用哪些列，再决定联合索引的列序：等值条件在前、范围条件在后、排序列顺带捎上。加之前先评估这张表的写入压力，写热点表每多一个索引就多一份维护成本；能用覆盖索引不回表的优先考虑，但不要为了覆盖把索引撑得过宽。
EXPLAIN SELECT user_id, status FROM orders
WHERE user_id = 1001 AND status = 1 ORDER BY create_time DESC LIMIT 20;
-- 等值列在前、排序列在后，一次索引同时服务过滤与排序
CREATE INDEX idx_orders_user_status_time ON orders (user_id, status, create_time);
2. 索引失效：多半是写法问题
索引明明存在却不走，常见就三种：对索引列套了函数或运算；字符串列传了数字、发生隐式类型转换；LIKE 以 % 开头做前导模糊。这类问题改 SQL 写法就能解决，成本最低，治理时要优先排查。典型例子是按天查数据时对时间列用 DATE() 截断，改写成范围条件后索引立刻可用。
-- 索引失效：对索引列使用函数
SELECT order_id, amount FROM orders WHERE DATE(create_time) = '2026-11-01';
-- 改写为范围条件，create_time 上的索引即可用
SELECT order_id, amount FROM orders
WHERE create_time >= '2026-11-01' AND create_time < '2026-11-02';
3. 深分页与大事务：换思路而不是加索引
列表页翻到几万页之后的 LIMIT 大偏移，扫多少丢多少，再好的索引也救不了。两种改法：游标书签法，让前端带上上一页最后一行的 id，WHERE 里用 id 大于书签值往后取；延迟关联法，先在覆盖索引上取本页主键，再回表取整行。另一类是大事务：一条 UPDATE 改几百万行，执行慢还堵住 binlog 和回滚段，要拆成按主键分批、每批几千行提交，既缩短单次耗时，也给主从复制留出喘息空间。
-- 反例：偏移量越大，扫描丢弃越多
SELECT id, user_id, amount FROM orders ORDER BY id LIMIT 100000, 20;
-- 书签法：记住上一页最后一个 id
SELECT id, user_id, amount FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;
-- 延迟关联：先在索引上取主键，再回表
SELECT o.id, o.user_id, o.amount FROM orders o
JOIN (SELECT id FROM orders ORDER BY create_time LIMIT 100000, 20) t ON o.id = t.id;
第四步：验证收益，别凭感觉收工
优化上线后，用和基线相同的窗口再跑一遍 pt-query-digest，前后两份报告对照着看：目标指纹的总耗时降了多少、是否还留在 Top 名单里、整体慢查询总条数与总耗时降了几个百分点。同时结合监控看实例的 CPU、活跃连接数和业务侧的 P95/P99 响应时间，至少观察一到两个完整高峰再宣布收口。只改了一条 SQL 的执行计划、当场测着快，不算验证通过——数据量和并发上来之后，计划可能又变回去。
第五步：长效机制，防止复发
- 新 SQL 评审：上线前对核心接口的 SQL 做 EXPLAIN 审核，出现全表扫描且表行数过万的直接打回，从源头堵住新增慢查询；
- 索引规范成文：索引命名、单表索引数量上限、联合索引列序约定写进团队规范，评审时有据可依；
- 慢日志周报：把 digest 汇总做成固定周报，Top 指纹认领到人，新进榜的指纹当周必须给出结论；
- 提前看数据增长：行数翻倍的表单独列出来，提前评估现有索引和查询在两倍、三倍数据量下还能不能扛。
验证
- 慢日志文件在持续增长，大小在轮转策略控制之内，磁盘无异常占用；
- 前后两份 digest 报告对比，目标指纹总耗时明显下降，整体慢查询总耗时下降可量化；
- 业务高峰期实例 CPU 与 P95 响应时间回落到正常区间；
- 抽两条已优化的 SQL 重新 EXPLAIN，确认实际执行走了预期索引。
常见问题
慢日志把磁盘写满了
多半是阈值设得太低又开了 log_queries_not_using_indexes，日志量一天几十 GB。处理上先把慢日志放独立目录并配 logrotate 按天切割、只保留有限份数，再回调阈值到合理水位；已经写满的先截断或移走旧日志腾出空间，不要直接删正在写入的那个文件句柄。
优化完过段时间又复发
常见原因是数据量增长后优化器换了执行计划、统计信息过期导致估算偏差，或者某次发版悄悄改了 SQL。处理上把已治理的指纹留在周报里持续观察，定期对大表执行 ANALYZE TABLE 维护统计信息，发版清单里加上「是否涉及核心 SQL 变更」一项。
log_queries_not_using_indexes 打开后全是噪音
小表本来就适合全表扫描，这类记录刷满日志后真正的慢查询反而被淹没。两个办法：配合 min_examined_row_limit 把扫描行数太少的过滤掉；或者干脆关掉这个开关，靠 digest 按总代价排序来发现问题——治理看的是代价，不是「有没有用索引」这个形式。
小结
慢查询治理的关键不是会写几条优化 SQL，而是把它做成闭环：慢日志提供持续的数据，pt-query-digest 按总代价排优先级，三类典型问题各有固定打法，上线后用同口径报告验证收益，最后靠评审、规范和周报防止复发。闭环转起来之后，慢查询会从「救火事件」变成「日常清单」，这才是治理的终点。
