跳到主要内容

EXPLAIN 执行计划详解

问题​

如何使用 EXPLAIN 分析 SQL 的执行计划？各列的含义是什么？如何通过 EXPLAIN 判断 SQL 是否需要优化？

答案​

基本用法​

EXPLAIN 基本用法
EXPLAIN SELECT * FROM user WHERE name = '张三';

-- MySQL 8.0+ 支持查看实际执行信息
EXPLAIN ANALYZE SELECT * FROM user WHERE name = '张三';

-- 输出 JSON 格式（更详细）
EXPLAIN FORMAT=JSON SELECT * FROM user WHERE name = '张三';

-- 输出 TREE 格式（MySQL 8.0.16+）
EXPLAIN FORMAT=TREE SELECT * FROM user WHERE name = '张三';

EXPLAIN 输出各列详解​

一条 EXPLAIN 的输出示例：

+----+-------------+-------+------+---------------+----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------+
|  1 | SIMPLE      | user  | ref  | idx_name      | idx_name | 152     | const |    1 | NULL  |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------+

id（查询序列号）​

情况含义
id 相同表示同一层查询，从上到下依次执行
id 不同数字越大优先级越高，先执行
id 为 NULL表示 UNION 的结果集合并

select_type（查询类型）​

值说明
SIMPLE简单查询，无子查询和 UNION
PRIMARY最外层的查询
SUBQUERYSELECT 或 WHERE 中的子查询
DERIVEDFROM 子句中的子查询（派生表）
UNIONUNION 中第二个及之后的查询
UNION RESULTUNION 的结果集
DEPENDENT SUBQUERY相关子查询（依赖外层查询）

type（访问类型）⭐最重要​

从优到劣排列，至少要达到 range 级别：

type说明性能
system表只有一行（系统表）最优
const通过主键或唯一索引等值查询，结果最多一行极好
eq_refJOIN 时对驱动表的每一行，被驱动表通过主键/唯一索引精确匹配一行很好
ref非唯一索引等值查询，可能返回多行好
range索引范围查询（>, <, BETWEEN, IN）一般
index全索引扫描（遍历整个索引树）差
ALL全表扫描，无索引最差
各 type 示例
-- const：主键等值查询
EXPLAIN SELECT * FROM user WHERE id = 1;

-- eq_ref：JOIN 通过主键关联
EXPLAIN SELECT * FROM orders o
JOIN user u ON o.user_id = u.id;

-- ref：非唯一索引等值查询
EXPLAIN SELECT * FROM user WHERE name = '张三';

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

-- ALL：全表扫描
EXPLAIN SELECT * FROM user WHERE age + 1 = 25;  -- 索引失效

possible_keys 和 key​

列说明
possible_keys可能使用的索引（基于查询条件）
key实际使用的索引。NULL 表示没使用索引

possible_keys 有值但 key 为 NULL，意味着优化器判断使用索引不如全表扫描。

key_len（索引使用长度）​

key_len 表示实际使用的索引长度（字节数），可用于判断联合索引使用了几个字段：

类型长度计算
INT4 字节
BIGINT8 字节
VARCHAR(n)n × 字符集字节数 + 2（变长标记）
CHAR(n)n × 字符集字节数
允许 NULL+1 字节
通过 key_len 判断联合索引使用情况
-- 联合索引 idx_a_b_c(a INT, b VARCHAR(50), c INT)
-- UTF-8: VARCHAR(50) = 50*3+2 = 152

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

-- 用 a + b: key_len = 4 + 152 = 156
EXPLAIN SELECT * FROM t WHERE a = 1 AND b = 'test';

-- 用 a + b + c: key_len = 4 + 152 + 4 = 160
EXPLAIN SELECT * FROM t WHERE a = 1 AND b = 'test' AND c = 10;

rows（预估扫描行数）​

MySQL 优化器预估需要扫描的行数。这个值越小越好。注意这是估算值，不是精确值。

filtered（过滤比例）​

表示经过表条件过滤后，剩余行数的百分比。rows × filtered% 就是最终参与下一步操作的行数。

Extra（额外信息）⭐重点关注​

值含义是否需优化
Using index覆盖索引，无需回表✅ 好
Using where存储引擎返回后还需在 Server 层过滤一般
Using index condition索引下推（ICP）✅ 好
Using temporary使用了临时表（常见于 GROUP BY、DISTINCT）⚠️ 需优化
Using filesort额外排序操作（未利用索引排序）⚠️ 需优化
Using join bufferJOIN 时使用了 Join Buffer（被驱动表无索引）⚠️ 需优化
Select tables optimized away优化器直接返回结果（如 MIN/MAX 命中索引）✅ 最优
Impossible WHEREWHERE 条件永远为 false检查 SQL 逻辑
Using filesort 和 Using temporary

