MySQL Explain详解：深入理解查询执行计划

2025-3-4

MySQL Explain 详解：深入理解查询执行计划

在数据库性能优化中，理解查询语句的执行方式至关重要。MySQL 提供了 EXPLAIN 语句，它能让我们洞悉 MySQL 查询优化器是如何处理 SQL 语句的。EXPLAIN 不会实际执行查询，而是返回一个描述查询执行计划的报告，其中包含了查询将如何访问表、使用哪些索引、连接表的顺序等信息。掌握 EXPLAIN 的使用，就如同拥有了数据库查询的“X 光透视”能力，能帮助我们发现潜在的性能瓶颈，并进行针对性的优化。

1. EXPLAIN 的基本用法

使用 EXPLAIN 非常简单，只需要在你的 SELECT 查询语句前加上 EXPLAIN 关键字即可。例如：

sql EXPLAIN SELECT * FROM employees WHERE department_id = 10;

执行这条语句后，MySQL 会返回一个表格，其中包含了多个列，每个列都提供了关于查询执行计划的不同方面的信息。

2. EXPLAIN 输出列详解

EXPLAIN 的输出结果包含多个列，理解这些列的含义是解读执行计划的关键。下面详细介绍各个列的含义：

2.1 id

id 列表示 SELECT 查询的标识符。如果查询包含子查询，则每个子查询都会有一个唯一的 id。id 的值有以下几种情况：

数字: 表示查询的执行顺序。id 值越大，执行优先级越高；id 值相同，则按照从上到下的顺序执行。
NULL: 表示这一行是对其他查询结果的合并操作（如 UNION），或者是一个派生表（Derived Table）的结果。

2.2 select_type

select_type 列表示查询的类型，它反映了查询的复杂程度。常见的 select_type 值有：

SIMPLE: 简单查询，不包含子查询或 UNION。
PRIMARY: 最外层的查询，通常是包含子查询或 UNION 的查询语句的最外层。
SUBQUERY: 子查询，即 SELECT 或 WHERE 列表中包含的子查询。
DEPENDENT SUBQUERY: 依赖外部查询的子查询，子查询的执行依赖于外部查询的结果。
UNION: UNION 操作中的第二个或后续的 SELECT 查询。
DEPENDENT UNION: 依赖外部查询的 UNION 操作中的第二个或后续的 SELECT 查询。
UNION RESULT: UNION 操作的结果。
DERIVED: 派生表，即 FROM 子句中的子查询。
MATERIALIZED: 物化子查询, MySQL 将子查询结果存储为临时表。
UNCACHEABLE SUBQUERY:不能被缓存的子查询，每次都需要重新计算。
UNCACHEABLE UNION: 属于UNION的查询，不能被缓存.

2.3 table

table 列表示查询涉及的表名。如果查询使用了别名，这里会显示别名。此外，还可能出现以下特殊值：

<derivedN>: 表示使用派生表，其中 N 是对应的 id 值。
<unionM,N>: 表示使用 UNION 操作的结果，其中 M 和 N 是对应的 id 值。
<subqueryN>：表示物化子查询。

2.4 partitions

partitions 列表示查询涉及的分区。如果表没有分区，则该列的值为 NULL。

2.5 type

type 列表示 MySQL 访问表的方式，这是评估查询性能非常重要的一个指标。type 的值有很多，按照从最佳到最差的顺序，常见的 type 值如下：

system: 表只有一行记录（系统表），这是 const 类型的特例，通常不会出现。
const: 通过索引一次就找到了，通常用于主键或唯一索引的等值查询。
eq_ref: 唯一性索引扫描，对于每个与前一个表关联的行，该表中只有一行被读取。常用于使用主键或唯一索引进行连接的情况。
ref: 非唯一性索引扫描，返回匹配某个单独值的所有行。
fulltext: 使用全文索引。
ref_or_null: 类似于 ref，但 MySQL 还额外搜索包含 NULL 值的行。
index_merge: 使用了索引合并优化，表示查询使用了多个索引。
unique_subquery: 类似于 eq_ref，但用于 IN 子查询中的唯一索引。
index_subquery: 类似于 ref，但用于 IN 子查询中的非唯一索引。
range: 使用索引进行范围扫描，通常出现在 WHERE 子句中的范围条件（如 BETWEEN、>、<、IN 等）。
index: 全索引扫描，MySQL 遍历整个索引树来查找匹配的行。
ALL: 全表扫描，MySQL 遍历整个数据表来查找匹配的行，这是最差的情况，通常需要优化。

2.6 possible_keys

possible_keys 列表示 MySQL 可能使用的索引。如果该列为 NULL，则表示没有可用的索引。

2.7 key

