42. 执行计划与 EXPLAIN

42.1 EXPLAIN 是什么

EXPLAIN 是 MySQL 提供的查询分析工具。在 SQL 语句前加 EXPLAIN，MySQL 不会真正执行这条语句，而是返回它的”执行计划”—MySQL 打算怎么执行这条查询。

EXPLAIN SELECT * FROM users WHERE id = 1;

42.2 EXPLAIN 输出字段总览

EXPLAIN SELECT u.username, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age > 20;

输出类似：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	u	range	PRIMARY,idx_age	idx_age	5	NULL	50	Using where
1	SIMPLE	o	ref	idx_uid	idx_uid	5	test.u.id	10	NULL

一共 12 个字段，逐个讲解最关键的几个。

42.3 id：执行顺序

id 表示查询的序号，反映操作的执行顺序。

• id 相同：从上往下执行；
• id 不同：id 越大越先执行（子查询先于外层执行）；
• id 为 NULL：表示 UNION 合并的结果集，最后执行。

EXPLAIN SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

如果 orders 子查询的 id=2，users 外层查询的 id=1，说明先执行子查询（orders），再执行外层查询（users）。

42.4 select_type：查询类型

值	含义
SIMPLE	简单查询，没有子查询或 UNION
PRIMARY	复杂查询中最外层的查询
SUBQUERY	子查询中的第一个 SELECT
DERIVED	派生表（FROM 子句中的子查询）
UNION	UNION 中的第二个及以后的 SELECT
UNION RESULT	UNION 合并的结果集

42.5 type：访问类型（最重要）

type 表示 MySQL 访问数据的方式，是判断查询效率的关键字段。从好到差排列：

等级	名称	说明	效率
system	system	表只有一行	最优
const	const	通过主键或唯一索引等值查询，最多匹配一行	极高
eq_ref	eq_ref	JOIN 时被驱动表通过主键/唯一索引等值匹配	高
ref	ref	通过普通索引等值查询，可能匹配多行	较高
range	range	索引范围扫描（BETWEEN、>、<、IN）	中
index	index	扫描整个索引树	中低
ALL	ALL	全表扫描	最差

const

-- 主键等值查询
EXPLAIN SELECT * FROM users WHERE id = 1;
-- type = const，效率最高

eq_ref

-- JOIN 时被驱动表用主键匹配
EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
-- 如果 user_id 是主键，o 表的 type = eq_ref

ref

-- 普通索引等值查询
EXPLAIN SELECT * FROM users WHERE email = 'zhangsan@test.com';
-- email 上有普通索引，type = ref

range

-- 索引范围查询
EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;
EXPLAIN SELECT * FROM users WHERE id IN (1, 2, 3);
-- type = range

index

-- 扫描整个索引（不需要回表时比 ALL 好）
EXPLAIN SELECT id FROM users;
-- 只查主键，走索引扫描，type = index

ALL

-- 全表扫描，最差
EXPLAIN SELECT * FROM users WHERE age = 25;  -- age 没有索引
-- type = ALL

42.6 possible_keys 与 key

• possible_keys：MySQL 认为可能使用的索引列表；
• key：MySQL 实际选择的索引。

EXPLAIN SELECT * FROM users WHERE email = 'zhangsan@test.com';
-- possible_keys: idx_email
-- key: idx_email

如果 possible_keys 为 NULL 但 key 也为 NULL，说明没有可用索引，走了全表扫描。

如果 possible_keys 有多个索引但 key 只选了一个，说明优化器选了它认为最优的。

SELECT * FROM users FORCE INDEX(idx_email)
WHERE email = 'zhangsan@test.com';

42.7 key_len：索引使用长度

key_len 表示 MySQL 实际使用了索引中多少字节。对于联合索引，可以通过这个值判断用了几列。

-- 联合索引 (age INT, status VARCHAR(20))
-- INT = 4 字节，VARCHAR(20) utf8mb4 = 20*4+2 = 82 字节

EXPLAIN SELECT * FROM users WHERE age = 25;
-- key_len = 4，只用到了 age 列

EXPLAIN SELECT * FROM users WHERE age = 25 AND status = 'active';
-- key_len = 86，用到了 age 和 status 两列

42.8 rows：预估扫描行数

rows 表示 MySQL 估计要扫描的行数。这个值越小越好。

