MySQL EXPLAIN 完全指南：新手入门

什么是 EXPLAIN？

EXPLAIN 是 MySQL 提供的模拟优化器执行 SQL 语句的工具。它不会真正执行你的 SQL，而是返回一个执行计划表。

可以比作医生的 X 光片：

  • 表是如何被扫描的？（全表扫还是走索引？）
  • 多表连接时，谁先谁后？
  • 有没有用到索引？用到了哪些？
  • 有没有发生临时表排序、文件排序等耗时操作？

如何使用？

在 SELECT 语句前加上 EXPLAIN 即可。

EXPLAIN SELECT * FROM users WHERE id = 1;

如果是更新或删除操作，也可以使用：

EXPLAIN UPDATE users SET name = 'Tom' WHERE id = 1;
EXPLAIN DELETE FROM users WHERE id = 1;

输出结果示例：

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE users const PRIMARY PRIMARY 4 const 1 NULL

核心字段详解（五大金刚）

不需要背诵所有字段，掌握以下 5 个核心字段即可解决 90% 的问题。

id：执行顺序的指挥官

id 表示查询中执行 SELECT 子句或操作表的顺序。

规则一：id 相同，从上到下

如果 id 值相同，执行顺序从上到下。

-- id 都是 1，先查 users，再查 orders
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;

规则二：id 不同，越大越先

如果 id 值不同，id 值越大，优先级越高，越先执行。

  • 通常出现在子查询中。内层子查询的 id 比外层大。
  • 执行流程：先执行 id 最大的子查询 -> 结果集作为临时表 -> 再执行外层。

规则三：混合情况

既有相同又有不同：先按组（id 大小）执行，组内按从上到下执行。

select_type：查询类型的骨架

表示查询的类型，主要用于区分普通查询、联合查询和子查询。

值 含义 说明
SIMPLE 简单查询 不包含子查询或 UNION。最简单的单表或多表 JOIN。
PRIMARY 主查询 查询中包含任何子部分时，最外层查询被标记为 PRIMARY。
SUBQUERY 子查询 在 SELECT 或 WHERE 列表中的子查询。只执行一次。
DERIVED 派生表 在 FROM 子句中的子查询。MySQL 会递归执行这些子查询，将结果放在临时表中。(性能隐患点)
DEPENDENT SUBQUERY 依赖子查询 子查询依赖外部查询的值。外层每行都执行一次子查询，性能极差！⚠️
UNION 联合查询 UNION 语句中第二个及以后的 SELECT。
UNION RESULT 联合结果 从 UNION 表获取结果的检索过程。

⚠️ 警惕：看到 DEPENDENT SUBQUERY 或 DERIVED 且数据量大时，务必考虑改写为 JOIN。

type：性能好坏的金标准

这是最重要的字段！ 表示 MySQL 在表中找到所需行的方式。
性能从好到坏排序：

类型 含义 性能评价 建议
NULL 不用查表 👻 神话 优化器直接算出结果（如 SELECT MIN(id)）。
const 常量查询 🏆 极致 最多匹配一行。通常是主键或唯一索引等值查询。
eq_ref 唯一索引匹配 🌟 优秀 多表 JOIN 时，主键或唯一索引关联。
ref 非唯一索引匹配 ✅ 良好 普通索引等值查询。
range 范围扫描 ✅ 及格 索引范围查询 (>, <, BETWEEN, LIKE 'abc%')。
index 全索引扫描 ⚠️ 较差 扫描整个索引树。虽比全表好，但数据量大依然慢。
ALL 全表扫描 ❌ 极差 必须优化！ 遍历整张表，数据量大时直接卡死。

🎯 优化目标：至少达到 range，争取 ref 或更高。严禁出现 ALL（除非表很小）。

key & key_len：索引的真相

  • key：实际使用的索引名称。如果是 NULL，说明没走索引。
  • key_len：使用索引的长度（字节数）。用于判断联合索引是否被完全利用。

如何计算 key_len？（判断联合索引用到哪一列）

  • 定长字符：char(n) = n 字节。
  • 变长字符：varchar(n) = n + 2 字节（2 字节存长度）。
  • 数字类型：int = 4, bigint = 8。
  • 允许 NULL：每个字段额外 +1 字节。

案例：
索引 (user_id bigint NOT NULL, status varchar(10) NULL)

  • 理论全长：8 (user_id) + 10+2 (status) + 1 (null标记) = 21。
  • 若 key_len = 8：只用了 user_id。
  • 若 key_len = 21：两个字段都用到了。

Extra：隐藏的杀手与宝藏

这里显示不适合在其他列中显示但很重要的额外信息。

