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

MySQL执行计划Explain如何分析 SQL语句？

article 2025/2/22 16:05:38

前言

之前写过相关的执行计划分析语句，但是内容是五年前写的，马马虎虎的，观感极差。所以这篇是弥补五年前的马虎而做。

首先，执行计划的用法是在查找语句前使用EXPLAIN 关键字，执行完以后会返回一个表格，其中表格的字段有id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra等。每个列都有不同的含义，需要逐一理解。

接下来，我们需要逐个了解这些列的作用：

id：表示查询中执行select子句或操作表的顺序。id相同，执行顺序由上至下；id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。我之前看到过复杂的查询中有多个id，这时候需要根据id的顺序来判断执行顺序。
select_type：表示查询的类型，比如是简单查询还是子查询，或者是联合查询等。常见的类型有SIMPLE（简单查询，不包含子查询或UNION）、PRIMARY（主查询，包含子查询的外层查询）、SUBQUERY（子查询）、DERIVED（派生表，FROM子句中的子查询）、UNION（UNION中的第二个或后面的查询）、UNION RESULT（UNION的结果）等。这个列帮助我了解查询的结构。
table：显示这一步访问的是哪个表，有时候会是派生表或者联合查询的临时表。
partitions：如果表有分区，这里显示查询涉及的分区。对于没有分区的表，这一列通常是NULL。
type：这是比较重要的列，表示MySQL在表中找到所需行的方式，也就是访问类型。常见的类型有：

system：表只有一行记录（等于系统表），这是const类型的特例。
const：通过索引一次就找到了，用于比较主键或唯一索引的等值查询。
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。
ref：非唯一性索引扫描，返回匹配某个单独值的所有行。
range：只检索给定范围的行，使用一个索引来选择行，比如BETWEEN、IN、>、<等。
index：全索引扫描，遍历整个索引来查找匹配的行。
ALL：全表扫描，效率最低。

我们需要根据type的值来判断查询是否高效，比如如果type是ALL，可能需要添加索引。

possible_keys：显示可能应用在这张表中的索引。如果为空，表示没有可能的索引。
key：实际使用的索引。如果为NULL，则表示没有使用索引。查询中若使用了覆盖索引（即查询的列都在索引中），则只出现在key列中。
key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
rows：MySQL估计要找到所需的行需要扫描的行数。这个值越小越好。
filtered：表示返回结果的行数占需读取行数的百分比。值越大越好。
Extra：包含不适合在其他列中显示的额外信息。常见的有：

Using where：表示使用了WHERE过滤。
Using index：表示使用了覆盖索引，避免访问表的数据行。
Using temporary：表示需要使用临时表来存储结果集，常见于排序和分组查询。
Using filesort：MySQL会对结果使用一个外部索引排序，而不是按索引次序读取表。这种情况下需要考虑优化。
Using join buffer：表示使用了连接缓存，可能需要增加join_buffer_size的值。
Impossible WHERE：WHERE子句的值总是false，无法获取任何数据。

现在，我们将这些知识点整合起来，通过一个具体的例子来练习分析。例如，假设有一个用户表users，包含id（主键）、name、age、email等字段，并且age字段有一个索引。

如果我执行：


EXPLAIN SELECT * FROM users WHERE age > 30 ORDER BY name;

执行后的EXPLAIN结果可能如下：

|----|-------------|-------|------|---------------|------|---------|------|------|----------|----------------|

| 1 | SIMPLE | users | range| age | age | 5 | NULL | 100 | 100.00 | Using where; Using filesort |

从结果中可以看出：

type是range，表示使用了范围查询，索引有效。
possible_keys和key都是age，说明实际使用了age索引。
rows是100，估计扫描了100行。
Extra中有Using filesort，说明在排序时没有使用索引，导致额外排序，可能需要优化。

这时候，可能需要为name字段添加索引或者调整查询语句，避免文件排序。

但是，如果type是ALL，说明没有使用索引，全表扫描，这时候需要考虑为条件字段添加索引。例如，如果查询中没有使用索引字段作为条件，或者索引失效（比如使用了函数或类型转换），就会导致全表扫描。

