跳到主要内容

EXPLAIN 执行计划详解

问题​

如何使用 EXPLAIN 分析 SQL 的执行计划？各字段（type、possible_keys、key、rows、Extra）分别代表什么意思？

答案​

EXPLAIN 基本用法​

在任何 SELECT 语句前加 EXPLAIN 即可查看执行计划：

EXPLAIN SELECT * FROM users WHERE age > 25;

MySQL 8.0+ 还支持分析 UPDATE/DELETE/INSERT：

EXPLAIN DELETE FROM orders WHERE status = 'expired';

EXPLAIN 输出字段全解析​

一个完整的 EXPLAIN 结果包含以下字段：

字段说明重要程度
id查询编号，值越大越先执行⭐⭐
select_type查询类型⭐⭐⭐
table访问的表⭐⭐
partitions匹配的分区⭐
type访问类型（最重要）⭐⭐⭐⭐⭐
possible_keys可能使用的索引⭐⭐⭐
key实际使用的索引⭐⭐⭐⭐⭐
key_len使用索引的字节数⭐⭐⭐
ref与索引比较的列或常量⭐⭐
rows预估扫描行数⭐⭐⭐⭐
filtered按条件过滤后的百分比⭐⭐⭐
Extra额外信息（很重要）⭐⭐⭐⭐⭐

type 字段详解（访问类型）​

type 是最重要的字段，表示 MySQL 如何查找行。从最优到最差排序：

system > const > eq_ref > ref > fulltext > ref_or_null > 
index_merge > unique_subquery > index_subquery > range > 
index > ALL
关键性能分界线

至少要达到 range 级别，如果出现 ALL（全表扫描）说明需要优化。

type说明示例
system表只有一行（系统表），const 的特例访问系统表
const通过主键或唯一索引查找一行WHERE id = 1
eq_refJOIN 时使用主键或唯一索引主键 JOIN
ref使用非唯一索引查找WHERE name = 'Tom'
range索引范围扫描WHERE age > 25、IN (...)
index全索引扫描（遍历整个索引树）覆盖索引但没有 WHERE
ALL全表扫描 ❌无可用索引
-- const：主键等值查找
EXPLAIN SELECT * FROM users WHERE id = 1;
-- type: const

-- ref：非唯一索引等值查找
EXPLAIN SELECT * FROM users WHERE name = 'Tom';
-- type: ref（假设 name 有普通索引）

-- range：索引范围扫描
EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;
-- type: range（假设 age 有索引）

-- ALL：全表扫描
EXPLAIN SELECT * FROM users WHERE age + 1 > 25;
-- type: ALL（索引列参与运算，索引失效）

key_len 计算规则​

key_len 表示实际使用了索引的多少个字节，用来判断联合索引用了几列：

数据类型字节数说明
INT4固定
BIGINT8固定
CHAR(n)n × 字符集字节数utf8mb4 = n × 4
VARCHAR(n)n × 字符集字节数 + 2+2 存长度
允许 NULL+1NULL 标志位
-- 联合索引 idx(a, b, c)，a 为 INT NOT NULL，b 为 VARCHAR(20)
-- key_len = 4 → 只用了 a
-- key_len = 4 + 82 = 86 → 用了 a + b（82 = 20×4+2）
-- key_len = 86 + ... → 用了 a + b + c

Extra 字段详解​

Extra 提供了 MySQL 执行查询的额外信息：

Extra 值说明好坏
Using index覆盖索引，无需回表✅ 好
Using where需要在 Server 层过滤中性
Using index condition索引条件下推（ICP）✅ 好
Using temporary使用了临时表❌ 需优化
Using filesort额外排序（非索引排序）❌ 需优化
Using join bufferJOIN 时使用了连接缓冲⚠️ 关注
Impossible WHEREWHERE 条件总为 false-
Select tables optimized away聚合可直接从索引获取✅ 好
Using filesort 和 Using temporary

这两个是性能杀手，出现时重点关注：

  • Using filesort：ORDER BY 没有利用索引排序 → 考虑添加包含排序列的索引
  • Using temporary：GROUP BY、DISTINCT、UNION 产生临时表 → 考虑优化查询或添加合适索引

select_type 常见值​

select_type说明
SIMPLE简单查询（无子查询、无 UNION）
PRIMARY最外层查询
SUBQUERY子查询中的第一个 SELECT
DERIVEDFROM 子句中的子查询（派生表）
UNIONUNION 中的第二个及后续 SELECT
DEPENDENT SUBQUERY相关子查询（依赖外层查询）

EXPLAIN 实战分析​

