漫舒听雨

Appearance

一、EXPLAIN含义 ​

MySQL的EXPLAIN执行计划是理解和优化SQL查询性能的核心工具。可以模拟优化器执行查询语句，帮助理解查询是如何执行的，分析查询执行计划可以帮助我们发现查询瓶颈，优化查询功能等。

二、EXPLAIN的作用 ​

  • 表的读取顺序
  • SQL执行时查询操作类型
  • 可以使用哪些索引，实际使用了哪些索引
  • 每张表有多少行记录被扫描
  • sql语句性能分析

三、EXPLAIN输出字段及含义 ​

使用 EXPLAIN + SQL语句 可以查看sql语句的执行计划信息。

id ​

查询序列号，表示select的执行顺序，每个select都有一个对应的id号，并且是从1开始自增的。生产环境主要用来判断复杂查询（如子查询、UNION）的执行顺序。

sql
EXPLAIN 
SELECT * FROM users u 
JOIN orders o ON u.id = o.user_id;
  • id相同：从上到下顺序执行；
  • id不同：id值越大，优先执行。
  • 两种都存在：先执行序号大的，同序号的从上往下执行。
  • NULL：最后执行，表示结果集，并且不需要使用它来进行查询，即表示是union结果或不需要查询的表。通常表示由其他行联合而成的结果集。

select_type ​

查询语句执行的查询操作类型。

  • SIMPLE：简单查询，不包含UNION或子查询。用于大多数单表查询或简单的多表连接查询.
  • PRIMARY：主查询，复杂查询的最外层查询。如果查询包含子查询或UNION，最外层的查询标记为PRIMARY。
  • SUBQUERY：子查询，SELECT或WHERE中的子查询。结果不依赖于外部查询（不在from语句中）。即在SELECT或WHERE列表中包含的不相关子查询（子查询可以独立运行）。例：
sql
SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);
  • DEPENDENT SUBQUERY：在 SELECT 或 WHERE 列表中包含的相关子查询（子查询依赖外部查询的结果）。性能杀手，因为需要对外部查询的每一行都执行一次子查询。
sql
SELECT * FROM t1 WHERE id IN (SELECT id FROM t2 WHERE t1.col = t2.col);
  • DERIVED：在 FROM 子句中的子查询（派生表）。MySQL会将其结果物化成一个临时表。表示从外部数据源中推导出来的，而不是select语句中的其他列中选择出来的。子查询会产生一个 DERIVED 表。在 MySQL 8.0+ 中，优化器会尝试使用“派生表合并”来避免此操作。例：
sql
SELECT * FROM (SELECT * FROM t1) AS derived_t;
  • MATERIALIZED：物化子查询。MySQL将子查询的结果物化为一个临时表，然后与外部查询进行连接（常见于 IN 子查询优化）。 一种对子查询的优化执行方式。
  • UNION：UNION操作，UNION中的第二个及以后的SELECT。
sql
SELECT a FROM t1 UNION SELECT a FROM t2;
  • UNION RESULT：UNION的结果集。若两个查询中有相同的列，会去重，只保留一列。从 UNION 操作的匿名临时表检索结果的 SELECT。

table ​

当前行访问的表名（可能是表、别名或派生表名），如果有多个表，显示多行记录。

partitions ​

匹配的表分区情况，会显示出查询语句在哪些分区上执行，以及是否使用了分区裁剪等信息，如果没有分区，值为NULL。

type ​

查询所用的访问类型（连接类型）（关键性能指标）
性能从优到劣排序如下:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。
生产环境中至少应达到 range 级别，推荐达到 ref。ALL（全表扫描）是需要重点优化的对象。

  • system: 表只有一行数据（等同于系统表），并且该表使用的存储引擎的统计数据是精确的。是 const 类型的特例。
  • const：通过主键（PRIMARY KEY）或唯一索引（UNIQUE）进行等值查询，最多返回一条记录。优化器将其视为常量，只读取一次。
sql
SELECT * FROM table WHERE id = 1;
  • eq_ref:在联表查询（JOIN）时使用。对于前一张表的每一行，使用主键或唯一非空索引进行等值匹配，从本表中只读取一行。通常出现在联表查询的 被驱动表（第二个表）上，且关联字段是主键或唯一键。
sql
--t2.id是主键
SELECT * FROM t1, t2 WHERE t1.id = t2.id;
  • ref：使用普通索引（非唯一索引）进行等值匹配。可能返回多行记录。最常见的理想类型之一。
sql
--name字段是普通索引
SELECT * FROM table WHERE name = ‘John’;
  • ref_or_null：类似 ref，但搜索条件增加了对 NULL 值的检查。比 ref 稍差，因为需要处理NULL。
sql
WHERE col = ‘abc’ OR col IS NULL
  • range:只检索给定范围的行，使用索引选择行。常见于范围查询。