另外，对于联合索引，需要注意最左前缀原则。比如，有一个联合索引（age, name），如果查询条件只用了name，那么索引可能不会被使用。

在分析过程中，还需要注意rows和filtered的结合，可以估计查询的效率。如果rows很大，filtered很小，说明索引选择性不高，可能需要调整索引。

另外，Extra列中的信息非常重要，特别是Using temporary和Using filesort，这些都是需要优化的信号。

总结来说，分析EXPLAIN的输出需要结合各个列的信息，判断索引的使用情况，访问类型，以及是否存在额外的排序或临时表等操作，从而找到优化查询的方法。

在实际操作中，可能会遇到更复杂的情况，比如子查询、JOIN操作等，这时候需要理解每个步骤的执行顺序和访问方式。例如，对于JOIN查询，需要查看每个表的访问类型和索引使用情况，以及JOIN类型（如ref、eq_ref等）。

另外，还需要注意，EXPLAIN的输出是基于统计信息的估算，实际执行可能会有差异，但通常可以作为优化的重要参考。

最后，通过多次实践和查阅文档，可以逐步提高对EXPLAIN输出的分析能力，从而更有效地优化SQL查询性能。

以下是分析MySQL执行计划EXPLAIN的步骤及关键点，以帮助您优化SQL查询性能：

1. 基本用法

在SQL语句前添加EXPLAIN或EXPLAIN FORMAT=JSON（详细版）获取执行计划：

EXPLAIN SELECT * FROM users WHERE age > 30;

2. 核心字段解析

列名	说明	优化关注点
id	查询的序列号，表示执行顺序（id越大越先执行）	子查询或复杂JOIN的执行顺序
select_type	查询类型（如SIMPLE、PRIMARY、SUBQUERY、DERIVED等）	识别复杂查询结构
table	访问的表名或别名	确认操作的表
type	访问类型（性能关键！常见值：const > eq_ref > ref > range > index > ALL）	避免`ALL`（全表扫描），尽量达到`ref`及以上
possible_keys	可能使用的索引	检查是否遗漏有效索引
key	实际使用的索引	确认索引是否生效
rows	预估扫描的行数	数值越小越好
Extra	额外信息（如`Using filesort`、`Using temporary`等）	发现潜在性能问题

3. 关键字段深度解析

(1) type（访问类型）

const/system：通过主键或唯一索引直接定位单行，效率最高。
eq_ref：JOIN时使用主键或唯一索引关联（如PRIMARY KEY或UNIQUE NOT NULL）。
ref：使用非唯一索引查找，返回匹配的多个行。
range：索引范围扫描（如BETWEEN、>）。
index：全索引扫描（遍历索引树）。
ALL：全表扫描，需优化（添加索引或调整查询）。

(2) Extra（额外信息）

Using where：WHERE条件过滤了部分数据。
Using index：覆盖索引（查询列全在索引中），无需回表。
Using filesort：需额外排序，考虑添加索引优化ORDER BY。
Using temporary：使用临时表（如GROUP BY无索引），需优化。
Select tables optimized away：优化器已优化（如使用MIN/MAX索引）。

4. 分析示例

示例查询：

EXPLAIN SELECT * FROM orders 
WHERE user_id = 100 
ORDER BY create_time DESC;

执行计划结果：

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	orders	ref	idx_user	idx_user	50	Using filesort

分析：

type=ref：使用了idx_user索引，但可能返回多行。
Extra=Using filesort：排序未走索引，需为(user_id, create_time)创建联合索引。

优化方案：

ALTER TABLE orders ADD INDEX idx_user_create_time (user_id, create_time);

5. 优化策略

索引优化：
- 确保WHERE、JOIN、ORDER BY、GROUP BY涉及的列有索引。
- 使用覆盖索引（查询列包含在索引中）。
- 避免冗余索引，遵循最左前缀原则。
避免全表扫描：
- 检查type是否为ALL，若无索引则添加。
减少文件排序和临时表：
- 为ORDER BY/GROUP BY字段添加索引。
- 简化查询，减少子查询或复杂JOIN。
控制扫描行数：
- 通过rows字段评估，若值过大需优化查询条件或索引。