MySQL慢查询分析工具：EXPLAIN

概述

• EXPLAIN 是 MySQL 中用于分析 SQL 查询执行计划的工具，能够帮助开发者理解查询的执行过程、索引使用情况、潜在性能瓶颈等。以下是 EXPLAIN 的详细解析及示例。
• 文章已经整理成PDF电子书，喜欢的朋友可以转存以下，方便查看：https://pan.quark.cn/s/f52968c518d3

---

一、EXPLAIN 输出字段详解

字段	说明
id	查询的序列号，表示执行顺序。相同 id 按顺序执行，不同 id 从大到小执行。子查询或 UNION 会生成不同 id。
select_type	查询类型： - SIMPLE：简单查询（无子查询或 UNION） - PRIMARY：最外层查询 - SUBQUERY：子查询 - DERIVED：派生表（FROM 子句中的子查询） - UNION：UNION 中的第二个或后续查询 - UNION RESULT：UNION 结果合并
table	当前行操作的表名（或别名），如派生表显示 <derivedN>（N 为子查询 id）。
partitions	匹配的分区（若表未分区则为 NULL）。
type	访问类型（性能关键指标）： - system：系统表（仅一行） - const：通过主键或唯一索引匹配一行 - eq_ref：JOIN 时使用主键或唯一索引 - ref：非唯一索引扫描 - range：范围扫描（BETWEEN、IN 等） - index：全索引扫描 - ALL：全表扫描
possible_keys	可能使用的索引。
key	实际使用的索引。
key_len	使用的索引字节数，可判断是否使用完整索引。
ref	与索引比较的列或常量（如 const 或 table.column）。
rows	预估需要扫描的行数（越小越好）。
filtered	返回结果占扫描行数的百分比（越高越好）。
Extra	额外信息： - Using where：使用 WHERE 过滤 - Using index：覆盖索引（无需回表） - Using temporary：使用临时表 - Using filesort：额外排序（需优化）

---

二、EXPLAIN 示例分析

示例 1：简单查询（主键查询）

EXPLAIN SELECT * FROM users WHERE id = 1;

输出：

id	select_type	table	type	key	rows	Extra
1	SIMPLE	users	const	PRIMARY	1	Using where

分析：
• type=const：通过主键直接定位到一行。

• key=PRIMARY：使用主键索引。

• rows=1：仅扫描一行，效率极高。

---

示例 2：全表扫描（无索引）

EXPLAIN SELECT * FROM orders WHERE total_amount > 100;

假设 total_amount 无索引：

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	orders	ALL	NULL	NULL	10000	Using where

分析：
• type=ALL：全表扫描，性能差。

• rows=10000：扫描所有行。

• 优化建议：为 total_amount 添加索引。

---

示例 3：联合索引（最左前缀原则）

-- 索引为 (country, city)
EXPLAIN SELECT * FROM addresses WHERE country = 'US' AND city = 'New York';

输出：

id	select_type	table	type	key	rows	Extra
1	SIMPLE	addresses	ref	idx_country_city	50	Using where

分析：
• key=idx_country_city：使用联合索引。

• key_len：可计算是否用到了 country 和 city 列。

若查询仅使用 city（不满足最左前缀）：

EXPLAIN SELECT * FROM addresses WHERE city = 'New York';

此时 possible_keys 可能为 NULL，导致全表扫描。

---

示例 4：JOIN 查询

EXPLAIN SELECT u.name, o.order_date FROM users u JOIN orders o ON u.id = o.user_id WHERE u.age > 30;

输出：

id	select_type	table	type	key	rows	Extra
1	PRIMARY	u	range	age_idx	200	Using where; Using index
1	PRIMARY	o	ref	user_id	5	Using where

分析：
• u 表使用 age_idx 索引进行范围扫描，返回 200 行。

• o 表通过 user_id 索引关联，每行 u 扫描 5 行 o 数据。

• 优化建议：确保 user_id 有索引，减少关联查询的行数。

---

示例 5：子查询与派生表

EXPLAIN SELECT * FROM (SELECT user_id FROM orders WHERE total_amount > 100) AS sub JOIN users ON sub.user_id = users.id;

输出：

id	select_type	table	type	key	rows	Extra
1	PRIMARY		ALL	NULL	1000	Using where
1	PRIMARY	users	eq_ref	PRIMARY	1	Using index
2	DERIVED	orders	range	amount_idx	1000	Using index condition

分析：
• 子查询生成派生表 <derived2>，使用 amount_idx 索引扫描 1000 行。

• 主查询通过 eq_ref（主键）关联 users 表。

---

三、优化建议

1. 避免全表扫描（type=ALL）：为 WHERE 条件或 JOIN 字段添加索引。
2. 利用覆盖索引（Using index）：尽量让查询仅通过索引返回数据。
3. 减少 Using filesort 和 Using temporary：优化 ORDER BY 和 GROUP BY，确保使用索引排序。
4. 关注 rows 和 filtered：若 rows 过大或 filtered 过低，需检查索引有效性。
5. 调整 JOIN 顺序：让小表驱动大表，减少关联行数。

通过 EXPLAIN 分析执行计划，可以精准定位性能瓶颈，针对性优化 SQL 和索引设计。