MySQL 执行计划 EXPLAIN 详解

我刚工作的时候，有次上线了个新功能，结果 SQL 查询慢得要命，用户投诉电话被打爆。DBA 帮我一看执行计划，发现没走索引，全表扫描 2000 万条数据。

从那以后，我每次写完 SQL 都会用 EXPLAIN 看看执行计划，避免线上事故。

今天咱们就来彻底搞懂 EXPLAIN，看完这篇，你就能自己优化 SQL 了。

EXPLAIN 是啥？

EXPLAIN 是 MySQL 提供的执行计划分析工具，能告诉你：这条 SQL 会怎么执行，走什么索引，扫描多少行，有没有性能问题。

基本用法

EXPLAIN SELECT * FROM users WHERE age = 25;

输出大概是这样：

+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ref  | idx_age       | idx_age | 5     | const | 100  |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

重点：我们主要看这几个字段：type、key、rows、Extra。

字段详解

1. id：查询的序列号

id 表示查询的执行顺序，有几个规则：

• id 相同：从上往下执行
• id 不同：id 越大越先执行（子查询）
• id 有 NULL：最后执行（union 的结果）

-- 子查询：id 不同
EXPLAIN SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

2. select_type：查询类型

常见的几种：

• SIMPLE：简单查询（不含子查询、union）
• PRIMARY：最外层的查询
• SUBQUERY：子查询（不在 FROM 里）
• DERIVED：派生表（FROM 里的子查询）
• UNION：UNION 的第二个及后面的 SELECT
• UNION RESULT：UNION 的结果

-- SUBQUERY 示例
EXPLAIN SELECT * FROM users WHERE id = (SELECT user_id FROM orders WHERE id = 1);

3. table：访问的表

表示这一步访问的是哪张表（或派生表）。

4. partitions：分区

如果表用了分区，这里显示访问的分区。没分区就是 NULL。

5. type：访问类型（重要！）

type 是最重要的字段，表示 MySQL 是怎么找到数据的。从好到坏排序：

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

必须记住的：

(1) const：常量查询

通过主键或唯一索引精确匹配，最多返回一条记录。

-- id 是主键
EXPLAIN SELECT * FROM users WHERE id = 1;
-- type = const

性能：最好，因为只扫描一行。

(2) eq_ref：唯一索引关联

多表关联时，被关联的表用主键或唯一索引关联。

-- users.id 是主键，orders.user_id 有唯一索引
EXPLAIN SELECT * FROM users 
JOIN orders ON users.id = orders.user_id;
-- orders 表的 type = eq_ref

性能：非常好，每条记录只匹配一条。

(3) ref：非唯一索引

通过普通索引精确匹配，可能返回多行。

-- age 是普通索引
EXPLAIN SELECT * FROM users WHERE age = 25;
-- type = ref

性能：不错，但可能扫描多行。

(4) range：范围查询

用索引做范围查询（BETWEEN、<、>、IN、OR）。

EXPLAIN SELECT * FROM users WHERE id > 10 AND id < 100;
-- type = range

性能：还行，但扫描的行数取决于范围大小。

(5) index：全索引扫描

扫描整个索引树（比全表扫描好点，因为索引文件比数据文件小）。

-- 覆盖索引（只查索引字段）
EXPLAIN SELECT age FROM users;
-- type = index（如果 age 有索引）

性能：差，但比 ALL 好。

(6) ALL：全表扫描（最差！）

扫描整个表，每条记录都检查。

-- age 没有索引
EXPLAIN SELECT * FROM users WHERE age = 25;
-- type = ALL

性能：最差，数据量大时直接崩。

实战建议：至少要达到 range 级别，最好能达到 ref 或 const。如果看到 ALL，赶紧加索引！

6. possible_keys：可能用到的索引

表示 MySQL 能选的索引（但不一定用）。

7. key：实际用到的索引

表示 MySQL 实际选的索引。如果为 NULL，说明没走索引。

坑：有时候 possible_keys 有值，但 key 是 NULL，说明 MySQL 觉得全表扫描更快（比如表很小，或者索引选择性太差）。

8. key_len：索引使用的字节数

表示索引使用的长度（越短越好）。

计算公式：

• INT：4 字节
• BIGINT：8 字节
• VARCHAR(50)：50 * 3 + 2 = 152 字节（utf8mb4 每个字符最多 3 字节，加 2 字节长度）
• 允许 NULL：+1 字节

-- 联合索引 (age, name)
EXPLAIN SELECT * FROM users WHERE age = 25 AND name = 'Alice';
-- key_len = 4 + 152 = 156（如果都用了）

实战：如果 key_len 比预期短，说明索引没完全用上（比如只用了联合索引的前缀）。

9. ref：索引的哪些列被使用

表示索引的哪些列被用在了查询条件里。

-- 主键查询
EXPLAIN SELECT * FROM users WHERE id = 1;
-- ref = const（常量）

-- 多表关联
EXPLAIN SELECT * FROM users JOIN orders ON users.id = orders.user_id;
-- ref = db.orders.user_id（关联字段）

10. rows：扫描的行数（重要！）

表示 MySQL 估计要扫描多少行才能找到目标数据。

注意：rows 是预估值，不是精确值。

实战建议：rows 越小越好。如果 rows 接近表的总行数，说明索引选择性差，或者没走索引。

11. filtered：过滤比例

表示存储引擎返回的数据在 Server 层过滤后，剩下多少比例的记录（百分比）。

-- 假设 users 表有 1000 行，age=25 的有 100 行
EXPLAIN SELECT * FROM users WHERE age = 25 AND name = 'Alice';
-- rows = 100, filtered = 10.00（100 行里只有 10% 满足 name='Alice'）

实战：如果 filtered 很低，说明 WHERE 条件里有很多无法用索引过滤的条件（比如函数、计算）。

