MySQL 执行计划 深度解析

从数据结构 · 生成流程 · EXPLAIN字段 · 性能诊断 · 优化实战 — 一图搞懂

🗺️ 什么是执行计划

执行计划（Execution Plan）是 MySQL 优化器对一条 SQL 语句制定的查询执行策略——它回答了：「我打算怎么找到你想要的数据」，而不是「我到底干了什么」。
本质
优化器输出的"查询执行树"，描述数据访问路径与操作顺序
触发方式
EXPLAIN / EXPLAIN ANALYZE / EXPLAIN FORMAT=JSON
核心价值
发现全表扫描、索引失效、低效 JOIN 等性能瓶颈
生成时机
SQL 解析完成后、实际执行前，由 Cost-Based Optimizer 生成
SQL 语句 SELECT / JOIN / WHERE Parser 解析器 词法/语法分析 → AST Optimizer 优化器 代价估算 → 执行计划 执行计划 访问路径 + 操作顺序 Executor 执行器 调用存储引擎返回结果 EXPLAIN 读取此处

🌳 执行计划的数据结构

执行计划在 MySQL 内部是一棵 查询树（Query Tree），每个节点是一个"操作算子"，叶子节点是表/索引访问，父节点是 JOIN/聚合/排序等操作。

Result Node 最终结果输出 Sort / Aggregate Node ORDER BY / GROUP BY / HAVING Join Node Nested Loop / Hash Join Table Scan Node 驱动表 orders（全表/索引） Index Scan Node 被驱动表 users（idx_id） 节点属性 • table: 表名 • type: 访问类型 • key: 使用的索引 节点属性 • rows: 估算行数 • filtered: 过滤比 • extra: 附加操作

QueryBlock 数据结构（源码层面）

MySQL 内部用 SELECT_LEX（8.0 重命名为 Query_block）表示每个查询块，JOIN 操作封装在 JOIN 对象里，每张表对应一个 JOIN_TAB：

// 简化示意（MySQL 源码 sql/sql_optimizer.h）
struct JOIN_TAB {
    TABLE        *table;          // 对应的表对象
    Key_use      *keyuse;         // 可用的索引访问方式列表
    ha_rows      records;         // 估计返回行数（统计信息）
    double       read_time;       // 读取代价估算
    enum join_type type;          // ALL / ref / range / eq_ref...
    Item         *condition;      // WHERE 过滤条件
    uint         use_quick;       // 是否使用 Range 优化
};

struct JOIN {
    JOIN_TAB     *join_tab;       // 所有表的 JOIN_TAB 数组
    uint         tables;          // 参与 JOIN 的表数量
    double       best_read;       // 当前最优计划代价
    JOIN_TAB    *best_ref[];      // 最优连接顺序
    ha_rows      best_rowcount;   // 预计总行数
};

⚙️ 执行计划生成流程

MySQL 使用 基于代价的优化器（Cost-Based Optimizer, CBO），通过枚举可能的执行计划并计算代价，选出代价最小的方案。
① SQL 解析 词法分析 → 语法分析 → AST ② 语义分析 校验表/列/权限，名称解析 ③ 逻辑重写 子查询转 JOIN、谓词下推 ④ 条件化简 常量折叠、等价替换 ⑤ 访问路径枚举 索引扫描 / 全表扫描 Range / ref / eq_ref... ⑥ JOIN 顺序枚举 Greedy / DP 算法 ≤7表穷举，>7表贪心 ⑦ 代价计算 IO代价 + CPU代价 → 最优方案 ⑧ 执行计划 输出字段 • id（查询序号） • select_type（查询类型） • table（访问的表） • type（访问方式）⭐ • possible_keys（候选索引） • key（实际使用索引） • key_len（索引长度） • rows（估计扫描行数） • filtered（过滤百分比） • Extra（附加信息）⭐ 统计信息来源 • information_schema.STATISTICS • mysql.innodb_table_stats • 索引基数（Cardinality） • 直方图（MySQL 8.0+） • 采样页数（innodb_stats_pages）

代价计算公式

