慢 SQL 从发现到根治的完整流程 —— 慢查询优化实战
属于 S1 MySQL 深入 · 深入篇第 4 章（重点章节） 上一篇：索引与 SQL 优化 下一篇：主从复制与高可用
《索引与 SQL 优化》讲清楚了"索引为什么生效/失效"。但线上真实的场景是：你根本不知道哪条 SQL 慢，也不知道它为什么慢；更扎心的是——索引已经建得很合理，SQL 还是慢。这时候 EXPLAIN + 建索引 这套"入门三板斧"就失效了，真正的战斗才刚刚开始。
这一篇是实战章：先讲"怎么发现慢 SQL、怎么定位根因"，再上实战中更高频的六大类进阶优化手段（按实战价值排序）：重构 SQL 逻辑 → 根治隐式转换与函数 → 深挖排序分组 → 数据量大的物理手段 → 调整连接与锁的姿势 → SQL Hints 干预执行计划，最后是血泪避坑总结 + 完整案例。
第一步：怎么发现慢 SQL —— 慢查询日志
MySQL 默认不记录慢查询，需要手动打开（生产建议打开，代价很小）：
-- 查看当前状态
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
-- 动态开启（重启失效；要永久生效改 my.cnf 的 [mysqld] 段）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 超过 1 秒的记录，单位秒
SET GLOBAL log_queries_not_using_indexes = ON; -- 没走索引的也记录（开发环境开）分析工具：
# MySQL 自带：按平均查询时间排序
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log
# 社区神器：pt-query-digest（Percona Toolkit），输出报表更专业
pt-query-digest /var/lib/mysql/slow.log正确姿势：把慢日志拉到本地用 pt-query-digest 汇总 → 按"总耗时 = 次数 × 单次耗时"排序 → 优先优化"次数多且单次慢"的语句（总耗时最大，收益最高），而不是只看单次最慢的。
注意：
long_query_time=1只抓 1 秒以上的；很多慢 SQL 单次 200ms 但每秒执行 100 次，累计耗时才是大头。抓取阈值和业务容忍度匹配（核心接口 P99 的容忍度就是你的阈值）。
第二步：怎么定位根因 —— EXPLAIN 全字段精讲
《索引与 SQL 优化》讲了 type/key/rows/Extra 四个关键列，这一篇补齐剩下的字段，凑成完整的地图：
EXPLAIN SELECT u.id, u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.city = '深圳' AND o.status = 1
ORDER BY o.created_at DESC
LIMIT 10;| 字段 | 含义 | 判断要点 |
|---|---|---|
| id | 执行步骤编号，越大越先执行 | id 相同 → 从上往下；id 不同 → 大者先 |
| select_type | 查询类型 | SIMPLE 简单查询 / PRIMARY 外层 / SUBQUERY 子查询 / DERIVED 派生表 |
| table | 访问哪张表（含别名） | — |
| type | 访问类型 | const > eq_ref > ref > range > index > ALL，目标是 range 及以上 |
| possible_keys | 可能用到的索引 | 有值但 key 为空 = 优化器评估后放弃了 |
| key | 实际用的索引 | NULL = 没走索引，重点排查 |
| key_len | 用到的索引字节数 | 联合索引看它判断"用到第几列"（数字大 = 用到的列多） |
| ref | 索引匹配的列/常量 | — |
| rows | 预估扫描行数 | 越小越好；与真实偏差大 = 统计信息过期 |
| filtered | 过滤比例（%） | 100% 表示没过滤，越小说明 WHERE 筛选越狠 |
| Extra | 附加信息 | 见下表，重点信号 |
Extra 里的关键信号（血泪教训：别只盯 rows，Using temporary 和 Using filesort 出现就意味着必有大坑，优先干掉它们）：
| Extra | 含义 | 处理 |
|---|---|---|
Using index | 覆盖索引 | ✅ 最优 |
Using where | 存储引擎返回后 Server 层再过滤 | 正常（配合 type=ref 等） |
Using index condition | 索引下推（ICP） | ✅ 8.0 常见，好 |
Using filesort | 额外排序 | ⚠️ CPU 杀手：排序字段没进索引，想办法消除 |
Using temporary | 用了临时表 | ⚠️ 常见于 GROUP BY/DISTINCT 无索引，最差要避免 |
Using join buffer | JOIN 没走索引，用了 join buffer | ⚠️ 右表关联列要建索引，或调大 join_buffer_size（见手段五） |
Using where; Using index | 覆盖 + 过滤 | ✅ 好 |
rows 不可全信：它是基于统计信息（
SHOW STATISTICS/ information_schema）的估算。表数据变化大但统计信息没更新（ANALYZE TABLE），优化器会选错索引——这是"明明有索引却不走"的一大原因。
第三步：优化器为什么"不听话" —— OPTIMIZER_TRACE
当 EXPLAIN 显示优化器没用你预期的索引时，打开优化器追踪看它到底怎么想的：
SET optimizer_trace = 'enabled=on';
SELECT * FROM orders WHERE status = 1 AND created_at > '2024-01-01' ORDER BY created_at;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G -- 看 JSON 输出
SET optimizer_trace = 'enabled=off';trace 里重点看 rows_estimation（各索引的行数估算）和 considered_execution_plans（对比了哪些方案、为什么选了这个）。常见"不听话"原因：
- 统计信息过期 →
ANALYZE TABLE t;更新统计。 - 强制索引比全表扫更贵：数据量小、或回表比例太高（比如过滤条件命中 60% 的行），优化器判断全表扫更快。这时别硬塞索引，先看 SQL 写法/查询需求。
- 实在要干预：
SELECT ... FORCE INDEX (idx_name) ...（临时手段，不推荐长期用，索引一改就失效；正确做法是让优化器自己选对）——详见手段六。
第四步：六大类优化手段（按实战高频排序）
手段一：重构 SQL 逻辑 —— 减量比提速更狠
索引是"提速"，重构是"减量"。很多时候 SQL 慢不是索引不行，而是要处理的数据量太大。三个立竿见影的重构：
① 改 SELECT * 为覆盖索引字段：强制只查索引树里有的字段，Extra 显示 Using index，免回表。
-- 慢：SELECT * 每行都要回表取全字段
SELECT * FROM orders WHERE status = 1 ORDER BY created_at DESC LIMIT 10;
-- 快：高频列表只查 id, name, status，配合联合索引 (status, created_at, id) 直接覆盖
SELECT id, name, status FROM orders WHERE status = 1 ORDER BY created_at DESC LIMIT 10;② 改"大 OFFSET"为"游标 / 延迟关联"：LIMIT 100000, 10 会让数据库扫描 10 万行再丢 9.9 万行。实战做法是先走覆盖索引取主键，再回表：
-- 慢：扫描 10 万行再丢弃
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;
-- 快（延迟关联）：子查询只扫索引树（覆盖索引，无回表），再连表取全行
SELECT * FROM orders t1
JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) t2 ON t1.id = t2.id;
-- 更快（游标分页）：记住上一页最后一个 id，扫描量从 10 万降到 10
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;③ 改"复杂关联"为"多次查询"：微服务/高并发场景，3 张表以上的 JOIN 往往不如拆成多次单表查询，在应用内存里做关联——尤其分库分表后，跨库 JOIN 是大忌（数据不在一个实例，物理上就 JOIN 不了）。
// 慢的源头：一次 3 表 JOIN，锁 + 网络 + 临时表全压在数据库
// SELECT u.name, o.amount, p.title FROM users u
// JOIN orders o ON u.id = o.user_id JOIN products p ON o.product_id = p.id
// WHERE u.id = 123;
// 重构：3 次单表查询，应用层组装（数据量小时更快、更好缓存、更好扩展）
order := db.Query(ctx, "SELECT * FROM orders WHERE user_id = ?", 123)
product := db.Query(ctx, "SELECT * FROM products WHERE id = ?", order.ProductID)
user := db.Query(ctx, "SELECT * FROM users WHERE id = ?", 123)适用边界：拆查询适合"返回行数少、能命中各自索引、可接受多一次网络往返"的场景；如果本来就是走索引的大结果集 JOIN，或数据在同一个实例且 JOIN 走索引（NLJ）很顺，别盲目拆——拆错反而多 N 次网络 + N 次索引查找。判断标准：单表能过滤掉绝大多数行，再拆。
手段二：根治"隐式类型转换"和"函数破坏索引"（最隐蔽的慢查询）
这是最隐蔽的一类：EXPLAIN 看起来用了索引，但实际只用到了一小部分数据——因为列被转换/运算后，索引的有序性被破坏了。
| 隐蔽场景 | 错误写法 | 正确写法 |
|---|---|---|
| 隐式类型转换 | WHERE phone = 13800138000（phone 是 varchar，全表转数字再比较，索引失效） | WHERE phone = '13800138000'（传字符串） |
| 索引列做函数/运算 | WHERE DATE(create_time) = '2026-08-24'（列被函数包裹） | WHERE create_time >= '2026-08-24 00:00:00' AND create_time < '2026-08-25 00:00:00'（范围查询，索引友好） |
| 前导模糊 | WHERE name LIKE '%关键词%'（前缀未知，无法定位起始） | WHERE name LIKE '关键词%'；必须前后模糊就上 Elasticsearch，别死磕数据库 |
补充：age + 1 > 30（列上运算）、LEFT(name, 1) = '张'（函数）同理失效；字符串列与数字列做 = 时，数字会被转成字符串还是字符串转数字，取决于类型——phone varchar = 数字 是字符串列转数字，必失效。
手段三：深挖"排序"与"分组"的陷阱（filesort / 临时表是 CPU 杀手）
ORDER BY 和 GROUP BY 导致的 Using filesort（文件排序）和 Using temporary（临时表）是 CPU 杀手，也是最常被忽略的两项。
① 排序走索引：让 ORDER BY 的字段和 WHERE 的字段组成联合索引，且顺序严格一致。
-- WHERE a=1 ORDER BY b：建索引 (a, b)
-- B+ 树先按 a 定位，a 相同时天然按 b 有序 → 直接顺序取，免 filesort
SELECT * FROM t WHERE a = 1 ORDER BY b LIMIT 10;
ALTER TABLE t ADD INDEX idx_a_b (a, b); -- 等值列在前、排序列在后② 分组前先过滤：GROUP BY 很重（要排序 + 建临时表），务必先用 WHERE 筛掉 90% 的数据再分组；能用 WHERE 就绝不用 HAVING 过滤（HAVING 在分组后才执行）。
-- 慢：先全表分组再筛组
SELECT city, COUNT(*) FROM users GROUP BY city HAVING status = 1;
-- 快：先 WHERE 过滤再分组（行数骤减，分组开销直线下降）
SELECT city, COUNT(*) FROM users WHERE status = 1 GROUP BY city;③ 拒绝 DISTINCT 滥用：DISTINCT 本质也是排序去重。能用 EXISTS 代替时，优先 EXISTS：
-- 慢：DISTINCT 对全结果排序去重
SELECT DISTINCT u.name FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = 1;
-- 快：EXISTS 命中即停，不去重不排序
SELECT u.name FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 1);手段四：数据量巨大的物理手段（降维打击）
当单表数据过亿，索引本身也变得臃肿（索引比数据还大、B+ 树层数变高、Buffer Pool 装不下），SQL 层面的优化到顶了，就需要物理层面动刀。三招按性价比排序：
① 冷热分离（归档）——性价比最高：把 3 年前的历史数据迁移到历史库/归档表，在线库只留热数据。比任何索引都管用——数据量减半，索引、Buffer Pool、扫描量全部跟着减。
-- 归档：把 2023 年之前的订单搬到 history_orders，再删除在线库数据（分批删，见手段五）
INSERT INTO history_orders SELECT * FROM orders WHERE created_at < '2023-01-01';
-- 分批删除，避免一次性大事务锁死
DELETE FROM orders WHERE created_at < '2023-01-01' LIMIT 1000; -- 循环执行② 分区表（Partitioning）：按日期范围分区（如每天/每月一个分区）。查询带上分区键，数据库裁剪分区，只扫对应区：
CREATE TABLE orders (
id BIGINT, created_at DATETIME, ...
) PARTITION BY RANGE (TO_DAYS(created_at)) (
PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
PARTITION p_max VALUES LESS THAN MAXVALUE
);
-- 查询带分区键 created_at，EXPLAIN 里 partitions 列只显示命中分区注意：分区键必须是主键/唯一键的一部分（InnoDB 限制）；分区数不宜过多（上千个分区元数据开销反而大）。分区解决的是"扫描量"，不是"并发写"——写并发高要上分库分表。
③ 分库分表（Sharding）：按用户 ID 哈希分 16/64 张表，把压力打散（详见 S5 高并发场景题"海量数据分片"与 backlog）。三个必须提前知道的代价：
- ID 生成要换雪花算法（分表后自增 ID 会撞）；
- 跨表聚合查询变复杂（
ORDER BY全局排序、COUNT 求和都要在应用层/中间件做）； - 跨库事务基本没戏（别指望 2PC，按最终一致设计）。
面试判断标准：分库分表是"最后的手段"。先冷热分离，再分区表，扛不住了才分库分表——上来就分库分表的，基本是没把前面三步做透。
手段五：调整"连接"与"锁"的姿势（等锁比执行更慢）
有时候 SQL 本身不慢，是等锁等慢了（Waiting for table metadata lock、行锁等待、Lock wait timeout exceeded）。优化"锁的姿势"往往被忽视，但实战价值极高：
① 事务里快查快放：把 SELECT ... FOR UPDATE 放在事务最后，缩小锁范围；不要在事务里做远程 RPC 调用或大批量循环查询——锁持有时间和事务长度成正比：
// 坏：事务里做 RPC（锁持有几十 ms ~ 几百 ms）
tx.Begin()
row := tx.Query("SELECT * FROM account WHERE id = 1 FOR UPDATE")
resp := rpc.Call("deduct", ...) // 远程调用，锁一直拿着！
tx.Commit()
// 好：先取数（不加锁/快照读），RPC 在外，最后才加锁改
tx.Begin()
row := tx.Query("SELECT * FROM account WHERE id = 1") // 快照读，不加锁
resp := rpc.Call("deduct", ...)
tx.Query("UPDATE account SET balance = ? WHERE id = 1", resp.Amount)
tx.Commit()② 拆分大事务：一个事务更新 10 万行，会产生巨大的行锁 + undo 日志（回滚段膨胀），还可能拖垮主从复制（binlog 单事务过大）。实战改为批次循环：
-- 坏：一次 UPDATE 10 万行，锁 10 万行 + undo 巨大
UPDATE orders SET status = 5 WHERE status = 1;
-- 好：分批 LIMIT 1000，循环 100 次提交，中间让出锁资源
UPDATE orders SET status = 5 WHERE status = 1 LIMIT 1000; -- 应用层循环，每次间隔 SLEEP(0.1)③ 调整 join_buffer_size：如果被迫有 JOIN 且无法走索引（驱动表全表扫描），适当调大 join_buffer_size 可减少临时表落盘（BNL 模式在内存里批处理）：
-- 查看当前值（默认 256KB）
SHOW VARIABLES LIKE 'join_buffer_size';
SET GLOBAL join_buffer_size = 4194304; -- 4MB（每个 JOIN 连接都会分配，别盲目调大）注意：
join_buffer_size是每个连接、每个 JOIN都分配一份，调太大会吃光内存；它是"全表扫描 JOIN 的兜底"，治标不治本——根本解法还是给被驱动表关联列建索引（见 S5 的 JOIN 优化）。
手段六：SQL Hints 干预执行计划（终极武器，慎用）
当优化器（CBO）选错索引时——比如明明有更好的索引，它却用了全表扫描——可以用 Hints 强行指定。这是"最后一招"，使用前必须先 ANALYZE TABLE 确认统计信息没骗人：
① FORCE INDEX 强制索引：
-- 优化器走了全表扫描，但我们知道 idx_create_time 更快
EXPLAIN SELECT * FROM orders WHERE create_time > '2024-01-01';
-- 强行指定索引
SELECT * FROM orders FORCE INDEX (idx_create_time) WHERE create_time > '2024-01-01';② STRAIGHT_JOIN 强制驱动表顺序：优化器可能用大表驱动小表（灾难），STRAIGHT_JOIN 强制按书写顺序执行：
-- 默认：优化器可能先执行大表 orders（全表扫当驱动表）
SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE u.vip = 1;
-- 强制：先执行小表 users，再驱动 orders（必须把"小表"写在前面）
SELECT STRAIGHT_JOIN * FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.vip = 1;使用原则（必背）：
- Hints 是临时手段：索引一变更、数据分布一变，Hints 就会失效甚至帮倒忙；
- 先
ANALYZE TABLE+OPTIMIZER_TRACE搞清楚优化器为什么选错，再考虑 Hints； - 长期方案是让优化器自己选对：更新统计信息、加更合适的索引、改写 SQL 让成本估算更准——Hints 只在"线上紧急止血"时用。
第五步：一个完整案例 —— 从慢日志到验证
背景：电商订单接口变慢，pt-query-digest 报表显示一条 SQL 占总耗时 60%。
Step 1 抓到 SQL（慢日志）：
SELECT * FROM orders
WHERE user_id = 12345 AND status IN (1, 2)
ORDER BY created_at DESC LIMIT 20;Step 2 EXPLAIN 定位：
type: ALL ← 全表扫描！
key: NULL ← 没走索引
rows: 2,400,000 ← 扫了全表
Extra: Using where; Using filesortStep 3 分析根因：user_id 有索引 idx_user_id，为什么没用？—— 查询里 status IN (1,2) + ORDER BY created_at，优化器算了下：用 idx_user_id 找到该用户所有订单（可能几千行）再 filesort；全表扫 + filesort 的行数估算反而…… 不对，这里真正的问题通常是：数据量大、统计信息过期，或优化器预估用 idx_user_id 后回表太多。
Step 4 对症下药（按优先级）：
-- 1) 先更新统计信息（10% 概率就是它）
ANALYZE TABLE orders;
-- 2) 覆盖索引：把查询要的列全塞进联合索引，免回表 + 免 filesort
ALTER TABLE orders ADD INDEX idx_user_status_ctime (user_id, status, created_at);
-- 现在 EXPLAIN 应显示：type=ref, key=idx_user_status_ctime, Extra=Using index condition（无 filesort）Step 5 验证（必须实测，不能只看 EXPLAIN）：
EXPLAIN SELECT ... ; -- 看 type/key/Extra 变化
-- 开 profiling 看真实耗时分布（EXPLAIN 只是预估！）
SET profiling = 1;
SELECT * FROM orders WHERE user_id = 12345 AND status IN (1, 2)
ORDER BY created_at DESC LIMIT 20;
SHOW PROFILES; -- 对比优化前后的真实耗时
-- 防缓存干扰：SQL_NO_CACHE + 多跑几次取均值
SELECT SQL_NO_CACHE ... ; -- 8.0 缓存默认关闭，重点是多次执行取 P50/P99
-- 优化前：1.2s；优化后：15ms → 80 倍提升验证铁律：优化必须用 EXPLAIN + **真实执行时间（profiling / 多次实测）**双重验证；生产上线走灰度；每次优化只改一处，变量隔离才能归因。
第六步：实战避坑总结（血泪教训）
- 别只看
rows，优先看Extra：Using temporary和Using filesort出现就意味着必有大坑，优先干掉它们（对应手段三），它们的危害比rows大一个量级。 - 监控实际耗时，别信 EXPLAIN 的预估：
EXPLAIN是估算，必须SET profiling=1; SHOW PROFILES;看真实耗时；对比测试用SQL_NO_CACHE+ 多次执行取均值（避免缓存干扰）。 - 优化是"对症下药"不是"套餐式"：一次只改一处、改完必验证、上线必灰度——否则多个变量混在一起，永远不知道是谁起的作用。
- 终极底线：如果上面六类手段都做透了，单次查询依然超过 1 秒，请放弃纯数据库解决方案——读多写少的查询接 Redis 缓存，复杂搜索/全文检索接 Elasticsearch，统计分析接 OLAP/数仓。数据库不是万能的，别死磕。
面试追问（能连答三层）
- Q：为什么 rows 很大但索引还是没用？ → 优化器比较的是"全表扫成本 vs 走索引+回表成本"，回表比例超过阈值（约 20% 行数）时全表扫更便宜；也可能统计信息过期导致成本估算错误（先
ANALYZE TABLE）。 - Q：NLJ 和 BNL 有什么区别？ → NLJ 是逐行嵌套循环查被驱动表；BNL 先把驱动表批量读进 join buffer，再一次性与被驱动表匹配，减少被驱动表访问次数（用空间换 IO）；BNL 是被驱动表无索引时的兜底，
Using join buffer出现就是它——调大join_buffer_size只能缓解，建索引才是根治。 - Q：什么情况下该用 FORCE INDEX？ → 统计信息已更新、优化器仍选错（OPTIMIZER_TRACE 里能看到它算了但没选）时的线上止血手段；长期要靠更优的索引设计或 SQL 改写，Hints 会随索引变更失效。
- Q：分库分表和分区表怎么选？ → 分区表解决"扫描量"（数据还在同一实例，单表过大、冷热明显时用）；分库分表解决"并发写与容量"（单实例扛不住时用）。顺序：冷热分离 → 分区表 → 分库分表，别一上来就分库分表。
串起来
慢查询优化是一条流水线：慢日志发现 → pt-query-digest 按总耗时排序 → EXPLAIN 看 type/key/rows/Extra → OPTIMIZER_TRACE 看优化器决策；索引合理还慢时，上六大类进阶手段：重构 SQL 减量（覆盖索引/延迟关联/拆 JOIN）、根治隐式转换与函数、深挖排序分组陷阱、数据量大的物理手段（冷热分离/分区表/分库分表）、调整锁与事务姿势、Hints 干预执行计划；最后用 profiling 实测 + SQL_NO_CACHE 验证，真优化不动就上 Redis / ES 兜底。掌握了这条链路，面试里任何"一条 SQL 慢怎么办"都能给出从工具到根因、从 SQL 到架构的完整回答。
下一篇进入 主从复制与高可用：单机 MySQL 扛不住读压力、也怕宕机丢数据，主从架构是怎么解决这两个问题的？