MySQL 的 EXPLAIN 命令。这是一个分析和优化 SQL 查询性能不可或缺的强大工具。它展示了 MySQL 如何执行一条 SQL 语句，包括如何使用索引、表连接顺序、估计的行数等关键信息。

1. 如何使用 EXPLAIN

在你要分析的 SELECT 语句前加上 EXPLAIN 或 EXPLAIN FORMAT=JSON（获取更详细的 JSON 格式信息）即可。

EXPLAIN SELECT * FROM users WHERE age > 30;

或者用于分析连接查询：

EXPLAIN SELECT u.name, o.order_id 
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.country = 'China';

2. EXPLAIN 输出列详解

执行 EXPLAIN 后，会返回一个包含多列的结果集。每一列都描述了执行计划的一个特定方面。以下是这些列的详细解释，按重要性排序：

列名	描述
id	查询中每个 SELECT 子句的唯一标识符。
select_type	SELECT 语句的类型（简单、子查询、联合等）。
table	正在访问的表名。
partitions	匹配的分区。如果表未分区，则为 NULL。
type (非常重要)	连接类型 或 访问类型。这是衡量查询效率的关键指标，从最优到最差排列。
possible_keys	MySQL 可能选择用来查找该表的索引。
key	MySQL 实际决定使用的索引。如果为 NULL，则未使用索引。
key_len	所使用的索引的长度（字节数）。用于判断是否充分利用了索引（例如，复合索引使用了最左前缀的多少部分）。
ref	显示索引的哪一列被用来与 key 列指定的索引进行比较，以从表中选择行。
rows (非常重要)	MySQL 估计为了找到所需的行而必须检查的行数。这是一个估计值，通常越小越好。
filtered	表示存储引擎返回的数据在服务器层过滤后，剩余的行数占估计总行数的百分比。理想是 100%。
Extra (非常重要)	包含 MySQL 解决查询的额外信息。这里经常会出现需要重点关注的内容，如是否使用了临时表、文件排序等。

---

3. 关键列深度解析

1. type (访问类型)

这是最重要的列之一。结果值从好到坏依次是：

• system: 表只有一行（系统表）。这是 const 类型的特例。

• const: 通过索引一次就能找到，用于比较 主键 或 唯一索引 的等值查询。速度极快。

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

• eq_ref: 在连接查询中，对于来自前表的每一行，从当前表中读取唯一的一行。通常出现在使用 主键 或 唯一索引 的联表查询中。

-- 假设 orders.user_id 是 users.id 的外键，并且有索引
EXPLAIN SELECT * FROM users 
INNER JOIN orders ON users.id = orders.user_id;

• ref: 使用非唯一性索引进行等值查找，或者使用索引的最左前缀规则进行查找。可能会返回多行。

EXPLAIN SELECT * FROM users WHERE age = 30; -- age 字段有一个普通索引

• range: 使用索引检索给定范围的行，关键操作符是 BETWEEN, >, <, IN 等。

EXPLAIN SELECT * FROM users WHERE age > 20 AND age < 30;

• index: 全索引扫描。只遍历索引树来获取数据，通常比 ALL 快，因为索引文件通常比数据文件小。

-- 假设 (age) 是一个索引
EXPLAIN SELECT age FROM users; -- 只需扫描索引，无需回表

• ALL: 全表扫描。性能最差，意味着MySQL必须从头到尾扫描整个表来找到匹配的行。如果数据量大，需要优化（如添加索引）。

目标：在查询优化中，我们至少要让 type 达到 range 级别，最好能达到 ref 或以上。

2. Extra (额外信息)

此列包含大量重要信息，常见值及其含义：

• Using index: 表示查询使用了 覆盖索引(Covering Index)，即所有需要的数据都可以从索引中获取，无需回表读取数据行。性能极佳。

• Using where: 表示存储引擎返回行后，MySQL 服务器层还需要再进行过滤（WHERE 子句中的条件不能完全用索引来过滤）。

• Using temporary: 表示 MySQL 需要创建一个临时表来存储结果以处理查询。常见于 GROUP BY 和 ORDER BY 子句。通常需要优化。

