Skip to content

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 区分“估算”和“实际”,定位是结构不可用、统计失真还是成本选错。

  • 分层要点:

    1. EXPLAIN 的边界:常规 EXPLAIN 只做优化、不执行 SQL(EXPLAIN ANALYZE 会执行 SELECT),输出的是估算计划;它回答“优化器准备怎么访问每张表”,不回答“实际跑了多久、真实多少行”。所以先看结构,再用 ANALYZE 验证。
    2. 输出格式:传统表格(一行一表)、FORMAT=JSON(树 + cost_info)、FORMAT=TREE(8.0.16+,连接顺序直观)、EXPLAIN ANALYZE(8.0.18+,实际 time/rows/loops)、EXPLAIN FOR CONNECTION(5.7+,看运行中语句)、optimizer_trace(优化过程)。不同格式解决不同问题,不能只看传统表格。
    3. id/select_type/table:id 标识 SELECT,不是执行顺序;相同 id 是同一 SELECT,不同 id 是子查询/UNION;select_type 区分 SIMPLE/PRIMARY/SUBQUERY/DEPENDENT/UNION/DERIVED/MATERIALIZED 等;table 可能是 <derivedN>、<unionM,N> 这样的临时对象。
    4. type 访问方式:system/const/eq_ref/ref/fulltext/ref_or_null/index_merge/unique_subquery/index_subquery/range/index/ALL;从好到差是粗略排序,不是绝对;index 全索引扫描在覆盖/小表时可能合理,ALL 在小表/结果占比高时也可能合理。
    5. possible_keys/key/key_len/ref:possible_keys 是候选,key 是实际选择,key_len 是实际使用的索引前缀字节数,ref 是比较对象(const/列/func)。key_len 可以反推联合索引用到第几列,但受字符集、可空、变长字段影响。
    6. rows/filtered:rows 是估算扫描行数,filtered 是估算过滤后剩余百分比;rows × filtered% 近似输出行数。两者都是统计估算,不是实际值;和 EXPLAIN ANALYZE 的 actual rows 对比,才能判断统计是否失真。
    7. Extra:Using index 覆盖、Using index condition ICP、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 等。每个都要能解释“为什么出现、代价是什么”。
    8. 分析流程:先看 type/key 判断访问方式;再看 key_len/ref 判断用了几列;再看 rows/filtered 估算结果集;再看 Extra 排序/临时表/连接缓冲;再看 id/select_type/table 理清连接与子查询;最后用 FORMAT=TREE/EXPLAIN ANALYZE/optimizer_trace 验证,不要只看一个字段下结论。
  • 图/流程:

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 执行)。
  • EXPLAIN 输出格式:

格式版本输出用途
传统表格5.6+一行一表快速看 type/key/rows/Extra
FORMAT=JSON5.6+JSON 树 + cost_info成本、used_key_parts、attached_condition
FORMAT=TREE8.0.16+树连接顺序、层级关系
EXPLAIN ANALYZE8.0.18+实际 time/rows/loops估算 vs 实际
EXPLAIN FOR CONNECTION5.7+运行中语句的计划线上排查
optimizer_trace5.6+优化过程 JSON为什么选/不选
  • type 全谱:
type含义典型场景备注
system系统表,只有一行系统表最好
const最多一行PK/唯一索引 = 常量优化阶段读取
eq_ref每行匹配一行JOIN 唯一/PK连接理想
ref每行匹配多行非唯一索引 = 常量/列常见
fulltext全文索引MATCH ... AGAINST不是 B+ 树
ref_or_nullref + IS NULL索引列 IS NULL可能额外扫 NULL
index_merge合并多个索引OR/多条件常是缺联合索引
unique_subqueryIN 子查询走唯一索引旧优化8.0 可能转半连接
index_subqueryIN 子查询走非唯一索引旧优化同上
range索引范围扫描> < BETWEEN IN LIKE 'x%'常见
index全索引扫描覆盖/小表/无更好索引不一定差
ALL全表扫描无索引/小表/结果占比高最慢但有时合理
  • 字段速查:
