第 28 章 慢SQL治理实战

📖 本篇简介：前面几篇零散讲过索引、EXPLAIN、改写，这一篇把它们串成一条完整的治理流水线——从"慢 SQL 是怎么被发现的"开始，到分析、改写、验证、再到上线后持续监控，形成闭环。学完后你拿到一条线上告警的慢 SQL，能按流程一步步把它搞定，而不是靠猜。本示例基于 MySQL 8.0 语法；执行计划用 sqlite3 实跑验证并贴出真实输出。

目录

• 1 慢SQL治理的完整闭环
• 2 第一步：抓取慢SQL
• 3 第二步：分析与定位
• 4 第三步：改写与优化
• 5 第四步：验证与回归
• 6 第五步：监控与长效机制
• 7 慢SQL治理速查表
• 8 常见坑与注意事项
• 9 练习题

1 慢SQL治理的完整闭环

慢 SQL 治理不是"DBA 偶尔改改 SQL"的一次性救火，而是一条标准化流水线。成熟团队的流程是五步闭环：

抓取(发现) → 分析(定位) → 改写(优化) → 验证(回归) → 监控(长效)
     ↑                                                    |
     └──────────── 上线后继续抓，发现新慢 SQL ─────────────┘

每一步都有明确的产出物：

| 步骤 | 干什么 | 产出 |

|---|---|---|

| 抓取 | 从日志/监控找出哪些 SQL 慢 | 一份慢 SQL 清单 |

| 分析 | EXPLAIN 看执行计划，找瓶颈 | 慢因定位 |

| 改写 | 改 SQL / 加索引 | 优化后的 SQL |

| 验证 | 测试环境跑对比，确认变快 | 前后耗时对比 |

| 监控 | 上线后盯性能，防回归 | 告警看板 |

下面逐步拆开讲。这套流程不是 MySQL 独有的，任何关系型数据库的性能治理都长这样；差别只在工具名——Oracle 用 AWR，PG 用 pg_stat_statements，MySQL 用慢日志和 performance_schema。思路是相通的。

另外要强调：慢 SQL 治理的责任不只在 DBA。写 SQL 的开发工程师才是第一道防线——上线前自己先 EXPLAIN 一下，能挡掉一大半问题。本篇后面会反复提到这点。

2 第一步：抓取慢SQL

你不可能优化一条"不知道它慢"的 SQL。所以第一步是让数据库主动把慢 SQL 记下来。

2.1 开启慢查询日志

MySQL 自带慢查询日志（slow query log），把执行时间超过阈值的 SQL 记到文件里：

-- 查看当前慢日志配置
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢日志，阈值设为 1 秒（生产可调到 0.5~1 秒）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
-- 没走索引的 SQL 也记下来（哪怕很快）
SET GLOBAL log_queries_not_using_indexes = ON;

慢日志默认在 datadir 下，文件名形如 主机名-slow.log。日志里每条慢 SQL 会记录：执行时间、锁等待时间、扫描行数、发送行数、完整 SQL 文本。

2.2 用 mysqldumpslow 汇总

慢日志可能很大，直接看文本不现实。MySQL 自带 mysqldumpslow 工具把相似 SQL 聚合统计：

# 按总耗时排序，取最慢的 10 类 SQL
mysqldumpslow -s t -t 10 /var/lib/mysql/主机名-slow.log

常见参数：-s t 按时间排、-s c 按调用次数排、-t N 取前 N 条。这样你一眼能看到"哪类 SQL 跑得最多、最慢"。

2.3 监控系统告警

除了日志，生产环境一般还有监控系统（如 Prometheus + Grafana）盯数据库指标：慢查询数、连接数、CPU、锁等待。当慢查询数突增，告警自动弹出。这是"主动发现"，比等人投诉更快。

2.4 performance_schema：另一个抓慢的入口

除了慢日志，MySQL 8.0 还能用 performance_schema 统计"哪类 SQL 跑得最多"，即使它没慢到触发慢日志：

-- 统计历史 SQL 中平均耗时最高的前 10 类（需要开启语句事件采集）
SELECT digest_text,
       count_star,
       avg_timer_wait / 1000000000 AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 10;

这个视图把"SQL 模板"（参数脱敏后）聚在一起，适合找"高频但单次不算最慢"的 SQL——它们累积起来也能拖垮库。慢日志抓"单次慢"，performance_schema 抓"累计重"，两个配合用。

