如何用 MySQL 的 EXPLAIN 语句进行查询分析

dj_master

于 2025-06-19 14:19:40 发布

阅读量1k

点赞数 13

CC 4.0 BY-SA版权

分类专栏： MySql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_43521001/article/details/148765737

MySql 专栏收录该内容

49 篇文章

订阅专栏

要详细且清晰地掌握如何使用 MySQL 的 EXPLAIN 语句进行查询分析，可按以下步骤深入了解，从基本用法到结果解读、优化思路全面展开：

一、`EXPLAIN` 基本使用方式

在需要分析的 SELECT（也可用于 INSERT、UPDATE、DELETE 等语句，不过最常用在 SELECT ）查询语句前加上 EXPLAIN 关键字即可。语法示例：

EXPLAIN SELECT * FROM table_name WHERE condition;

比如分析查询用户表中年龄大于 20 岁用户的语句：

EXPLAIN SELECT * FROM users WHERE age > 20;

执行后，MySQL 会返回一个结果集，里面包含查询执行计划的各类详细信息，通过解读这些信息，就能知晓查询是如何被执行的，进而找出可能存在的性能瓶颈。

二、`EXPLAIN` 结果列详细解读

执行 EXPLAIN 后，结果集通常包含 id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra 等列，下面逐个详细说明：

1. `id` 列

含义：代表查询的标识符，每个 SELECT 语句（包括子查询、UNION 中的查询等）会被分配唯一 id 值。若查询里有子查询、UNION 操作，会出现多个 id 。id 值越大，执行顺序越靠前；id 相同的语句，按从上到下顺序执行。
分析要点：当 id 有重复且存在嵌套（如子查询嵌套）时，要留意子查询执行顺序和效率。若子查询执行代价高，可考虑优化，比如用 JOIN 替代子查询，减少多层嵌套带来的性能损耗。例如：

EXPLAIN 
SELECT * 
FROM article 
WHERE id = (SELECT id FROM article WHERE content = '示例内容');

主查询 id 为 1 ，子查询 id 为 2 ，子查询执行时可能全表扫描（若没合适索引），需关注优化。

2. `select_type` 列

含义：标识查询的类型，常见值及说明：
- SIMPLE：简单查询，不包含子查询、UNION 操作，最基础的单表或多表连接（无嵌套子查询）查询属于这种类型。比如 EXPLAIN SELECT * FROM users WHERE age > 20; ，若没子查询等复杂结构，select_type 就是 SIMPLE 。
- PRIMARY：主查询，当查询存在复杂子部分（如子查询、UNION ）时，最外层的查询会标记为 PRIMARY 。
- SUBQUERY：不在 FROM 子句中的子查询，像 SELECT 子查询（如 SELECT (SELECT col FROM table2) FROM table1 ）、WHERE 子句里的子查询（前面示例中 WHERE id = (SELECT id ...) 里的子查询）。
- DERIVED：表示 FROM 子句中包含的子查询（派生表子查询），MySQL 会递归执行这类子查询，并把结果放到临时表中，服务器内部称其为“派生表” 。例如 EXPLAIN SELECT * FROM (SELECT id, name FROM users) AS derived_table; ，里面 FROM 子句的子查询 select_type 就是 DERIVED 。
- UNION：UNION 关键字后面的那个 SQL 查询的类型标识。比如 EXPLAIN SELECT * FROM table1 UNION SELECT * FROM table2; ，第二个 SELECT 的 select_type 为 UNION 。
- UNION RESULT：用于从 UNION 产生的匿名临时表中检索结果的 SELECT ，即合并 UNION 各部分结果的操作对应的类型。
分析要点：复杂的 select_type（如多层子查询、大量派生表）可能让查询性能变差，可考虑优化查询结构，像用 JOIN 替换子查询，简化查询层级，提升执行效率。

3. `table` 列

含义：显示该行数据对应的表名，若是子查询、派生表，会显示对应的别名。如果是多表连接查询，会逐行显示涉及的表。
分析要点：关注涉及的表数量，表连接过多（比如超过 3 - 5 张表复杂连接）可能因多表关联的开销（如笛卡尔积计算、连接条件判断）影响性能。可思考是否能减少表连接，或者优化连接条件（如确保连接字段有合适索引）。比如多表连接时，若某些表的连接可提前通过业务逻辑过滤数据，就减少参与连接的记录数。

