EXPLAIN 执行计划详解

EXPLAIN 是 SQL 优化的第一手证据，但多数人只看 type 和 key，忽略了 rows × filtered 才是估算代价的核心，也不清楚 Extra 里 Using filesort 和 Using temporary 谁更贵。这篇文章逐字段拆解输出，给出可复现的示例，并说明 8.0 引入的树形格式与 EXPLAIN ANALYZE 的差别。基准 MySQL 8.0，5.7 的输出差异集中标注。

一、准备数据

CREATE DATABASE IF NOT EXISTS explain_demo DEFAULT CHARACTER SET utf8mb4;
USE explain_demo;

CREATE TABLE dept (
  id   INT          NOT NULL AUTO_INCREMENT,
  name VARCHAR(32)  NOT NULL,
  PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE emp (
  id         INT           NOT NULL AUTO_INCREMENT,
  dept_id    INT           NOT NULL,
  name       VARCHAR(32)   NOT NULL,
  salary     DECIMAL(10,2) NOT NULL,
  hired_at   DATE          NOT NULL,
  PRIMARY KEY (id),
  KEY idx_dept (dept_id),
  KEY idx_salary (salary)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

SET SESSION cte_max_recursion_depth = 10000000;

INSERT INTO dept (name)
WITH RECURSIVE s AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM s WHERE n < 20)
SELECT CONCAT('dept_', n) FROM s;

SET SESSION cte_max_recursion_depth = 10000000;

INSERT INTO emp (dept_id, name, salary, hired_at)
WITH RECURSIVE s AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM s WHERE n < 100000)
SELECT 1 + n % 20, CONCAT('emp_', n), 3000 + (n % 200) * 50,
       DATE_ADD('2020-01-01', INTERVAL (n % 2000) DAY)
FROM s;

ANALYZE TABLE dept, emp;

二、基本用法

EXPLAIN SELECT e.name, e.salary, d.name AS dept_name
FROM emp e
JOIN dept d ON d.id = e.dept_id
WHERE e.salary > 10000
ORDER BY e.hired_at DESC
LIMIT 10;

8.0 默认输出仍是传统表格，与 5.7 字段基本一致（5.7 多一个 partitions 的位置差异，实际 5.7 已有该列）。核心列有 10 个：

| 列 | 含义 |

| --- | --- |

| id | 查询序号，越大越先执行；相同则从上往下 |

| select_type | 查询类型：SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION |

| table | 访问的表或派生表名 |

| partitions | 命中的分区 |

| type | 访问类型，最重要的列 |

| possible_keys | 可能用到的索引 |

| key | 实际选择的索引 |

| key_len | 使用的索引字节数，可判断联合索引用了几列 |

| ref | 与索引比较的列或常量 |

| rows | 估算扫描行数 |

| filtered | 按条件过滤后剩余行数百分比 |

| Extra | 附加信息，信息量最大 |

rows × filtered 才是优化器估算的最终行数。只看 rows 会误判：rows=1000, filtered=10% 实际只返回 100 行。

三、type 访问类型

从优到劣：

| type | 含义 | 典型场景 |

| --- | --- | --- |

| system | 表只有一行 | 系统表 |

| const | 主键或唯一索引等值命中 | WHERE id = 1 |

| eq_ref | JOIN 时被驱动表用主键/唯一索引等值匹配 | 主键 JOIN |

| ref | 非唯一索引等值匹配 | WHERE dept_id = 3 |

| range | 索引范围扫描 | BETWEEN、>、IN |

| index | 全索引扫描 | 覆盖索引但扫全树 |

| ALL | 全表扫描 | 无可用索引 |

index 常被误认为比 ALL 好，实际上它扫的是整棵索引树，如果索引很大，代价可能接近甚至超过 ALL。判断标准是"是否真的需要扫全部索引页"。

-- const
EXPLAIN SELECT * FROM dept WHERE id = 1;

-- ref
EXPLAIN SELECT * FROM emp WHERE dept_id = 3;

-- range
EXPLAIN SELECT * FROM emp WHERE salary BETWEEN 5000 AND 6000;

-- ALL
EXPLAIN SELECT * FROM emp WHERE name = 'emp_100';

最后一条因为 name 无索引，只能是 ALL。

四、select_type 与 id

