EXPLAIN 执行计划详解 | 清晨的一缕阳光

引言

EXPLAIN 是 MySQL 最常用的性能分析工具，它可以显示查询的执行计划，帮助我们了解 MySQL 如何执行 SQL 语句。掌握 EXPLAIN 是进行 SQL 优化的必备技能。

本章将深入讲解：

EXPLAIN 各字段的详细含义
type 访问类型的 7 个级别
Extra 关键信息解读
实际案例分析与优化

EXPLAIN 基础

基本用法

EXPLAIN SELECT * FROM users WHERE id = 1;

输出示例：

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | users | const | PRIMARY       | PRIMARY | 8       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

EXPLAIN 输出字段

字段名	说明	重要性
id	查询的序列号	⭐⭐
select_type	查询类型	⭐⭐⭐
table	显示的表	⭐
type	访问类型	⭐⭐⭐⭐⭐
possible_keys	可能使用的索引	⭐⭐⭐
key	实际使用的索引	⭐⭐⭐⭐
key_len	使用索引的长度	⭐⭐⭐
ref	显示哪个字段或常数与索引一起使用	⭐⭐
rows	估算需要扫描的行数	⭐⭐⭐⭐
Extra	额外信息	⭐⭐⭐⭐⭐

字段详解

1. id（查询序列号）

表示查询中执行 SELECT 子句或操作表的顺序：

id 相同：执行顺序由上至下
id 不同：id 值越大优先级越高，越先被执行
id 为 NULL：表示一个结果集，不需要使用它查询

示例 1：简单查询

EXPLAIN SELECT * FROM users WHERE id = 1;
-- id: 1

示例 2：子查询

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

-- 输出：
-- +----+-------------+---------+-------+
-- | id | select_type | table   | type  |
-- +----+-------------+---------+-------+
-- |  1 | PRIMARY     | users   | ALL   |
-- |  2 | SUBQUERY    | orders  | range |
-- +----+-------------+---------+-------+
-- 先执行 id=2 的子查询，再执行 id=1 的主查询

示例 3：UNION 查询

EXPLAIN SELECT * FROM users WHERE id = 1
UNION
SELECT * FROM users WHERE id = 2;

-- 输出：
-- +----+--------------+------------+-------+
-- | id | select_type  | table      | type  |
-- +----+--------------+------------+-------+
-- |  1 | PRIMARY      | users      | const |
-- |  2 | UNION        | users      | const |
-- | NULL| UNION RESULT| <union1,2>| ALL   |
-- +----+--------------+------------+-------+

2. select_type（查询类型）

值	说明	示例
SIMPLE	简单查询（不包含子查询或 UNION）	普通 SELECT
PRIMARY	主查询（最外层的查询）	包含子查询的外层
SUBQUERY	子查询（在 SELECT 或 WHERE 子句中）	SELECT (SELECT …)
DERIVED	派生表（FROM 子句中的子查询）	FROM (SELECT …)
UNION	UNION 的第二个或之后的查询	UNION 后的 SELECT
UNION RESULT	UNION 的结果表	UNION 结果

示例：复杂查询

EXPLAIN 
SELECT * FROM (
    SELECT user_id, SUM(amount) as total 
    FROM orders 
    GROUP BY user_id 
    HAVING total > 1000
) t1
JOIN users u ON t1.user_id = u.id;

-- 输出：
-- +----+-------------+------------+--------+
-- | id | select_type  | table      | type   |
-- +----+-------------+------------+--------+
-- |  1 | PRIMARY      | <derived2> | ALL    |
-- |  1 | PRIMARY      | u          | eq_ref |
-- |  2 | DERIVED      | orders     | ALL    |
-- +----+-------------+------------+--------+

3. type（访问类型）⭐⭐⭐⭐⭐

最重要的字段！ 表示 MySQL 在表中找到所需行的方式。

从好到坏排序：

system > const > eq_ref > ref > range > index > ALL

3.1 system（最优）

表只有一行数据（系统表），这是 const 类型的特例。

CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (1);

EXPLAIN SELECT * FROM t1 WHERE i = 1;
-- type: system

3.2 const（极优）

表中最多匹配一行数据，通常用于主键或唯一索引查询。

-- 主键查询
EXPLAIN SELECT * FROM users WHERE id = 1;
-- type: const

-- 唯一索引查询
EXPLAIN SELECT * FROM users WHERE phone = '13800138000';
-- type: const

特点：

