Charles Shao
Go back

MySQL 内核深挖 · EXPLAIN 计划与慢查询优化

–views

上一篇我们梳理了事务隔离与日志流转，但在实际的计费与投放业务中，即便锁冲突与复制延迟已被抚平，线上照样会冒出「一条 SQL 把 CPU 打满」的险情：报表导出卡在深分页、配置后台一条 IN 圈选扫穿整张投放表、优化器放着覆盖索引不用偏走全表。本篇我们将视线从存储引擎上移，站在内核优化器与执行计划一侧，讲清 EXPLAIN 看什么、优化器怎么估价、常见失效与 JOIN/分页怎么改——这是守住广告配置库与报表库性能边界的日常兵器。

本文是 MySQL 内核 系列的第 4 篇（执行计划与慢查询优化）。 全系列 5 篇：

  1. MySQL 内核深挖 · InnoDB 存储结构与 B+ 树索引
  2. MySQL 内核深挖 · 事务隔离级别、MVCC 与行锁
  3. MySQL 内核深挖 · redo、undo 与 binlog 机制
  4. MySQL 内核深挖 · EXPLAIN 计划与慢查询优化（本篇）
  5. MySQL 内核深挖 · 主从复制链路与 HA 高可用

一句话定位：慢查询治理 = 读懂执行计划 + 让谓词与索引形状匹配 + 控制扫描行数与排序/临时表；先量（慢日志 / EXPLAIN ANALYZE），再改，最后才谈硬件。

TL;DR

Table of contents

Open Table of contents

1. 优化器在干什么

在我们讨论过的 InnoDB 层，核心职责是存与取 16KB 的内存页，而位于 Server 层的优化器，则负责在多条「都能算出正确答案」的执行路径里，挑出估算代价最低的那一条。究竟是走聚簇索引还是二级索引，表连接的顺序如何排布，以及是否需要物化临时表或触发文件排序，都由它结合多维度指标来定夺。这种决策机制主要依赖三类基准：表与索引的基数 / 直方图等统计数据，评估 IO 与 CPU 消耗的代价常数，以及常量传播、条件消除等启发式重写规则。

正因如此，统计信息一旦过期，rows 的估算就会大幅偏离，导致索引自然选错。例如在广告配置表中发生剧烈的分批下线操作后，业务偶发「执行计划飘了」的告警，根因往往就在于引擎与实际数据分布状态不一致。此时，与其盲目改写 SQL，不如优先通过 ANALYZE TABLE 刷新统计数据或开启持久化统计来兜底。

从 SQL 经 Parser、Optimizer 到执行计划与 Executor，以及 EXPLAIN 关键列。

EXPLAIN 给出的仅是基于代价模型的估算，唯有 EXPLAIN ANALYZE 才能揭示各个算子真实的耗时与实际输出行数（MySQL 8.0.18+ 引入）。

2. EXPLAIN 实战读法

既然优化器掌控着全局路线图，下一步便是学会读懂它的输出结论——EXPLAIN，从而透视 SQL 在底层的流转脉络。

2.1 type：从好到差的直觉

type 字段刻画了访问数据源的具体方式，按性能由优到劣大致排列为：system / const → eq_ref → ref → range → index → ALL。

2.2 key / possible_keys / rows

possible_keys 代表优化器眼中的备选方案，而 key 是最终拍板的索引；若 key=NULL，就必须优先排查谓词是否失效。rows 给出的是估算扫描行数，我们还需结合 filtered 字段来推演有效结果集的过滤比例。当估算值远大于真实数据量，或反之远小于实际规模时，都强烈提示统计信息滞后或谓词设计存在逻辑缺陷。

以广告配置表为例，status 字段往往呈现极度倾斜（海量历史下线记录，少量实时在线数据）。优化器一旦低估或高估其数据选择性，就可能弃用原本「完美匹配」的联合索引。针对这种场景，主动更新统计信息、引入直方图，或者把「永远只要在线」这一条件剥离成生成列并配合冷热分区，往往比强加 FORCE INDEX 更具长期健壮性。

2.3 Extra 危险信号

Extra 字段承载了执行过程中的附加动作，往往是排查隐蔽性能杀手的关键：

看完 EXPLAIN 后，我们能快速建立一条研判准则：若 type 层级太差，优先造索引或重写谓词；若 key 未命中，重点查隐式转换与最左前缀；若 rows 巨大且 filtered 极低，说明过滤条件未能有效下推；遭遇 filesort 或 temporary 时，则需考察能否利用索引天然的有序性来消除额外开销；若是各项指标堪称完美却依然缓慢，那就果断祭出 EXPLAIN ANALYZE 去追踪真实耗时算子，瓶颈往往藏在海量回表的随机 IO 或网络传输中。