这两个是最需要关注的性能警告：

  • Using filesort：说明 ORDER BY 没有利用到索引排序，需要额外的排序操作。解决方法：让 ORDER BY 的字段在索引中
  • Using temporary：说明需要临时表来处理查询（常见于 GROUP BY 的字段不在索引中）。解决方法：为 GROUP BY 字段添加索引

EXPLAIN ANALYZE（MySQL 8.0.18+）​

EXPLAIN ANALYZE 会实际执行 SQL，并显示每一步的真实执行时间和行数：

EXPLAIN ANALYZE 示例
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM user u
JOIN orders o ON u.id = o.user_id
WHERE u.age > 20
GROUP BY u.id;

输出（TREE 格式）：

-> Group aggregate: count(o.id)  (cost=2.5 rows=3) (actual time=0.15..0.25 rows=3 loops=1)
    -> Nested loop join  (cost=1.8 rows=5) (actual time=0.08..0.20 rows=5 loops=1)
        -> Filter: (u.age > 20)  (cost=0.6 rows=3) (actual time=0.05..0.07 rows=3 loops=1)
            -> Table scan on u  (cost=0.6 rows=5) (actual time=0.04..0.06 rows=5 loops=1)
        -> Index lookup on o using idx_user_id (user_id=u.id)  (cost=0.3 rows=2) (actual time=0.03..0.04 rows=2 loops=3)

关键信息：

  • cost：预估成本
  • actual time：实际执行时间（毫秒），格式为 首行时间..全部行时间
  • rows：实际返回行数
  • loops：执行次数

实战分析案例​

案例：优化一个慢查询
-- 原始查询：查找 2024 年支付金额 > 100 的订单
EXPLAIN SELECT * FROM orders
WHERE YEAR(create_time) = 2024
AND amount > 100
ORDER BY create_time DESC
LIMIT 20;
问题EXPLAIN 表现优化方案
函数导致索引失效type=ALL改为范围查询
SELECT *无法覆盖索引指定需要的字段
filesortExtra: Using filesort利用索引排序
优化后
-- 添加联合索引
ALTER TABLE orders ADD INDEX idx_time_amount(create_time, amount);

-- 优化 SQL
EXPLAIN SELECT id, user_id, amount, create_time FROM orders
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'
AND amount > 100
ORDER BY create_time DESC
LIMIT 20;
-- type: range, Extra: Using where; Backward index scan

常见面试问题​

Q1: EXPLAIN 中最重要的几个字段？​

答案：

最需要关注的 4 个字段：

  1. type：访问类型，反映查询效率。至少要达到 range，最好是 ref 或 const。ALL 表示全表扫描，必须优化
  2. key：实际使用的索引。NULL 表示没用索引
  3. rows：预估扫描行数。越大说明效率越低
  4. Extra：
    • Using index → 好（覆盖索引）
    • Using filesort → 需优化（额外排序）
    • Using temporary → 需优化（临时表）

Q2: type 中 ref 和 eq_ref 的区别？​

答案：

  • eq_ref：被驱动表通过主键或唯一索引进行等值匹配，最多返回一行。通常出现在 JOIN 的被驱动表使用主键关联时
  • ref：通过非唯一索引等值匹配，可能返回多行
-- eq_ref：user 表通过主键 id 精确匹配一行
SELECT * FROM orders o JOIN user u ON o.user_id = u.id;

-- ref：name 是普通索引，可能有多个张三
SELECT * FROM user WHERE name = '张三';

Q3: 如何判断联合索引用了几个字段？​

答案：

通过 key_len 计算：

以联合索引 idx(a INT NOT NULL, b VARCHAR(50), c INT) 为例（UTF-8 编码）：

  • a 的长度：4 字节
  • b 的长度：50 × 3 + 2（变长）+ 1（NULL）= 153 字节
  • c 的长度：4 + 1（NULL）= 5 字节

如果 key_len = 4，说明只用了 a；key_len = 157，说明用了 a + b；key_len = 162，说明用了 a + b + c。

Q4: Using index 和 Using index condition 的区别？​

答案：

  • Using index：覆盖索引。查询的所有字段都在索引中，完全不需要回表，直接从索引返回数据
  • Using index condition：索引下推（ICP）。在索引层面对非等值条件进行过滤，减少回表次数，但仍然需要回表获取完整数据
-- Using index（覆盖索引）：只查 name，索引已包含
SELECT name FROM user WHERE name = '张三';

-- Using index condition（索引下推）：索引中先过滤 age，再回表取 *
-- 假设联合索引 idx_name_age(name, age)
SELECT * FROM user WHERE name LIKE '张%' AND age = 25;

相关链接​