MySQL 将总代价分为 IO 代价 和 CPU 代价，默认权重：IO = 1.0，CPU = 0.2。 
-- 全表扫描代价（简化）
Total_cost = pages × 1.0  /* IO：读取数据页数 */
           + rows  × 0.2  /* CPU：处理每行代价 */

-- 索引范围扫描代价
Total_cost = range_rows / records_per_key × 1.0   /* 索引IO */
           + range_rows × 1.0                      /* 回表IO（如需） */
           + range_rows × 0.2                      /* CPU */

-- 查看 MySQL 内置代价常量
SELECT * FROM mysql.server_cost;
SELECT * FROM mysql.engine_cost;

📋 EXPLAIN 字段详解

EXPLAIN SELECT o.id, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid'
AND u.city = 'Shanghai';
▶ EXPLAIN 输出（模拟）
idselect_typetabletype possible_keyskeykey_len refrowsfilteredExtra
1SIMPLEo range idx_statusidx_status4 const1250100.00 Using index condition
1SIMPLEu eq_ref PRIMARY,idx_cityPRIMARY4 o.user_id110.00 Using where
字段 含义 关键值 / 说明
id 查询序号 相同 id 表示同一层，id 越大越先执行（子查询）。NULL 表示 UNION 结果合并行
select_type 查询类型 SIMPLE / PRIMARY / SUBQUERY / DERIVED / UNION / DEPENDENT SUBQUERY...
table 当前行操作的表 <derived N> 表示派生表，<union M,N> 表示 UNION 结果
partitions 匹配的分区 仅分区表有效，NULL 表示未分区
type 访问类型（性能关键） system > const > eq_ref > ref > range > index > ALL ↓ 性能递减，见 §5
possible_keys 候选索引列表 优化器考虑过的所有索引，不一定实际使用
key 实际选择的索引 NULL = 未用索引（警惕！），覆盖索引时 key 可能出现在 possible_keys 之外
key_len 索引使用的字节数 可推算出用了几列。INT=4B，VARCHAR(N) UTF8MB4 = N×4+2B
ref 与索引比较的值 const（常量）/ 表名.列名（关联列）
rows 预估扫描行数 基于统计信息的估算，不是精确值。乘以所有表的 rows ≈ 总工作量
filtered WHERE 过滤后剩余比例 rows × filtered% = 实际传给下一步的行数。越低越好
Extra 附加执行信息 Using filesort / Using temporary 是性能警告，见 §6

🏎️ type 访问类型图谱

type 字段是判断执行计划质量最关键的指标，从上到下性能递减：

快 慢 system 表只有一行（系统表）。const 的特例 SELECT * FROM (SELECT 1) AS t → type=system 最优 const 主键/唯一索引等值查询，最多返回 1 行 WHERE id = 5 → 主键等值，结果确定为 1 行，早期"物化"为常量 eq_ref JOIN 时被驱动表通过主键/唯一索引等值匹配，每行最多对应 1 条 t1 JOIN t2 ON t1.id = t2.id → t2 type=eq_ref ref 非唯一索引等值查询，可能返回多行 WHERE status = 'paid' 且 status 上有普通索引 range 索引范围扫描，BETWEEN / > / < / IN / LIKE 'xx%' WHERE age BETWEEN 20 AND 30 → 扫描索引的某段范围 index 全索引扫描（扫描整棵索引树），比 ALL 略好（不回表但仍全扫） SELECT id FROM orders → 覆盖索引全扫（Extra: Using index） 需优化 ALL 全表扫描！读取所有数据页，性能最差 无可用索引，或优化器认为全扫比索引+回表更快（小表 or 统计信息偏差） 危险！ 须检查
记忆口诀： system → const → eq_ref → ref → range → index → ALL，
生产环境至少达到 range，核心查询要求 ref 或以上，ALL 必须排查！

🔍 Extra 字段详解

