MySQL 内核 · 执行计划与慢查询优化

索引建了、事务也对了，线上照样会冒出「一条 SQL 把 CPU 打满」。优化器有时选错路，开发有时写出让索引失效的谓词，运营后台则爱 LIMIT 100000,20。本篇站在内核与执行计划一侧，讲清：EXPLAIN 看什么、优化器怎么估价、常见失效与 JOIN/分页怎么改——广告配置库与报表库的日常兵器。

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

MySQL 内核（开篇）· InnoDB 存储结构与 B+ 树索引

MySQL 内核 · 事务隔离、MVCC 与锁

MySQL 内核 · redo log、undo log 与 binlog

MySQL 内核 · 执行计划与慢查询优化（本篇）

MySQL 内核 · 主从复制与高可用

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

TL;DR

路径：SQL → 解析 → 优化器（代价模型） → 执行计划 → 执行器；EXPLAIN 看见的是优化器结论。
先看四列：type（访问类型）、key（用了哪个索引）、rows（估算行数）、Extra（filesort / temporary / Using index…）。
索引失效高频因：列上函数/运算、隐式类型转换、最左前缀断裂、% 前导模糊、坏 OR、统计信息过期致选错索引。
JOIN：Nested-Loop 家族为主（含 Index NLJ、Block NLJ）；8.0 等值大表可 Hash Join；原则是小结果集驱动、被驱动表走索引。
深分页：大 OFFSET 会扫后丢；改用 keyset（WHERE id > ?）或延迟关联先取主键。
治理闭环：慢日志 → digest Top N → EXPLAIN ANALYZE → 改 SQL/索引 → 回归扫描行与 BP。

Open Table of contents

1. 优化器在干什么
2. EXPLAIN 实战读法
3. 索引失效与选错
4. JOIN：算法与原则
5. 深分页与慢日志治理
- 5.1 OFFSET 的代价
- 5.2 慢查询流水线
6. 一张「改之前先自问」清单
参考

1. 优化器在干什么

InnoDB 负责存与取页；优化器负责在多条「能算出正确答案」的路径里挑估算代价最低的一条：用哪个索引、表连接顺序、是否物化临时表、是否 filesort 等。依据主要是：

表与索引的基数 / 直方图等统计信息；
IO、CPU 的代价常数；
启发式规则（常量传播、条件消除等）。

统计过期 → 估错 rows → 选错索引：广告配置表剧烈更新后偶发「计划飘了」，需要 ANALYZE TABLE 或开持久化统计。

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

EXPLAIN 是估算；EXPLAIN ANALYZE 才给你真实时间与实际行数（MySQL 8.0.18+）。

2. EXPLAIN 实战读法

2.1 `type`：从好到差的直觉

常见由优到劣大致：system / const → eq_ref → ref → range → index → ALL。

const / eq_ref：主键或唯一键等值，理想；
ref：非唯一索引等值，广告里 advertiser_id=? 常见；
range：范围、IN 列表等；
index：扫整棵索引（仍可能优于全表）；
ALL：全表扫——大表上通常要警惕。

2.2 `key` / `possible_keys` / `rows`

possible_keys 候选；key 实际选用；key=NULL 先怀疑。
rows 是估算扫描行；结合 filtered 看有效过滤比例。
估算远大于真实或反之，都提示统计或谓词问题。

广告配置表里 status 往往极倾斜（大量下线、少量在线）。优化器若低估/高估过滤性，可能弃用看起来「完美」的联合索引。此时更新统计、加直方图，或把「永远只要在线」写成生成列/分区冷热，比盲目 FORCE INDEX 健康。

2.3 `Extra` 危险信号

Using filesort：额外排序，大结果集很痛；
Using temporary：临时表，易爆内存/磁盘；
Using where：存储引擎回传后再过滤（不一定坏）；
Using index：覆盖索引，通常好事；
Using join buffer：被驱动表没走好索引时的补救。

一张「看完 EXPLAIN 下一步干什么」的口诀：

type 太差 → 先造/改索引或改写谓词；
key 为空或离谱 → 查隐式转换、统计、最左前缀；
rows 巨大且 filtered 低 → 过滤条件没推下去；
filesort / temporary → 看能否用索引顺序或缩小排序集；
一切「好看」仍慢 → 上 EXPLAIN ANALYZE 找真正耗时的算子（往往在回表或网络）。

