EXPLAIN 关键字：MySQL 查询执行计划的 “透视镜”
你想深入了解EXPLAIN关键字，它是 MySQL 中分析查询性能、排查索引失效、优化 SQL 的核心工具 —— 通过EXPLAIN可以 “看透” MySQL 优化器如何执行你的 SQL 语句，包括是否使用索引、扫描行数、连接方式等关键信息，无需实际执行查询就能定位性能问题。
下面我会从基本用法→输出字段详解→实战案例→进阶用法四个维度，把EXPLAIN讲透，让你能独立分析任意 SQL 的执行计划。
一、EXPLAIN 基本用法
1. 核心作用
- 显示 MySQL 执行 SQL 时的执行计划（而非执行结果）；
- 告诉我们：SQL 将如何扫描表、是否使用索引、扫描多少行、表之间如何连接等；
- 支持
SELECT、DELETE、INSERT、UPDATE（MySQL 8.0+），最常用的是SELECT。
2. 基础语法
-- 最基础用法：分析SELECT语句 EXPLAIN SELECT * FROM user WHERE name = '张三'; -- 简写（效果相同） DESC SELECT * FROM user WHERE name = '张三'; -- MySQL 8.0+支持分析UPDATE/DELETE EXPLAIN UPDATE user SET age = 21 WHERE id = 1;执行后会返回一个表格（通常 1 行或多行，对应多表连接），每行包含 12 个字段（MySQL 5.7+），每个字段都有明确的性能含义。
二、EXPLAIN 输出字段详解（按重要性排序）
为了方便理解，先给出一个典型的执行计划示例：
EXPLAIN SELECT id, name FROM user WHERE name = '张三';输出结果（核心字段）：
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | user | ref | idx_name | idx_name | 62 | const | 2 | Using index |
下面逐个解析每个字段的含义，重点标注核心关注项：
1. id：查询的执行顺序标识
- 含义：表示查询中执行子查询 / 表连接的顺序，数字越大越先执行；
- 规则：
- 相同 id：按从上到下顺序执行（多表连接）；
- 不同 id：id 值越大，执行优先级越高；
- NULL：表示这是一个临时表 / 汇总结果（如
GROUP BY的临时表）。
- 示例：
-- 子查询，id=2先执行，再执行id=1 EXPLAIN SELECT * FROM user WHERE id = (SELECT id FROM order WHERE order_no = 'O123');
2. select_type：查询类型（判断查询复杂度）
核心值说明（按常见程度排序）：
| 值 | 含义 |
|---|---|
| SIMPLE | 简单查询（无子查询、无 UNION），最常见 |
| PRIMARY | 主查询（包含子查询 / UNION 时，外层查询的类型） |
| SUBQUERY | 子查询（内层查询，不依赖外层） |
| DERIVED | 派生表（FROM 中的子查询，MySQL 会先执行并生成临时表） |
| UNION | UNION 中的第二个及以后的查询 |
| UNION RESULT | UNION 的最终结果集 |
3. table：当前行对应的表 / 临时表
- 显示执行计划对应的表名（如
user、order）； - 若为派生表，显示
derivedN（N 为 id 值）； - 若为 UNION 结果，显示
unionM,N（M、N 为 id 值）。
4. type：访问类型（核心！判断索引是否生效）
含义：MySQL 扫描表中数据的方式，是判断查询性能的最核心指标，从优到劣排序如下：
| 类型 | 含义 | 性能等级 | 说明 |
|---|---|---|---|
| system | 表只有 1 行数据（系统表），极致优化（几乎遇不到） | 最优 | 特殊情况，比 const 更好 |
| const | 通过主键 / 唯一索引查询，最多匹配 1 行 | 最优 | 如WHERE id = 1 |
| eq_ref | 多表连接时，被驱动表通过主键 / 唯一索引匹配（如JOIN t2 ON t1.id=t2.id） | 优秀 | 关联查询的理想类型 |
| ref | 通过非唯一索引匹配，返回多行（如WHERE name = '张三'） | 良好 | 索引生效的常见类型 |
| ref_or_null | 类似 ref，但包含 NULL 值查询（如WHERE name = '张三' OR name IS NULL） | 良好 | 略逊于 ref |
| range | 索引范围扫描（如WHERE id BETWEEN 1 AND 10、IN (1,2,3)） | 一般 | 索引生效，但扫描范围较大 |
| index | 扫描整个索引树（未回表，如覆盖索引） | 较差 | 比 ALL 好，但仍扫描全索引 |
| ALL | 全表扫描（Full Table Scan） | 最差 | 索引失效，性能暴跌 |
核心判断：
- ✅ 理想情况：
const/eq_ref/ref（索引高效生效）； - ⚠️ 需要优化：
range（尽量缩小范围）； - ❌ 必须优化：
index/ALL（索引失效，全表 / 全索引扫描）。
5. possible_keys：可能使用的索引
- 含义：MySQL 优化器认为该查询可能用到的索引列表（仅为候选，不一定实际使用）；
- 若为
NULL：表示没有可用的索引（大概率会全表扫描）。
6. key：实际使用的索引（核心！）
- 含义：MySQL 优化器最终选择的索引名称；
- 若为
NULL：表示未使用任何索引（索引失效）； - 重点：
possible_keys有值但key为NULL，说明优化器判断走索引成本更高，主动放弃（如匹配行数太多）。
7. key_len：使用的索引长度（字节）
- 含义：表示 MySQL 使用的索引字段的总长度，可用于判断联合索引的生效范围；
- 计算规则（示例）：
varchar(20)（utf8）：20×3 + 2（字符串长度标识）= 62 字节；int：4 字节；- 联合索引
idx_name_age(name, age)：若key_len=66（62+4），说明两个字段都生效；若 = 62，说明仅 name 生效。
8. ref：与索引比较的列 / 常量
- 含义：表示与索引字段匹配的内容，分为两种：
const：匹配常量（如WHERE name = '张三'，' 张三 ' 是常量）；- 表名。字段名：多表连接时，匹配另一张表的字段（如
t1.name = t2.name）；
- 若为
NULL：表示没有使用等值匹配（如范围查询）。
9. rows：预估扫描行数（核心！）
- 含义：MySQL 优化器预估的、执行查询需要扫描的行数（不是实际行数）；
- 数值越小越好：行数越多，说明需要扫描的数据越多，性能越差；
- 注意：该值是基于表的统计信息估算的，可能与实际有偏差，可通过
ANALYZE TABLE 表名更新统计信息。
10. Extra：额外信息（核心！）
含义：补充说明执行计划的关键细节，常见重要值如下：
| 值 | 含义 | 优化指导 |
|---|---|---|
| Using index | 使用覆盖索引（无需回表），性能极佳 | 理想状态，尽量保留 |
| Using where | 先扫描数据，再用 WHERE 过滤（可能索引失效） | 需检查是否能用索引过滤 |
| Using index condition | 索引条件下推（ICP），先通过索引过滤部分数据，减少回表 | 优化器自动优化，正常 |
| Using filesort | 需额外排序（未使用索引排序），性能差 | 必须优化（加排序字段索引） |
| Using temporary | 需创建临时表（如 GROUP BY/ORDER BY 字段无索引），性能极差 | 必须优化（加联合索引） |
| Using join buffer | 多表连接时使用连接缓冲区（未用索引连接） | 需优化连接字段的索引 |
| Impossible WHERE | WHERE 条件永远为假（如WHERE 1=0），无数据返回 | 检查 SQL 逻辑 |
| Select tables optimized away | 聚合函数（如 COUNT (*)）直接通过索引统计获取，无需扫描数据 | 最优状态 |
三、EXPLAIN 实战案例（手把手分析）
案例 1：索引生效（理想情况）
-- 表结构：user(id PK, name 索引, age) EXPLAIN SELECT id, name FROM user WHERE name = '张三';输出核心字段分析：
| type | key | rows | Extra | 结论 |
|---|---|---|---|---|
| ref | idx_name | 2 | Using index | 索引生效，覆盖索引无回表 |
分析：
type=ref：使用非唯一索引匹配，索引生效；key=idx_name：实际使用 name 索引；Extra=Using index：覆盖索引，无需回表；rows=2：仅扫描 2 行，性能优秀。
案例 2：索引失效（全表扫描）
EXPLAIN SELECT * FROM user WHERE DATE(create_time) = '2025-12-27';输出核心字段分析：
| type | key | rows | Extra | 结论 |
|---|---|---|---|---|
| ALL | NULL | 1000 | Using where | 索引失效，全表扫描 |
分析：
type=ALL：全表扫描，索引失效；key=NULL：未使用任何索引；rows=1000：预估扫描 1000 行，性能差；- 原因：对索引字段
create_time使用了DATE()函数，导致索引失效。
案例 3：联合索引部分生效
-- 联合索引：idx_a_b_c(a,b,c) EXPLAIN SELECT * FROM user WHERE a = 1 AND c = 3;输出核心字段分析：
| key | key_len | Extra | 结论 |
|---|---|---|---|
| idx_a_b_c | 4 | Using where | 仅 a 字段的索引生效，c 失效 |
分析：
key=idx_a_b_c：使用了联合索引，但key_len=4（仅 a 字段的长度）；- 原因：破坏了最左前缀原则（跳过 b 直接用 c），导致 c 的索引失效；
- 优化：调整查询条件为
a=1 AND b IS NOT NULL AND c=3，或修改联合索引为idx_a_c。
四、EXPLAIN 进阶用法
1. EXPLAIN EXTENDED（MySQL 5.6+）
- 额外输出
filtered字段（过滤百分比），表示经过 WHERE 过滤后剩余的行数比例； - 可通过
SHOW WARNINGS查看优化器改写后的 SQL（比如隐式转换、条件简化）：EXPLAIN EXTENDED SELECT * FROM user WHERE phone = 13800138000; SHOW WARNINGS; -- 会显示优化器将phone=13800138000改为phone=CAST(13800138000 AS CHAR)
2. EXPLAIN FORMAT=JSON（结构化输出）
- 输出 JSON 格式的执行计划，包含更详细的成本计算（如
cost_info、rows_examined_per_scan）； - 适合深度分析
3. 分析多表连接
多表连接时，EXPLAIN会返回多行（每张表一行），按id和执行顺序分析：
EXPLAIN SELECT * FROM user u JOIN `order` o ON u.id = o.user_id WHERE u.name = '张三';重点关注：
- 驱动表（先执行的表）的
type是否为ref/const； - 被驱动表的
type是否为eq_ref（主键 / 唯一索引连接）； - 避免
type=ALL的表出现在连接的外层（会导致全表扫描后再连接）。
总结
- EXPLAIN 核心价值：无需执行 SQL，就能查看优化器的执行计划，定位索引失效、全表扫描、临时表 / 文件排序等性能问题；
- 核心关注字段：
type（访问类型，避免 ALL/index）、key（实际使用的索引，避免 NULL）、rows（预估扫描行数，越小越好）、Extra（是否有 Using filesort/Using temporary）； - 使用流程：写 SQL → EXPLAIN 分析 → 定位问题（如 type=ALL、key=NULL） → 优化（加索引 / 改 SQL） → 重新 EXPLAIN 验证。
