当前位置：首页 > news >正文

EXPLAIN：你的SQL性能优化透视镜

news 2025/7/21 9:39:45

一、EXPLAIN是什么？

二、如何使用EXPLAIN？

三、EXPLAIN输出解读（核心字段）

四、EXPLAIN的实际应用场景

五、实战优化案例

六、使用 EXPLAIN 的注意事项与小贴士

七、总结

一、EXPLAIN是什么？

简单来说，EXPLAIN 是一条 SQL 命令（或命令前缀），你将它加在你的 SELECT, INSERT, UPDATE, DELETE, REPLACE 语句之前。数据库不会真正执行你的操作语句，而是返回该语句的“执行计划”。

执行计划是什么？ 它是数据库优化器（Optimizer）基于当前数据库统计信息（如表大小、索引分布等），为你的 SQL 语句制定的一套最优（或接近最优）的执行方案。它详细说明了：

如何访问表（全表扫描？走哪个索引？）
如何连接多个表（使用哪种 JOIN 算法？嵌套循环？哈希连接？排序合并？）
执行步骤的顺序（先做哪一步，后做哪一步）
每个步骤的预估成本（需要读取多少行？处理多少数据？）
是否使用了临时表或文件排序（通常影响性能的关键点）

二、如何使用EXPLAIN？

语法极其简单

EXPLAIN SELECT * FROM users WHERE country = 'USA' AND age > 30;

三、EXPLAIN输出解读（核心字段）

执行 EXPLAIN 后，你会得到一个表格（或 JSON/TREE 结构），包含以下关键列：

1. id：查询中 SELECT 子句的执行顺序标识符。相同 id 按 select_type 顺序执行，id 越大优先级越高，NULL 表示是结果行。

2. select_type：查询类型。常见值：

SIMPLE：简单查询（无子查询或 UNION）。
PRIMARY：最外层查询。
SUBQUERY：子查询中的第一个 SELECT。
DERIVED：派生表（FROM 子句中的子查询结果）。
UNION：UNION 中第二个及以后的 SELECT。
UNION RESULT：UNION 的结果。

3. table：当前步骤访问的表名（或别名，或如 <derivedN>, <unionM,N>）。

4. partitions：匹配的分区（如果表分区了）。

5. type (非常关键！)：访问类型，即如何查找行。性能从最优到最差排序：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
理想目标：system, const, eq_ref, ref, range。
警惕信号：index（全索引扫描，通常比全表快但也不佳），ALL（全表扫描，性能杀手！）。

6. possible_keys：查询可能使用到的索引列表。

7. key：查询实际决定使用的索引。NULL 表示未使用索引。

8. key_len：使用的索引的长度（字节数）。可判断是否充分利用了复合索引。

9. ref：显示索引的哪一列或常量被用来与 key 一起从表中筛选行。

10. rows (重要！)：MySQL 优化器预估需要扫描的行数（不是精确值！）。这个值越小越好，是衡量查询效率的关键指标。

11. filtered：表示存储引擎返回的数据在 MySQL 服务器层进行 WHERE 条件过滤后，剩余行数的百分比预估。100 表示没有在服务器层过滤。

12. Extra (包含重要信息！)：额外信息，常揭示性能问题：

Using index：使用了覆盖索引（查询所需列都在索引中，无需回表），好！
Using where：服务器层在存储引擎返回行后进行了过滤。
Using temporary：使用了临时表来处理结果（排序、GROUP BY、DISTINCT 等可能导致），需警惕！
Using filesort：使用了文件排序（在内存或磁盘排序），需警惕！ (尤其对大结果集)
Using join buffer (Block Nested Loop)：使用了连接缓冲区（通常发生在没有合适索引的 JOIN 时）。
Select tables optimized away：优化器确定只需访问索引，无需访问表（如 MIN(key_col)）。

四、EXPLAIN的实际应用场景

1. 诊断慢查询：
当发现某个查询执行缓慢时，第一时间 EXPLAIN 它！重点看：

type 是否为 ALL 或 index？
key 是否为 NULL (没走索引)？
rows 预估是否巨大？
Extra 是否有 Using temporary 或 Using filesort？

2. 验证索引是否有效：

你创建了一个索引，但 EXPLAIN 显示 key 还是 NULL？可能索引未被选中（如查询条件写法不匹配索引最左前缀、数据类型不匹配、函数操作列、OR 条件不当等）。
检查 possible_keys 是否包含了你期望的索引？key 是否选择了它？

3. 优化 JOIN 查询：

查看每个表的访问类型 (type)。
查看 rows 预估，驱动表（通常是 rows 小的表）选择是否合理？
检查连接条件 (ON / USING) 上是否有合适的索引 (ref 列)。
是否出现了 Using join buffer？可能需要优化索引或查询。

4. 优化子查询：

查看子查询的类型 (select_type) 和访问方式 (type)。
考虑是否可重写为 JOIN（有时性能更好）。

5. 理解复杂查询的执行流程：通过 id 和 select_type 理清执行顺序和逻辑。

五、实战优化案例

场景：查询 orders 表中 2023 年来自 'London' 客户的订单，并按日期排序。

原始查询 (可能慢)：

SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.city = 'London' AND o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY o.order_date DESC;

EXPLAIN 结果 (可能的问题)：

customers 表 type = ALL (全表扫描找 'London' 客户)
orders 表 type = ref (用 customer_id 索引)，但 rows 可能很多。
Extra 可能包含 Using filesort (在 orders 表很大时排序代价高)

优化步骤：

1. 为 customers(city) 添加索引: 避免全表扫描。

2. 为 orders(order_date) 添加索引？可能不够，因为 WHERE 条件主要是 customer_id 和日期范围。考虑复合索引 (customer_id, order_date)：

先通过 customer_id 快速定位到该客户的订单。
在该客户的订单中，索引已按 order_date 排序，可以高效进行范围查找 (BETWEEN) 并且按 order_date 排序 (ORDER BY order_date DESC)，可能避免 filesort。如果查询只选择索引包含的列，还能实现覆盖索引 (Using index)。

3. 修改后 EXPLAIN 验证：