2.5 抓不到慢 SQL 但就是感觉慢怎么办

有时用户反馈页面慢，但慢日志里啥也没有。可能的原因：

• 慢日志阈值设太高（比如 1 秒），实际 800ms 的查询没记下来，先把阈值调低试试。
• 慢不是 SQL 本身慢，是连接获取慢、锁等待久，看 SHOW ENGINE INNODB STATUS 和锁等待监控。
• 是网络或应用层慢，根本不是数据库的锅，别冤枉 MySQL。

抓之前先定位"慢在哪一段"，再决定开什么日志。

3 第二步：分析与定位

抓到一条慢 SQL，先别急着改，先搞清楚它为什么慢。工具就是 EXPLAIN。

3.1 EXPLAIN 看什么

-- 在慢 SQL 前加 EXPLAIN，看执行计划
EXPLAIN SELECT o.order_id, u.nickname, o.total_amount
FROM orders o JOIN users u ON u.user_id = o.user_id
WHERE o.user_id = 10001
ORDER BY o.created_at DESC LIMIT 20;

重点列回顾（详见第 26 章）：

| 列 | 健康值 | 危险信号 |

|---|---|---|

| type | ref / range | ALL（全表扫） |

| key | 有索引名 | NULL |

| rows | 小 | 几十万上百万 |

| Extra | 无 | Using filesort / temporary |

3.2 实跑对比：索引前后

下面这段在 sqlite3 实跑，演示"同一条 SQL，建索引前后执行计划怎么变"：

# Python + sqlite3：治理前后执行计划对比
import sqlite3
con = sqlite3.connect("demo_ecom.db")
cur = con.cursor()

sql = "SELECT * FROM orders WHERE user_id=1 AND paid_at>='2026-09-20'"

print("-- 治理前（无索引）--")
for r in cur.execute("EXPLAIN QUERY PLAN " + sql):
    print(r)

# 模拟"改写+加索引"这一步
cur.execute("CREATE INDEX idx_orders_user_paid ON orders(user_id, paid_at)")

print("-- 治理后（有索引）--")
for r in cur.execute("EXPLAIN QUERY PLAN " + sql):
    print(r)

真实输出（本机 sqlite3 3.53.1）：

-- 治理前（无索引）--
(2, 0, 216, 'SCAN orders')
-- 治理后（有索引）--
(3, 0, 51, 'SEARCH orders USING INDEX idx_orders_user_paid (user_id=? AND paid_at>?)')

治理前是 SCAN orders（全表扫），治理后变成按索引精确查找。数据量一大，这个差别就是几秒 vs 几毫秒。

3.3 常见慢因归类

分析完，把慢因归到下面某一类，对号入座：

| 慢因 | EXPLAIN 特征 | 典型场景 |

|---|---|---|

| 缺索引 | type=ALL | WHERE 条件没索引 |

| 索引失效 | key=NULL | 函数包裹列、隐式转换 |

| 回表过多 | rows 大 + 回表 | SELECT * + 深分页 |

| filesort | Extra=Using filesort | 排序没走索引 |

| 临时表 | Extra=Using temporary | GROUP BY 无索引 |

| JOIN 太多 | 多表嵌套循环 | 报表类复杂查询 |

3.4 一个贯穿案例

假设慢日志抓到这么一条：

-- 客服后台按状态查今天的订单，跑了 8 秒
SELECT * FROM orders
WHERE status = 1
  AND DATE(created_at) = CURDATE()
ORDER BY created_at DESC;

EXPLAIN 一看：type=ALL、key=NULL、rows=500万。慢因清楚了：对 created_at 套了 DATE()，索引失效，全表扫 500 万行再排序。这就属于"索引失效"那一类。接下来的改写见下一节。

4 第三步：改写与优化

定位到慢因后，对症下药。最常用的三板斧：加索引、改写法、拆大查询。

上面那个案例，对应的改写就是把 DATE(created_at)=CURDATE() 换成范围条件，并加 (status, created_at) 联合索引。

4.1 加索引

最直接。但要结合第 26 章讲的最左前缀、联合索引顺序，别瞎加：

-- 给"按用户+支付时间"查询加联合索引
ALTER TABLE orders ADD INDEX idx_user_paid (user_id, paid_at);

