使用EXPLAIN命令深度解析MySQL查询执行计划

betcha

发布时间：2025-09-09 12:01:01

736人浏览过

来源于php中文网

原创

EXPLAIN是MySQL查询优化的核心工具，通过分析执行计划中的type、key、rows、Extra等字段，可识别全表扫描、索引失效、排序与临时表等性能瓶颈，进而指导索引优化、SQL重写和配置调整，实现查询性能的显著提升。

使用explain命令深度解析mysql查询执行计划

EXPLAIN命令是MySQL查询优化的核心工具，它能帮我们揭示SQL语句在数据库内部是如何被执行的，从而找到性能瓶颈。如果你想让你的MySQL查询飞起来，理解并善用EXPLAIN是绕不过去的一步。它就像一个X光机，能透视查询的骨架，让我们看清哪些地方出了问题，哪些地方还有优化的空间。

要使用EXPLAIN命令，很简单，只需在你的

SELECT

语句前加上

EXPLAIN

关键字即可。例如：

EXPLAIN SELECT * FROM users WHERE age > 25 AND city = 'New York';

执行后，MySQL会返回一张表格，这张表格的每一行代表查询执行计划中的一个操作，而每一列则提供了关于这个操作的详细信息。

我个人觉得，初次接触EXPLAIN时，这张表格可能会让人有点望而却步，因为它包含了

id

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

等十几个字段。但别担心，我们不需要一次性掌握所有细节，有些字段在日常优化中更为关键。最核心的，是理解这些字段如何共同描绘出一个查询的“生命周期”，以及它们如何指向潜在的性能问题。

深入理解EXPLAIN的输出字段：不仅仅是看个热闹

当我拿到一个EXPLAIN结果时，我的目光通常会先落在几个关键字段上，它们往往能快速暴露问题。

type

：访问类型，这是我最关注的。

type

字段描述了MySQL如何查找表中的行。它的值从最好到最差大致是：

```
system
```
,
```
const
```
,
```
eq_ref
```
,
```
ref
```
：这些都是非常高效的访问类型，通常意味着使用了索引，并且能快速定位到少量行。看到它们，我通常会松一口气。
```
range
```
：表示通过索引扫描一个范围。比如
```
WHERE id BETWEEN 10 AND 100
```
。虽然比全表扫描好，但范围越大，效率越低。
```
index
```
：全索引扫描。虽然避免了全表扫描，但仍然需要读取整个索引。如果索引很大，这也会很慢。
```
ALL
```
：最糟糕的，全表扫描。这意味着MySQL不得不遍历表中的每一行来找到匹配的记录。如果我看到
```
ALL
```
，那基本上可以确定这是一个性能瓶颈的起点，需要立即着手优化索引。

key

和
key_len
：索引的使用情况。

key

字段显示了MySQL实际决定使用的索引。如果这里是

NULL

，那基本可以确定没有使用索引，或者说MySQL认为不使用索引更划算（虽然这种情况很少）。

key_len

则表示MySQL在索引中使用的字节长度，它能间接告诉我们索引的哪一部分被使用了。比如，一个复合索引

(col1, col2, col3)

，如果

key_len

只反映了

col1

的长度，那说明只有

col1

被用于索引查找，后面的

col2

col3

可能就没有发挥作用，这通常是索引失效的信号。

HTTPie AI

AI API开发工具

下载

rows

和
filtered
：成本估算的直观体现。

rows

字段是MySQL估计为了找到所需行而必须读取的行数。这个数字越小越好。

filtered

字段则表示经过WHERE条件过滤后，剩余的行数占

rows

的百分比。一个低的

filtered

值意味着MySQL读取了很多行，但大部分都被WHERE条件排除了，这通常暗示着索引不够精确，或者WHERE条件没有充分利用索引。我经常会把

rows

和

filtered

结合起来看，如果

rows

很大而

filtered

很小，那我的心头就会一紧，知道这地方肯定有优化空间。

Extra

：额外信息，往往是优化的突破口。 这个字段提供了很多有价值的提示，比如：

```
Using filesort
```
：表示MySQL需要对结果进行外部排序，通常发生在
```
ORDER BY
```
或
```
GROUP BY
```
的列上没有合适的索引时。这非常耗时，是我最不想看到的一个。
```
Using temporary
```
：表示MySQL需要创建一个临时表来处理查询，通常发生在复杂的
```
GROUP BY
```
、
```
DISTINCT
```
或
```
UNION
```
操作中。这也会严重影响性能。
```
Using index
```
：这是个好消息，表示查询所需的所有数据都可以在索引中找到，无需回表查询，效率很高。
```
Using where
```
：表示MySQL需要通过WHERE条件过滤数据。如果
```
type
```
是
```
ALL
```
，同时有
```
Using where
```
，那更是雪上加霜。
```
Block Nested Loop
```
/
```
Batched Key Access
```
：这些是连接算法，特别是
```
Block Nested Loop
```
，在处理大数据量连接时效率较低，通常需要优化JOIN条件或增加索引。

