MySQL执行计划详解与优化

看懂 MySQL 的执行计划（EXPLAIN）是进行 SQL 性能调优的核心技能。它就像给 SQL 语句做了一次“X光检查”，能告诉你数据库打算如何执行这条语句，以及在哪里可能遇到了性能瓶颈。

我将从浅入深，教你如何看懂它。

---

1. 如何获取执行计划？

非常简单，在你要分析的 SELECT 语句前加上 EXPLAIN 或者 EXPLAIN FORMAT=JSON（后者会输出更详细的 JSON 格式信息）即可。

EXPLAIN SELECT * FROM users WHERE name = 'John Doe';

或者用于 UPDATE/DELETE（但实际是先看 SELECT 部分）：

EXPLAIN UPDATE users SET status = 1 WHERE name = 'John Doe';

执行后，你会得到一张表格，而不是你的查询结果。这张表就是执行计划。

---

2. 执行计划结果列解读（重中之重）

执行计划的结果包含多列，每一列都提供了关键信息。以下是你需要重点关注的那些列：

列名	描述	解读与关注点
id	查询的序列号	标识 SELECT 子查询的执行顺序。id 相同，执行顺序从上到下；id 不同，如果是子查询，id 值会递增，id 值越大，优先级越高，越先执行。
select_type	查询的类型	说明查询是简单查询还是复杂查询。常见值： - SIMPLE：简单查询（无子查询或 UNION） - PRIMARY：最外层的查询 - SUBQUERY：在 SELECT 或 WHERE 中的子查询 - DERIVED：在 FROM 子句中的子查询（派生表） - UNION：UNION 中的第二个或之后的查询
table	访问的表	显示这一步访问的是哪张表。有时会是 <derivedN> 或 <union1,2> 这样的临时表。
partitions	匹配的分区	如果表定义了分区，这里显示命中哪些分区。
type	访问类型（极其重要）	这是判断查询效率的关键指标。表示 MySQL 如何查找表中的行。从好到坏依次为： - system > const > eq_ref > ref > range > index > ALL 至少要达到 range 级别，最好能达到 ref。ALL 表示全表扫描，必须优化。
possible_keys	可能使用的索引	显示查询可能会使用哪些索引来查找。如果为 NULL，说明没有可能的索引。
key	实际使用的索引	显示查询实际决定使用的索引。如果为 NULL，则没有使用索引。强制使用索引或忽略索引时，此列会失效。
key_len	使用的索引长度	表示索引中使用的字节数。可通过该列计算查询中使用的索引部分（比如联合索引是否被完全使用）。长度越短，效率一般越高。
ref	与索引比较的列	显示哪些列或常量被用于查找索引列上的值。
rows	预估要扫描的行数	非常直观的性能指标。MySQL 估计为了找到所需的行而必须检查的行数。这个值越小越好！
filtered	按条件过滤的百分比	表示存储引擎返回的数据在服务器层过滤后，剩余多少满足查询。百分比越高越好，100% 表示没有在服务器层过滤。
Extra	额外信息（非常重要）	包含 MySQL 解决查询的详细信息。常见的重要值： - Using index：使用了“覆盖索引”，极好。 - Using where：在存储引擎检索行后进行了过滤。 - Using temporary：使用了临时表，通常需要优化。 - Using filesort：使用了外部排序，无法利用索引排序，通常需要优化。 - Select tables optimized away：使用了一些优化，例如通过索引 MIN/MAX。

---

3. 核心性能指标速查

当你拿到一个执行计划，可以按照以下顺序快速定位问题：

1. 看 type 列：是否出现了 ALL（全表扫描）？如果是，并且表很大，那这就是首要优化目标。想办法让它用上索引（ref, range）。
2. 看 key 列：是否实际使用了索引 (NULL 表示没使用)？使用的索引是你期望的那个吗？
3. 看 rows 列：预估扫描行数是否非常大？如果很大，即使用了索引，索引的选择性可能也不高。
4. 看 Extra 列：是否出现了 Using filesort 或 Using temporary？这通常意味着需要优化 ORDER BY、GROUP BY 或 JOIN 的字段，考虑为其建立索引。

---

4. 实战分析示例

假设我们有两张表：users (用户表) 和 orders (订单表)。

SQL 语句：

EXPLAIN SELECT u.name, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.city = 'Beijing'
ORDER BY u.created_at;

可能的结果分析：

假设执行计划结果中，users 表的那一行显示：

• type: ref (还不错，用到了索引查找)
• key: idx_city (使用了为 city 字段建的索引)
• rows: 1000 (北京有1000个用户)

而 orders 表的那一行显示：

• type: ALL (问题来了！全表扫描)
• key: NULL (没有使用索引)
• rows: 1000000 (订单表有100万行)
• Extra: Using join buffer (Block Nested Loop) (因为没索引，只能使用连接缓冲来加速JOIN)

诊断结论：
查询在关联 orders 表时出现了全表扫描，这是性能瓶颈。因为它在通过 o.user_id 查找时没有索引。

优化方案：
在 orders 表的 user_id 字段上添加一个索引。

CREATE INDEX idx_user_id ON orders (user_id);

优化后预期：
再次查看执行计划，orders 表的访问类型 type 应该会变成 ref，key 会显示 idx_user_id，rows 会大幅下降（例如每个用户平均10个订单，则扫描行数会从100万降到 1000 * 10 = 10000 行），性能得到巨大提升。

---

总结

1. 养成习惯：对复杂查询或慢查询，先用 EXPLAIN 看一下。
2. 重点关注：type、key、rows、Extra 这四列。
3. 优化目标：避免 ALL（全表扫描），避免 Using filesort 和 Using temporary，努力实现 Using index（覆盖索引），让 rows 值尽可能小。
4. 深入工具：对于更复杂的分析，可以使用 EXPLAIN FORMAT=JSON 获得更详细的信息，或者使用 MySQL Workbench 等可视化工具来更直观地查看执行计划。

掌握 EXPLAIN，你就拥有了洞察数据库性能问题的“火眼金睛”。