4.2 改写 SQL

-- 慢：函数包裹列，索引失效
SELECT * FROM orders WHERE DATE(paid_at) = '2026-09-18';

-- 快：改成范围条件
SELECT * FROM orders
WHERE paid_at >= '2026-09-18 00:00:00'
  AND paid_at <  '2026-09-19 00:00:00';

4.3 深分页换 keyset

-- 慢
SELECT * FROM orders WHERE user_id=1 ORDER BY created_at DESC LIMIT 100000, 20;

-- 快：记住上一页最后一条 order_id
SELECT * FROM orders
WHERE user_id=1 AND order_id < 上一页末id
ORDER BY order_id DESC LIMIT 20;

4.4 拆分大事务/大查询

有些慢不是索引问题，是一次查太多数据。比如一个 SELECT ... GROUP BY 扫了半年数据，这种不该在交易库硬算，该挪到报表库或预聚合表（见第 27 章）。

4.5 JOIN 慢的常见处理

多表 JOIN 慢，先看 EXPLAIN 里 JOIN 顺序。MySQL 是"小表驱动大表"原则，但如果写错了 JOIN 顺序或缺被驱动表的关联索引，就会变成嵌套循环全扫。

-- 慢：order_item 关联 orders，被驱动表 orders 上没索引
SELECT oi.* FROM order_item oi JOIN orders o ON o.order_id = oi.order_id
WHERE o.status = 1;

-- 快：确保 orders.order_id 是主键（天然有索引），并让小结果集驱动

经验：被 JOIN 的表，关联列上一定要有索引；让过滤后行数少的那张表当驱动表。

5 第四步：验证与回归

改完 SQL 别急着上线，先验证它真的变快了，且没改坏业务逻辑。

5.1 前后耗时对比

在测试环境（尽量复制线上数据量）分别跑优化前后两条 SQL，记录耗时：

-- 打开 profiling，看每条语句耗时
SET profiling = 1;
SELECT ...;   -- 优化前
SELECT ...;   -- 优化后
SHOW PROFILES;

健康的标准：优化后耗时至少下降一个数量级（比如从 5 秒到 50 毫秒）。如果只从 5 秒降到 4 秒，说明没治到根上，重新分析。

5.2 结果一致性验证

改写 SQL 最怕"快了但结果错了"。要对比优化前后两条 SQL 返回的行数和关键聚合值是否一致：

-- 对比：优化前后结果集行数应一致
SELECT COUNT(*) FROM (原SQL) t1;
SELECT COUNT(*) FROM (新SQL) t2;

特别是 WHERE 条件改写（比如把 DATE() 改成范围），要确认边界数据没漏没多。

5.3 上线要灰度

加索引这种 DDL 在大表上是有风险的（锁表、复制延迟）。生产大表加索引用 ALGORITHM=INPLACE, LOCK=NONE，或用 pt-online-schema-change 这类工具，避开高峰。

-- 在线加索引，尽量不锁表
ALTER TABLE orders ADD INDEX idx_user_paid (user_id, paid_at), ALGORITHM=INPLACE, LOCK=NONE;

5.4 回滚预案

上线不是终点。万一优化后出现新问题（比如优化器选错索引、CPU 反而升高），要有快速回滚手段：

• 改 SQL 的：准备好旧版本 SQL，配置中心切回。
• 加索引的：确认新索引没帮上忙就 DROP INDEX，代价小。
• 大版本变更的：灰度发布，先 10% 流量观察，没问题再全量。

留好后路，治理才不会越治越慌。

5.5 测试环境要尽量像线上

验证结果可不可信，取决于测试环境的数据量和分布。在只有几百行的测试库上跑“很快”，到了上亿行的线上可能还是慢。条件允许时，用生产数据的脱敏副本做压测，或者至少复制一份量级接近的表。否则验证就是自欺欺人。

6 第五步：监控与长效机制

治理不是一锤子买卖。上线后要持续盯，防止新慢 SQL 冒出来、老 SQL 退化。

6.1 持续盯指标

监控看板上长期保留这几条：

| 指标 | 看什么 |

|---|---|

| 慢查询数（每分钟） | 突增说明有新慢 SQL |

| 平均扫描行数 | 恶化说明索引失效 |