将行数据读取次数优化为常数
查询速度极快
适用于 =、IN、IS NULL 等操作

3.3 eq_ref（优秀）

对于每个索引键，表中只有一行与之匹配。通常用于主键或唯一索引的关联查询。

EXPLAIN SELECT * FROM users u
JOIN orders o ON u.id = o.user_id;

-- 输出：
-- +----+------+-------+--------+---------------+---------+---------+------+------+
-- | id | type | table | type   | key           | key_len | ref    | rows |
-- +----+------+-------+--------+---------------+---------+---------+------+------+
-- |  1 | SIMPLE | u     | ALL    | NULL          | NULL    | NULL    | 1000 |
-- |  1 | SIMPLE | o     | eq_ref | idx_user_id   | 8       | u.id   |    1 |
-- +----+------+-------+--------+---------------+---------+---------+------+------+

特点：

多表 JOIN 中最理想的关系
使用主键或唯一索引关联

3.4 ref（良好）

对于索引键，表中有多行与之匹配。用于非唯一索引查询。

-- 普通索引查询
CREATE INDEX idx_status ON orders(status);
EXPLAIN SELECT * FROM orders WHERE status = 1;
-- type: ref

-- 多表 JOIN 中的非唯一索引
EXPLAIN SELECT * FROM users u
JOIN orders o ON u.id = o.user_id;
-- users 表：type: const（主键）
-- orders 表：type: ref（非唯一索引）

特点：

比 eq_ref 稍差（可能返回多行）
仍可使用索引，性能较好

3.5 range（中等）

索引范围扫描，适用于范围查询。

-- 范围查询
EXPLAIN SELECT * FROM orders 
WHERE create_time >= '2024-01-01';
-- type: range

-- IN 查询
EXPLAIN SELECT * FROM users WHERE id IN (1, 2, 3, 4, 5);
-- type: range

-- BETWEEN 查询
EXPLAIN SELECT * FROM products 
WHERE price BETWEEN 100 AND 500;
-- type: range

特点：

使用索引扫描一部分数据
性能优于全表扫描
常见于 WHERE 条件使用 >、<、BETWEEN、IN 等

3.6 index（较差）

全索引扫描，扫描整个索引树。

-- 覆盖索引查询
EXPLAIN SELECT id, name FROM users;
-- type: index（扫描整个索引树，但无需回表）

-- ORDER BY 索引列
EXPLAIN SELECT * FROM users ORDER BY create_time;
-- type: index

特点：

需要扫描整个索引树
但索引通常比数据文件小，所以比 ALL 快
如果是覆盖索引，性能可以接受

3.7 ALL（最差）

全表扫描，性能最差。

-- 无索引查询
EXPLAIN SELECT * FROM users WHERE name = '张三';
-- type: ALL（name 列无索引）

-- 函数导致索引失效
EXPLAIN SELECT * FROM orders WHERE YEAR(create_time) = 2024;
-- type: ALL

特点：

扫描整个表
数据量大时性能极差
需要优化（添加索引或改写 SQL）

4. possible_keys 和 key

possible_keys：可能使用的索引列表
key：实际使用的索引（NULL 表示未使用索引）

EXPLAIN SELECT * FROM users 
WHERE name = '张三' AND age = 25;

-- 输出：
-- possible_keys: idx_name, idx_age, idx_name_age
-- key: idx_name_age

分析：

possible_keys 有值但 key 为 NULL：索引未被使用（可能优化器认为全表扫描更快）
possible_keys 和 key 都为 NULL：没有可用的索引

5. key_len（索引长度）

表示使用的索引长度（字节数），越短越好。

计算规则：

索引字段长度 + 是否为 NULL（1 字节）+ 字符集编码

常见类型的长度：

CREATE TABLE test (
    id INT NOT NULL,           -- 4 字节
    name VARCHAR(50),          -- 可变长度
    phone CHAR(11) NOT NULL,   -- 11 字节
    create_time DATETIME       -- 5 字节
);

-- 索引长度计算
CREATE INDEX idx_id ON test(id);
-- key_len: 4

CREATE INDEX idx_phone ON test(phone);
-- key_len: 11 (CHAR) * 3 (utf8mb4) = 33

CREATE INDEX idx_name ON test(name(10));
-- key_len: 10 (前缀) * 3 (utf8mb4) + 1 (NULL 标记) = 31

用途：

判断联合索引使用了多少列
验证索引是否被完整使用