4. `type` 列

含义：表示 MySQL 查找表中行的方式，也就是表的“访问类型”，这是判断查询性能优劣的关键指标之一。从性能好到差常见的类型依次为：system > const > eq_ref > ref > range > index > ALL 。
- system：是 const 的特殊情况，当表只有一行记录（系统表）且用于 const 连接类型时出现，非常罕见，性能最优。
- const：表中最多有一行匹配的记录，可被看作常量，通常是查询条件命中主键索引或唯一索引，且返回单行数据。比如 EXPLAIN SELECT * FROM users WHERE id = 1;（id 是主键），type 可能为 const ，查询效率很高。
- eq_ref：常用于多表连接查询，JOIN 条件中使用主键索引或唯一索引进行关联，每次关联操作都能准确匹配到单行数据。比如 SELECT * FROM orders o JOIN users u ON o.user_id = u.id（u.id 是主键），对于 orders 表和 users 表连接，users 表的访问类型可能是 eq_ref 。
- ref：表示使用非唯一索引（或普通索引）进行查询，会找到多个符合条件的行。比如查询条件是 WHERE name = '张三'（name 是普通索引），可能匹配到多个叫“张三”的用户，type 为 ref ，性能也较好，但比 eq_ref 稍差。
- range：表示索引范围扫描，即查询条件是一个范围，常见于 >、<、BETWEEN、IN 等操作。比如 EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;（age 有索引），type 为 range ，相比全表扫描好很多，但因扫描部分索引，性能不如 ref 等。
- index：全索引扫描，会遍历索引的所有行，虽没全表扫描（ALL ）读取的数据多（只读索引），但如果索引很大，也会有性能问题。比如 EXPLAIN SELECT id FROM users;（id 是索引），若直接扫描索引就能获取结果，type 可能是 index 。
- ALL：全表扫描，会遍历表中所有记录，性能最差，数据量大时要尽量避免。比如查询条件没有有效索引，或者索引选择性太差（如用 WHERE sex = '男' ，sex 重复值多），就可能触发 ALL 。
分析要点：尽量让查询的 type 达到 ref 及以上更优级别。若 type 是 ALL（全表扫描）或 index（全索引扫描），且数据量较大，就要考虑优化，通常可通过给查询条件字段添加合适索引来改善。

5. `possible_keys` 列

含义：显示 MySQL 认为可能用于查找该行数据的索引，如果为 NULL ，说明没有相关索引可用。这些索引是基于查询的 WHERE 条件、连接条件等判断出来的候选索引。
分析要点：若该列有值，但 key 列没选对应的索引，可能是索引选择性不高（即索引区分度低，比如用性别字段索引查询，重复值多），或者查询优化器认为使用索引不是最优选择（比如表数据量小，全表扫描更快）。这时候需要进一步分析，判断是否要调整索引（如更换更合适的索引、优化索引结构）。

6. `key` 列

含义：表示 MySQL 实际执行查询时用到的索引，如果没用到索引，该列值为 NULL 。
分析要点：若 key 为 NULL ，说明查询没利用索引，可能需要给查询条件里的字段添加索引来优化。若 key 有值，要确认是否是预期的索引，也可通过 FORCE INDEX、USE INDEX、IGNORE INDEX 等语句强制 MySQL 使用或忽略某些索引。比如：

-- 强制使用 idx_age 索引
EXPLAIN SELECT * FROM users USE INDEX (idx_age) WHERE age > 20;

7. `key_len` 列

含义：表示 MySQL 选用的索引字段按字节计算的长度，若 key 为 NULL ，该列长度也为 NULL 。通过 key_len 可判断 MySQL 实际用了多列索引（复合索引）中的几个字段，还能了解索引的使用效率（一般来说，key_len 越短，索引使用越高效，不过要在能满足查询需求的前提下）。
分析要点：结合复合索引的字段和长度，检查索引是否被合理使用。比如复合索引是 idx_name_age (name, age) ，若 key_len 只对应 name 字段的长度，说明查询可能没用到 age 部分的索引，需分析查询条件是否符合最左匹配原则等。

8. `ref` 列