| 锁等待时间 | 长说明并发冲突 |

| 连接数 | 满了说明慢查询堆积 |

6.2 制度兜底

技术之外，还要靠制度：

• 代码评审：新上线的 SQL 要过评审，复杂查询先 EXPLAIN。
• 上线前压测：大 SQL 在测试环境跑过再上线。
• 定期清理无用索引：参考第 26 章 sys.schema_unused_indexes。
• 慢 SQL 周会：每周过一遍慢日志，top N 限期优化。

这样慢 SQL 才不会"治一批又冒一批"。

6.3 先治哪条：优先级怎么排

慢 SQL 不止一条，人力有限时先治哪个？按"影响面 × 严重程度"排：

| 优先级 | 特征 | 例子 |

|---|---|---|

| P0 | 阻塞主流程、超时告警 | 下单接口慢查询 |

| P1 | 高频调用、累积耗时大 | 商品列表每次都慢 |

| P2 | 偶发、影响小 | 后台月报每天慢一次 |

| P3 | 实验性查询 | 临时 ad-hoc |

判断公式：单条耗时 × 调用次数 = 总消耗。一条每次 1 秒、一天调 1 万次的 SQL，比一条每次 10 秒、一天调 1 次的更该先治。这也是为什么 performance_schema 按"累计耗时"排序往往比按"单次最慢"排序更有指导意义。

把人力花在刀刃上，先啃 P0/P1，P2/P3 排期慢慢消化。

治理不是和慢 SQL 拼命，而是用最小代价把最影响业务的问题先解决掉。

7 慢SQL治理速查表

| 阶段 | 工具/命令 | 关键动作 |

|---|---|---|

| 抓取 | slow log / mysqldumpslow | 开启慢日志，聚合 top SQL |

| 分析 | EXPLAIN | 看 type/key/rows/Extra |

| 慢因 | type=ALL / key=NULL | 缺索引或索引失效 |

| 改写 | 范围替代函数 / keyset | 让索引生效 |

| 加索引 | ALTER TABLE ... INPLACE | 联合索引，在线加 |

| 验证 | SHOW PROFILES | 前后耗时对比 + 结果一致 |

| 监控 | Prometheus/Grafana | 盯慢查询数与扫描行数 |

一句话流程：先抓、再看、改完验、上线盯。缺了任何一步，治理都会打折扣。

把这张表和第 26 章的"订单查询优化速查表"配合用：第 26 章教"怎么改一条具体 SQL"，本篇教"怎么在一个团队里系统化地把慢 SQL 管起来"。前者是手艺，后者是流程，两者都要有。

8 常见坑与注意事项

坑 1：不看 EXPLAIN 凭感觉加索引。 猜一个字段加索引，结果慢 SQL 根本没走它，白加还拖慢写入。永远先 EXPLAIN。

坑 2：改完不验证结果一致性。 把 DATE() 改范围时，边界数据（比如 23:59:59 的订单）可能漏掉，结果对不上。必须对比行数和聚合值。

坑 3：大表直接 ALTER 加索引。 高峰期在亿级表上 ALTER TABLE，锁表几分钟，线上直接挂。用在线 DDL 工具、低峰期操作。

坑 4：只治单次，不建监控。 这次改好了，下周又冒出来新的，因为没有监控告警。治理闭环最后一步"监控"不能省。

坑 5：把报表慢查询当交易慢查询治。 报表类 GROUP BY 扫半年数据，加索引也救不了，该挪去数仓就挪，别在交易库死磕。

坑 6：以为加完索引就万事大吉。 数据量涨了、分布变了，原来的索引可能失效或优化器改主意。要持续观察，定期 ANALYZE TABLE 更新统计信息。

坑 7：慢日志阈值设太低。 设成 0 秒，日志全是正常 SQL，淹没重点。生产一般 0.5~1 秒起步。

坑 8：优化后没观察就宣布成功。 改完 SQL 测一次很快就收工，结果第二天业务高峰又慢了——因为测试环境没复现真实并发。上线后至少观察一个业务高峰周期，确认指标稳定。

坑 9：把所有慢 SQL 都当成要加索引。 有些慢是因为锁等待、连接池打满、甚至磁盘 IO 饱和，加索引治标不治本。先用 EXPLAIN 确认是 SQL 本身的问题，再动手。

