掌握MySQL执行计划分析优化SQL查询性能的实用技巧-小浪学习网

explain是sql性能优化的基石，因为它能揭示查询执行的内部细节，通过分析type字段可判断访问方法的效率，如all为全表扫描需避免，ref或eq_ref则较优；extra字段中的using filesort和using temporary提示存在排序或临时表性能瓶颈，而using index表示索引覆盖是理想状态，结合key、rows等信息可精准定位问题并优化索引设计，使sql优化从经验驱动变为数据驱动的科学过程。

掌握MySQL执行计划分析优化SQL查询性能的实用技巧

mysql的执行计划分析，简单来说，就是通过

EXPLaiN

命令，让你像X光一样看透一条sql语句在数据库内部是怎么跑的。它会告诉你这条查询用了哪个索引、扫描了多少行数据、是否需要排序或创建临时表，从而帮助你精准定位性能瓶颈，把慢查询变成快查询。这玩意儿，是优化SQL绕不过去的一道坎。

EXPLAIN

命令的使用非常直接，你只需要在任何

select

、

INSERT

、

UPDATE

或

delete

语句前加上

EXPLAIN

关键字即可。比如：

EXPLAIN SELECT * FROM users WHERE id = 1;

它会返回一个表格，里面包含了许多列，每一列都提供了关于查询执行过程的关键信息。我们最常关注的几个核心字段包括：

id

: 查询中每个
```
SELECT
```
子句的标识符。
select_type

: 查询的类型，比如
```
SIMPLE
```
（简单查询）、
```
PRIMARY
```
（主查询）、
```
SUBQUERY
```
（子查询）、
```
union
```
（联合查询中的第二个或后续查询）等。
table

: 查询涉及的表名。
type

: 这是最重要的字段之一，表示MySQL如何找到行。它的值从最差到最好依次是：
ALL
（全表扫描）、
index
（全索引扫描）、
```
range
```
（索引范围扫描）、
ref
（非唯一索引查找）、
```
eq_ref
```
（唯一索引查找）、
```
const
```
/
```
system
```
（单行查找，非常快）。
possible_keys

: MySQL在执行查询时可能选择的索引。
key

: MySQL实际选择使用的索引。如果这里是
```
NULL
```
，那基本就是没用上索引。
key_len

: MySQL实际使用的索引的长度。
ref

: 指示哪些列或常量被用于查找索引列上的值。
rows

: MySQL估计要扫描的行数。这个值越小越好。
filtered

: MySQL估计通过表条件过滤出的行百分比。越高越好。
Extra

: 另一个非常重要的字段，包含了额外的信息，比如是否使用了临时表、是否需要排序、是否使用了索引覆盖等。

理解这些字段的含义，是读懂执行计划，进而优化SQL的钥匙。

为什么EXPLAIN是SQL性能优化的基石？

我刚开始接触sql优化那会儿，也走了不少弯路，总是凭感觉去加索引，或者改写一些自认为“聪明”的SQL。结果呢，很多时候不仅没效果，反而可能适得其反。直到我真正开始用

EXPLAIN

，才发现它简直是性能优化的“透视眼”。

为什么它是基石？因为它把那些平时你看不到的、数据库内部的“黑箱操作”给亮出来了。比如，你可能觉得一个简单的

SELECT * FROM orders WHERE status = 'completed'

应该很快，但

EXPLAIN

一跑，如果

type

是

ALL

，

rows

几百万，

Extra

里还有个

Using filesort

，你立马就明白了：哦，原来数据库在全表扫描，而且扫描完了还得排序！这下，优化方向就清晰了——加索引，并且让索引能够覆盖到排序的字段。

没有

EXPLAIN

，你的优化工作就像在黑暗中摸索，可能永远也抓不住真正的痛点。它能告诉你：你的索引是不是真的被用上了？是不是用了错误的索引？查询是不是扫描了太多不必要的行？有没有产生昂贵的临时表或文件排序？这些问题，只有

EXPLAIN

能给出明确的答案。它让优化从玄学变成了科学。

如何解读EXPLAIN输出中的关键字段，特别是’type’？

type

字段是

EXPLAIN

输出里最能直观反映查询效率的指标。它描述了MySQL如何从表中找到所需的行。我们来详细拆解一下它的常见值，从最差到最优：

ALL

(全表扫描)：这是最糟糕的情况。意味着MySQL必须遍历整个表来找到匹配的行。当你的表很大时，这几乎必然导致查询慢如蜗牛。通常发生在没有索引，或者where条件中索引失效的情况下。
- 例子：
```
EXPLAIN SELECT * FROM products WHERE description LIKE '%apple%';
```
  (如果
```
description
```
  上没有全文索引，或者用了
```
%
```
  开头的模糊匹配，很可能就是
  ALL
  )
index

(全索引扫描)：比
ALL
好一点，但也好不到哪去。MySQL遍历整个索引来查找匹配的行。虽然比全表扫描快，因为它只读取索引数据，但如果索引很大，性能依然堪忧。通常发生在查询只涉及索引列，但索引无法缩小查找范围时。
- 例子：
```
EXPLAIN SELECT id, name FROM users ORDER BY created_at;
```
  (如果
```
created_at
```
  上有索引，但查询需要扫描整个索引来满足排序)
range