• Using filesort: 表示 MySQL 无法使用索引来完成排序，需要额外的排序操作。ORDER BY 、 GROUP BY 可能会引发此问题。在数据量大时性能很差，需要优化。

• Using join buffer (Block Nested Loop): 表示连接查询时，被驱动表没有使用索引，需要用到连接缓冲区。应考虑为被驱动表的连接字段添加索引。

• Impossible WHERE: WHERE 子句的条件始终为 false，无法获取任何行。

3. rows

MySQL 根据统计信息估算的需要读取的行数。这个值乘以 filtered 百分比，可以估算出将要和下一张表连接的行数。这个值对于找出性能瓶颈非常有用，值越小越好。

4. key

实际使用的索引。如果为 NULL，则说明没有使用索引，需要检查 possible_keys 为什么没有被选用，或者考虑创建合适的索引。

---

4. 实战分析示例

假设我们有两张表：

users 表

• id (INT, PRIMARY KEY)

• name (VARCHAR(100))

• age (INT)

• country (VARCHAR(100))

• 索引： idx_age_country (age, country)

orders 表

• order_id (INT, PRIMARY KEY)

• user_id (INT)

• amount (DECIMAL)

• 索引： idx_user_id (user_id)

查询： 查找年龄在 25 到 35 岁之间、来自‘China’的用户的所有订单金额。

EXPLAIN 
SELECT o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.age BETWEEN 25 AND 35
AND u.country = 'China';

可能的 EXPLAIN 输出分析：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	u	range	idx_age_country	idx_age_country	208	NULL	100	10.00	Using where
1	SIMPLE	o	ref	idx_user_id	idx_user_id	5	test.u.id	1	100.00	NULL

逐行解读：

1. 第一行 (users 表 u)：

   • type: range: 很好！使用了索引范围扫描来查找年龄在 25-35 之间的用户。

   • key: idx_age_country: 实际使用了我们创建的复合索引。

   • key_len: 208: 索引使用的长度，可以推断出 age (INT 为 4 字节) 和 country (VARCHAR(100), 假设 utf8mb4 字符集，最坏情况 100*4 + 长度前缀) 部分都被用到了。

   • rows: 100: MySQL 估计大约要扫描 100 行 users 表记录。

   • Extra: Using where: 因为 country='China' 是索引的第二部分，它在索引范围内进行查找（BETWEEN）后，可能还需要用这个条件进一步过滤数据。如果索引是 (country, age)，效率可能会更高。

2. 第二行 (orders 表 o)：

   • type: ref: 很好！对于从 u 表找到的每一个 id，通过非唯一索引 idx_user_id 在 o 表中快速查找匹配的行。

   • key: idx_user_id: 实际使用了连接字段上的索引。

   • ref: test.u.id: 使用的是 u.id 的值来查找 o 表。

   • rows: 1: 对于每一个 u.id，MySQL 估计在 o 表中只找到 1 行记录（这是一个很好的估计，假设一个用户只有一个订单）。

结论：这个查询的执行计划相当高效。两张表都有效地使用了索引 (range 和 ref)。没有出现 Using temporary 或 Using filesort 等危险信号。

5. 总结与最佳实践

1. 关注核心列：优先查看 type, key, rows, Extra 列。

2. 索引是王道：目标是让 type 达到 range 级别以上，避免出现 ALL（全表扫描）。

3. 警惕坏信号：在 Extra 列中，出现 Using temporary 和 Using filesort 通常是需要优化的信号，尤其是在大表查询中。

4. 覆盖索引：努力让 Extra 列出现 Using index，这能极大提升性能。

5. 联表查询：确保连接条件（ON 子句）和被驱动表（第二张表）的 WHERE 子句上有索引。EXPLAIN 结果中，第一张表是驱动表。

6. 不要迷信估计：rows 列是基于统计信息的估计值，有时可能不准确。可以用 ANALYZE TABLE table_name; 来更新统计信息。

7. 使用 JSON 格式：对于复杂查询，使用 EXPLAIN FORMAT=JSON 可以获取更详尽的分析信息，包括成本估算。