12. Extra：额外信息（重要！）

Extra 包含很多关键信息，常见的有：

(1) Using index：覆盖索引

表示查询的字段都在索引里，不需要回表。

-- age 有索引，且只查 age
EXPLAIN SELECT age FROM users WHERE age = 25;
-- Extra = Using index

性能：非常好，因为不需要回表。

(2) Using where：Server 层过滤

表示存储引擎返回的记录，在 Server 层还要再过滤一遍（因为索引无法完全过滤）。

-- age 有索引，但 name 没有
EXPLAIN SELECT * FROM users WHERE age = 25 AND name = 'Alice';
-- Extra = Using index condition; Using where

(3) Using temporary：用临时表

表示 MySQL 需要创建临时表来处理查询（比如 GROUP BY、DISTINCT、UNION）。

EXPLAIN SELECT DISTINCT age FROM users;
-- Extra = Using temporary

性能：差，因为临时表要写入磁盘（如果内存不够）。

优化方案：给 GROUP BY / DISTINCT 的字段加索引。

(4) Using filesort：文件排序

表示 MySQL 无法利用索引完成排序，需要额外排序。

-- age 有索引，但 ORDER BY name
EXPLAIN SELECT * FROM users ORDER BY name;
-- Extra = Using filesort

性能：差，因为要排序（如果内存不够，会用到磁盘）。

优化方案：给 ORDER BY 的字段加索引，或者让 ORDER BY 用上索引。

(5) Using join buffer：用连接缓存

表示多表关联时，被驱动的表没有索引，MySQL 用 join buffer 来缓存驱动表的结果。

-- orders.user_id 没有索引
EXPLAIN SELECT * FROM users JOIN orders ON users.id = orders.user_id;
-- Extra = Using join buffer (Block Nested Loop)

性能：差，因为要扫描被驱动表多次。

优化方案：给关联字段加索引。

实战：优化一条慢 SQL

假设有这么条 SQL，执行很慢：

SELECT * FROM users 
WHERE age > 20 AND age < 30 
ORDER BY name 
LIMIT 10;

第 1 步：看执行计划

EXPLAIN SELECT * FROM users 
WHERE age > 20 AND age < 30 
ORDER BY name 
LIMIT 10;

输出：

+-------+------+---------------+------+---------+------+------+----------+----------------------------------+
| type  | key  | rows          | Extra                                      |
+-------+------+---------------+------+---------+------+------+----------+----------------------------------+
| range | idx_age | 100000    | Using index condition; Using filesort       |
+-------+------+---------------+------+---------+------+------+----------+----------------------------------+

问题：

1. type = range：还行，但可以更好
2. rows = 100000：扫描 10 万行，太多
3. Extra = Using filesort：文件排序，性能差

第 2 步：优化索引

问题是 ORDER BY name 没走索引，导致 filesort。

方案 1：建联合索引 (age, name)

CREATE INDEX idx_age_name ON users(age, name);

再看看执行计划：

EXPLAIN SELECT * FROM users 
WHERE age > 20 AND age < 30 
ORDER BY name 
LIMIT 10;

输出：

+-------+---------------+------+------+----------+-----------------------------+
| type  | key           | rows | Extra                                      |
+-------+---------------+------+------+----------+-----------------------------+
| range | idx_age_name  | 100  | Using index condition                      |
+-------+---------------+------+------+----------+-----------------------------+

优化效果：

1. rows 从 10 万降到 100（因为索引覆盖了 ORDER BY，不需要扫描那么多行）
2. Extra 里没有 Using filesort 了（因为索引是有序的，直接读就行）

实战建议

1. 每次写完 SQL 都用 EXPLAIN 看看

这是最重要的建议。很多线上慢查询，都是因为没看执行计划，导致全表扫描。

2. 重点关注 type、rows、Extra

• type 至少要达到 range，最好 ref 或 const
• rows 越小越好，如果接近全表，说明索引有问题
• Extra 里如果有 Using temporary 或 Using filesort，赶紧优化

3. 联合索引要注意顺序

联合索引有"最左前缀"原则，比如 (age, name)：

-- 能用到索引
WHERE age = 25 AND name = 'Alice'
WHERE age = 25
WHERE age > 20 AND age < 30

-- 用不到索引（因为没用到最左前缀 age）
WHERE name = 'Alice'

建议：把区分度高的字段放前面（比如 name 比 age 区分度高，放前面）。

4. 避免索引失效

常见的索引失效场景：

-- 1. 对索引字段用函数
WHERE LEFT(name, 5) = 'Alice'  -- 失效

-- 2. 类型转换
WHERE age = '25'  -- 如果 age 是 INT，失效（隐式类型转换）

-- 3. 模糊查询前缀通配符
WHERE name LIKE '%Alice'  -- 失效
WHERE name LIKE 'Alice%'  -- 有效（能用索引）

-- 4. OR 连接的条件中有字段没索引
WHERE age = 25 OR name = 'Alice'  -- 如果 name 没索引，失效

总结

• EXPLAIN 是 MySQL 的执行计划分析工具，能告诉你 SQL 怎么执行
• 重点关注 type（访问类型）、key（实际用的索引）、rows（扫描行数）、Extra（额外信息）
• type 从好到坏：const > eq_ref > ref > range > index > ALL，至少要达到 range
• Extra 里如果有 Using temporary 或 Using filesort，性能差，需要优化
• 实战建议：每次写完 SQL 都用 EXPLAIN 看看，重点关注 type、rows、Extra

如果你能把 EXPLAIN 的每个字段讲清楚，并且能优化慢 SQL，面试官绝对觉得你是高级开发。

---

实战代码都在我本地跑过，你可以放心复制。 如果有问题，欢迎评论区交流！