含义：显示与索引一起用于查找记录的列或常量。比如是用某个表的字段、还是常量（如 const ）来和索引做匹配。
分析要点：关注 ref 列的值，判断是否用了合适的列或常量进行索引查找。若发现用了不必要的常量或者不合适的列，可能需要调整查询条件或索引设计。例如，若 ref 是 const ，说明用常量匹配索引；若 ref 是某表的字段，要确保关联合理。

9. `rows` 列

含义：表示 MySQL 估计执行查询时需要扫描的行数，是一个估计值，但能大致反映查询效率。
分析要点：rows 值越大，查询效率可能越低（因为要扫描更多数据）。可通过优化查询条件（如添加更精准的过滤条件，缩小查询范围）、添加合适索引等方式，减少需要扫描的行数。比如原本 rows 是 1000 ，优化后通过索引过滤，rows 可能降到 100 ，查询会快很多。

10. `Extra` 列

含义：包含 MySQL 处理查询时的额外信息，这些信息无法用其他列展示，却对分析查询性能至关重要，常见值及说明：
- Using filesort：表示查询需要进行文件排序，即无法利用索引完成排序，会在内存（或临时文件）中对结果集排序，性能较低，要尽量优化（如给排序字段添加合适索引）。比如 EXPLAIN SELECT * FROM users ORDER BY name;（name 无索引），可能出现 Using filesort 。
- Using temporary：表示查询过程中使用了临时表，通常在分组（GROUP BY ）、去重（DISTINCT ）等操作时出现，性能开销较大，需优化查询结构，如确保分组、去重字段有合适索引。比如 EXPLAIN SELECT DISTINCT name FROM users; ，若没索引可能用到临时表。
- Using index：表示使用了覆盖索引（即查询的字段都包含在索引中，无需回表查询原数据），查询效率较高，是优化的理想状态，要尽量利用这种情况。比如 EXPLAIN SELECT id, name FROM users WHERE id = 1;（id 和 name 组成复合索引，且 id 是主键相关），可能出现 Using index 。
- Using where：表示查询使用了 WHERE 条件过滤数据，但不一定利用了索引；若同时出现 Using index 和 Using where ，说明是通过索引过滤数据（索引下推，MySQL 5.6+ 支持），效率较高。
- Using join buffer：多表连接时，MySQL 使用了连接缓冲来存储中间结果，若出现此情况，可能需优化连接条件或索引，减少连接数据量。
分析要点：特别关注 Extra 列里的内容，若有 Using filesort、Using temporary ，往往是查询性能瓶颈所在，要重点优化查询语句；若有 Using index ，则说明查询利用索引做得较好，可借鉴这种模式优化其他查询。

三、基于 `EXPLAIN` 结果的优化步骤

查看 type 列：若为 ALL 或 index 且数据量大，优先给查询条件字段添加合适索引，将 type 提升到更优级别（如 ref、range 等）。
检查 key 列：若为 NULL ，分析 possible_keys ，判断是否因索引缺失或索引选择性差导致，补充或调整索引。
关注 Extra 列：对 Using filesort ，给排序字段建索引；对 Using temporary ，优化分组、去重逻辑或给相关字段建索引；尽量让查询达到 Using index 状态（利用覆盖索引）。
优化查询结构：若 select_type 复杂（如多层子查询），尝试用 JOIN 替换子查询；减少不必要的表连接，简化查询逻辑。
调整查询条件：通过分析 rows 列，添加更精准的过滤条件，缩小查询范围，减少扫描行数。

举个完整优化示例：
原始查询（可能性能差）：

SELECT * FROM orders o 
JOIN users u ON o.user_id = u.id 
WHERE o.order_time > '2025-01-01' 
ORDER BY o.order_amount;

执行 EXPLAIN 后，发现：

type 列中 orders 表是 ALL（全表扫描），因为 order_time 无索引；users 表是 eq_ref（连接用了主键，较优）。
Extra 列有 Using filesort（因 order_amount 无索引，排序需文件排序）。

优化步骤：

给 orders 表的 order_time 字段建索引：CREATE INDEX idx_order_time ON orders(order_time);
给 orders 表的 order_amount 字段建索引（或建包含 order_time 和 order_amount 的复合索引，视情况），若查询常用这两个条件，可建 CREATE INDEX idx_order_time_amount ON orders(order_time, order_amount); ，这样排序可能利用索引，消除 Using filesort 。
优化后再次用 EXPLAIN 分析，查看 type、key、Extra 等列是否改善，确保查询更高效执行。