3. 索引失效与选错

读懂了执行计划的各列指标后，我们在实战中真正高频遭遇的性能坠崖，往往是因为精心设计的索引被非标准的谓词「绕开」，或是被优化器的代价模型错选。由于 B+ 树的有序性建立在原始值之上，一旦我们在条件中嵌套函数，整棵树的搜索机制便会瞬间瘫痪。

六类高频索引失效/用错场景与对策卡片。

在撰写 SQL 时应当反复自省：过滤条件能否「干净地」贴合在索引列上，还是被函数调用与隐式类型转换严严实实地包裹住了。

针对广告投放流水库等高并发场景，补充几点极易踩坑的写法：

-- 坏：函数包住列，导致只能全表扫描
WHERE DATE(created_at) = '2026-07-14'
-- 好：转化为区间范围查询，完美利用索引有序性
WHERE created_at >= '2026-07-14' AND created_at < '2026-07-15'

-- 坏：varchar 列与数字比较，触发隐式转换而导致索引失效
WHERE campaign_code = 10086
-- 好：保持类型严格匹配
WHERE campaign_code = '10086'

此外，最左前缀原则一旦断裂，就会毫无悬念地出现「明明建了联合索引却退化为 type=ALL」的窘境：例如 idx(a,b) 面对仅包含 WHERE b=? 的查询时只能爱莫能助（除非触发索引下推等特例且效果受限）。当发现优化器因统计偏差选错索引时，短期内固然可以通过 FORCE INDEX 来止血，但长期来看，必须回到梳理统计数据与重构索引设计的正轨上。

4. JOIN：算法与原则

单表的索引失效尚可通过改写 SQL 来挽救，而一旦踏入多表关联的深水区，执行计划的崩塌往往牵一发而动全身。

Simple/Index/Block Nested-Loop 与 Hash Join，以及深分页坏写法与 keyset/延迟关联对照。

多表关联的铁律：永远坚持小结果集驱动大表，并确保被驱动表的连接条件能够精准走索引。

在 MySQL 的演进历程中，处理 JOIN 的底层算法也在不断丰富：

在处理多张计费维表的复杂关联时，务必保障驱动顺序的合理性：应当优先通过谓词过滤出极小的 campaign 结果集，再以此为基础去关联海量的创意表，绝对要杜绝先引发局部笛卡尔积再执行 WHERE 过滤的粗暴作法。虽然 STRAIGHT_JOIN 能够强行锁定连接顺序，但它仅适合应急救火；长治久安之策依然是依托精准的统计信息与索引结构，让优化器心甘情愿地选对路径。需要强调的是，老版本中子查询极易被物化成劣质计划，尽管 MySQL 8.0 在此有了长足进步，仍强烈建议针对头部慢 SQL 运用 EXPLAIN ANALYZE，精准定位时间到底损耗在了哪一层循环。

另一方面，超长 IN 列表的破坏力同样不容小觑：当 SQL 强行圈选成百上千个 campaign_id 时，不仅会让优化器的成本估算彻底失真，甚至会迫使其倒戈选择全表扫描。面对广告定向圈选这类典型场景，将其转化为临时表 JOIN、预先构建 ID 映射表或是分批次打平处理，远比将巨大的 IN 集合硬塞进单一语句更契合数据库的运行心智。

5. 深分页与慢日志治理

将 JOIN 顺序与单表索引理顺之后，深分页查询与慢日志的常态化治理，便是彻底守住库表稳定性的收尾两道关。

5.1 OFFSET 的代价

执行诸如 LIMIT 100000, 20 的翻页语句，本质上意味着引擎必须沿着叶子链表范围扫描足足 100020 行数据，在承受了大量回表读取聚簇索引的 IO 代价后，最终却在 Server 层被无情地丢弃掉前 100000 行。这种越往后翻越慢的机制，使得运营后台一次随意的「跳页到末尾」操作，就能直接打穿数据库的 CPU 与 IO 资源。应对之策在于彻底改写分页模型：

-- 推荐方案 1：keyset / seek 分页（记住上一页的边界）
SELECT * FROM creative
WHERE advertiser_id = ? AND id > ?
ORDER BY id
LIMIT 20;

-- 推荐方案 2：延迟关联（先利用覆盖索引极速取下 id，再精准回表）
SELECT c.*
FROM (
  SELECT id FROM creative WHERE advertiser_id=? ORDER BY id LIMIT 100000, 20
) t
JOIN creative c ON c.id = t.id;

5.2 慢查询流水线