字段含义好/坏信号常见误读
idSELECT 标识相同=同一 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/列/funcfunc 可能是函数/转换
rows估算扫描行数越小越好,但看 filtered不是实际行数
filtered过滤后剩余比例越低说明过滤越多也是估算值
Extra附加信息覆盖/ICP/排序/临时表/连接缓冲需逐项解释
  • Extra 全字段:
Extra含义关注点
Using index覆盖索引不回表;但二级索引 MVCC 可能仍访问聚簇索引
Using index conditionICP索引层过滤,减少回表;不减少索引扫描
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 MRRMulti-Range Read回表主键排序批量读
Using union / Using intersect / Using sort_unionindex merge多索引合并,常是缺联合索引
Using index for skip scan8.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 个):

    1. id 不是执行顺序:id 只标识 SELECT;相同 id 表示同一 SELECT 内的表,不同 id 表示子查询/UNION。连接顺序要看 FORMAT=TREE 或 EXPLAIN ANALYZE 的树,不能按 id 大小猜“谁先执行”。
    2. type 不是绝对排名:index 是全索引扫描,但如果覆盖索引、索引比表小很多,可能比 ALL 好;ALL 在小表、结果占比高时也可能是最优。判断要结合 rows、filtered、Extra 和实际耗时。
    3. rows/filtered 是估算,不是实际:它们来自统计信息和采样,数据倾斜、统计过期时会严重失真;EXPLAIN ANALYZE 的 actual rows 和 loops 才能看出真实扫描量。估算 100 行、实际 100 万行,通常是统计问题。
    4. key_len 能反推联合索引列数:key_len 是本次实际使用的前缀字节,不是索引总长度;字符集、可空、变长都会影响计算。Using index、Using index condition、Using where 是三种不同层面的过滤/覆盖,不要混为一谈。
    5. Using filesort / Using temporary 不一定灾难:小结果集的内存排序、临时表可以接受;要看 rows、结果集大小、sort_buffer_size、tmp_table_size。真正要优化的是“大表 + 大结果集 + 磁盘排序/临时表”。
    6. FORMAT=JSON 的 cost_info 和 optimizer_trace 才是“为什么”:传统 EXPLAIN 只告诉你结果,JSON 给出成本构成,optimizer_trace 给出候选访问路径和被拒原因;优化器选错时,先看成本假设是否失真,再决定改 SQL、补索引还是更新统计。
  • 追问链:

    1. EXPLAIN 能做什么、不能做什么? → 能看访问方式、索引选择、连接顺序、估算行数、排序/临时表;不能告诉你实际耗时/真实行数,也不能保证计划在生产数据上最优。
    2. EXPLAIN 会执行 SQL 吗? → 常规 EXPLAIN 不执行;EXPLAIN ANALYZE 会执行 SELECT,生产大表上要谨慎。
    3. EXPLAIN 有哪些格式? → 传统表格、FORMAT=JSON、FORMAT=TREE(8.0.16+)、EXPLAIN ANALYZE(8.0.18+)、EXPLAIN FOR CONNECTION;优化过程用 optimizer_trace。
    4. EXPLAIN ANALYZE 和 EXPLAIN 的区别? → 前者执行并给出实际 time/rows/loops;后者只给估算计划,不执行。
    5. id 越大越先执行吗? → 不是。id 标识 SELECT,不保证执行顺序;连接顺序看 FORMAT=TREE/ANALYZE。
    6. select_type 有哪些? → SIMPLE、PRIMARY、SUBQUERY、DEPENDENT SUBQUERY、UNION、DEPENDENT UNION、UNION RESULT、DERIVED、MATERIALIZED、UNCACHEABLE 等。
    7. table 列的 <derivedN>/<unionM,N> 是什么? → 派生表/物化子查询/UNION 结果的临时对象,不是真实表。
    8. type 有哪些,怎么排? → system/const/eq_ref/ref/fulltext/ref_or_null/index_merge/unique_subquery/index_subquery/range/index/ALL;大致从好到差,但要结合场景。
    9. const、eq_ref、ref 区别? → const 是常量等值最多一行;eq_ref 是 join 中唯一/PK 每行匹配一行;ref 是非唯一索引等值,可能多行。
    10. range 和 index 区别? → range 是索引范围扫描;index 是全索引扫描。range 通常更精准,但 index 覆盖/小表时也可能合理。
    11. ALL 一定最差吗? → 不一定;小表、结果占比高、全表顺序读可能比大量回表更便宜。
    12. possible_keys 和 key 区别? → 前者是候选,后者是实际选择;有候选但 key=NULL 通常是成本选择。
    13. key 为 NULL 说明什么? → 没有使用索引;可能是结构不可用,也可能是优化器算下来全表更便宜。
    14. key_len 怎么算? → 实际使用的索引前缀字节;varchar(20) utf8mb4 NULL 约 83,bigint 8,int 4,datetime 5;受字符集/可空/变长影响。
    15. ref 列怎么看? → 表示索引列和什么比较:const、某表的列、func(函数/表达式)等;func 可能意味着转换。
    16. rows 是实际行数吗? → 不是,是统计估算;真实行数看 EXPLAIN ANALYZE 的 actual rows/loops。
    17. filtered 是什么? → 估算的过滤后剩余百分比;rows × filtered% 近似输出行数,也是估算。
    18. 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 等。
    19. Using index 和 Using index condition 区别? → 前者是覆盖索引,不回表;后者是 ICP,索引层过滤以减少回表,但仍可能回表。
    20. Using where 什么意思? → 存储引擎返回行后还要用 WHERE 过滤;如果同时有 Using index,是在索引层过滤;否则可能已回表。
    21. Using filesort 一定慢吗? → 不一定;小结果集内存排序可接受;大表/大结果集/磁盘排序才需要优化。
    22. Using temporary 什么时候出现? → GROUP BY、DISTINCT、UNION、ORDER BY 等需要临时表时;可能内存或磁盘。
    23. Using join buffer 说明什么? → 被驱动表没有可用索引,走 BNL 或 hash join;OLTP 高并发下通常要补索引。
    24. hash join 和 BNL 区别? → BNL 是块嵌套循环,hash join(8.0.18)建哈希表匹配;无索引等值连接更快,但仍不如索引嵌套循环。
    25. Using MRR 是什么? → Multi-Range Read,把回表主键排序后批量读,降低随机 IO。
    26. index merge 的 Extra 长什么样? → Using union(...)、Using intersect(...)、Using sort_union(...);常是缺联合索引。
    27. skip scan 的 Extra? → Using index for skip scan;8.0.13,前导列低基数时可能生效。
    28. Using index for group-by 是什么? → 利用索引完成分组(loose index scan),避免临时表。
    29. Backward index scan 是什么? → 反向索引扫描;8.0 支持,配合降序/逆序排序。
    30. Select tables optimized away 是什么? → 优化器直接用索引得出结果(如 MIN/MAX),不需要真正扫描。
    31. Impossible WHERE / No tables used? → 前者条件永假,不返回行;后者语句不需要表(如 SELECT 1)。
    32. Range checked for each record 是什么? → 连接时每行都要检查可用索引,通常说明缺合适索引,代价高。
    33. partitions 列怎么看? → 分区表看命中的分区;为空或命中全部分区,说明裁剪可能失效。
    34. FORMAT=JSON 的 cost_info 怎么看? → 看 query_cost、read_cost、eval_cost、prefix_cost,对比不同 SQL/索引的成本构成。
    35. FORMAT=TREE 有什么用? → 直观展示连接顺序和层级,弥补传统表格 id 的误读。
    36. optimizer_trace 怎么用? → 开启 optimizer_trace,执行 SQL,读 information_schema.OPTIMIZER_TRACE,看 considered_access_paths、chosen、cost、why_not。
    37. EXPLAIN FOR CONNECTION 怎么用? → 用 SHOW PROCESSLIST 拿连接 id,EXPLAIN FOR CONNECTION <id> 看运行中语句的计划。
    38. 怎么用 EXPLAIN 判断统计信息失真? → 对比 rows 估算和 EXPLAIN ANALYZE 的 actual rows;差距大且 filtered 异常,考虑 ANALYZE TABLE/直方图。
    39. 怎么用 EXPLAIN 判断连接顺序? → 看 FORMAT=TREE 的嵌套顺序,或 EXPLAIN ANALYZE 树的层级;不要只看传统表格的 id。
    40. 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 → 分析流程与生产案例。