key 列表示 MySQL 实际使用的索引。如果该列为 NULL，则表示 MySQL 没有使用索引。如果 key 列的值出现在 possible_keys 列中，则表示 MySQL 选择了其中一个索引来执行查询。

2.8 key_len

key_len 列表示 MySQL 使用的索引的长度（字节数）。对于多列索引，key_len 可以帮助我们判断 MySQL 实际使用了索引的哪些列。

2.9 ref

ref 列表示与索引进行比较的值。可能的值有：

const: 表示与索引进行比较的是一个常量值。
列名: 表示与索引进行比较的是另一个表的列。
func:表示一个函数的结果。
NULL: 表示没有值用于比较。

2.10 rows

rows 列表示 MySQL 估计需要扫描的行数。这个值是一个估计值，并不一定准确。rows 值越小，通常表示查询的效率越高。

2.11 filtered

filtered列表示返回结果的行数占需读取行数的百分比。它的值是一个百分比, 范围从0到100。例如，如果rows为1000，filtered为50.00 (50%)，则表示只有500行符合条件。这个指标在表连接时非常重要，可以帮助评估连接的效率。

2.12 Extra

Extra 列包含了一些额外的、非常重要的信息，可以帮助我们进一步了解查询的执行情况。常见的 Extra 值有：

Using index: 表示查询使用了覆盖索引（Covering Index），即 MySQL 可以直接从索引中获取所需的数据，而不需要访问数据表。这是非常高效的查询方式。
Using where: 表示 MySQL 在存储引擎检索行后，还需要进行额外的过滤操作。
Using temporary: 表示 MySQL 需要使用临时表来存储中间结果，通常出现在包含 ORDER BY 和 GROUP BY 的查询中。
Using filesort: 表示 MySQL 需要使用文件排序（外部排序）来对结果进行排序，而不是使用索引排序。这通常是一个需要优化的信号。
Using join buffer: 表示 MySQL 使用了连接缓冲区来加速连接操作。
Impossible WHERE: 表示 WHERE 子句的条件永远为假，导致查询无法返回任何行。
Select tables optimized away: 表示MySQL优化器已经从查询中删除了表，因为它可以确定该表不包含任何相关数据。
Distinct: MySQL在找到第一个匹配行之后停止搜索更多行。
Not exists: MySQL能够对查询进行LEFT JOIN优化，并且在找到与LEFT JOIN条件匹配的一行后，不会再为前面的行组合在该表上检查更多的行。
Range checked for each record (index map: N): 表示没有找到好的索引可以使用,对前面表中的每一个行组合, MySQL检查是否可以使用range或index_merge访问方法来检索行。
Using index condition: 表示使用索引条件下推（Index Condition Pushdown）优化。
Using index for group-by:类似于Using index, 表示可以利用索引完成group by 操作。
LooseScan:表示使用了半连接优化中的LooseScan策略
Start temporary, End temporary: 表示使用了半连接优化中的Duplicate Weedout策略
FirstMatch(table_name): 表示使用半连接优化中的 FirstMatch 策略.

3. 案例分析

下面通过几个案例来演示如何使用 EXPLAIN 分析查询执行计划。

3.1 案例 1：全表扫描

sql EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

假设 employees 表的 last_name 列没有索引，执行上述查询的 EXPLAIN 输出可能如下：

分析：

type: ALL 表示全表扫描。
possible_keys: NULL 表示没有可用的索引。
key: NULL 表示没有使用索引。
rows: 100 表示 MySQL 估计需要扫描 100 行。
filtered: 10.00 表示预估只有10%的行满足条件。
Extra: Using where 表示 MySQL 需要在存储引擎检索行后进行过滤。

这个查询的性能很差，因为 MySQL 需要扫描整个 employees 表来查找 last_name 为 'Smith' 的行。优化方法是在 last_name 列上创建索引。

3.2 案例 2：索引扫描

在 last_name 列上创建索引：

sql CREATE INDEX idx_lastname ON employees (last_name);

再次执行相同的查询：

sql EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

EXPLAIN 输出可能如下：

+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | employees | NULL | ref | idx_lastname | idx_lastname | 768 | const | 1 | 100.00 | | +----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+

分析：

type: ref 表示非唯一性索引扫描。
possible_keys: idx_lastname 表示可能使用的索引。
key: idx_lastname 表示实际使用的索引。
key_len: 768 表示索引的长度。
ref: const 表示与索引进行比较的是一个常量值。
rows: 1 表示 MySQL 估计只需要扫描 1 行。
filtered: 100.00 表示预估所有扫描的行都满足条件。
Extra: 空白表示没有额外的信息。

这个查询的性能得到了显著提升，因为 MySQL 可以直接使用索引来定位 last_name 为 'Smith' 的行，而不需要扫描整个表。

