05.1.3.1 执行计划与代价模型
所属:全栈开发面试题库 / 05 数据库与存储 / 05.1 关系型数据库通用能力 / 05.1.3 SQL 优化与执行计划
覆盖范围
- 执行计划形态:文本计划、图形计划、实际执行计划(ANALYZE)
- 访问路径:全表扫描、索引扫描、索引查找、回表
- 连接算法:嵌套循环、哈希连接、归并连接与连接顺序
- 代价估算:IO/CPU 代价、选择率、行数估算与偏差
- MySQL
EXPLAIN与 PostgreSQLEXPLAIN (ANALYZE, BUFFERS)输出解读 - 排序、临时表、落盘判断,计划缓存与计划稳定性
高频题速览
| 编号 | 题目 | 难度 | 频率 |
|---|---|---|---|
| 05.1.3.1-001 | 执行计划应该看哪些信息 | 基础 | ★★★★★ |
| 05.1.3.1-002 | MySQL EXPLAIN 字段解读 | 进阶 | ★★★★★ |
| 05.1.3.1-003 | PG EXPLAIN ANALYZE BUFFERS 解读 | 进阶 | ★★★★★ |
| 05.1.3.1-005 | 索引扫描、索引查找与回表 | 进阶 | ★★★★★ |
| 05.1.3.1-011 | 估算行数与实际行数偏差 | 进阶 | ★★★★★ |
计划解读主线
SQL → 解析 → 优化器按代价选计划 → 执行计划(树状节点)
每个节点看:访问路径 / 输出行数 / 代价 / 实际行数与缓冲
连接层:连接算法 + 连接顺序(小结果集驱动大结果集)
排序聚合:是否临时表、是否落盘、是否 filesort
05.1.3.1-001 看一条 SQL 的执行计划,主要看哪些信息?
频率 ★★★★★ | 难度 基础
一句话结论
执行计划先看访问路径(是否全表扫描、走了哪个索引、是否回表),再看估算行数与实际行数偏差、连接方式与顺序、排序/聚合是否落盘,最后看代价与 IO/CPU 占比;不能只盯"有没有走索引"。
概念解释
- 访问路径:优化器为每个表选择的读取方式,是计划最核心的信息。
- 行数估算(rows):优化器基于统计信息估算的输出行数;与实际行数偏差大,说明统计信息或选择率估算有问题。
- 连接方式:多表之间用嵌套循环、哈希还是归并连接,以及哪张表先被驱动。
- 代价(cost):优化器模型估算的 IO + CPU 成本,用于比较候选计划。
原理流程
- 从计划根节点向下逐层看:每个节点处理什么、输出多少行。
- 先定位最慢的节点(全表扫描、大表连接、排序落盘),再看它的估算与实际偏差。
- 对照 SQL 语义判断计划是否符合预期:走索引、避免回表、连接顺序合理。
代码 / 命令
-- MySQL:查看计划(不实际执行)
EXPLAIN
SELECT o.id, o.amount, u.name
FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.created_at >= '2026-01-01';
-- PostgreSQL:实际执行并输出实际行数、耗时、缓冲
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.amount, u.name
FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.created_at >= '2026-01-01';
常见误区
- 误区:计划里只要出现"索引"就是好计划。正解:还要看索引是否真的过滤出少量行、回表代价是否被高估或低估;低区分度索引可能比全表扫描更差。
- 误区:
cost绝对值可以跨 SQL、跨库直接比较。正解:代价是优化器模型内部估算值,不同引擎、不同参数下口径不同,同一计划内横向比较节点才有意义。
追问
- 问:静态计划和实际执行计划有什么区别?答:静态计划只给估算值;
EXPLAIN ANALYZE(PG)或 MySQL 的ANALYZE执行后给出实际行数、实际耗时与缓冲,能直接暴露估算偏差。 - 问:计划树里哪个节点最该先看?答:先找输出行数大、代价占比高、或带排序/临时表/全表扫描标记的节点,从那里定位根因。
05.1.3.1-002 MySQL EXPLAIN 的关键字段怎么读?
频率 ★★★★★ | 难度 进阶
一句话结论
MySQL EXPLAIN 每行对应一张表的访问路径;重点看 type(访问类型)、key(实际索引)、rows(估算扫描行)、Extra(额外信息如回表、临时表、排序),key 为 NULL 或 type 为 ALL 通常需要重点排查。
概念解释
id:查询中表的顺序编号;同层 id 相同,子查询 id 递增。select_type:SIMPLE / PRIMARY / SUBQUERY / DERIVED 等,标识查询角色。type:访问类型,从优到劣常见为const、ref、range、index、ALL;前三者为索引访问,ALL是全表扫描。key:优化器实际选中的索引;possible_keys是候选索引。rows:估算需要扫描的行数;filtered:估算经过条件过滤后剩下的比例。Extra:Using index(覆盖索引,无需回表)、Using where(服务层过滤)、Using filesort(额外排序)、Using temporary(临时表)。
原理流程
- 先看
type:是否为ALL,是否退化为索引全扫。 - 再看
key:是否命中预期索引;possible_keys有而key为NULL,说明优化器放弃了索引。 - 看
rows × filtered:估算最终保留多少行,判断过滤力度。 - 看
Extra:是否Using filesort、Using temporary、出现回表。
代码 / 命令
EXPLAIN
SELECT id, amount FROM orders
WHERE user_id = 1001 AND status = 'PAID'
ORDER BY created_at DESC LIMIT 20;
-- 典型关注点:
-- type=ref / key=idx_user_status / rows=估算扫描行
-- Extra 含 Using index 表示覆盖索引;含 Using filesort 表示排序未走索引顺序
常见误区
- 误区:
type=index就是好的索引访问。正解:index是扫描整个索引树(索引全扫),只比ALL少回表,范围仍很大;真正高效的是ref、range、const。 - 误区:
Extra里出现Using where一定是问题。正解:Using where只是说明条件在存储引擎返回后由服务层过滤;只要索引过滤力度足够,并不需要消除。
追问
- 问:
Using index和Using index condition(ICP)有什么区别?答:前者是覆盖索引,索引即满足所需列,不回表;后者是索引下推,把部分过滤条件下推到存储引擎,减少回表次数。 - 问:
rows是实际扫描行数吗?答:不是,是优化器基于统计信息的估算值;与实际偏差大时优先更新统计信息。
05.1.3.1-003 PostgreSQL 的 EXPLAIN(ANALYZE, BUFFERS)输出怎么读?
频率 ★★★★★ | 难度 进阶
一句话结论
PG 计划是缩进的节点树,每节点给出 cost(估算启动/总代价)、rows(估算行)、width;加 ANALYZE 后出现实际行数、实际耗时与循环次数,加 BUFFERS 显示命中/读取的缓冲块;先找实际耗时占比最高的节点。
概念解释
- 节点类型:Seq Scan(顺序扫描全表)、Index Scan(索引扫描 + 回表)、Index Only Scan(索引覆盖,不回表)、Bitmap Index Scan + Bitmap Heap Scan、Nested Loop、Hash Join、Merge Join、Sort、HashAggregate 等。
- cost:
启动代价..总代价,单位是优化器内部代价单位,不是毫秒。 - ANALYZE 实际值:
actual time毫秒、actual rows、loops;actual rows与rows差距大即估算偏差。 - BUFFERS:
shared hit(内存命中块)、read(从磁盘读块)、temp(临时文件块)。
原理流程
- 从顶层节点往下,按缩进识别父子关系:内层节点的结果喂给外层。
- 对比
actual time找耗时最高的节点;对比rows与actual rows找估算偏差。 - 看
BUFFERS:磁盘读多说明缓存未命中或数据量超内存;temp大说明排序/哈希落盘。
代码 / 命令
-- 实际执行 + 缓冲统计;注意 ANALYZE 会真正跑这条 SQL,写操作需包在事务里回滚
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.amount, u.name
FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.created_at >= '2026-01-01';
-- 关注:Seq Scan 还是 Index Scan;rows 与 actual rows 偏差;Buffers read/hit/temp
常见误区
- 误区:PG 计划里的
cost单位是毫秒。正解:cost 是 IO/CPU 加权的内部估算单位,不能当耗时读;耗时以ANALYZE的actual time为准。 - 误区:
Index Only Scan也会回表取数据。正解:只要所需列都在索引里,索引可见性 map 判断后直接返回索引项,不回堆表。
追问
- 问:为什么 PG 有时主动选 Seq Scan 而不用索引?答:当结果集占表比例较高时,随机回表的代价超过顺序扫描全表,优化器按代价选全表扫描;这通常是合理的。
- 问:
loops是什么意思?答:该节点被外层驱动循环执行的次数;嵌套循环下内层节点的actual time × loops才是它的总耗时。
05.1.3.1-004 全表扫描(Seq Scan / ALL)在什么情况下出现,是否一定差?
频率 ★★★★☆ | 难度 进阶
一句话结论
全表扫描在结果集占比高、无可用索引、索引区分度低或优化器判断回表代价更高时出现;对大表低选择性查询,全表扫描反而可能比走索引更快,不能见到 Seq Scan 就判定为坏计划。
概念解释
- 顺序扫描:按磁盘顺序依次读取表数据页,随机 IO 少;单次读全表的 IO 模式友好。
- 索引扫描 + 回表:先走索引定位行号,再按行号随机回表取数据;结果集越大,随机回表次数越多。
- 拐点:当需要回表的行数超过表的一定比例(经验上 10%~20%,随数据布局变化),顺序扫描总代价更低。
原理流程
- 优化器估算命中行数与占表比例。
- 比较两条路径:顺序扫描全表 vs 索引扫描加随机回表。
- 若索引路径代价更高,选择全表扫描;查询条件命中大比例数据时这是正常选择。
代码 / 命令
-- 命中大半张表:优化器通常选全表扫描
SELECT * FROM orders WHERE status = 'PAID'; -- PAID 占绝大多数
-- 命中极少行:才更可能走索引
SELECT * FROM orders WHERE status = 'REFUND' AND amount > 10000;
-- PG 中查看某条件的选择性
SELECT status, count(*) FROM orders GROUP BY status;
常见误区
- 误区:大表上任何 Seq Scan 都要消灭掉。正解:先看返回行数与占比;聚合全表、统计大部分行的场景,全表扫描本就是合理路径。
- 误区:全表扫描一定比索引慢。正解:顺序扫大块连续数据的吞吐很高;索引随机回表在结果集大时更慢,差异取决于缓存命中率与磁盘类型。
追问
- 问:什么情况下 Seq Scan 真的需要优化?答:条件本应命中少量行却扫了全表,说明索引缺失、索引失效或统计过期导致估算错误。
- 问:PG 如何让优化器更倾向用索引?答:核对统计信息(
ANALYZE)、调整random_page_cost(SSD 环境可调低),但不能违背实际数据分布硬推索引。
05.1.3.1-005 索引扫描、索引查找和回表分别是什么?
频率 ★★★★★ | 难度 进阶
一句话结论
索引扫描沿索引结构遍历定位键值;索引查找(ref/range)按条件在索引上定点/范围查找;非聚集/二级索引拿到主键后再去主表取整行,这一步叫回表;所需列全部在索引中即覆盖索引,无需回表。
概念解释
- 索引扫描(Index Scan):读取索引项后按行指针回表取整行数据。
- 索引查找(ref / range / const):以固定值或范围在 B+ 树中定位,扫描的索引区间小。
- 回表:二级索引叶子节点存主键(InnoDB)或行指针(PG 堆表),据此回到主表/堆取其余列。
- 覆盖索引:查询所需列都包含在索引键中,直接从索引返回,避免回表。
原理流程
- 二级索引按条件定位到叶子节点区间。
- 若只取索引列:直接返回(覆盖索引)。
- 若需要其他列:取出主键/行指针,回主表逐行取整行;回表是随机 IO,行数多时代价上升。
代码 / 命令
-- 覆盖索引:联合索引 (user_id, status, created_at) 覆盖下列所需列
SELECT user_id, status, created_at
FROM orders
WHERE user_id = 1001;
-- 需要 amount 等未入索引列时会回表
SELECT * FROM orders WHERE user_id = 1001;
-- MySQL Extra 出现 Using index 表示覆盖索引;PG 计划显示 Index Only Scan
常见误区
- 误区:建了二级索引就一定避免回表。正解:只有查询列被索引覆盖才不回表;
SELECT *几乎必然触发回表。 - 误区:回表是数据库设计缺陷。正解:回表是二级索引的固有代价;通过覆盖索引、减小查询列来减少回表,而不是去掉二级索引。
追问
- 问:联合索引如何配合覆盖索引设计?答:把高频查询的过滤列放前、返回列补进索引尾部,使查询列全部落在索引中。
- 问:PG 的二级索引回表依据什么?答:堆表行指针(ctid);索引项存 ctid,据此回堆取行。
05.1.3.1-006 嵌套循环连接(Nested Loop)是怎么工作的?
频率 ★★★★☆ | 难度 进阶
一句话结论
嵌套循环把一张表作为驱动表(外层),逐行用连接键去另一张表(内层)查找;内层每次都能走索引时效率很高,适合外层结果集小、内层有索引的场景。
概念解释
- 外层(驱动表):先被扫描的表,输出多少行就循环多少次。
- 内层:每行外层结果都去执行一次查找;内层访问路径决定单次循环代价。
- 适用条件:外层行数少,内层连接列有索引;总代价 ≈ 外层行数 × 内层单次查找代价。
原理流程
- 优化器选小结果集表作外层。
- 外层逐行取连接键值。
- 内层按连接键查索引(ref/range),匹配后拼接输出;外层行数越大,内层被调用次数越多。
代码 / 命令
-- users 行数少且有主键,orders.user_id 有索引时典型嵌套循环
EXPLAIN
SELECT u.name, o.amount
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.city = '昆明';
-- 计划中:Nested Loop 上方是外层(users),下方是内层(orders,走 idx_user_id)
常见误区
- 误区:嵌套循环一定是最差的连接方式。正解:外层小、内层有索引时,嵌套循环能在匹配第一批行后尽早返回,延迟往往优于哈希连接。
- 误区:两张表谁在前就是谁驱动。正解:计划里的父子关系才是驱动关系;外层(父节点)循环驱动内层(子节点),与 SQL 中
JOIN书写顺序无关。
追问
- 问:外层结果集很大时嵌套循环为什么差?答:内层被重复扫描次数随外层行数膨胀,总代价 = 外层行数 × 内层单次代价,容易远超哈希连接。
- 问:MySQL 与 PG 的嵌套循环语义一样吗?答:核心机制相同;PG 计划节点名
Nested Loop,MySQL 对应计划描述中也体现外层驱动内层的循环结构。
05.1.3.1-007 哈希连接(Hash Join)是怎么工作的?
频率 ★★★★☆ | 难度 进阶
一句话结论
哈希连接先扫描较小的输入构建内存哈希表(以连接键为哈希键),再扫描另一输入逐行探测匹配;适合两表都较大、连接键无索引或等值连接、结果集大的场景。
概念解释
- 构建侧(build):选择行数较少的输入,按连接键哈希放入内存哈希表。
- 探测侧(probe):扫描另一输入,对连接键算哈希并在表中查找匹配。
- 等值连接专用:哈希连接只支持等值条件;非等值连接需用嵌套循环或归并。
- 落盘:哈希表超过内存容量时,分批落盘(PG 称 hash spill / 临时文件),磁盘 IO 上升。
原理流程
- 优化器估算两表行数,选小表作构建侧。
- 遍历构建侧,连接键哈希分组存入哈希表。
- 遍历探测侧,逐行探测哈希表,命中即拼接输出。
代码 / 命令
-- 两表数据量都不小、等值连接、无合适索引时,计划常出现 Hash Join
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.category_name
FROM orders o
JOIN categories c ON c.id = o.category_id;
-- PG 计划节点:Hash Join;子节点一个 Build 侧 Hash,一个 Probe 侧 Seq Scan
常见误区
- 误区:哈希连接比嵌套循环"更高级"。正解:两者按数据量与索引条件选型;小结果集嵌套循环延迟更低,大结果集哈希连接更稳。
- 误区:哈希连接不会落盘。正解:内存装不下哈希表时分批写临时文件,
BUFFERS里temp上升;落盘后性能明显下降。
追问
- 问:哈希连接支持非等值连接吗?答:不支持,哈希只能按等值键匹配;
>、<、BETWEEN等条件走嵌套循环或归并连接。 - 问:构建侧选错有什么后果?答:构建侧过大时哈希表占内存甚至落盘,探测代价上升;优化器依据行数估算选构建侧,估算偏差会导致选型变差。
05.1.3.1-008 归并连接(Merge Join)是怎么工作的?
频率 ★★★☆☆ | 难度 进阶
一句话结论
归并连接要求两输入按连接键有序,各自排序后双指针顺序扫描匹配;连接键已有索引顺序时可免排序,等值/等值范围内连接效率高,是有序大表连接的可选路径。
概念解释
- 有序输入:两表按连接键排序,或索引本身提供有序遍历。
- 双指针合并:两侧指针同时前进,键相等输出匹配,较小一侧指针前移。
- 适用场景:两表都大、连接键有序、需要保持连接键顺序输出;有范围条件时也可配合。
原理流程
- 确保两侧输入按连接键排序(无索引顺序则先 Sort)。
- 两个指针分别指向两侧首行。
- 比较键值:相等则输出并推进;键小的一侧推进;直到任一侧扫完。
代码 / 命令
-- 两表连接键上都有 B+ 树索引、数据按索引有序时,可能出现 Merge Join
EXPLAIN
SELECT o.id, u.name
FROM orders o JOIN users u ON u.id = o.user_id
ORDER BY o.user_id;
常见误区
- 误区:归并连接一定需要显式排序。正解:连接键索引提供有序扫描时,计划可直接合并,省去 Sort 节点。
- 误区:归并连接是默认首选连接方式。正解:它依赖输入有序;无序数据先排序的代价可能高于哈希连接,选型由优化器按代价决定。
追问
- 问:三种连接算法怎么选?答:小外层 + 内层索引选嵌套循环;大结果集等值连接选哈希;连接键已有序或需保持顺序选归并。
- 问:归并连接能处理重复键吗?答:可以,一侧重复键会与另一侧所有匹配行合并,双指针在重复区间内继续比对。
05.1.3.1-009 多表连接顺序由什么决定?
频率 ★★★★☆ | 难度 高级
一句话结论
连接顺序由优化器按代价与选择率决定:优先把过滤后结果集小的表作为驱动表,使后续连接的行数尽早收敛;SQL 里 JOIN 的书写顺序不决定执行顺序(除非优化器锁定顺序)。
概念解释
- 驱动表:连接树中最外层先被扫描的表。
- 选择率(selectivity):条件过滤后剩余行占原表比例,比例越小越适合先做驱动。
- 连接顺序空间:N 表连接有多种排列,优化器在受预算限制的搜索范围内选最低代价组合。
- 顺序锁定:部分语法或优化器行为会固定连接顺序(如 MySQL 直连写法、PG
STRAIGHT_JOIN等价提示),需谨慎使用。
原理流程
- 优化器评估各表过滤条件后的估算行数。
- 枚举连接组合,估算每种顺序下中间结果集大小与总代价。
- 选代价最低的连接树;中间结果集尽早变小,通常总代价更低。
代码 / 命令
-- 计划中从父节点向下读:先访问的表即驱动表
EXPLAIN
SELECT o.id, u.name, c.category_name
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN categories c ON c.id = o.category_id
WHERE u.city = '昆明' AND c.active = true;
-- 过滤最强的 users / categories 通常先被收缩,再驱动 orders
常见误区
- 误区:
JOIN写在最前面的表先执行。正解:书写顺序与执行顺序无关,由优化器按代价决定。 - 误区:永远小表驱动大表。正解:更准确的说法是"过滤后中间结果集小的一侧先做驱动";大表若有强过滤条件,也可能先被收缩后再驱动。
追问
- 问:连接顺序选错会怎样?答:中间结果集膨胀,后续连接与哈希/排序代价成倍上升,表现为慢查询。
- 问:如何锁定连接顺序排查问题?答:可用优化器提示(如 MySQL
STRAIGHT_JOIN)做对照验证,但生产环境不应长期依赖提示锁顺序。
05.1.3.1-010 优化器的代价模型是怎么估算代价的?
频率 ★★★★☆ | 难度 高级
一句话结论
代价模型按 IO 代价 + CPU 代价加权估算:读取数据页、遍历记录、比较与排序等操作各有权重系数,结合统计信息估算的行数与宽度,得到每个节点的启动代价与总代价,再选代价最低的计划。
概念解释
- IO 代价:读取磁盘/缓存页的成本;顺序读与随机读权重不同(PG 用
seq_page_cost、random_page_cost等参数调节)。 - CPU 代价:记录比较、过滤、投影、聚合等处理成本。
- 行数与宽度:估算输出行数与平均行宽,决定扫描与传输规模。
- 系数可调:部分参数允许按磁盘类型(SSD/HDD)调整 IO 权重,影响索引与全表扫描的取舍。
原理流程
- 统计信息给出表行数、列分布、索引基数等输入。
- 对每个候选访问路径/连接方式估算行数与页访问量。
- 按 IO/CPU 系数折算代价,自底向上汇总到计划根节点。
- 比较候选计划总代价,选最低者。
代码 / 命令
-- PG:查看代价相关参数(权重系数)
SHOW seq_page_cost;
SHOW random_page_cost;
SHOW cpu_tuple_cost;
SHOW cpu_index_tuple_cost;
-- 调整示例(SSD 环境可降低随机读权重,使索引更易被选中;需结合实测)
-- ALTER SYSTEM SET random_page_cost = 1.1;
常见误区
- 误区:代价模型能给出真实执行时间。正解:代价是基于统计的模型估算,与真实耗时可能偏差很大;
ANALYZE实测才是实际耗时。 - 误区:调小随机读权重就能让所有查询走索引。正解:权重只改变模型取舍,数据分布与索引区分度不变时,强行推索引仍可能更慢。
追问
- 问:模型估算偏差主要来自哪里?答:统计信息过期或采样不足、多列相关性未建模、条件分布与假设不符、参数绑定导致的通用计划。
- 问:MySQL 与 PG 代价模型一样吗?答:都是 IO+CPU 加权的基于代价模型,但参数、系数与统计口径不同;具体参数分别见 05.2.2 与 05.3.2。
05.1.3.1-011 估算行数与实际行数偏差大说明什么?
频率 ★★★★★ | 难度 进阶
一句话结论
计划中估算行数(rows)与实际行数(actual rows)相差一个数量级以上,通常说明统计信息过期/采样不足、多列相关性未建模、条件选择率估算错误,进而导致连接方式与索引选错;优先更新统计信息并复查计划。
概念解释
- 估算行数:优化器依据统计信息算出的输出行数。
- 实际行数:
ANALYZE实测返回行数。 - 偏差方向:估算偏少会低估中间结果集,可能选错嵌套循环;估算偏多会高估结果集,可能放弃索引选全表扫描。
- 常见成因:统计未更新、直方图/采样覆盖不足、列间相关、LIKE/范围/函数条件估计不准。
原理流程
- 对比计划节点
rows与actual rows。 - 定位偏差最大的节点:是表扫描过滤后偏差,还是连接后膨胀。
- 更新统计信息重跑计划;偏差仍在则检查列相关性、扩展统计或 SQL 改写。
代码 / 命令
-- MySQL:更新表统计信息
ANALYZE TABLE orders;
-- PostgreSQL:收集统计信息
ANALYZE orders;
-- PG:查看列统计与直方图明细(信息架构)
SELECT attname, n_distinct, correlation
FROM pg_stats WHERE tablename = 'orders';
常见误区
- 误区:估算偏差一定是统计信息问题。正解:也可能是列间相关、条件复杂(函数、类型转换、多条件组合)导致模型无法准确估计,需要扩展统计或改写。
- 误区:偏差大就直接强制走索引。正解:先定位是估算错误还是索引本身不合适;强制索引可能在数据变化后反而更慢。
追问
- 问:MySQL 统计信息何时自动更新?答:由
innodb_stats_auto_recalc等机制在数据变更达阈值后自动重算;具体参数见 05.2.2。 - 问:PG 有哪些扩展统计手段?答:多列相关性统计、表达式统计、
mcv列表等;普通单列直方图建模不了列间关联。
05.1.3.1-012 计划里的排序、临时表、落盘怎么判断?
频率 ★★★★☆ | 难度 进阶
一句话结论
排序未利用索引顺序时产生 Sort/filesort;聚合、去重、分组可能使用临时表;结果集超过内存工作区时排序/哈希落盘写临时文件。计划中看到 Using filesort、Using temporary、Sort Method: External Merge Disk、temp 缓冲上升即需关注。
概念解释
- filesort(MySQL):索引未提供所需顺序时,在内存或磁盘上做额外排序。
- 临时表:
GROUP BY、DISTINCT、子查询物化等需要暂存中间结果。 - 落盘(spill):内存工作区装不下排序/哈希数据时写入临时文件,磁盘 IO 显著增加。
- 工作区参数:MySQL
sort_buffer_size、tmp_table_size;PGwork_mem,决定内存排序/哈希上限。
原理流程
- 看计划节点是否有 Sort、HashAggregate、临时表标记。
- 判断数据是否已按索引有序:有序可免排序。
- 关注是否落盘:实测计划的磁盘临时文件/缓冲统计。
代码 / 命令
-- MySQL:出现 Using filesort / Using temporary 的典型查询
EXPLAIN
SELECT user_id, count(*)
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY user_id
ORDER BY count(*) DESC;
-- PG:查看排序方法是否落盘
EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, count(*) FROM orders GROUP BY user_id ORDER BY 2 DESC;
-- 计划 Sort Method: External Merge Disk 表示落盘;Buffers temp 为临时文件块
常见误区
- 误区:
Using temporary一定是磁盘临时表。正解:MySQL 先尝试内存临时表,超出tmp_table_size/max_heap_table_size才转磁盘;需结合实际执行确认。 - 误区:排序慢只靠调大排序缓冲区解决。正解:根因常是扫描行数过大、排序无索引支持;先减数据量与优化索引,再谈调工作区。
追问
- 问:如何让排序免掉 filesort?答:让
ORDER BY列与索引顺序一致(联合索引覆盖过滤 + 排序列),或改为索引有序扫描。 - 问:PG 排序落盘受哪个参数控制?答:
work_mem,单个排序/哈希操作可用内存;设过大需考虑并发下的总内存占用。
05.1.3.1-013 子查询会被展开还是物化?
频率 ★★★☆☆ | 难度 高级
一句话结论
相关/非相关子查询可能被优化器展开为连接(并入主查询计划),也可能物化为临时表/派生表;展开后可走索引与连接优化,物化则先算出子查询结果再参与外层。计划中子查询的形态取决于优化器代价与版本行为。
概念解释
- 子查询展开(flatten):优化器把子查询条件并入外层,改写成连接或谓词,便于使用索引。
- 物化(materialize):先执行子查询生成中间结果(临时表/派生表),外层再基于它访问。
- 相关子查询:内层引用外层列,通常按外层逐行执行;非相关子查询可一次性算出。
- 半连接(semi-join):
IN/EXISTS匹配到即停止,MySQL 有物化半连接、索引半连接等策略。
原理流程
- 优化器判断子查询能否展开为连接/半连接。
- 能展开则并入主查询计划,共享索引与连接优化。
- 不能展开则物化派生表;相关子查询按外层逐行触发内层。
代码 / 命令
-- IN 子查询:计划可能转半连接,而非先物化子查询
EXPLAIN
SELECT * FROM orders o
WHERE o.user_id IN (SELECT id FROM users WHERE city = '昆明');
-- 派生表:可能被展开,也可能物化
EXPLAIN
SELECT t.cnt, u.name FROM
(SELECT user_id, count(*) AS cnt FROM orders GROUP BY user_id) t
JOIN users u ON u.id = t.user_id;
常见误区
- 误区:
IN子查询一定比JOIN慢。正解:现代优化器常把IN/EXISTS转半连接执行,性能差异主要取决于索引与数据量。 - 误区:派生表一定会物化到临时表。正解:优化器可将派生表展开并入外层计划;是否物化看计划节点与版本行为,部分边界行为标「待确认」需实测。
追问
- 问:相关子查询为什么容易慢?答:内层依赖外层值,外层每行都触发一次内层查询,次数 = 外层行数;改为连接通常更优。
- 问:
EXISTS和IN选哪个?答:多数场景优化器都会转半连接;注意IN列表含 NULL 时语义不同(NULL 参与比较结果为空)。
05.1.3.1-014 什么是谓词下推?它对计划有什么影响?
频率 ★★★☆☆ | 难度 高级
一句话结论
谓词下推是把过滤条件尽可能推到最接近数据读取的节点执行,先过滤再连接/聚合,缩小中间结果集;能否下推取决于条件是否可在底层表达式计算、视图/子查询边界是否被展开。
概念解释
- 谓词(predicate):
WHERE、ON、HAVING中的过滤条件。 - 下推(pushdown):条件从外层投影/连接节点向下推到扫描节点,在读取阶段就过滤。
- 收益:提前减少行数,降低连接、排序、聚合与传输代价。
- 边界:视图层、派生表、外连接的空值侧条件、不可下推的函数(如部分窗口函数)会限制下推。
原理流程
- 优化器改写阶段分析条件能否在扫描层计算。
- 可下推条件被移到表/索引扫描节点。
- 先过滤出少量行,再进入上层连接与聚合。
代码 / 命令
-- 过滤条件下推到 orders 扫描层,先按时间收缩再连接 users
EXPLAIN
SELECT u.name, o.amount
FROM users u
JOIN (SELECT * FROM orders WHERE created_at >= '2026-01-01') o
ON o.user_id = u.id;
-- 若计划中条件停留在 Join 之后的 Filter 节点,说明未下推(或不可下推)
常见误区
- 误区:写在子查询里的条件一定会先执行。正解:是否先过滤取决于条件能否下推与查询是否展开;计划里 Filter 节点位置才反映实际执行顺序。
- 误区:外连接条件放
ON和放WHERE等价。正解:外连接ON只在匹配时过滤,不匹配侧保留 NULL;放WHERE会把 NULL 行也过滤掉,实际变成内连接语义。
追问
- 问:哪些条件难以下推?答:依赖上层聚合结果、窗口函数结果、外连接空值侧、部分不可在存储层计算的表达式。
- 问:谓词下推与索引下推(ICP)是一回事吗?答:不是;谓词下推是查询改写层把条件推到扫描节点,索引下推是 MySQL 存储引擎层把索引上可判断的条件提前过滤,减少回表。
05.1.3.1-015 计划缓存与计划重生成是怎么回事?
频率 ★★★☆☆ | 难度 高级
一句话结论
预编译/参数化 SQL 会缓存执行计划,后续相同语句复用;当表数据分布、统计信息、索引发生变化,或计划被判定过期时,会重新生成计划。计划缓存命中率低、或参数差异大导致通用计划不适用时,会出现性能抖动。
概念解释
- 计划缓存:语句文本/参数化签名对应一份计划,避免重复解析优化。
- 计划重生成:统计更新、结构变更、缓存失效后重新优化。
- 通用计划与参数敏感:同一参数化计划复用不同参数,若数据分布倾斜,通用计划对部分参数更差。
- 计划稳定性:通过绑定计划、提示或统计治理,避免计划频繁漂移。
原理流程
- 首次执行:解析 → 优化 → 生成计划入缓存。
- 后续执行:按语句签名命中缓存,直接复用计划。
- 统计/结构变更或缓存淘汰后重新优化;参数敏感场景下复用计划可能不匹配当前数据。
代码 / 命令
-- MySQL:查看语句执行与计划相关状态(是否反复解析)
SHOW STATUS LIKE 'Slow_queries';
SHOW STATUS LIKE 'Com_select';
-- PostgreSQL:查看缓存中的计划与通用计划相关配置
SHOW plan_cache_mode; -- 控制自定义/通用计划选择策略(版本相关,待确认以当前版本文档为准)
常见误区
- 误区:计划缓存永远带来收益。正解:参数分布倾斜时,缓存的通用计划对某些参数是次优的;计划缓存命中不等于计划最优。
- 误区:统计信息更新后所有计划立即刷新。正解:缓存计划何时失效与重建由引擎机制决定;部分场景需手动触发重优化,具体行为随版本变化,边界标「待确认」。
追问
- 问:什么是参数嗅探?答:优化器用首次绑定的参数值估算分布并生成计划,后续不同参数复用该计划;数据倾斜时表现为"时快时慢",详见 05.1.3.4。
- 问:计划漂移(plan flip-flop)是什么?答:同一 SQL 在两个计划间反复切换,性能随之波动;常由统计波动或参数分布变化引起。
05.1.3.1-016 如何对比两条 SQL 或两个版本计划的性能?
频率 ★★★★☆ | 难度 进阶
一句话结论
对比时固定数据规模与环境,分别取静态计划与实测计划,比较访问路径、扫描行数、回表次数、连接方式、是否排序落盘、实际耗时与缓冲命中,避免只看单一指标或单次耗时。
概念解释
- 对照维度:访问路径、索引使用、行数估算/实际、连接算法、排序临时表、IO 缓冲、总耗时。
- 公平前提:相同数据量、相同缓存预热状态、相同参数配置;多次取样取稳定值。
- 实测优先:静态计划只看估算,
ANALYZE实测行数/耗时/缓冲才反映真实代价。
原理流程
- 准备相同数据快照,分别执行原始 SQL 与改写 SQL。
- 取计划与实测:扫描行数、回表、缓冲 read/hit、耗时。
- 逐项对比瓶颈是否消除,确认改写未改变语义与结果集。
代码 / 命令
-- PG:对比实测计划与缓冲
EXPLAIN (ANALYZE, BUFFERS) <改写前 SQL>;
EXPLAIN (ANALYZE, BUFFERS) <改写后 SQL>;
-- MySQL:慢日志与 EXPLAIN 配合,关注 rows 与 Extra
EXPLAIN <改写前 SQL>;
EXPLAIN <改写后 SQL>;
-- 可用 SHOW STATUS LIKE 'Handler%' 观察实际读取行数(会话内前后对比)
常见误区
- 误区:单次快就是改写有效。正解:冷缓存/热缓存、并发负载都会影响单次耗时;应多次取样并看计划与扫描行数。
- 误区:只比较总耗时就下结论。正解:总耗时受缓存与并发干扰;扫描行数、回表次数、落盘等结构性指标更能反映改善是否可持续。
追问
- 问:改写后结果集如何校验一致?答:对比前后返回行数、关键聚合值;必要时用
EXCEPT/差值校验两边结果一致后再上生产。 - 问:测试库数据量小,计划能代表生产吗?答:不能;统计分布与数据量不同会导致计划不同,应尽量在接近生产的数据规模上验证。
05.1.3.1-017 MySQL 与 PostgreSQL 的执行计划有哪些主要差异?
频率 ★★★★☆ | 难度 进阶
一句话结论
两者都是基于代价的优化器,但计划呈现方式不同:MySQL EXPLAIN 是每行一表的扁平描述,PG 是缩进节点树;PG 原生支持 ANALYZE 实测与缓冲输出,MySQL 需配合慢日志、ANALYZE 语句或性能 schema;连接算法支持与统计口径也各有细节。
概念解释
| 维度 | MySQL InnoDB | PostgreSQL |
|---|---|---|
| 计划形式 | EXPLAIN 扁平表,一行一表 |
缩进节点树,父子关系清晰 |
| 实测计划 | 不直接输出实际行数/耗时(需辅助工具) | EXPLAIN (ANALYZE, BUFFERS) 原生实测 |
| 连接算法 | 嵌套循环为主,版本内有哈希连接支持(版本相关,待确认) | 嵌套循环、哈希、归并三类明确可见 |
| 统计信息 | 索引统计、采样页估算 | 直方图、MCV、相关性、扩展统计 |
| 排序落盘 | filesort + 排序缓冲区 |
Sort 节点 + work_mem,显示 External Merge Disk |
原理流程
- 学习时先掌握计划通用主线(路径 → 行数 → 连接 → 排序聚合),再对照各自字段名与节点名;引擎细节见 05.2.2 与 05.3.2。
代码 / 命令
-- MySQL
EXPLAIN SELECT ...;
-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
常见误区
- 误区:MySQL
EXPLAIN的rows就是实际扫描行数。正解:它是估算值;实测读取需借助Handler状态或慢日志分析。 - 误区:PG 计划节点名能直接套到 MySQL。正解:节点语义可对照(Hash Join 等),但输出格式、字段与统计口径不同,不能直接照搬结论。
追问
- 问:为什么 PG 计划更易看出连接顺序?答:缩进树直接表达父子驱动关系;MySQL 扁平表需结合
id、type与 Extra 推断。 - 问:跨库优化结论能直接迁移吗?答:原理相通(减少扫描、避免回表、控制中间结果集),但具体索引、参数与节点行为需在目标库实测。
05.1.3.1-018 常见计划节点与标记速查有哪些?
频率 ★★★★☆ | 难度 基础
一句话结论
高频标记分为访问路径、连接、排序聚合三类:访问路径看全表扫描/索引扫描/覆盖;连接看嵌套循环/哈希/归并;排序聚合看 Sort、临时表、落盘;认识这些标记即可快速判断计划好坏。
概念解释
| 类别 | MySQL 标记/描述 | PG 节点 | 含义 |
|---|---|---|---|
| 全表扫描 | type=ALL |
Seq Scan | 顺序扫描整张表 |
| 索引扫描 | type=index/ref/range |
Index Scan / Index Only Scan / Bitmap Scan | 走索引;Only 为覆盖 |
| 回表 | 非覆盖索引的二级索引访问 | Index Scan 回堆 | 取索引外列 |
| 嵌套循环 | 外层驱动内层循环 | Nested Loop | 小外层 + 内层索引 |
| 哈希连接 | 哈希连接(版本相关) | Hash Join | 小表建哈希,大表探测 |
| 归并连接 | 归并连接 | Merge Join | 有序双指针合并 |
| 排序 | Using filesort |
Sort | 额外排序,可能落盘 |
| 临时表 | Using temporary |
HashAggregate / 物化 | 暂存中间结果 |
| 覆盖索引 | Using index |
Index Only Scan | 不回表 |
原理流程
- 先归类节点属于访问路径、连接还是排序聚合,再按对应判断标准评估代价。
代码 / 命令
-- 对照读取:先跑计划,再按上表归类每个节点
EXPLAIN <待分析 SQL>;
EXPLAIN (ANALYZE, BUFFERS) <待分析 SQL>;
常见误区
- 误区:看到
Using index就不用看其余计划。正解:覆盖索引只解决回表问题,仍需检查扫描范围、连接顺序与排序落盘。 - 误区:位图扫描(Bitmap Scan)比普通索引扫描差。正解:位图扫描适合中等命中范围,先按索引收集位图再批量回表,减少随机回表次数。
追问
- 问:Bitmap Index Scan 与 Bitmap Heap Scan 配合做什么?答:前者在索引上收集命中行的位图,后者按位图批量回堆取行,适合中等范围查询。
- 问:计划里出现
Materialize意味着什么?答:输入被物化为临时结果复用,常见于内层被多次扫描的嵌套循环;需关注物化规模与内存占用。
参考来源
- MySQL 8.0 Reference Manual:EXPLAIN Output Format:https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
- PostgreSQL Documentation:Using EXPLAIN:https://www.postgresql.org/docs/current/using-explain.html
- PostgreSQL Documentation:Planner Cost Constants:https://www.postgresql.org/docs/current/runtime-config-query.html