-- 联合索引：idx_name_age (name, age)
EXPLAIN SELECT * FROM users WHERE name = '张三' AND age = 25;
-- key_len: 203（name 使用了）

EXPLAIN SELECT * FROM users WHERE name = '张三';
-- key_len: 200（只使用了 name 列）

6. rows（扫描行数）

估算需要扫描的行数，越少越好。

EXPLAIN SELECT * FROM users WHERE id = 1;
-- rows: 1（最优）

EXPLAIN SELECT * FROM users WHERE status = 1;
-- rows: 5000（需要扫描 5000 行）

EXPLAIN SELECT * FROM users;
-- rows: 10000（全表扫描）

注意：

rows 是估算值，不是精确值
基于表的统计信息
定期 ANALYZE TABLE 可以更新统计信息

7. Extra（额外信息）⭐⭐⭐⭐⭐

包含 MySQL 解决查询的额外信息，常见值：

7.1 Using index（好）⭐⭐⭐

使用覆盖索引，无需回表。

EXPLAIN SELECT id, name FROM users WHERE name = '张三';
-- Extra: Using index

7.2 Using where（一般）

需要在 Server 层进行 WHERE 过滤。

EXPLAIN SELECT * FROM users WHERE name = '张三';
-- Extra: Using where

注意：

Using where 不一定表示性能差
结合 type 一起看（type=range + Using where 可以接受）

7.3 Using index condition（好）⭐⭐⭐

使用了索引下推优化（MySQL 5.6+）。

EXPLAIN SELECT * FROM users 
WHERE name LIKE '张%' AND age = 25;
-- Extra: Using index condition

7.4 Using filesort（注意）⚠️

无法使用索引排序，需要额外的排序操作。

EXPLAIN SELECT * FROM users ORDER BY create_time;
-- Extra: Using filesort

优化方案：

-- 为排序列创建索引
CREATE INDEX idx_create_time ON users(create_time);

-- 或者使用 LIMIT 限制数据量
SELECT * FROM users ORDER BY create_time LIMIT 10;

7.5 Using temporary（注意）⚠️

使用了临时表，常见于 GROUP BY、DISTINCT、UNION。

EXPLAIN SELECT name, COUNT(*) FROM users GROUP BY name;
-- Extra: Using temporary

优化方案：

-- 为 GROUP BY 列创建索引
CREATE INDEX idx_name ON users(name);

-- 使用索引避免临时表
SELECT name FROM users GROUP BY name;
-- Extra: Using index（无需临时表）

7.6 Using join buffer（注意）⚠️

JOIN 时使用了连接缓冲区，通常表示关联条件没有使用索引。

EXPLAIN SELECT * FROM users u
JOIN orders o ON u.name = o.customer_name;
-- Extra: Using join buffer

优化方案：

-- 为关联列创建索引
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_customer_name ON orders(customer_name);

7.7 NULL（好）

没有额外信息，通常表示查询很简单。

EXPLAIN SELECT * FROM users WHERE id = 1;
-- Extra: NULL（主键查询，无需额外操作）

实战案例分析

案例 1：优化慢查询

问题 SQL：

SELECT * FROM orders 
WHERE user_id = 100 
ORDER BY create_time DESC 
LIMIT 10;
-- 执行时间：2 秒

EXPLAIN 分析：

EXPLAIN SELECT * FROM orders 
WHERE user_id = 100 
ORDER BY create_time DESC 
LIMIT 10;

-- 输出：
-- +----+------+--------+------+---------------+------+---------+------+------+
-- | id | type | table  | type | possible_keys | key  | key_len| ref  | rows | Extra          |
-- +----+------+--------+------+---------------+------+---------+------+------+
-- |  1 | SIMPLE | orders | ref  | idx_user_id | idx_user_id | 8 | const | 5000 | Using filesort |
-- +----+------+--------+------+---------------+------+---------+------+------+

问题诊断：

type: ref（良好）
rows: 5000（需要扫描 5000 行）
Extra: Using filesort（需要额外排序）

优化方案：

-- 创建联合索引
CREATE INDEX idx_user_time ON orders(user_id, create_time DESC);

-- 验证优化效果
EXPLAIN SELECT * FROM orders 
WHERE user_id = 100 
ORDER BY create_time DESC 
LIMIT 10;

-- 新输出：
-- type: ref
-- rows: 5000
-- Extra: NULL（无 filesort）