EXPLAIN SELECT * FROM users WHERE id = 1;
-- rows = 1（主键等值，扫 1 行）

EXPLAIN SELECT * FROM users WHERE age = 25;
-- age 没有索引时 rows = 10000（全表扫描）
-- age 有索引时 rows = 50（索引定位后只扫 50 行）

42.9 Extra：附加信息（关键）

Extra 包含执行计划的额外信息，对优化非常有价值。常见的值：

Using where

使用了 WHERE 条件过滤。这个最常见，本身不是问题，但如果同时 type = ALL，说明全表扫描后再用 WHERE 过滤，需要加索引。

Using index

覆盖索引：查询所需数据直接从索引中获取，不需要回表。这是最理想的情况。

EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 1;
-- 联合索引 (user_id, status) 包含查询列
-- Extra: Using index

Using temporary

使用了临时表。通常出现在 GROUP BY、DISTINCT、ORDER BY 场景。临时表有性能开销，尽量优化掉。

EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status;
-- 如果 status 没有索引，Extra: Using temporary; Using filesort

Using filesort

使用了文件排序。ORDER BY 的列没有走索引时会出现。filesort 需要在内存（或磁盘）中排序，数据量大时很慢。

EXPLAIN SELECT * FROM users ORDER BY age;
-- age 没有索引，Extra: Using filesort

Using join buffer

JOIN 时被驱动表没有可用索引，使用 Join Buffer（内存缓冲区）做嵌套循环。需要给 JOIN 条件列加索引。

Using index condition

使用了索引下推（ICP），在存储引擎层提前过滤。这是个好标记，说明优化器在努力减少回表。

Extra 常见值速查

Extra 值	含义	好坏
Using index	覆盖索引，不回表	好
Using where	用 WHERE 过滤	正常
Using index condition	索引下推	好
Using temporary	临时表	需优化
Using filesort	文件排序	需优化
Using join buffer	JOIN 无索引	需优化

42.10 EXPLAIN 实战分析

-- 建表和数据
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    age INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_age_username (age, username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 分析查询 1：走索引
EXPLAIN SELECT * FROM users WHERE email = 'zhangsan@test.com';
-- type=ref, key=idx_email, rows=1, Extra=NULL
-- 结论：走了索引，效率高

-- 分析查询 2：覆盖索引
EXPLAIN SELECT age, username FROM users WHERE age = 25;
-- type=ref, key=idx_age_username, Extra=Using index
-- 结论：覆盖索引，不回表，最优

-- 分析查询 3：全表扫描
EXPLAIN SELECT * FROM users WHERE username = 'zhangsan';
-- type=ALL, key=NULL, rows=10000, Extra=Using where
-- 结论：没有可用索引，全表扫描，需优化
-- username 在联合索引的第二位，不能单独使用
-- 解决方案：给 username 单独建索引，或调整查询条件

-- 分析查询 4：文件排序
EXPLAIN SELECT * FROM users ORDER BY created_at;
-- type=ALL, key=NULL, Extra=Using filesort
-- 结论：created_at 没有索引，需要排序
-- 解决方案：给 created_at 建索引

42.11 EXPLAIN FORMAT

MySQL 8.0+ 支持多种输出格式：

-- 传统表格格式（默认）
EXPLAIN SELECT * FROM users WHERE id = 1;

-- JSON 格式（信息更详细）
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 1;

-- 树状格式（MySQL 8.0.16+，更直观）
EXPLAIN FORMAT=TREE SELECT * FROM users WHERE id = 1;

-- ANALYZE 格式（实际执行并显示真实耗时，MySQL 8.0.18+）
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

42.12 小结

• EXPLAIN 是 SQL 调优第一工具，在 SQL 前加 EXPLAIN 即可查看执行计划；
• type 是最关键字段，从 system 到 ALL 共 8 个等级，至少要到 range；
• key 显示实际使用的索引，NULL 表示没走索引；
• rows 是预估扫描行数，越小越好；
• Extra 重点关注：Using index（好）、Using temporary（需优化）、Using filesort（需优化）；
• 覆盖索引显示 Using index，是查询优化的目标；
• MySQL 8.0+ 支持 JSON、TREE 格式和 EXPLAIN ANALYZE 实际执行分析。

下一节讲慢查询分析和性能调优，把 EXPLAIN 用到实战中。