摘要：在数据库面试和生产事故排查中，EXPLAIN 是最基础也是最重要的工具。很多人只看 type 和 key，这远远不够。本文将按照官方标准，对 EXPLAIN 输出的 12 个列（id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra）进行逐一深度解析，帮你彻底吃透 SQL 优化的底层逻辑。

---

一、 EXPLAIN 概览

不管你的 SQL 写得多么花哨，在 MySQL 看来，最终都要转化成一系列的底层操作。使用 EXPLAIN 关键字，我们可以看到 MySQL 优化器（Optimizer）是如何执行你的 SQL 的。

执行命令：

EXPLAIN SELECT * FROM user WHERE id = 1;

结果通常包含以下 12 个字段。我们将从左到右，抽丝剥茧。

---

二、 12 个核心字段深度解析

1. id (查询序列号)

含义：EXPLAIN 输出中的 id 列代表 SELECT 查询的序列号，它由一组数字组成，用于表示查询中执行 SELECT 子句或操作表的顺序。这个序列号可以帮助我们理解复杂查询的执行流程。

详细说明：

1. id 相同的情况：

   • 当多个 SELECT 子句具有相同的 id 值时，表示这些子句是同一级别的查询
   • 执行顺序遵循从上往下的原则
   • 示例：

     EXPLAIN SELECT * FROM table1, table2 WHERE table1.id = table2.id;
     

     这种情况下，table1 和 table2 的 id 值相同，执行顺序取决于它们在 SQL 中的位置

2. id 不同的情况：

   • 当 id 值不同时，通常表示存在子查询或嵌套查询
   • id 值会随着查询的嵌套层级递增
   • 执行原则：id 值越大优先级越高，该查询会越先被执行
   • 示例：

     EXPLAIN SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
     

     这里子查询的 id 值会比主查询大

3. id 为 NULL 的情况：

   • 这种特殊情况通常出现在 UNION 操作的结果集中
   • 表示该行是 UNION 结果的合并操作
   • 在查询执行顺序中，这类操作通常是最后执行的
   • 示例：

     EXPLAIN SELECT * FROM table1 UNION SELECT * FROM table2;
     

     UNION 结果的合并行会显示 id 为 NULL

应用场景：

• 分析复杂查询的执行计划时，通过 id 列可以快速识别查询的层级关系
• 优化查询性能时，了解执行顺序有助于发现潜在的性能瓶颈
• 在包含多个子查询或 JOIN 操作的复杂 SQL 中，id 列是理解执行流程的关键指标

2. select_type (查询类型)

含义：用来分辨查询的类型，主要是用于区别普通查询、联合查询、子查询等复杂查询。这个字段对于理解查询执行过程和优化SQL语句非常重要。

类型	描述	示例场景
SIMPLE	简单的 SELECT 查询，不包含子查询或 UNION。	SELECT * FROM users WHERE id = 1;
PRIMARY	查询中若包含任何复杂的子部分，最外层查询则被标记为 PRIMARY。	在包含子查询的语句中，外层查询会被标记为PRIMARY
SUBQUERY	在 SELECT 或 WHERE 列表中包含了子查询。	SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);
DERIVED	在 FROM 列表中包含的子查询被标记为 DERIVED（衍生），MySQL 会递归执行这些子查询，把结果放在临时表里。	SELECT * FROM (SELECT * FROM orders WHERE status='paid') AS temp_table;
UNION	若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION。	SELECT id FROM users UNION SELECT id FROM customers;
UNION RESULT	从 UNION 表获取结果的 SELECT。	在执行UNION查询时，合并结果的查询会被标记为UNION RESULT

补充说明：

1. 对于复杂查询，MySQL会先执行子查询（SUBQUERY或DERIVED），然后将结果保存在临时表中
2. UNION RESULT通常出现在EXPLAIN结果的最后一行，表示合并UNION操作的结果
3. DERIVED子查询会创建临时表，这可能影响查询性能，在优化时需要考虑

3. table (表名)

含义：显示这一行的数据是关于哪张表的。

• 如果是子查询或衍生表，这里可能会显示 <derived2>，表示该结果来自于 id 为 2 的查询结果。

4. partitions (分区)

含义：匹配的分区信息。

• 如果表没有分区，通常为 NULL。
• 对于分区表，显示查询将访问的分区名称。这在优化分区表的查询时非常关键。

5. type (访问类型) —— ⭐ 核心指标

含义：显示查询使用了何种类型。这是衡量 SQL 性能好坏最直观的指标，通过分析访问类型可以快速判断查询是否需要优化。

性能从优到差排序：
system > const > eq_ref > ref > range > index > ALL

详细说明及示例：

• system：表只有一行记录（系统表），这是 const 类型的特例。例如：

  SELECT * FROM mysql.proc WHERE name='procedure_name';
  

• const：通过索引一次就找到了，const 用于比较 primary key 或 unique 索引。例如：

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

• eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。例如：

  SELECT * FROM users JOIN orders ON users.user_id = orders.user_id;  -- orders.user_id是唯一索引
  