id 相同表示同一个查询块，从上往下执行；id 不同时，值越大越先执行。子查询、派生表会生成新的 id。

select_type 的常见取值：

• SIMPLE：不含子查询或 UNION 的简单查询。
• PRIMARY：最外层的查询。
• SUBQUERY：SELECT 列表或 WHERE 中的不相关子查询，先于外层执行。
• DEPENDENT SUBQUERY：相关子查询，外层每行都要执行一次，性能杀手。
• DERIVED：FROM 子句中的派生表，8.0 可能被合并（Extra 无 Using temporary 即合并成功）。
• UNION / UNION RESULT：UNION 的各分支与结果集。

-- DEPENDENT SUBQUERY：外层每行执行一次
EXPLAIN SELECT * FROM emp e
WHERE EXISTS (SELECT 1 FROM dept d WHERE d.id = e.dept_id AND d.name = 'dept_3');

看到 DEPENDENT SUBQUERY 且 rows 很大时，优先改写为 JOIN，通常能有一个数量级的提升。

五、key_len 判断联合索引使用列数

key_len 是索引列在索引中的字节数之和，可以反推用了联合索引的前几列。

计算规则（以 utf8mb4 为例）：

• INT：4 字节，可空 +1。
• BIGINT：8 字节，可空 +1。
• VARCHAR(n)：n × 4 + 2，可空再 +1。
• CHAR(n)：n × 4，可空 +1。
• DATETIME：5 字节（8.0），可空 +1。

ALTER TABLE emp ADD KEY idx_dept_hired (dept_id, hired_at);

EXPLAIN SELECT * FROM emp WHERE dept_id = 3;
-- key_len = 4（dept_id 为 INT NOT NULL）

EXPLAIN SELECT * FROM emp WHERE dept_id = 3 AND hired_at > '2023-01-01';
-- key_len = 7（4 + 3，DATE 为 3 字节；若列是 DATETIME 则为 4 + 5 = 9）

key_len 变小往往意味着联合索引只用到了前几列，是排查"索引只生效一半"的利器。

六、Extra 常见取值

| 值 | 含义 | 严重度 |

| --- | --- | --- |

| Using index | 覆盖索引，无需回表 | 好 |

| Using index condition | 索引下推（ICP） | 好 |

| Using where | Server 层过滤 | 一般 |

| Using filesort | 额外排序，未用索引顺序 | 需关注 |

| Using temporary | 使用临时表 | 高 |

| Using join buffer | JOIN 无索引，用 join buffer | 高 |

| Using MRR | 多范围读 | 好 |

| Select tables optimized away | 优化器直接算出结果 | 好 |

| Using intersect/union | index_merge | 视情况 |

Using filesort 不一定是磁盘排序，内存里也可能叫 filesort；它的真实含义是"无法利用索引顺序，需要额外排序"。排序能否在内存完成由 sort_buffer_size 决定，超出后会退化为磁盘归并，这也是大结果集排序突然变慢的常见原因。Using temporary 常见于 GROUP BY 无索引、DISTINCT、UNION，代价通常更高——临时表可能落到磁盘，还要额外的写入与回读。

-- Using filesort
EXPLAIN SELECT * FROM emp WHERE dept_id = 3 ORDER BY salary;

-- 消除：建 (dept_id, salary) 联合索引
ALTER TABLE emp ADD KEY idx_dept_salary (dept_id, salary);
EXPLAIN SELECT * FROM emp WHERE dept_id = 3 ORDER BY salary;  -- 不再 filesort

-- Using temporary
EXPLAIN SELECT dept_id, COUNT(*) FROM emp GROUP BY dept_id;
-- dept_id 有索引，通常可避免 temporary

七、8.0 的树形格式

8.0.16 起支持 EXPLAIN FORMAT=TREE，8.0.18 起 EXPLAIN ANALYZE 会真正执行并给出实际耗时与行数。

EXPLAIN FORMAT=TREE
SELECT d.name, COUNT(*) AS cnt
FROM emp e JOIN dept d ON d.id = e.dept_id
WHERE e.salary > 8000
GROUP BY d.name;

EXPLAIN ANALYZE
SELECT d.name, COUNT(*) AS cnt
FROM emp e JOIN dept d ON d.id = e.dept_id
WHERE e.salary > 8000
GROUP BY d.name;