sql
SELECT * FROM table WHERE id > 10;
  • index:全索引扫描。遍历整个索引树获取数据，但无需回表（数据在索引中可全部获取，即覆盖索引）。比 ALL 快，因为索引文件通常比数据文件小。当查询是覆盖索引，但需要扫描全部索引条目时出现。
sql
-- id是索引
SELECT id FROM table;
  • ALL:全表扫描。从磁盘读取表的每一行，是最低效的方式。在表数据量大且无有效索引可用时出现，是主要的优化对象。

possible_keys ​

可能使用的索引。如果此列为空，考虑为查询条件添加合适的索引。

key ​

实际使用的索引。对比possible_keys，确认优化器是否选择了预期索引。如果为NULL，说明未使用索引。

key_len ​

使用的索引长度（主要使用在联合索引）。可推断出实际使用了联合索引中的前几个字段。越短越好，但需覆盖查询条件。

ref ​

使用到索引时，与索引进行等值匹配的列或常量。即将哪个字段或常量和key列所使用的字段进行比较。用于检查连接条件是否有效使用了索引。

rows ​

预估扫描的行数（索引行数或记录行数）。全表扫描时表示需要扫描的行数预估值，索引扫描时表示扫描索引的行数预估值，值越小越好，可以与后续执行后的真实行数对比，评估优化器估算准确性。

filtered ​

符合查询条件的数据百分比。表示符合查询条件的数据百分比。存储引擎返回的数据，在Server层经过WHERE条件过滤后，剩余记录数的百分比估算值。用于估算多表连接时，下一张表需要处理的行数（rows * filtered）。值越大越好。

Extra ​

额外信息（关键性能指标）

  • using index:使用了覆盖索引。查询的列全部包含在某个索引中，无需回表，直接从索引获取数据。性能极佳。是索引优化的理想目标之一。
  • using index condition:表示查询列不被索引覆盖，where条件中是一个索引范围查找，过滤完索引后回表找到所有符合条件的数据行。使用了索引下推优化。即在存储引擎层，利用索引过滤掉不满足条件的行，减少回表次数。
  • using where:在存储引擎返回行后，Server层再次进行了过滤。这意味着索引可能没有完全覆盖 WHERE 条件。需要关注，如果 rows 值很大，说明大量数据被从存储引擎读出后又被过滤掉，应考虑优化索引（例如，让 WHERE 条件中的列也包含在索引中）。
  • using temporay:为了处理查询，MySQL创建了一个临时表。常见于 GROUP BY、DISTINCT、ORDER BY 且没有有效索引可利用时。
  • using filesort:当查询中包含order by 操作而且无法利用索引完成的排序操作，数据较少时从内存排序，如果数据较多需要在磁盘中排序，需优化成索引排序。使用了外部排序（非索引排序），即无法利用索引直接完成排序，需要在内存或磁盘上进行额外的排序操作。需优化。优化方法是为 ORDER BY 的列建立索引，或利用索引的最左前缀。
  • select tables optimized away:使用某些聚合函数来访问某个索引值。
  • Using join buffer：联表查询时，被驱动表没有有效索引，需要为其在连接缓冲区中建立缓存以提高效率。通常意味着被驱动表的连接字段缺少索引，应尝试为其添加索引。

四、底层原理 ​

执行流程 ​

sql
SQL语句 → 解析器 → 优化器 → 生成执行计划 → EXPLAIN输出

五、优化建议 ​

  • 诊断流程：查看执行计划时，遵循 type → key → rows → Extra 的顺序进行诊断。
    • 首先看 type 是否为 ALL（全表扫描）。
    • 然后看 key 是否使用了预期的索引。
    • 接着看 rows 估算扫描的行数是否过大。
    • 最后重点分析 Extra 中的警告信息（如 Using temporary; Using filesort）。
  • 核心优化方向：
    • 索引是灵魂：通过创建合适的联合索引、覆盖索引，努力让查询的 type 达到 ref/eq_ref，至少应优化到 range 级别。并在 Extra 中看到 Using index。
    • 避免相关子查询：警惕 DEPENDENT SUBQUERY，尝试将其改写为 JOIN。
    • 消除临时表和文件排序：为 ORDER BY、GROUP BY 的列建立索引，是解决Using temporary; Using filesort 最直接有效的方法。
    • 在MySQL 8.0+中，务必使用 EXPLAIN ANALYZE。它会在语句实际执行后，给出每个步骤的实际耗时和返回行数，与优化器的估算值进行对比，是更精确的性能剖析工具。
    • 特殊情况：当表数据量极小时，优化器可能认为全表扫描成本低于索引查找，此时出现 ALL 也属正常。

Released under the MIT License.

Copyright © 2025-present 漫舒听雨