• ref：非唯一性索引扫描，返回匹配某个单独值的所有行。例如：

  SELECT * FROM users WHERE username = 'john';  -- username是普通索引
  

• range：只检索给定范围的行，使用一个索引来选择行（如 between, >, <, in）。这是 SQL 优化的底线。例如：

  SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';
  

• index：Full Index Scan，全索引扫描。只遍历索引树，通常比 ALL 快，因为索引文件通常比数据文件小。例如：

  SELECT username FROM users;  -- username列有索引
  

• ALL：Full Table Scan，全表扫描。必须优化。例如：

  SELECT * FROM users WHERE last_name LIKE '%son%';  -- 没有合适的索引可用
  

6. possible_keys (可能用到的索引)

含义：显示可能应用在这张表中的索引，一个或多个。

• 查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

7. key (实际使用的索引) —— ⭐ 核心指标

含义：实际使用的索引。

• 如果为 NULL，则没有使用索引。
• 覆盖索引提示：如果查询中使用了覆盖索引（Covering Index），则该索引仅出现在 key 列表中，可能不出现在 possible_keys 中。

8. key_len (索引长度)

含义：表示索引中使用的字节数。

• 可通过该列计算查询中使用的索引的长度。
• 作用：在联合索引中，key_len 可以帮你判断索引是否被完全使用，还是只用了前缀部分。
• 例如：联合索引 (a, b, c)，如果 key_len 长度只对应 a 的长度，说明 b 和 c 没用到。

9. ref (索引比较对象)

含义：在 EXPLAIN 输出的 ref 列中，显示的是索引查找时使用的比较对象，即哪些列或常量被用于与索引列进行值匹配。如果可能的话，这个比较对象会显示为一个常数（const）。

详细说明：

1. const 情况：当查询条件使用常量值直接与索引列比较时，ref 会显示为 “const”。例如：

   EXPLAIN SELECT * FROM users WHERE id = 10;
   

   这里 id 是主键索引，与常量值 10 比较，ref 列会显示 “const”。

2. 列引用情况：当使用其他表的列作为比较对象时，会显示该列名。例如在多表连接时：

   EXPLAIN SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;
   

   如果 orders.customer_id 使用了 customers.id 列的值进行索引查找，ref 会显示 “customers.id”。

3. func 情况：当使用函数处理后的结果进行索引匹配时，会显示 “func”。例如：

   EXPLAIN SELECT * FROM users WHERE MONTH(created_at) = 5;
   

   这里对 created_at 使用了 MONTH() 函数，ref 会显示 “func”。

10. rows (预估行数) —— ⭐ 核心指标

含义：MySQL 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。

• 越少越好。注意，这不是结果集的大小，而是扫描的大小。

11. filtered (过滤比例)

含义：表示返回结果的行数占开始查找行数的百分比。

• 值越大越好。
• 例如：rows = 1000，filtered = 50.00，说明 MySQL 扫描了 1000 行，但最后只有 50%（500行）是符合 WHERE 条件的。这暗示索引可能不够精准。

12. Extra (额外信息) —— ⭐ 核心指标

含义：包含不适合在其他列中显示但十分重要的额外信息。

出现以下情况需重点关注：

• ✅ Using index：好！ 表示相应的 SELECT 操作中使用了覆盖索引，避免访问表的数据行（回表），效率不错。
• ❌ Using filesort：坏！ 说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。需优化索引。
• ❌ Using temporary：极坏！ 使用了临时表保存中间结果，常见于 ORDER BY 和 GROUP BY。
• ℹ️ Using where：表明使用了 WHERE 过滤。
• ℹ️ Using join buffer：使用了连接缓存，说明 JOIN 效率可能不高，需要检查关联字段的索引。

---

三、 实战演练：如何看懂这 12 个字段？

假设有如下 SQL：

EXPLAIN SELECT * FROM order_info WHERE user_id = 1001 ORDER BY create_time;

模拟输出分析：

id	select_type	table	type	key	key_len	Extra
1	SIMPLE	order_info	ref	idx_userid	4	Using index condition; Using filesort

深度解读：

1. id=1, select_type=SIMPLE：这是一个简单查询。
2. type=ref：使用了非唯一索引扫描，性能尚可。
3. key=idx_userid：实际使用了 user_id 的索引。
4. Extra=Using filesort：这是性能痛点！

• 虽然 user_id 走了索引，但 ORDER BY create_time 并没有利用到索引，导致 MySQL 进行了文件排序。
• 优化建议：建立联合索引 idx_userid_createtime (user_id, create_time)，利用索引的有序性消除 filesort。

---

四、 总结

EXPLAIN 是 MySQL 调优的入场券。请务必记住以下 3 条黄金法则：

1. **紧盯 type**：努力优化到 ref 或 range，坚决消灭 ALL。
2. **关注 Extra**：出现 Using filesort 或 Using temporary 时，意味着你的索引设计有问题。
3. **计算 key_len**：在使用联合索引时，确保最左前缀法则生效，让索引“用满”。