EXPLAIN ANALYZE 的输出形如：

-> Table scan on <temporary>  (actual time=12.1..12.3 rows=20 loops=1)
    -> Aggregate: count(0)  (actual time=11.8..12.0 rows=20 loops=1)
        -> Nested loop inner join  (actual time=0.4..10.9 rows=1600 loops=1)
            -> Filter: (e.salary > 8000.00)  (actual time=0.2..5.1 rows=1600 loops=1)
                -> Table scan on e  (actual time=0.1..4.0 rows=100000 loops=1)

每一行的 actual time=起点..终点 是该算子处理第一行与最后一行的时刻（毫秒），rows 是实际输出的行数，loops 是被执行的次数。判断瓶颈的方法很直接：用子节点的 终点 - 起点 乘以 loops，得到该算子的实际耗时。上面的例子中 Table scan on e 花了约 4 ms 输出 10 万行，而 Filter 阶段耗时 5.1 ms、只剩 1600 行，说明过滤比扫描本身更贵。

关键还要对比 rows 的估算值与实际值：两者差一个数量级，说明统计信息失真，此时优化器很可能选错索引，需要 ANALYZE TABLE 或加直方图。

注意：EXPLAIN ANALYZE 会真正执行 SQL（8.0.18+ 对 SELECT 会执行），在线上对慢查询使用时要评估影响，必要时加 LIMIT 或放到从库。5.7 没有这个能力，只能用 EXPLAIN 估算加 SHOW PROFILES。

八、JOIN 的执行顺序

EXPLAIN 的 id 相同表示同一个查询块，此时 table 列的顺序就是 JOIN 顺序，上面的表是驱动表。

EXPLAIN SELECT * FROM emp e JOIN dept d ON d.id = e.dept_id WHERE e.salary > 9000;

MySQL 8.0 默认使用 hash join（8.0.18 起对无索引等值 JOIN 生效），在 Extra 中显示 Using join buffer (hash join)。5.7 只有 Block Nested Loop，这是升级后 JOIN 性能改善的重要原因。两者都会用 join_buffer_size，但 hash join 建的是哈希表，等值匹配是 O(1)，而 BNL 要对缓冲区里的每一行做比较。

如果被驱动表的关联列有索引，优化器会选 Index Nested Loop Join，type 显示为 ref 或 eq_ref，这是最理想的形态。判断方法：看 EXPLAIN 里被驱动表的 key 是否有值。没有索引时才会退化成 hash join 或 BNL。

用 STRAIGHT_JOIN 可以强制驱动表顺序，但一般不建议，交给优化器更稳：

EXPLAIN SELECT * FROM emp e STRAIGHT_JOIN dept d ON d.id = e.dept_id;

九、常见坑 / 最佳实践

1. 不要只看 type 和 key。rows、filtered、Extra 三者结合才能判断真实代价。type=ref 但 rows=50万 依然是慢查询。
2. key_len 是排查联合索引的必看项。如果预期用 2 列却只有 1 列的字节数，说明第 2 列因为范围条件或类型不匹配没生效。
3. 用 EXPLAIN ANALYZE 验证估算准确性（8.0.18+）。估算与实际差 10 倍以上时，先 ANALYZE TABLE，再考虑 8.0 的直方图：ANALYZE TABLE emp UPDATE HISTOGRAM ON dept_id。
4. Using temporary 优先于 Using filesort 处理。临时表涉及磁盘落盘的概率更高，尤其在大结果集 GROUP BY 时。
5. 5.7 与 8.0 的 EXPLAIN 输出不能直接对比。8.0 增加了 hash join、树形格式、直方图影响，同一 SQL 的执行计划可能完全不同，升级后要重新基线化。

十、小结

• EXPLAIN 的核心是 type、key_len、rows × filtered、Extra 四组信息。
• type 从 const 到 ALL 逐级变差，index 并不等于好。
• key_len 能反推联合索引实际使用了几列，是排查"半生效"的关键。
• EXPLAIN ANALYZE（8.0.18+）给出真实耗时与行数，用于验证优化器估算是否失真。
• JOIN 顺序由 id 相同的行序决定，8.0 的 hash join 让无索引等值 JOIN 明显改善。