Appearance
4.1-8 EXPLAIN 全字段与执行计划分析
所属:04 数据存储 / 4.1 MySQL 分级:A 状态:◐ 初稿 关联:4.1-5、4.1-6、4.1-7、4.1-9、4.1-10、4.1-11
题型:工具 / 排查 / 调优
一句话结论:EXPLAIN 不是“看有没有走索引”,而是读一棵执行计划树:从
id/select_type/table/partitions/type/possible_keys/key/key_len/ref/rows/filtered/Extra判断访问方式、连接顺序、索引使用长度、估算行数和过滤比例、排序/临时表/覆盖/ICP;再结合FORMAT=JSON/TREE、EXPLAIN ANALYZE、optimizer_trace区分“估算”和“实际”,定位是结构不可用、统计失真还是成本选错。分层要点:
- EXPLAIN 的边界:常规 EXPLAIN 只做优化、不执行 SQL(
EXPLAIN ANALYZE会执行 SELECT),输出的是估算计划;它回答“优化器准备怎么访问每张表”,不回答“实际跑了多久、真实多少行”。所以先看结构,再用 ANALYZE 验证。 - 输出格式:传统表格(一行一表)、
FORMAT=JSON(树 +cost_info)、FORMAT=TREE(8.0.16+,连接顺序直观)、EXPLAIN ANALYZE(8.0.18+,实际 time/rows/loops)、EXPLAIN FOR CONNECTION(5.7+,看运行中语句)、optimizer_trace(优化过程)。不同格式解决不同问题,不能只看传统表格。 - id/select_type/table:
id标识 SELECT,不是执行顺序;相同id是同一 SELECT,不同id是子查询/UNION;select_type区分 SIMPLE/PRIMARY/SUBQUERY/DEPENDENT/UNION/DERIVED/MATERIALIZED 等;table可能是<derivedN>、<unionM,N>这样的临时对象。 - type 访问方式:system/const/eq_ref/ref/fulltext/ref_or_null/index_merge/unique_subquery/index_subquery/range/index/ALL;从好到差是粗略排序,不是绝对;
index全索引扫描在覆盖/小表时可能合理,ALL在小表/结果占比高时也可能合理。 - possible_keys/key/key_len/ref:
possible_keys是候选,key是实际选择,key_len是实际使用的索引前缀字节数,ref是比较对象(const/列/func)。key_len可以反推联合索引用到第几列,但受字符集、可空、变长字段影响。 - rows/filtered:
rows是估算扫描行数,filtered是估算过滤后剩余百分比;rows × filtered%近似输出行数。两者都是统计估算,不是实际值;和EXPLAIN ANALYZE的 actual rows 对比,才能判断统计是否失真。 - Extra:
Using index覆盖、Using index conditionICP、Using where引擎层后过滤、Using filesort额外排序、Using temporary临时表、Using join buffer (BNL/hash join)被驱动表无索引、Using MRR、Using union/intersect、Using index for skip scan、Using index for group-by、Backward index scan等。每个都要能解释“为什么出现、代价是什么”。 - 分析流程:先看
type/key判断访问方式;再看key_len/ref判断用了几列;再看rows/filtered估算结果集;再看Extra排序/临时表/连接缓冲;再看id/select_type/table理清连接与子查询;最后用FORMAT=TREE/EXPLAIN ANALYZE/optimizer_trace验证,不要只看一个字段下结论。
- EXPLAIN 的边界:常规 EXPLAIN 只做优化、不执行 SQL(
图/流程:
text
EXPLAIN 分析顺序:
1. id/select_type/table → 哪些 SELECT、哪些表/派生表
2. type → 每张表怎么访问
3. possible_keys/key/key_len/ref → 用了哪个索引、几列
4. rows/filtered → 估算扫描/输出行数
5. Extra → 覆盖/ICP/排序/临时表/连接缓冲
6. FORMAT=TREE / ANALYZE → 连接顺序、实际行数/耗时
7. optimizer_trace → 为什么选这个计划text
type 粗略梯度:
好 ← system > const > eq_ref > ref > range > index > ALL → 差
唯一/主键等值 非唯一等值 范围
index/ALL 不一定坏:小表、覆盖索引、结果占比高时可能合理
index_merge / skip scan / ref_or_null 是特殊路径,单独看text
key_len 反推:
idx(a,b,c)
varchar(20) utf8mb4 NULL:20*4 + 2 + 1 = 83
bigint:8;int:4;datetime:5(无小数秒)
只用到 a → key_len ≈ 83
用到 a+b → key_len ≈ 83 + len(b)
key_len 不是索引总长度,而是“本次实际用到的前缀”text
Extra 快速判断:
Using index → 覆盖,不回表
Using index condition → ICP,索引层过滤,减少回表
Using where → 引擎返回后过滤(可能已回表)
Using filesort → 排序没走索引
Using temporary → GROUP BY/DISTINCT/UNION 临时表
Using join buffer → 被驱动表没索引(BNL/hash join)text
FORMAT=JSON:
query_block
├─ cost_info.query_cost
└─ table
├─ access_type / key / used_key_parts / key_length
├─ rows_examined_per_scan / rows_produced_per_join
├─ filtered / attached_condition
└─ cost_info.read_cost / eval_cost / prefix_cost版本/源码锚点:
- MySQL 5.6:
EXPLAIN FORMAT=JSON;支持对 UPDATE/DELETE 做 EXPLAIN。 - MySQL 5.7:
EXPLAIN增加partitions、filtered;EXPLAIN FOR CONNECTION;optimizer cost tables。 - MySQL 8.0:
EXPLAIN FORMAT=TREE(8.0.16);EXPLAIN ANALYZE(8.0.18);hash join(8.0.18);prefer_ordering_index(8.0.21);derived_condition_pushdown(8.0.22)。 - 相关参数:
optimizer_trace、optimizer_trace_max_mem_size(默认 1MB)、end_markers_in_json、optimizer_switch、eq_range_index_dive_limit、range_optimizer_max_mem_size。 - 源码/实现:
sql/opt_explain.cc(传统/JSON 输出)、sql/opt_explain_format.cc(JSON/TREE)、sql/sql_optimizer.cc(计划生成)、sql/opt_costmodel.cc(成本)、sql/opt_range.cc(range)、sql/sql_join_buffer.cc(BNL/hash join)、storage/innobase/row/row0sel.cc(ICP/MRR 执行)。
- MySQL 5.6:
EXPLAIN 输出格式:
| 格式 | 版本 | 输出 | 用途 |
|---|---|---|---|
| 传统表格 | 5.6+ | 一行一表 | 快速看 type/key/rows/Extra |
FORMAT=JSON | 5.6+ | JSON 树 + cost_info | 成本、used_key_parts、attached_condition |
FORMAT=TREE | 8.0.16+ | 树 | 连接顺序、层级关系 |
EXPLAIN ANALYZE | 8.0.18+ | 实际 time/rows/loops | 估算 vs 实际 |
EXPLAIN FOR CONNECTION | 5.7+ | 运行中语句的计划 | 线上排查 |
optimizer_trace | 5.6+ | 优化过程 JSON | 为什么选/不选 |
- type 全谱:
| type | 含义 | 典型场景 | 备注 |
|---|---|---|---|
system | 系统表,只有一行 | 系统表 | 最好 |
const | 最多一行 | PK/唯一索引 = 常量 | 优化阶段读取 |
eq_ref | 每行匹配一行 | JOIN 唯一/PK | 连接理想 |
ref | 每行匹配多行 | 非唯一索引 = 常量/列 | 常见 |
fulltext | 全文索引 | MATCH ... AGAINST | 不是 B+ 树 |
ref_or_null | ref + IS NULL | 索引列 IS NULL | 可能额外扫 NULL |
index_merge | 合并多个索引 | OR/多条件 | 常是缺联合索引 |
unique_subquery | IN 子查询走唯一索引 | 旧优化 | 8.0 可能转半连接 |
index_subquery | IN 子查询走非唯一索引 | 旧优化 | 同上 |
range | 索引范围扫描 | > < BETWEEN IN LIKE 'x%' | 常见 |
index | 全索引扫描 | 覆盖/小表/无更好索引 | 不一定差 |
ALL | 全表扫描 | 无索引/小表/结果占比高 | 最慢但有时合理 |
- 字段速查:
| 字段 | 含义 | 好/坏信号 | 常见误读 |
|---|---|---|---|
id | SELECT 标识 | 相同=同一 SELECT;不同=子查询/UNION | 不是执行顺序 |
select_type | 查询类型 | SIMPLE/PRIMARY 常见;DEPENDENT 要关注 | MATERIALIZED 不一定坏 |
table | 表/派生表 | <derivedN>/<unionM,N> | 不是真实表名 |
partitions | 命中分区 | 为空/全分区=裁剪失效 | 只有分区表才看 |
type | 访问方式 | const/eq_ref/ref/range 较好;index/ALL 结合场景 | index 不一定比 ALL 差 |
possible_keys | 候选索引 | NULL=结构不可用 | 有候选不代表会用 |
key | 实际索引 | NULL=没选 | 可能是成本选择 |
key_len | 使用的前缀字节 | 反推联合索引列数 | 受字符集/可空/变长影响 |
ref | 比较对象 | const/列/func | func 可能是函数/转换 |
rows | 估算扫描行数 | 越小越好,但看 filtered | 不是实际行数 |
filtered | 过滤后剩余比例 | 越低说明过滤越多 | 也是估算值 |
Extra | 附加信息 | 覆盖/ICP/排序/临时表/连接缓冲 | 需逐项解释 |
- Extra 全字段:
| Extra | 含义 | 关注点 |
|---|---|---|
Using index | 覆盖索引 | 不回表;但二级索引 MVCC 可能仍访问聚簇索引 |
Using index condition | ICP | 索引层过滤,减少回表;不减少索引扫描 |
Using where | 引擎返回后过滤 | 可能已回表;结合是否有 Using index 看 |
Using filesort | 额外排序 | 检查 ORDER BY 与索引;可能内存/磁盘 |
Using temporary | 临时表 | GROUP BY/DISTINCT/UNION;可能内存/磁盘 |
Using join buffer (Block Nested Loop) | BNL | 被驱动表无索引 |
Using join buffer (hash join) | 8.0.18 hash join | 无索引/等值连接,OLTP 高并发要谨慎 |
Using MRR | Multi-Range Read | 回表主键排序批量读 |
Using union / Using intersect / Using sort_union | index merge | 多索引合并,常是缺联合索引 |
Using index for skip scan | 8.0.13 skip scan | 前导列低基数时跳过 |
Using index for group-by | 索引分组 | 避免临时表 |
Backward index scan | 反向索引扫描 | 8.0;配合降序/逆序 |
Select tables optimized away | 优化器直接得出结果 | MIN/MAX 等 |
Impossible WHERE | 条件永假 | 不返回行 |
No tables used | 无表 | SELECT 1 |
Range checked for each record | 每行检查索引 | 连接缺索引,代价高 |
Not exists | 反连接优化 | LEFT JOIN ... IS NULL |
Distinct | 去重优化 | 可能配合临时表 |
- FORMAT=JSON 关键字段:
| 字段 | 含义 | 用途 |
|---|---|---|
query_block.cost_info.query_cost | 整条查询估算成本 | 对比不同写法 |
table.access_type | 访问方式 | 对应传统 type |
table.used_key_parts | 实际使用的索引列 | 比 key_len 更直观 |
table.key_length | 索引前缀字节 | 对应 key_len |
table.rows_examined_per_scan | 每次扫描估算行数 | 评估扫描量 |
table.rows_produced_per_join | 参与 join 的估算行数 | 评估中间结果 |
table.filtered | 过滤比例 | 对应 filtered |
table.attached_condition | 附加条件 | 看 ICP/回表后过滤 |
table.cost_info.read_cost/eval_cost/prefix_cost | 读/计算/前缀成本 | 看成本构成 |
ordering_operation / grouping_operation | 排序/分组 | 看 filesort/temporary |
materialized_from_subquery | 物化子查询 | 看派生表 |
深挖点(面试官常追的 6 个):
id不是执行顺序:id只标识 SELECT;相同id表示同一 SELECT 内的表,不同id表示子查询/UNION。连接顺序要看FORMAT=TREE或EXPLAIN ANALYZE的树,不能按id大小猜“谁先执行”。type不是绝对排名:index是全索引扫描,但如果覆盖索引、索引比表小很多,可能比ALL好;ALL在小表、结果占比高时也可能是最优。判断要结合rows、filtered、Extra和实际耗时。rows/filtered是估算,不是实际:它们来自统计信息和采样,数据倾斜、统计过期时会严重失真;EXPLAIN ANALYZE的actual rows和loops才能看出真实扫描量。估算 100 行、实际 100 万行,通常是统计问题。key_len能反推联合索引列数:key_len是本次实际使用的前缀字节,不是索引总长度;字符集、可空、变长都会影响计算。Using index、Using index condition、Using where是三种不同层面的过滤/覆盖,不要混为一谈。Using filesort/Using temporary不一定灾难:小结果集的内存排序、临时表可以接受;要看rows、结果集大小、sort_buffer_size、tmp_table_size。真正要优化的是“大表 + 大结果集 + 磁盘排序/临时表”。FORMAT=JSON的cost_info和optimizer_trace才是“为什么”:传统 EXPLAIN 只告诉你结果,JSON 给出成本构成,optimizer_trace给出候选访问路径和被拒原因;优化器选错时,先看成本假设是否失真,再决定改 SQL、补索引还是更新统计。
追问链:
- EXPLAIN 能做什么、不能做什么? → 能看访问方式、索引选择、连接顺序、估算行数、排序/临时表;不能告诉你实际耗时/真实行数,也不能保证计划在生产数据上最优。
- EXPLAIN 会执行 SQL 吗? → 常规 EXPLAIN 不执行;
EXPLAIN ANALYZE会执行 SELECT,生产大表上要谨慎。 - EXPLAIN 有哪些格式? → 传统表格、
FORMAT=JSON、FORMAT=TREE(8.0.16+)、EXPLAIN ANALYZE(8.0.18+)、EXPLAIN FOR CONNECTION;优化过程用optimizer_trace。 - EXPLAIN ANALYZE 和 EXPLAIN 的区别? → 前者执行并给出实际 time/rows/loops;后者只给估算计划,不执行。
id越大越先执行吗? → 不是。id标识 SELECT,不保证执行顺序;连接顺序看FORMAT=TREE/ANALYZE。select_type有哪些? → SIMPLE、PRIMARY、SUBQUERY、DEPENDENT SUBQUERY、UNION、DEPENDENT UNION、UNION RESULT、DERIVED、MATERIALIZED、UNCACHEABLE 等。table列的<derivedN>/<unionM,N>是什么? → 派生表/物化子查询/UNION 结果的临时对象,不是真实表。type有哪些,怎么排? → system/const/eq_ref/ref/fulltext/ref_or_null/index_merge/unique_subquery/index_subquery/range/index/ALL;大致从好到差,但要结合场景。const、eq_ref、ref区别? → const 是常量等值最多一行;eq_ref 是 join 中唯一/PK 每行匹配一行;ref 是非唯一索引等值,可能多行。range和index区别? → range 是索引范围扫描;index 是全索引扫描。range 通常更精准,但 index 覆盖/小表时也可能合理。ALL一定最差吗? → 不一定;小表、结果占比高、全表顺序读可能比大量回表更便宜。possible_keys和key区别? → 前者是候选,后者是实际选择;有候选但key=NULL通常是成本选择。key为 NULL 说明什么? → 没有使用索引;可能是结构不可用,也可能是优化器算下来全表更便宜。key_len怎么算? → 实际使用的索引前缀字节;varchar(20) utf8mb4 NULL约 83,bigint8,int4,datetime5;受字符集/可空/变长影响。ref列怎么看? → 表示索引列和什么比较:const、某表的列、func(函数/表达式)等;func 可能意味着转换。rows是实际行数吗? → 不是,是统计估算;真实行数看EXPLAIN ANALYZE的 actual rows/loops。filtered是什么? → 估算的过滤后剩余百分比;rows × filtered%近似输出行数,也是估算。Extra有哪些关键值? →Using index、Using index condition、Using where、Using filesort、Using temporary、Using join buffer、Using MRR、Using union/intersect、Using index for skip scan等。Using index和Using index condition区别? → 前者是覆盖索引,不回表;后者是 ICP,索引层过滤以减少回表,但仍可能回表。Using where什么意思? → 存储引擎返回行后还要用 WHERE 过滤;如果同时有Using index,是在索引层过滤;否则可能已回表。Using filesort一定慢吗? → 不一定;小结果集内存排序可接受;大表/大结果集/磁盘排序才需要优化。Using temporary什么时候出现? → GROUP BY、DISTINCT、UNION、ORDER BY 等需要临时表时;可能内存或磁盘。Using join buffer说明什么? → 被驱动表没有可用索引,走 BNL 或 hash join;OLTP 高并发下通常要补索引。- hash join 和 BNL 区别? → BNL 是块嵌套循环,hash join(8.0.18)建哈希表匹配;无索引等值连接更快,但仍不如索引嵌套循环。
Using MRR是什么? → Multi-Range Read,把回表主键排序后批量读,降低随机 IO。- index merge 的 Extra 长什么样? →
Using union(...)、Using intersect(...)、Using sort_union(...);常是缺联合索引。 - skip scan 的 Extra? →
Using index for skip scan;8.0.13,前导列低基数时可能生效。 Using index for group-by是什么? → 利用索引完成分组(loose index scan),避免临时表。Backward index scan是什么? → 反向索引扫描;8.0 支持,配合降序/逆序排序。Select tables optimized away是什么? → 优化器直接用索引得出结果(如 MIN/MAX),不需要真正扫描。Impossible WHERE/No tables used? → 前者条件永假,不返回行;后者语句不需要表(如SELECT 1)。Range checked for each record是什么? → 连接时每行都要检查可用索引,通常说明缺合适索引,代价高。partitions列怎么看? → 分区表看命中的分区;为空或命中全部分区,说明裁剪可能失效。FORMAT=JSON的cost_info怎么看? → 看query_cost、read_cost、eval_cost、prefix_cost,对比不同 SQL/索引的成本构成。FORMAT=TREE有什么用? → 直观展示连接顺序和层级,弥补传统表格id的误读。optimizer_trace怎么用? → 开启optimizer_trace,执行 SQL,读information_schema.OPTIMIZER_TRACE,看considered_access_paths、chosen、cost、why_not。EXPLAIN FOR CONNECTION怎么用? → 用SHOW PROCESSLIST拿连接 id,EXPLAIN FOR CONNECTION <id>看运行中语句的计划。- 怎么用 EXPLAIN 判断统计信息失真? → 对比
rows估算和EXPLAIN ANALYZE的 actual rows;差距大且filtered异常,考虑ANALYZE TABLE/直方图。 - 怎么用 EXPLAIN 判断连接顺序? → 看
FORMAT=TREE的嵌套顺序,或EXPLAIN ANALYZE树的层级;不要只看传统表格的id。 - EXPLAIN 分析常见误读? →
id当执行顺序、type绝对排名、rows当实际行数、Using index当绝对不回表、Using filesort当一定磁盘排序、key有值当一定高效。
生产案例/取舍(示例,替换成你的项目):
- 慢查询
type=ALL、key=NULL,possible_keys=NULL;补联合索引后变range,RT 下降。 type=index全索引扫描但表很大;改成覆盖索引/更精准的联合索引,减少扫描量。key有值但rows很大、filtered很低;说明索引选择性差或条件过滤多,补更合适的联合索引。Using filesort+Using temporary同时出现;调整索引列序/分组列,减少排序和临时表。Using join buffer (hash join);给被驱动表 join 列加索引,改回eq_ref/ref。- EXPLAIN 估算 100 行,
EXPLAIN ANALYZE实际 100 万行;统计过期/倾斜,更新统计/建直方图。 key_len只覆盖联合索引第一列;调整列序或补索引,让后续列参与定位。Using index condition但扫描行数仍大;ICP 只减少回表,不减少索引扫描,需缩小范围/改索引。- 分区表
partitions命中全部分区;WHERE 没带分区键,补分区键或检查分区设计。 - 派生表物化导致慢;改写 JOIN/EXISTS,或利用
derived_condition_pushdown(8.0.22)。 - 取舍:
EXPLAIN快但只是估算;EXPLAIN ANALYZE真实但会执行 SQL;optimizer_trace信息全但开销大,生产按需开启。
- 慢查询
易错点:
- 认为
id越大越先执行。 - 认为
type=ALL一定最差。 - 认为
type=index一定比range好。 - 认为
key有值就一定高效。 - 认为
rows是实际行数。 - 认为
filtered是实际过滤比例。 - 认为
possible_keys有索引就一定会用。 - 认为
key_len是索引总长度。 - 认为
Using index一定绝对不回表,忽略二级索引 MVCC。 - 认为
Using index condition等于覆盖索引。 - 认为
Using where一定是坏事。 - 认为
Using filesort一定磁盘排序。 - 认为
Using temporary一定磁盘临时表。 - 认为
Using join buffer一定是 BNL,忽略 8.0 hash join。 - 认为 EXPLAIN 会执行 SQL。
- 认为
EXPLAIN ANALYZE不执行 SQL。 - 认为
FORMAT=JSON的 cost 是实际耗时。 - 认为
optimizer_trace可以长期全局开启。 - 认为
partitions为空就是没分区/裁剪失败。 - 认为一次 EXPLAIN 就能定论,忽略数据分布和统计变化。
- 认为传统表格的
id顺序就是 join 顺序。 - 认为所有 SQL 都能随意
EXPLAIN ANALYZE(会执行,注意 DML/大表)。
- 认为
30 秒口述稿:
EXPLAIN 是看执行计划,不是只看有没有走索引。先看
type判断访问方式,再看possible_keys/key/key_len/ref判断用了哪个索引、几列,再看rows/filtered估算扫描和输出行数,最后看Extra有没有覆盖、ICP、filesort、临时表、join buffer。id不是执行顺序,连接顺序看FORMAT=TREE。估算不准时用EXPLAIN ANALYZE对比实际 rows/loops,用optimizer_trace看为什么选这个计划。最后结合业务 RT 和压测下结论。
- 3 分钟展开顺序:结论(计划树 + 估算 vs 实际)→ EXPLAIN 的边界与格式 → id/select_type/table → type 全谱 → possible_keys/key/key_len/ref → rows/filtered → Extra 全字段 → FORMAT=JSON/TREE → EXPLAIN ANALYZE/optimizer_trace → 分析流程与生产案例。