常见查询优化陷阱与EXPLAIN的揭示

在我的实际工作中，EXPLAIN经常能帮助我发现一些看似不起眼，实则影响巨大的性能陷阱。

全表扫描（

type: ALL

）的噩梦： 这是最常见的问题。一个简单的

SELECT * FROM large_table WHERE non_indexed_column = 'value'

就可能导致

ALL

。看到

ALL

，我的第一反应就是检查

WHERE

条件中的列是否有索引，或者是否可以通过创建复合索引来覆盖查询。有时候，即使有索引，如果查询条件使用了

LIKE '%value%'

这种左模糊匹配，索引也可能失效，EXPLAIN同样会显示

ALL

。这让我意识到，索引不是万能的，使用方式同样重要。

Using filesort

和
Using temporary
：这两个是性能杀手。比如，

SELECT * FROM orders ORDER BY order_date DESC LIMIT 100;

如果

order_date

上没有索引，或者索引不符合排序方向，就可能出现

Using filesort

。我曾经遇到过一个非常复杂的报表查询，因为它包含了多个

GROUP BY

和

DISTINCT

，导致

Using temporary

和

Using filesort

同时出现，查询时间从几秒飙升到几十秒。当时的解决方案是重构查询逻辑，并针对性地添加了复合索引，将

Extra

字段的这些“刺眼”提示消除了。

子查询的性能陷阱： 虽然SQL语法上子查询很方便，但在某些情况下，它们可能导致性能问题。EXPLAIN可以帮助我们识别子查询是否被优化为

MATERIALIZED

（物化）或者

DEPENDENT SUBQUERY

（依赖子查询）。依赖子查询通常效率较低，因为它会对外层查询的每一行执行一次。我个人倾向于将可以改写成

JOIN

的子查询都改写成

JOIN

，因为JOIN通常能更好地利用索引，并且EXPLAIN的结果也更容易分析和优化。

隐式类型转换： 这是一个非常隐蔽的陷阱。比如，

WHERE string_column = 123

，如果

string_column

是字符串类型，但你用数字进行比较，MySQL可能会进行隐式类型转换，这会导致索引失效。EXPLAIN在这种情况下可能会显示

ALL

或者

range

，但

key

字段会是

NULL

或者不理想的索引。这需要我们仔细检查查询条件的数据类型是否与列的数据类型一致。

如何结合EXPLAIN与实际业务场景进行迭代优化

EXPLAIN不是一次性的魔法，它是一个持续迭代的工具。我的优化过程通常是这样的：

识别问题查询： 通常通过慢查询日志（slow query log）或者应用监控系统发现。
EXPLAIN分析： 对问题查询执行EXPLAIN，仔细分析输出，找出
```
type
```
、
```
rows
```
、
```
filtered
```
、
```
Extra
```
等字段中的“红旗”。
制定优化策略： 根据EXPLAIN的分析结果，思考可能的优化方案。这可能包括：
- 添加/修改索引： 这是最常见的手段。我会考虑创建单列索引、复合索引，或者调整现有索引的列顺序。比如，如果
```
WHERE a = ? AND b = ?
```
  ，那么
```
INDEX(a, b)
```
  可能比
```
INDEX(b, a)
```
  更优，这需要EXPLAIN来验证。
- 重写SQL： 比如将子查询改为JOIN，优化
```
WHERE
```
  条件顺序，避免使用
```
OR
```
  ，或者使用
```
UNION ALL
```
  代替
```
UNION
```
  。
- 调整表结构： 比如适当的冗余字段（反范式化）以避免复杂的JOIN，或者对大表进行分区。
- 调整MySQL配置： 比如缓冲区大小、排序缓冲区大小等，但这通常是最后一步，且需要谨慎。
实施优化并验证： 在测试环境中实施优化，然后再次使用EXPLAIN进行验证。看看
```
type
```
是否改善了，
```
rows
```
和
```
filtered
```
是否显著下降，
```
Extra
```
字段中的
```
Using filesort
```
或
```
Using temporary
```
是否消失了。
上线监控： 优化后的查询上线后，持续监控其性能，确保优化是有效的，并且没有引入新的问题。