3.3 案例 3：覆盖索引

sql EXPLAIN SELECT last_name FROM employees WHERE last_name = 'Smith';

EXPLAIN 输出可能如下：

+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ref | idx_lastname | idx_lastname | 768 | const | 1 | 100.0 | Using index | +----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+

分析：

Extra: Using index 表示使用了覆盖索引。

这个查询的性能非常高，因为 MySQL 可以直接从索引中获取 last_name 列的值，而不需要访问数据表。

3.4 案例4：多表连接

sql EXPLAIN SELECT e.first_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;

假设employees表的department_id列和departments表的department_id列都有索引，EXPLAIN输出如下：

优化建议：尽管departments表在department_id上有主键，但在这个例子中，优化器可能选择不使用它，尤其是当departments表很小的时候。可以尝试使用FORCE INDEX(PRIMARY)来强制使用主键，看看是否能提高性能。对employees表的department_id列的索引被有效利用，这是好的。

4. 执行计划的局限性

尽管 EXPLAIN 是一个强大的工具，但它也有一些局限性：

估计值: rows 列的值是一个估计值，并不一定准确。MySQL 的统计信息可能过时，导致估计值与实际值存在偏差。
不考虑缓存: EXPLAIN 不会考虑查询缓存的影响。如果查询结果被缓存，实际执行时间会比 EXPLAIN 预测的要短。
不考虑并发: EXPLAIN 只显示单个查询的执行计划，不考虑并发执行的影响。在并发环境下，查询的性能可能会受到其他查询的影响。
不执行查询: EXPLAIN 只是分析查询的执行计划, 并不真正执行查询, 所以无法获得查询实际运行的开销信息 (例如，实际的 I/O 操作次数、CPU 时间等)。

5. 进阶技巧和最佳实践

关注 type 列: 优先优化 type 为 ALL 的查询，尽量将 type 提升到 ref 或更优的级别。
关注 rows 列: rows 值越大，通常表示查询的效率越低，需要重点关注。
利用 Extra 列: Extra 列提供了很多有用的信息，如是否使用了索引、是否需要排序、是否使用了临时表等，可以帮助我们发现潜在的性能问题。
创建合适的索引: 索引是提高查询性能的关键。根据查询的特点，创建合适的索引，包括单列索引、多列索引、全文索引等。
优化查询语句: 避免使用 SELECT *，只选择需要的列。尽量使用简单的查询，避免复杂的子查询和 JOIN 操作。
定期更新统计信息: 使用 ANALYZE TABLE 命令定期更新表的统计信息，确保 MySQL 优化器能够做出正确的决策。
结合其他工具: EXPLAIN 可以与其他性能分析工具（如 SHOW PROFILE、Performance Schema）结合使用，更全面地了解查询的性能瓶颈。
使用 EXPLAIN EXTENDED: EXPLAIN EXTENDED 提供更详细的输出, 之后可以使用SHOW WARNINGS;查看优化器改写后的查询语句.

6. 火眼金睛辨真伪

EXPLAIN 提供的执行计划是 MySQL 查询优化器的“杰作”，但它并非总是完美无缺。有时，优化器可能会做出错误的决策，导致查询性能下降。因此，我们需要具备一双“火眼金睛”，能够识别出执行计划中的潜在问题，并进行手动干预。

例如，如果发现 possible_keys 列有多个索引，而 key 列选择了其中一个非最优的索引，我们可以使用 FORCE INDEX 或 USE INDEX 提示来强制 MySQL 使用我们指定的索引。

又如，如果发现 type 列为 index，而实际上我们期望的是 range 或 ref，我们可以尝试调整查询条件或索引，以引导优化器选择更优的访问方式。

总结

EXPLAIN 是 MySQL 性能优化中不可或缺的工具。通过深入理解 EXPLAIN 的输出结果，我们可以洞悉查询的执行过程，发现潜在的性能瓶颈，并进行针对性的优化。掌握 EXPLAIN 的使用，就如同掌握了数据库查询的“命门”，能够让我们在海量数据中快速定位问题，提升查询效率，构建高性能的数据库应用。然而，EXPLAIN 只是工具，真正的优化还需要结合业务场景、数据特点和数据库原理进行综合分析和调优。

作者：admin

链接：https://hostlocvps.com/2025/03/04/mysql-explain%e8%af%a6%e8%a7%a3%ef%bc%9a%e6%b7%b1%e5%85%a5%e7%90%86%e8%a7%a3%e6%9f%a5%e8%af%a2%e6%89%a7%e8%a1%8c%e8%ae%a1%e5%88%92/

文章版权归作者所有，未经允许请勿转载。

THE END