(索引范围扫描)：这是一个不错的
type
。表示MySQL通过索引进行范围查找，例如
```
WHERE id > 100 AND id < 200
```
，或者
```
WHERE status IN ('active', 'pending')
```
。它利用了索引的有序性，只扫描索引的一部分。
- 例子：
```
EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';
```
  (如果
```
order_date
```
  上有索引)
ref

(非唯一索引查找)：非常好的
type
。表示MySQL通过非唯一索引（或唯一索引的非唯一前缀）查找匹配的行。它会找到所有符合条件的行。
- 例子：
```
EXPLAIN SELECT * FROM users WHERE status = 'active';
```
  (如果
```
status
```
  上是非唯一索引)
eq_ref

(唯一索引查找)：优秀的
type
。通常出现在多表连接时，当一个表通过主键或唯一索引关联到另一个表时。对于前一个表的每一行，MySQL只需要从当前表中读取一行。
- 例子：
```
EXPLAIN SELECT o.*, u.username FROM orders o JOIN users u ON o.user_id = u.id;
```
  (如果
```
u.id
```
  是主键或唯一索引)
const

/

system

(单行查找)：这是最理想的
type
，意味着MySQL可以直接通过主键或唯一索引找到一行数据，并且知道只有一行。效率极高。
```
system
```
是
```
const
```
的特例，当表只有一行时。
- 例子：
```
EXPLAIN SELECT * FROM products WHERE product_id = 123;
```
  (如果
```
product_id
```
  是主键)

当你看到

type

是

ALL

或

index

时，通常就是优化工作开始的地方。你需要考虑是否能添加合适的索引，或者调整查询条件，让MySQL能够使用

range

、

ref

或更好的方式。同时，结合

rows

字段来看，如果

rows

很大而

type

又很差，那性能问题就非常明显了。

EXPLAIN输出中的’Extra’字段揭示了哪些潜在的性能问题？

Extra

字段，顾名思义，提供了额外的信息，这些信息往往是关于MySQL在执行查询时的一些“幕后操作”。很多时候，这些操作就是导致查询变慢的罪魁祸首。理解它们，能帮你找到那些隐形的性能杀手。

Using filesort

: 这是一个常见的性能陷阱。它表示MySQL需要对结果集进行外部排序，而不是通过索引的顺序直接获取。当
```
ORDER BY
```
子句中的列没有被索引覆盖，或者索引的顺序不符合排序要求时，MySQL就不得不把数据读取出来，然后在内存或磁盘上进行排序。这在数据量大时，会非常耗时。
- 优化思路：为
  ORDER BY
  涉及的列创建复合索引，或者确保
  ORDER BY
  的顺序与索引的顺序一致。
- 举例：
```
EXPLAIN SELECT * FROM users WHERE gender = 'male' ORDER BY age;
```
  如果
```
gender
```
  和
```
age
```
  没有合适的复合索引，很可能出现
  Using filesort
  。
Using temporary

: 同样是性能杀手。表示MySQL需要创建一个临时表来存储中间结果。这通常发生在
```
GROUP BY
```
、
```
DISTINCT
```
或
```
UNION
```
操作中，当这些操作的列没有被索引覆盖，或者查询逻辑比较复杂时。临时表可能在内存中，也可能在磁盘上，但无论哪种，都增加了额外的开销。
- 优化思路：尽量通过索引避免临时表，或者优化查询逻辑，减少对临时表的依赖。例如，对
```
GROUP BY
```
  的列建立索引。
- 举例：
```
EXPLAIN SELECT DISTINCT user_id FROM logs WHERE action = 'login';
```
  如果
```
user_id
```
  和
```
action
```
  没有合适的复合索引，可能导致
  Using temporary
  。
Using index

(索引覆盖)：这是非常理想的情况。表示查询所需的所有数据都可以从索引中直接获取，而无需访问实际的数据行。这被称为“索引覆盖查询”，效率极高，因为它避免了回表（从索引到数据行的查找）的开销。
- 优化思路：创建包含查询所需所有列的复合索引，即使这些列不用于
  WHERE
  条件。
- 举例：
```
EXPLAIN SELECT id, username FROM users WHERE status = 'active';
```
  如果在
```
(status, id, username)
```
  上创建了复合索引，并且查询只取这三列，就可能出现
```
Using index
```
  。
Using where

: 表示MySQL将根据
WHERE
子句中的条件过滤记录。这本身不是坏事，但如果与
ALL
或
index
类型的查询结合，就意味着MySQL扫描了大量不相关的行，然后再进行过滤，效率会很低。
- 优化思路：确保
  WHERE
  条件能充分利用索引，减少扫描的行数。
Using index condition

(索引条件下推ICP)：这是MySQL 5.6版本引入的优化。表示MySQL在存储引擎层（而不是服务器层）对索引中的数据进行过滤。它可以在回表之前，先根据索引条件过滤掉不符合要求的数据，减少回表次数。
- 优化思路：利用好复合索引，让
  WHERE
  条件能够被ICP优化。
- 举例：
```
EXPLAIN SELECT * FROM users WHERE name LIKE 'A%' AND age > 20;
```
  如果在
```
(name, age)
```
  上有复合索引，ICP可能会在读取完整行之前，先利用索引过滤掉不符合
```
age > 20
```
  的行。