9 练习题

练习 1：把慢 SQL 治理的五个步骤按顺序写出来。

<details>

<summary>参考答案</summary>

抓取（从慢日志/监控发现慢 SQL）→ 分析（EXPLAIN 定位慢因）→ 改写（加索引/改 SQL）→ 验证（前后耗时对比 + 结果一致）→ 监控（上线后持续盯指标）。形成闭环。

</details>

练习 2：一条慢 SQL 的 EXPLAIN 显示 type=ALL、key=NULL，下一步该做什么？

<details>

<summary>参考答案</summary>

说明这条 SQL 没走任何索引、全表扫描。看 WHERE 条件里有哪些列，为它们设计联合索引（注意最左前缀），加完再 EXPLAIN 确认 type 变成 ref/range、key 填上索引名。

</details>

练习 3：为什么加完索引要在测试环境验证"结果一致性"？

<details>

<summary>参考答案</summary>

改写 SQL（比如把函数包裹列改成范围条件）可能改变结果集——边界数据被漏掉或多算。快是快了，但数错了更严重。必须对比优化前后返回的行数和关键聚合值，确认一致才能上线。

</details>

练习 4：生产环境给亿级订单表加索引，要注意什么？

<details>

<summary>参考答案</summary>

直接 ALTER 可能锁表、阻塞写入。要用 ALGORITHM=INPLACE, LOCK=NONE 或 pt-online-schema-change 在线加，选业务低峰期操作，同时盯主从复制延迟，避免延迟过大影响从库读。

</details>

练习 5：mysqldumpslow 的作用是什么？为什么不直接看慢日志文本？

<details>

<summary>参考答案</summary>

慢日志里每条 SQL 的参数都不同（不同 user_id、不同时间），直接看文本会被海量相似 SQL 淹没。mysqldumpslow 把相似 SQL 聚合成一类、统计总耗时和调用次数，让你一眼看到"哪类 SQL 最该先治"。

</details>

练习 6：一条报表 SELECT SUM(...) ... GROUP BY 月份 跑了 30 秒，加索引能解决吗？

<details>

<summary>参考答案</summary>

多半不能。这种全表聚合的报表查询，瓶颈是扫描数据量太大，加索引帮助有限。正确做法是把它挪到报表库/数仓，或建每日预聚合表（凌晨算好当天指标存小表），报表查小表。交易库不该扛这种重查询。

</details>

练习 7：为什么慢日志要配合 mysqldumpslow 用，而不是直接看日志文件？

<details>

<summary>参考答案</summary>

慢日志是一条条原始记录，量级巨大，且每条 SQL 参数都不同，肉眼无法归纳"哪类 SQL 最慢"。mysqldumpslow 把相似 SQL 模板聚合，统计总耗时、平均耗时、调用次数，让你按优先级治理。直接看日志相当于在海里找针。

</details>

练习 8：治理一条慢 SQL 后，从 5 秒只降到 4.5 秒，这说明什么？

<details>

<summary>参考答案</summary>

说明没治到根上。健康的优化应该至少提升一个数量级（5 秒 → 几十毫秒）。只降一点，可能是加了个没用的索引、或者改写没命中真正瓶颈。回到 EXPLAIN 重新分析，看 type/rows 有没有实质变化，别把"稍微快一点"当成功。

</details>

练习 9：一条 SQL 单次只要 200ms，但一天被调用 50 万次，它值得治吗？

<details>

<summary>参考答案</summary>

值得。总消耗 = 200ms × 50 万 = 约 28 小时的数据库时间，累积下来非常可观。这正是 performance_schema 按"累计耗时"排序能抓出来的类型——单次不慢但高频。优化它哪怕只省 100ms，一天就省 14 小时数据库负载。

</details>

练习 10：为什么说慢 SQL 治理是个闭环，而不是一次性任务？

<details>

<summary>参考答案</summary>

因为系统在不断演化：新功能上线会带来新 SQL，数据量增长会让原来快的查询变慢，业务改版会产生新的查询模式。一次性改完一批，过段时间又会冒出来。只有建立“抓取→分析→改写→验证→监控”的循环，配合代码评审和周会制度，才能让慢 SQL 数量长期可控，而不是按下葫芦浮起瓢。

</details>

下一篇预告：第 29 章 MySQL面试题精讲：基础