Extra 值含义说明
Using index 覆盖索引 查询所需列都在索引中，无需回表访问数据行 ✅ 最佳状态
Using index condition 索引条件下推（ICP） MySQL 5.6+，WHERE 条件在索引层过滤，减少回表次数
Using where; Using index 覆盖索引 + WHERE 过滤 有额外 WHERE 过滤，但仍只读索引，无回表
const row not found const 扫描无结果 说明常量查找命中索引但结果为空，不影响性能
Extra 值含义建议
Using where Server 层 WHERE 过滤 索引扫描后还需在 server 层过滤，可能意味着索引不够精准
Using join buffer 使用 Join Buffer 被驱动表没有用索引，用内存 buffer 做 BNL join，考虑加索引
Not exists LEFT JOIN 优化 LEFT JOIN 被优化为"找到一行即停止"，通常无需处理
Select tables optimized away 聚合被优化掉 MIN/MAX 直接读索引极值，非常快
Extra 值含义危害解决方案
Using filesort 文件排序（内存或磁盘） 无法通过索引顺序满足 ORDER BY，需额外排序，大数据量慢 建复合索引覆盖 WHERE + ORDER BY 列
Using temporary 使用临时表 GROUP BY / DISTINCT 无法用索引，建内存/磁盘临时表，极耗资源 确保 GROUP BY 列有索引，或调整查询
Using filesort + Using temporary 同时排序 + 临时表 双重开销，大表查询极危险 重构查询，拆分聚合，加合适索引
Impossible WHERE WHERE 矛盾 WHERE 1=0 等永假条件，不执行查询，但说明 SQL 有逻辑问题 检查查询逻辑

🧠 优化器核心策略

CBO 优化器 Cost-Based Optimizer 谓词下推 WHERE 尽早过滤，减少处理行数 常量折叠 WHERE 1+1=2 提前计算，等价替换 子查询转 JOIN IN/EXISTS 子查询半连接优化 派生表合并 FROM (SELECT...) 子查询内联化 JOIN 顺序优化 小结果集驱动大表，减少嵌套循环 索引选择 基数越高索引越优先，回表代价权衡 覆盖索引优化 查询列全在索引中，免回表 MRR 优化 批量排序主键再回表，顺序IO 输出最优执行计划

JOIN 顺序枚举算法

表数量 ≤ optimizer_search_depth（默认7）
穷举所有排列组合，计算每种顺序代价，选最小值
表数量 > optimizer_search_depth
贪心算法（Greedy Search），每步选局部最优，不保证全局最优
STRAIGHT_JOIN 提示
强制固定 JOIN 顺序，跳过枚举，优化器不调整
optimizer_switch 开关
控制 ICP / MRR / BNL / Hash Join 等优化开关

🔧 实战诊断 · 交互式模拟器

选择一个常见的慢查询场景：

性能诊断 Checklist

1

看 type 字段

是否有 ALL（全表扫描）？是否有 index（全索引扫描）？目标：range 及以上

2

看 key 字段

是否为 NULL？若 possible_keys 非空但 key 是 NULL，优化器认为全扫更快，说明索引基数低或统计信息偏差

3

看 rows × filtered

rows 越小越好，filtered 越大越好（意味着 WHERE 更精准）。rows 过大说明索引选择性差

4

看 Extra 字段

发现 Using filesort 或 Using temporary？立即排查 ORDER BY / GROUP BY 的索引覆盖情况

5

用 EXPLAIN ANALYZE 获取真实代价（MySQL 8.0+）

EXPLAIN ANALYZE 会真正执行 SQL 并返回实际执行时间和行数，对比估算值发现统计信息偏差

常用诊断 SQL

-- 1. 基础执行计划
EXPLAIN SELECT * FROM orders WHERE status = 'paid';

-- 2. JSON 格式（包含代价数值，MySQL 5.7+）
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE status = 'paid';

-- 3. 实际执行分析（MySQL 8.0.18+）
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'paid';

-- 4. 查看优化器开关
SELECT @@optimizer_switch;

-- 5. 强制使用 / 忽略某个索引
SELECT * FROM orders FORCE INDEX (idx_status) WHERE status = 'paid';
SELECT * FROM orders IGNORE INDEX (idx_status) WHERE status = 'paid';

-- 6. 查看索引统计信息
SHOW INDEX FROM orders;
ANALYZE TABLE orders;  -- 重新收集统计信息