跳到主要内容

EXPLAIN 执行计划详解

问题​

如何使用 EXPLAIN 分析 SQL 的执行计划？各字段含义是什么？如何根据执行计划优化查询？

答案​

一、EXPLAIN 基本用法​

EXPLAIN SELECT * FROM users WHERE age > 25 ORDER BY name;

在 MySQL 8.0+ 中，还支持 JSON/TREE 格式：

-- JSON 格式，包含更详细的成本信息
EXPLAIN FORMAT=JSON SELECT ...;

-- TREE 格式（8.0.16+），展示实际执行树
EXPLAIN FORMAT=TREE SELECT ...;

-- ANALYZE（8.0.18+），真正执行并返回实际行数
EXPLAIN ANALYZE SELECT ...;

二、EXPLAIN 各字段详解​

字段一览​

字段说明重要程度
idSELECT 标识符⭐⭐
select_type查询类型⭐⭐
table输出行对应的表⭐⭐
partitions匹配的分区⭐
type访问类型⭐⭐⭐⭐⭐
possible_keys可能使用的索引⭐⭐⭐
key实际使用的索引⭐⭐⭐⭐
key_len使用索引的长度⭐⭐⭐
ref索引等值匹配的列⭐⭐
rows预估扫描行数⭐⭐⭐⭐
filtered条件过滤的百分比⭐⭐⭐
Extra额外信息⭐⭐⭐⭐⭐

2.1 type（访问类型） — 最重要​

从最优到最差排序：

type含义说明
system表只有一行系统表的特例
const主键/唯一索引常量匹配WHERE id = 1
eq_ref主键/唯一索引关联多表 JOIN，被驱动表用主键匹配
ref非唯一索引等值查找WHERE name = '张三'（name 有普通索引）
range索引范围扫描WHERE age > 25、IN()、BETWEEN
index全索引扫描扫描整棵索引树（比全表好）
ALL全表扫描最差，需要优化
优化目标

至少达到 range 级别，最好是 ref 或以上。出现 ALL 说明没有用到索引。

-- const：主键常量查找
EXPLAIN SELECT * FROM users WHERE id = 1;

-- ref：普通索引查找
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- range：范围查找
EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;

-- ALL：全表扫描
EXPLAIN SELECT * FROM users WHERE YEAR(create_time) = 2024;

2.2 key 与 key_len​

  • key：实际使用的索引名。为 NULL 表示没有使用索引
  • key_len：使用了索引的多少字节，可用于判断联合索引使用了几个列

key_len 计算规则：

类型字节数
INT4
BIGINT8
CHAR(n)n × 字符集字节数（utf8mb4=4）
VARCHAR(n)n × 字符集字节数 + 2（变长标记）
允许 NULL额外 +1

联合索引示例：假设有联合索引 (a INT, b VARCHAR(50), c INT)

-- key_len = 4 → 只用了 a
EXPLAIN SELECT * FROM t WHERE a = 1;

-- key_len = 4 + 50*4+2 = 206 → 用了 a, b
EXPLAIN SELECT * FROM t WHERE a = 1 AND b = 'hello';

-- key_len = 4 + 202 + 4 = 210 → 用了 a, b, c
EXPLAIN SELECT * FROM t WHERE a = 1 AND b = 'hello' AND c = 10;

2.3 Extra — 重要的额外信息​

Extra 值含义好/坏
Using index覆盖索引，不需要回表✅ 好
Using whereServer 层过滤⚠️ 一般
Using index condition索引条件下推（ICP）✅ 好
Using temporary使用临时表❌ 坏
Using filesort额外排序（非索引排序）❌ 坏
Using join bufferJOIN 使用了连接缓冲⚠️ 注意
Using MRR多范围读优化✅ 好
Select tables optimized away聚合函数直接从索引取值✅ 好
Using filesort 和 Using temporary

这两个是最需要关注的「坏信号」：

  • Using filesort：ORDER BY 无法使用索引排序，需要额外排序操作
  • Using temporary：GROUP BY / DISTINCT / UNION 需要临时表 可以通过调整索引来消除。

三、实战分析案例​

案例 1：消除 Using filesort​

-- 表结构
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id INT,
  status TINYINT,
  create_time DATETIME,
  INDEX idx_user_id (user_id)
);

-- 查询：查找某用户的订单并按时间排序
EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY create_time DESC;

结果：type=ref, key=idx_user_id, Extra=Using filesort

优化：创建联合索引覆盖查询和排序

ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time);

优化后：type=ref, key=idx_user_time, Extra=NULL（排序也走索引了）

案例 2：覆盖索引消除回表​

-- 查询：只需要 user_id 和 create_time
EXPLAIN SELECT user_id, create_time FROM orders WHERE user_id = 100;

如果有 idx_user_time (user_id, create_time) 索引：

  • Extra = Using index：覆盖索引，无需回表

案例 3：多表 JOIN 分析​

EXPLAIN 
SELECT u.name, o.amount 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE o.status = 1;

关注点：

  • 第一行（驱动表）的 type 尽量好
  • 第二行（被驱动表）的 type 至少是 ref（eq_ref 最理想）
  • 被驱动表的 key 不为 NULL

四、EXPLAIN 排查清单​

常见面试问题​

Q1: type 为 index 和 ALL 有什么区别？​

答案：
两者都是全扫描，但：

  • index：扫描整棵索引树（比 ALL 好，因为索引通常比数据小）
  • ALL：扫描整个数据文件（最差）

比如 SELECT count(*) FROM users 可能是 index 类型——扫描最小的二级索引来计数。

Q2: possible_keys 有值但 key 为 NULL 是什么原因？​

答案：
优化器评估后认为使用索引的成本比全表扫描更高（比如表数据量很小，或者查询需要返回大部分行时回表成本太高）。可以通过 FORCE INDEX 强制使用索引来验证。

Q3: filtered 字段有什么用？​

答案：
filtered 表示经过 WHERE 条件过滤后，预估剩余行数的百分比。实际返回行数 ≈ rows × filtered%。

在多表 JOIN 中特别有用：驱动表的 rows × filtered% 决定了被驱动表需要查找多少次。filtered 值越低，说明过滤越有效。

Q4: EXPLAIN ANALYZE 和 EXPLAIN 的区别？​

答案：

  • EXPLAIN：只预估，不真正执行
  • EXPLAIN ANALYZE（MySQL 8.0.18+）：真正执行 SQL，返回每个节点的实际行数、循环次数和执行时间
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;
-- 输出示例：
-- -> Filter: (users.age > 25) (cost=10.5 rows=33) (actual time=0.1..0.3 rows=42 loops=1)
--    -> Table scan on users (cost=10.5 rows=100) (actual time=0.05..0.2 rows=100 loops=1)

相关链接​