MySQL EXPLAIN 详解

MySQL 的 EXPLAIN 是分析和优化 SQL 性能的核心工具，它能揭示 SQL 的执行计划（查询优化器选择的执行路径）。以下是各字段的深度解析与实战示例：

---

一、EXPLAIN 关键字段详解

字段	含义	常见值及说明
id	查询的序列号（多表关联时标识执行顺序）	相同 id 按从上到下执行；不同 id 从大到小执行（id 越大优先级越高）
select_type	查询类型	SIMPLE：简单查询 PRIMARY：外层查询 SUBQUERY：子查询 DERIVED：派生表（FROM 子句中的子查询） UNION：UNION 中的第二个及后续查询
table	访问的表名	可能是表名、别名、<derivedN>（派生表）、<unionM,N>（UNION 结果）
partitions	匹配的分区	分区表生效时显示命中的分区
type ⭐	访问类型（性能核心指标）	从优到差排序： system > const > eq_ref > ref > range > index > ALL （至少达到 range，避免 ALL）
possible_keys	可能使用的索引	查询涉及字段上存在的索引（不一定实际使用）
key ⭐	实际使用的索引	NULL 表示未用索引；优先选择覆盖索引
key_len	索引使用的字节数	数值越大说明索引利用率越高（联合索引中可判断使用了几个字段）
ref	索引的引用关系	const：常量值 func：函数结果 字段名：关联查询的字段
rows ⭐	预估扫描的行数	数值越小越好（实际可能偏差较大）
filtered	存储引擎层过滤后剩余数据的百分比	100 表示未过滤，值越小说明过滤效果越好
Extra ⭐	额外执行信息（关键性能提示）	Using index：覆盖索引 Using where：Server 层过滤 Using temporary：使用临时表 Using filesort：额外排序 Using index condition：索引下推

---

二、核心字段深度解析

1. type 访问类型详解

类型	触发场景	示例
system	表只有一行数据（系统表）	EXPLAIN SELECT * FROM mysql.proxies_priv;
const	通过主键/唯一索引定位单条记录	EXPLAIN SELECT * FROM users WHERE id = 1;
eq_ref	多表 JOIN 时，使用主键/唯一索引关联（每行只匹配一条记录）	EXPLAIN SELECT * FROM orders JOIN users ON orders.user_id = users.id;
ref	使用非唯一索引扫描	EXPLAIN SELECT * FROM users WHERE age = 30;（age 有索引）
range	索引范围扫描（BETWEEN、>、IN）	EXPLAIN SELECT * FROM orders WHERE amount > 1000;
index	全索引扫描（比 ALL 快，但仍需遍历索引树）	EXPLAIN SELECT COUNT(*) FROM products;（用二级索引统计）
ALL	全表扫描（必须优化）	EXPLAIN SELECT * FROM logs WHERE content LIKE '%error%';

2. Extra 关键值解析

值	含义	优化建议
Using index	覆盖索引（查询列都在索引中，无需回表）	优先使用覆盖索引
Using where	Server 层对存储引擎返回的数据进行过滤	检查索引是否失效或缺失
Using temporary	使用临时表（常见于 GROUP BY、DISTINCT、UNION）	优化 SQL 结构或增加索引
Using filesort	额外排序（无法利用索引排序）	为 ORDER BY 字段增加索引
Using index condition	索引下推（ICP）：在存储引擎层过滤数据	MySQL 5.6+ 默认开启，无需干预
Select tables optimized away	优化器已优化（如 MIN()/MAX() 直接走索引）	表明优化成功

---

三、EXPLAIN 实战分析示例

场景：订单查询优化

EXPLAIN 
SELECT o.order_id, u.username, o.amount 
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid'
ORDER BY o.create_time DESC
LIMIT 10;

执行计划结果：

id	select_type	table	type	key	rows	Extra
1	SIMPLE	o	ref	idx_status	1000	Using filesort
1	SIMPLE	u	eq_ref	PRIMARY	1	NULL

问题诊断：

1. o 表访问类型为 ref：status 字段有索引，但需扫描 1000 行。
2. Extra: Using filesort：无法利用索引排序，需额外排序。
3. 未利用覆盖索引：查询了 order_id, amount, create_time 但索引仅含 status。

优化方案：

-- 创建联合索引（覆盖查询+排序）
ALTER TABLE orders ADD INDEX idx_status_time (status, create_time);

-- 重写后的执行计划：

id	select_type	table	type	key	rows	Extra
1	SIMPLE	o	ref	idx_status_time	100	Using index
1	SIMPLE	u	eq_ref	PRIMARY	1	NULL

优化效果：

• 扫描行数从 1000 → 100
• 消除 Using filesort
• 实现覆盖索引 (Using index)

---

四、高级技巧

1. EXPLAIN FORMAT=JSON（MySQL 5.6+）

EXPLAIN FORMAT=JSON 
SELECT * FROM products WHERE category_id = 5;

输出包含详细成本计算、索引选择原因（cost_info），适合深度分析。

2. 可视化工具推荐

• MySQL Workbench：图形化解释执行计划
• Percona Toolkit 的 pt-visual-explain：

  pt-visual-explain explain_output.txt
  

3. 索引下推（ICP）示例

-- 表结构
CREATE TABLE sales (
  id INT PRIMARY KEY,
  region VARCHAR(20),
  amount INT,
  INDEX idx_region_amount (region, amount)
);

-- 查询
EXPLAIN SELECT * FROM sales 
WHERE region = 'Asia' AND amount > 1000;

结果：
Extra: Using index condition
说明：存储引擎直接过滤 region='Asia' AND amount>1000，减少回表。

---

五、总结：EXPLAIN 优化四步法

1. 看 type：确保至少达到 range（避免 ALL/index）
2. 盯 key：检查是否使用正确索引（尤其联合索引）
3. 查 rows：预估扫描行数越少越好
4. 析 Extra：解决 Using temporary、Using filesort 等警告

通过 EXPLAIN 理解优化器的选择逻辑，是定位慢 SQL 和设计高效索引的基石。