什么是EXPLAIN
EXPLAIN是MySQL提供的一个强大的性能分析工具，用于查看SQL语句的执行计划。通过EXPLAIN，我们可以了解MySQL如何执行一条SQL语句，包括：
- 表的访问顺序
- 使用的索引
- 数据读取方式
- 预计扫描行数
- 连接类型等
使用语法：
EXPLAIN SELECT * FROM users WHERE id = 1;
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1; -- MySQL 8.0+
EXPLAIN输出字段详解
EXPLAIN的输出包含多个字段，每个字段都提供了重要的执行信息：
| 字段 | 说明 | 重要性 |
|---|---|---|
id |
查询执行顺序标识 | - |
select_type |
查询类型（简单查询、子查询、联合查询等） | - |
table |
涉及的表名 | - |
partitions |
匹配的分区（分区表时显示） | - |
type |
连接类型（ALL、index、range、ref、eq_ref等） | 重点 |
possible_keys |
可能使用的索引 | - |
key |
实际使用的索引 | 重点 |
key_len |
使用的索引长度 | 重点 |
ref |
与索引比较的列或常量 | - |
rows |
预计扫描的行数 | - |
filtered |
过滤后剩余行的百分比 | - |
Extra |
额外信息（Using index、Using where等） | 重点 |
主要关注的字段：
type、key、key_len、Extra，这四个字段是分析SQL性能的核心指标。
1. type字段（核心字段）
表示MySQL在表中找到所需行的方式，性能从好到差排序：system → const → eq_ref → ref → range → index → ALL
| type | 说明 |
|---|---|
system |
表仅一行（或空表），极优，仅适用于MyISAM/Memory引擎的系统表 |
const |
通过主键或唯一索引等值查找，最多匹配一行，查询中可视为常量 |
eq_ref |
多表JOIN时，对前表每行都用唯一索引（主键/非空唯一键）精准定位本表一行 |
ref |
使用非唯一索引等值查找，可能返回多行（如普通索引列 = 常量） |
range |
索引范围扫描（如BETWEEN、>、IN等），仅扫描索引中符合条件的部分区间 |
index |
全索引扫描，遍历整个索引树（可能覆盖索引，但仍比全表快） |
ALL |
全表扫描，逐行读取表中所有数据，性能最差，需优化 |
type字段优化目标：至少达到range级别，最好达到ref或eq_ref级别。
4. key字段
显示MySQL实际决定使用的索引。如果为NULL，表示没有使用索引。
关键点：
-
possible_keys：MySQL认为可能使用的索引列表 -
key：MySQL实际选择使用的索引（可能为空或不同于possible_keys）
3. key_len字段（核心字段）
表示使用的索引长度（字节数），可以用来判断索引的使用情况：
判断要点：
- key_len越大：说明使用的索引列越多或列的数据类型越大
- key_len越小：说明使用的索引列越少或列的数据类型越小
计算规则（InnoDB）：
| 数据类型 | 长度（字节） | 说明 |
|---|---|---|
| NULL | 1 | 允许NULL的列需要额外1字节 |
| TINYINT | 1 | 无符号需额外1字节 |
| SMALLINT | 2 | 无符号需额外1字节 |
| INT | 4 | 无符号需额外1字节 |
| BIGINT | 8 | 无符号需额外1字节 |
| VARCHAR(n) | n + 2（变长）+ 1（NULL） | 字符集影响实际长度 |
| CHAR(n) | n * 字符集字节数 + 1（NULL） | 固定长度 |
用途：通过key_len可以判断联合索引是否完全使用。
4. Extra字段（核心字段）
包含MySQL处理查询的额外信息：
| 值 | 说明 |
|---|---|
Using index |
使用覆盖索引（索引包含所有查询字段） |
Using where |
使用WHERE条件过滤数据 |
Using filesort |
需额外排序（性能问题） |
Using temporary |
使用临时表（性能问题） |
Using join buffer |
使用连接缓冲 |
Impossible WHERE |
WHERE条件永远为false |
Select tables optimized away |
优化器通过索引直接返回结果 |
Using filesort出现场景：
- ORDER BY条件没有索引可用
- GROUP BY条件没有索引可用
- 排序字段有索引但查询条件无法使用
- 多表JOIN后需要对结果集排序
- 排序需要使用文件排序算法而非索引排序
Using temporary出现场景：
- GROUP BY条件没有索引可用
- DISTINCT去重操作没有索引可用
- UNION/UNION ALL结果集去重
- 复杂子查询需要临时表存储中间结果
- 窗口函数（Window Functions）运算
EXPLAIN实践案例
案例1：全表扫描优化
问题SQL：
SELECT * FROM orders WHERE status = 'COMPLETED';
EXPLAIN结果：
| id | select_type | table | type | key | rows | Extra |
|---|---|---|---|---|---|---|
| 1 | SIMPLE | orders | ALL | NULL | 10000 | Using where |
问题分析：type=ALL表示全表扫描，key=NULL表示未使用索引。
优化方案：创建索引
CREATE INDEX idx_orders_status ON orders(status);
优化后EXPLAIN结果：
| id | select_type | table | type | key | rows | Extra |
|---|---|---|---|---|---|---|
| 1 | SIMPLE | orders | ref | idx_orders_status | 1000 | Using where |
案例2：Using filesort优化
问题SQL：
SELECT name, age FROM users WHERE department = 'IT' ORDER BY age;
EXPLAIN结果：
| id | select_type | table | type | key | Extra |
|---|---|---|---|---|---|
| 1 | SIMPLE | users | ref | idx_dept | Using where; Using filesort |
问题分析：Using filesort表示MySQL需要额外排序操作。
优化方案：创建联合索引
CREATE INDEX idx_dept_age ON users(department, age);
优化后EXPLAIN结果：
| id | select_type | table | type | key | Extra |
|---|---|---|---|---|---|
| 1 | SIMPLE | users | ref | idx_dept_age | Using index |
案例3：Using temporary优化
问题SQL：
SELECT department, COUNT(*) FROM users GROUP BY department;
EXPLAIN结果：
| id | select_type | table | type | key | Extra |
|---|---|---|---|---|---|
| 1 | SIMPLE | users | ALL | NULL | Using temporary; Using filesort |
问题分析：Using temporary表示MySQL需要创建临时表来存储分组结果。
优化方案：创建索引
CREATE INDEX idx_department ON users(department);
EXPLAIN ANALYZE（MySQL 8.0+）
MySQL 8.0引入了EXPLAIN ANALYZE，不仅显示执行计划，还会实际执行语句并显示真实的执行统计：
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;
输出示例：
-> Filter: (users.age > 25) (cost=100.00 rows=1000) (actual time=0.01..5.23 rows=956 loops=1)
-> Index range scan on users using idx_age (cost=100.00 rows=1000) (actual time=0.01..4.87 rows=956 loops=1)
关键字段：
-
cost：估算成本 -
rows：估算/实际行数 -
actual time：实际执行时间 -
loops：循环次数
总结
EXPLAIN是SQL优化的必备工具，通过分析执行计划可以：
-
识别全表扫描：关注
type=ALL -
检查索引使用：关注
key字段 -
发现性能问题：关注
Extra字段中的Using filesort和Using temporary - 验证优化效果：对比优化前后的执行计划
优化流程：
- 使用
EXPLAIN分析慢查询 - 定位性能瓶颈（全表扫描、filesort、临时表等）
- 创建合适的索引或重写SQL
- 使用
EXPLAIN ANALYZE验证优化效果
掌握EXPLAIN的使用方法，能够帮助我们快速定位和解决SQL性能问题，是每个数据库开发者必备的技能。