3. 索引失效与选错

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

写 SQL 时先问：条件能否「直接贴」在索引列上，还是被函数/隐式转换包住了。

补充几点 AdTech 常见写法：

-- 坏：函数包住列
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=? 爱莫能助（除非 ICP 等特例且仍受限）。选错索引时，可短期 FORCE INDEX，长期应修统计或索引设计。

4. JOIN：算法与原则

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

小表（小结果）驱动大表；被驱动表条件必须能走索引。

Index Nested-Loop：对外表每一行，用索引点查内表——维表关联配置的主力。
Block Nested-Loop：用 join buffer 缓存外表一块，减少内表扫描次数。
Hash Join（8.0）：等值连接、内表建哈希，适合大表且缺合适索引时。

多表关联配置与计费维表时，确保驱动顺序合理：先过滤出小 campaign 集合，再关联创意，而不是先笛卡儿再 where。

STRAIGHT_JOIN 能强制顺序，只适合救急；长期应靠正确统计与索引让优化器自己选对。子查询在老版本易物化成烂计划，8.0 有不少改善，但仍建议对 Top SQL 用 EXPLAIN ANALYZE 看实际时间落在哪一步。

IN 列表过长（成百上千个 campaign_id）会让优化器成本估计失真，甚至宁愿全表；广告定向圈选这类场景更适合：临时表 / JOIN 一张 id 表，或打散批次，而不是把巨大 IN 糊进 SQL。

5. 深分页与慢日志治理

5.1 OFFSET 的代价

LIMIT 100000, 20 意味着引擎可能拿走 100020 行再丢 100000——越往后越慢。运营后台「跳页到末尾」能直接打穿数据库。

对策：

-- keyset / seek 分页
SELECT * FROM creative
WHERE advertiser_id = ? AND id > ?
ORDER BY id
LIMIT 20;

-- 延迟关联：先索引取 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 慢查询流水线

打开慢日志（或观察 Performance Schema）；
pt-query-digest / 自研聚合找出 Top SQL；
EXPLAIN / EXPLAIN ANALYZE；
改 SQL / 索引 / 归档；
回归看扫描行与锁等待，而非只看「感觉快了」。

配置变更走主库、重报表走从库或 OLAP，是读写分离与列存 OLAP 的分界——别让分析扫库和决策点查抢同一块 Buffer Pool。

慢日志阈值建议按业务定：决策库可以 100ms 就记；报表库也许 1s。关键是持续看 Top N，而不是只在雪崩时翻日志。把「新增慢 SQL」接进发布门禁（变更后自动对比 digest）能拦住一大批回归。

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

对着可疑 SQL，按顺序问：

有没有合适索引？谓词列、顺序、覆盖？
谓词有没有破坏索引？函数、隐式转换、前导 %？
扫描行是否合理？rows 与 EXPLAIN ANALYZE 实际行差多少？
要不要排序/临时表？能否用索引顺序消掉 filesort？
JOIN 驱动顺序？小结果是否在前、被驱动是否 ref/eq_ref？
分页是否在深翻？能否 keyset？
是否不该打 OLTP 库？聚合报表是否该去从库 / ClickHouse / Doris？

示例：创意列表慢查询对照。

-- 慢：大 OFFSET + 非覆盖排序
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 看为何选/弃某索引；直方图（8.0）改善倾斜列（如大量 status=0）的代价估计。短期 FORCE INDEX 只能当止血，不是长期方案。

计数类查询也要注意：SELECT COUNT(*) 在 InnoDB 不是「免费元数据」，大表精确计数很贵——广告报表的「大概有多少创意」可用近似、汇总表或 OLAP，别拿 OLTP 硬扛实时精确 count。

参考

MySQL 官方文档. EXPLAIN Output Format
MySQL 官方文档. Optimizing SELECT Statements
MySQL 官方文档. Nested-Loop Join Algorithms
MySQL 8.0. EXPLAIN ANALYZE
MySQL 官方文档. Controlling the Query Optimizer（hints、优化器开关）
《高性能 MySQL》查询性能优化章节。