治理慢查询不应停留在单次救火，而必须构建一条完整的自动化流水线：

  1. 开启慢日志采集（或深度整合 Performance Schema）；
  2. 依托 pt-query-digest 或自研聚合平台，精准捕捉消耗资源的 Top N 语句；
  3. 挂载 EXPLAIN 与 EXPLAIN ANALYZE 剖析真实执行代价；
  4. 推进 SQL 重构、索引补全或历史数据归档；
  5. 发布后严密回归观测扫描行数波动与锁等待状态，绝不能仅凭「体感变快了」就草草结案。

关于慢日志的记录阈值，建议根据业务场景的严苛程度灵活定制——承接高并发点查的决策库大可设定在 100ms，而跑批分析为主的报表库放宽至 1s 亦无不可。关键在于建立持续观测 Top N 的排查机制，而非等到库表雪崩才仓促翻看日志。将「新增慢 SQL 检测」无缝接入发布门禁，在变更后自动对比 digest 摘要，能够从源头拦住一大批性能倒退。

这也解释了为什么我们要严格划定系统边界：将高频配置变更留在主库，而将重度的聚合报表引流至从库或专业 OLAP 引擎。这不仅是 读写分离 与 列存 OLAP 的分水岭，更是为了确保分析型大查询与决策型点查永远不要去竞争同一块 Buffer Pool 的内存红利。

6. 一张「改之前先自问」清单

为了避免在复杂的查询面前迷失焦点，我们将前面这些零散的实战经验收敛成一张可复用的自问清单。在动手修改任何可疑 SQL 之前，请严格按顺序拷问自己：

  1. 有没有合适索引？谓词列是否精准覆盖、排列顺序是否契合、能否利用覆盖索引直接返回？
  2. 谓词有没有破坏索引？是否存在隐式类型转换、函数包裹、或是前导 % 引发的沿叶子链表范围扫描？
  3. 扫描行是否合理？EXPLAIN 估算的 rows 与 EXPLAIN ANALYZE 的实际触碰行数究竟存在多大鸿沟？
  4. 要不要排序/临时表？能否巧妙利用 B+ 树索引天然的有序性，直接将高昂的 filesort 开销彻底消灭？
  5. JOIN 驱动顺序？是否坚守了小结果集在前、且被驱动表能够通过 ref/eq_ref 极速匹配？
  6. 分页是否在深翻？面对大 OFFSET 场景，能否转换为 keyset 或延迟关联机制？
  7. 是否不该打 OLTP 库？当查询涉及海量聚合时，是否早该将这部分重负载剥离至从库、ClickHouse 或 Doris 中？

以一段典型的创意列表慢查询为例，我们可以清晰地看到不同决策下的性能鸿沟：

-- 慢：承受巨大 OFFSET 开销 + 非覆盖排序导致的 filesort
SELECT * FROM creative
WHERE advertiser_id = 42
ORDER BY updated_at DESC
LIMIT 50000, 50;

-- 快：游标分页 + 仅提取必备列，完美吃满索引红利
SELECT id, title, status, bid, updated_at
FROM creative
WHERE advertiser_id = 42
  AND (updated_at, id) < (?, ?)   -- 锁定上一页最后一条的边界值
ORDER BY updated_at DESC, id DESC
LIMIT 50;
-- 配合此查询的最佳索引形态：KEY(advertiser_id, updated_at, id, title, status, bid)

在线上排查陷入僵局时，我们还有备用武器：利用 optimizer_trace 揪出优化器放弃某条索引的真实动因，或通过直方图（MySQL 8.0）大幅改善数据严重倾斜列（例如海量的 status=0）的代价估算精度。务必铭记，短期的 FORCE INDEX 只能充当止血绷带，绝不能作为长治久安的技术方案。

此外，针对计数类查询也需保持敬畏：在 InnoDB 引擎中，SELECT COUNT(*) 从来就不是「免费的元数据读取」，大表上的精确计数需要付出高昂的遍历代价。在广告报表这类仅需知晓「大概有多少创意」的场景下，引入近似计数、维护汇总表或旁路至 OLAP，才是避免拖垮 OLTP 主库的明智之举。

慢查询排障口诀：看 type 优劣；查 key 命中；对 rows 估算；灭 filesort；EXPLAIN ANALYZE 探底耗时。

理解了优化器如何挑选路径，接下来就必须直面它最终将数据落盘并分发到整个集群的过程。一旦单机遭遇瓶颈，我们又该如何利用 主从复制与高可用 架构，将这些优化后的读写流量平滑摊薄。

参考

–views
Share this post on:
Share this post via WhatsApp Share this post on Facebook Share this post on X Share this post via Telegram Share this post on Pinterest Share this post via email
Previous Post
数据预处理：洗菜择菜的三步——观察、清洗、整理
Next Post
特征工程系统化框架：能用哪些料、料从哪进、料坏了怎么报警