-- 执行时间：0.01 秒（提升 200 倍）

案例 2：JOIN 优化

问题 SQL：

SELECT u.name, o.amount 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 1
  AND o.create_time >= '2024-01-01';
-- 执行时间：5 秒

EXPLAIN 分析：

EXPLAIN SELECT u.name, o.amount 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 1
  AND o.create_time >= '2024-01-01';

-- 输出：
-- +----+------+-------+-------+---------------+------+---------+------+------+
-- | id | type | table | type  | possible_keys | key  | key_len| ref  | rows | Extra        |
-- +----+------+-------+-------+---------------+------+---------+------+------+
-- |  1 | SIMPLE | o     | ALL   | NULL          | NULL | NULL   | NULL | 100000 | Using where |
-- |  1 | SIMPLE | u     | eq_ref| PRIMARY       | PRIMARY | 8   | o.user_id | 1 | NULL        |
-- +----+------+-------+-------+---------------+------+---------+------+------+

问题诊断：

orders 表：type: ALL（全表扫描！）
rows: 100000（扫描 10 万行）

优化方案：

-- 为 orders 表创建联合索引
CREATE INDEX idx_status_time_user ON orders(status, create_time, user_id);

-- 验证优化效果
EXPLAIN SELECT u.name, o.amount 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 1
  AND o.create_time >= '2024-01-01';

-- 新输出：
-- type: ref
-- key: idx_status_time_user
-- rows: 1000（从 10 万降到 1000）

-- 执行时间：0.1 秒（提升 50 倍）

案例 3：子查询优化

问题 SQL：

SELECT * FROM users 
WHERE id IN (
    SELECT user_id FROM orders WHERE amount > 1000
);
-- 执行时间：3 秒

EXPLAIN 分析：

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

-- 输出：
-- +----+-------------+---------+-------+
-- | id | select_type | table   | type  |
-- +----+-------------+---------+-------+
-- |  1 | PRIMARY     | users   | ALL   |
-- |  2 | SUBQUERY    | orders  | ALL   |
-- +----+-------------+---------+-------+
-- 两个表都是全表扫描！

优化方案：

-- 方案 1：改为 JOIN
EXPLAIN SELECT u.* FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;

-- 为 orders 表创建索引
CREATE INDEX idx_amount_user ON orders(amount, user_id);

-- 方案 2：使用 EXISTS
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.amount > 1000
);

-- 执行时间：0.1 秒（提升 30 倍）

注意事项

1. EXPLAIN 的局限性

rows 是估算值：基于统计信息，可能不准确
不显示缓存情况：Query Cache 的影响无法体现
不显示实际执行时间：需要结合 profiling 或慢查询日志

2. 结合其他工具使用

-- 开启 profiling
SET profiling = 1;
SELECT * FROM users WHERE id = 1;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

-- 使用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- 使用 performance_schema
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

3. 不要过度优化

-- 小表不需要优化
EXPLAIN SELECT * FROM small_table WHERE status = 1;
-- 如果 small_table 只有 100 行，全表扫描也很快

-- 关注影响最大的查询
-- 优先优化执行频率高、数据量大的查询

总结

EXPLAIN 诊断流程

graph TD
    A[执行 EXPLAIN] --> B{type 是什么？}
    B -->|system/const| C[最优，无需优化]
    B -->|eq_ref/ref| D[良好，检查 rows]
    B -->|range| E[中等，可接受]
    B -->|index/ALL| F[需要优化]
    
    F --> G{key 是否为 NULL?}
    G -->|是 | H[添加索引]
    G -->|否 | I[检查索引是否合适]
    
    D --> J{Extra 是否有问题？}
    J -->|Using filesort| K[优化 ORDER BY]
    J -->|Using temporary| L[优化 GROUP BY]
    J -->|Using index| M[覆盖索引，最优]

关键要点

type 字段最重要：至少达到 range 级别
key 字段看索引：NULL 表示未使用索引
rows 字段看数量：越少越好
Extra 字段看优化：Using index 是好信号
结合业务场景：不要盲目追求全 const

优化检查清单

type 是否达到 range 或更好
key 是否使用了预期索引
rows 是否在合理范围
Extra 是否有 Using filesort/Using temporary
是否可以使用覆盖索引
索引设计是否合理

参考资料

MySQL 官方文档 - EXPLAIN
《高性能 MySQL》第 4 章：查询性能优化
MySQL Query Optimization
Understanding EXPLAIN Output