案例 1：全表扫描优化为索引查找

-- 优化前
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';
-- type: ALL, rows: 1000000 ❌

-- 添加索引
ALTER TABLE orders ADD INDEX idx_user_status(user_id, status);

-- 优化后
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';
-- type: ref, key: idx_user_status, rows: 15 ✅

案例 2：消除 Using filesort

-- 优化前
EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY create_time DESC;
-- type: ref, Extra: Using filesort ❌

-- 添加联合索引（查询列 + 排序列）
ALTER TABLE orders ADD INDEX idx_user_time(user_id, create_time);

-- 优化后
EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY create_time DESC;
-- type: ref, Extra: Using index condition ✅（filesort 消失）

案例 3：覆盖索引避免回表

-- 优化前（需要回表查 name）
EXPLAIN SELECT id, name FROM users WHERE age > 25;
-- Extra: Using where

-- 添加覆盖索引
ALTER TABLE users ADD INDEX idx_age_name(age, name);

-- 优化后
EXPLAIN SELECT id, name FROM users WHERE age > 25;
-- Extra: Using where; Using index ✅（覆盖索引）

EXPLAIN ANALYZE（MySQL 8.0.18+）​

EXPLAIN ANALYZE 会实际执行 SQL，返回真实的执行时间和行数：

EXPLAIN ANALYZE 
SELECT u.name, COUNT(o.id) 
FROM users u 
JOIN orders o ON u.id = o.user_id 
GROUP BY u.id;

输出中包含：

  • actual time：实际耗时（毫秒）
  • rows：实际扫描行数
  • loops：循环次数
注意

EXPLAIN ANALYZE 会真正执行 SQL，对于 UPDATE/DELETE 要谨慎使用，建议在只读副本执行。

EXPLAIN FORMAT=JSON/TREE​

-- JSON 格式：包含成本信息
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 25;

-- TREE 格式（8.0.16+）：直观展示执行树
EXPLAIN FORMAT=TREE SELECT * FROM users WHERE age > 25;

JSON 格式中的重要字段：

  • query_cost：查询总成本
  • read_cost：IO 成本
  • eval_cost：CPU 计算成本

常见面试问题​

Q1: EXPLAIN 中 type 字段从最优到最差是什么？​

答案：

从最优到最差：system > const > eq_ref > ref > range > index > ALL

日常优化中的关键分界线：

  • const/eq_ref：主键/唯一索引查找，最优
  • ref：普通索引查找，常见且可接受
  • range：索引范围扫描，需要保持在这个级别及以上
  • index：全索引扫描，虽然比 ALL 好但仍需关注
  • ALL：全表扫描，通常需要优化

Q2: Extra 中出现 Using filesort 怎么优化？​

答案：

Using filesort 表示 MySQL 需要额外的排序操作（而非利用索引的有序性），优化方法：

  1. 添加排序列的索引：让 ORDER BY 的列包含在索引中
  2. 利用联合索引：WHERE a = 1 ORDER BY b → 创建 (a, b) 联合索引
  3. 避免排序方向不一致：ORDER BY a ASC, b DESC 在 MySQL 8.0 之前无法用索引
  4. 减少排序数据量：只 SELECT 需要的列，避免 SELECT *

Q3: 如何通过 key_len 判断联合索引用了几列？​

答案：

根据每列的类型计算字节数累加：

  • INT = 4，BIGINT = 8
  • VARCHAR(n) = n × 4 + 2（utf8mb4）
  • 允许 NULL 额外 +1

例如联合索引 (user_id INT, status VARCHAR(10))：

  • key_len = 4 → 只用了 user_id
  • key_len = 4 + 42 = 46 → 用了 user_id + status

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

答案：

说明有可用索引但优化器选择不使用，可能原因：

  1. 数据量太小：表数据少，全表扫描比用索引快
  2. 选择性太低：索引区分度差（如性别列），用索引反而要大量回表
  3. 统计信息不准：ANALYZE TABLE 更新统计信息后可能会使用
  4. 优化器误判：可以用 FORCE INDEX(idx_name) 强制使用

Q5: EXPLAIN 和 EXPLAIN ANALYZE 有什么区别？​

答案：

对比EXPLAINEXPLAIN ANALYZE
是否执行 SQL否（只分析）是（实际执行）
rows预估值实际值
耗时无有（actual time）
适用范围所有 SQLSELECT（DML 慎用）
MySQL 版本所有版本8.0.18+

EXPLAIN ANALYZE 的信息更准确，但因为实际执行了 SQL，对性能有影响。

相关链接​