❌ 看到这些词，要警惕（通常需要优化）

  1. Using filesort
    • 含义：无法利用索引完成排序，需要额外的排序操作（内存或磁盘）。
    • 对策：检查 ORDER BY 字段是否符合“最左前缀”原则，或建立覆盖索引。
  2. Using temporary
    • 含义：使用了临时表来存储中间结果（常见于 GROUP BY, DISTINCT, ORDER BY 组合）。
    • 对策：给 GROUP BY 字段加索引，利用索引有序性消除临时表。
  3. Using index condition
    • 含义：使用了 ICP (Index Condition Pushdown)。索引过滤了一部分，回表后再过滤一部分。说明联合索引未完全覆盖查询条件。

✅ 看到这些词，算是正常情况

  1. Using index (注意：没有 Using where)
    • 含义：覆盖索引。查询的所有字段都在索引树上，不需要回表。性能极佳！
  2. Using where
    • 含义：正常的过滤操作。配合 ref 或 range 出现是正常的。

实战诊断三步法

拿到 EXPLAIN 结果，按此顺序“读片”：

第一步：看 type

  • 是不是 ALL？如果是，立刻检查 WHERE 条件字段是否有索引。
  • 是不是 range 以下？如果是，尝试优化索引或 SQL 逻辑。

第二步：看 Extra

  • 有没有 Using temporary 或 Using filesort？
  • 如果有，检查 GROUP BY 和 ORDER BY 字段是否能利用现有索引。

第三步：看 key_len (针对联合索引)

  • 建立了 (a, b, c) 索引，但 key_len 很短？
  • 说明可能只用了 a，或者 b 因为范围查询/类型转换断掉了。

常见场景案例解析

场景 A：简单的单表查询

EXPLAIN SELECT * FROM users WHERE phone = '13800000000';
  • 理想结果：type=ref, key=idx_phone, Extra=NULL 或 Using where。
  • 糟糕结果：type=ALL, key=NULL。
    • 原因：phone 字段没索引。
    • 解决：ALTER TABLE users ADD INDEX idx_phone(phone);

场景 B：联合索引失效（最左前缀原则）

索引：idx_a_b_c (a, b, c)

-- 情况 1：跳过中间列
EXPLAIN SELECT * FROM t WHERE a = 1 AND c = 3; 
-- 结果：只用到了 a。key_len 较短。c 用不到索引。

-- 情况 2：范围查询断开
EXPLAIN SELECT * FROM t WHERE a = 1 AND b > 2 AND c = 3;
-- 结果：用到了 a 和 b。c 用不到（因为 b 是范围查询，后面的列无法利用索引有序性）。

场景 C：复杂的子查询与 JOIN

SELECT u.name, COUNT(o.id) 
FROM users u
JOIN (SELECT user_id FROM orders WHERE amount > 100) o_sub ON u.id = o_sub.user_id
GROUP BY u.name;
  • 关注点：
    1. select_type：会出现 DERIVED（子查询生成的临时表）。
    2. type：检查 DERIVED 表是否走了索引。
    3. Extra：是否有 Using temporary（由 GROUP BY 引起）。
  • 优化思路：尽量将 DERIVED 子查询改写为直接 JOIN，让优化器有机会下推条件。

避坑指南与优化口诀

🚫 常见导致索引失效的坑

  1. 对索引列做运算：WHERE YEAR(create_time) = 2023 ❌ -> WHERE create_time BETWEEN ... ✅
  2. 隐式类型转换：字符串字段不加引号 WHERE phone = 1380000 ❌ -> WHERE phone = '1380000' ✅
  3. 模糊查询通配符在前：LIKE '%abc' ❌ -> LIKE 'abc%' ✅
  4. OR 连接无索引列：WHERE a = 1 OR b = 1 (若 b 无索引，全表扫) ❌ -> 改用 UNION ALL ✅

🧠 独家记忆口诀

一看 Type 怕全表 (ALL)，
二看 Extra 怕临时 (temporary) 排序 (filesort)，
三看 Len 短没喂饱 (联合索引没用完)，
覆盖索引 (Using index) 是块宝。

ID 大号先跑小号后，
Dependent 子查询赶紧丢 (改 JOIN)！

📚 附录：快速检查清单

在执行慢查询优化时，依次核对：

  • ☐ type 是否为 ALL？
  • ☐ key 是否为 NULL？
  • ☐ Extra 是否包含 Using temporary 或 Using filesort？
  • ☐ key_len 是否符合预期（联合索引是否用完）？
  • ☐ select_type 是否有 DEPENDENT SUBQUERY？
  • ☐ WHERE 条件是否存在隐式转换或函数运算？