MySQL explain执行计划每个字段的意义

ps：如果这篇帖子对于还在找工作和找实习的你有所帮助，可以关注我，给本贴点赞、评论、收藏并订阅专栏；同时不要吝啬您的花花

MySQL的EXPLAIN语句用于查看SQL语句的执行计划，帮助开发者分析SQL的执行效率、优化查询性能。执行EXPLAIN后会返回多个字段，每个字段对应执行计划的不同维度，以下是每个字段的详细意义（基于MySQL 5.7及以上版本，兼顾常见场景）。

1. id

表示查询中每个select子句的执行顺序，核心规则如下：

• id相同：执行顺序由上至下（同一层级的查询，按书写顺序执行）；
• id不同：id值越大，执行优先级越高（先执行id大的子查询，再执行id小的主查询）；
• id为NULL：表示这是一个临时表的操作（如union结果的合并），不属于任何select子句。

实用提示：若id出现断层或异常，可能是子查询嵌套过深，需考虑优化（如改为join）。

2. select_type

表示当前select语句的类型，决定了查询的复杂程度，常见取值及意义如下：

• SIMPLE：简单查询，不包含子查询、union、视图等，是最基础的查询类型；
• PRIMARY：主查询，当查询包含子查询时，最外层的select语句会标记为PRIMARY；
• SUBQUERY：子查询（非关联子查询），位于select或where子句中的子查询，会被标记为SUBQUERY；
• DERIVED：派生表，由子查询生成的临时表（如from子句中的子查询），会标记为DERIVED；
• UNION：union语句中第二个及以后的select子句（第一个select会标记为PRIMARY）；
• UNION RESULT：union查询的结果集合并操作，id通常为NULL；
• DEPENDENT SUBQUERY：关联子查询，子查询的执行依赖于外层查询的结果（效率较低，需谨慎使用）；
• DEPENDENT UNION：union语句中，第二个及以后的select子句，且依赖于外层查询的结果。

3. table

表示当前执行步骤所操作的表（或临时表），常见取值说明：

• 直接显示表名：表示操作的是实际存在的表；
• 显示derivedN（如derived2）：表示操作的是id为N的派生表（临时表）；
• 显示unionN,M（如union1,2）：表示操作的是id为N和M的union结果集；
• 显示NULL：表示不涉及具体表（如只查询常量、函数计算，无from子句）。

4. type

表示MySQL在表中查找数据的方式（即访问类型），是判断查询效率的核心字段之一，取值从优到差排序如下（重点记住前4种）：

• system：表中只有一行数据（如系统表），是const类型的特例，效率最高；
• const：通过主键或唯一索引查询，最多返回一行数据，效率极高（如where id=1）；
• eq_ref：多表join时，被join的表通过主键或唯一索引匹配，每行主表数据只匹配一行从表数据（如join on a.id = b.id，b.id是主键）；
• ref：通过非唯一索引或唯一索引的前缀查询，可能返回多行数据（如where name='张三'，name是普通索引）；
• fulltext：通过全文索引查询（仅适用于fulltext索引字段）；
• ref_or_null：与ref类似，但允许查询字段为NULL的情况；
• index_merge：MySQL会合并多个索引的查询结果（如where id=1 or name='张三'，id和name分别有索引）；
• unique_subquery：子查询中使用唯一索引，替代eq_ref的一种形式；
• index_subquery：子查询中使用非唯一索引，替代ref的一种形式；
• range：通过索引范围查询（如where id between 1 and 10、where id > 5），效率中等；
• index：全索引扫描（遍历整个索引树，不访问表数据），比全表扫描略优（索引文件比数据文件小）；
• ALL：全表扫描（遍历整个表的所有数据），效率最低，需尽量避免（尤其是大表）。

实用提示：正常优化目标是将type优化到ref及以上，避免出现ALL（全表扫描）和index（全索引扫描）。

5. possible_keys

表示MySQL可能会使用的索引（候选索引），即MySQL在执行查询时，认为可能适用的索引列表。

注意事项：

• 该字段显示的索引，不一定会被实际使用（最终使用的索引看key字段）；
• 若该字段为NULL，表示没有可用的索引，MySQL会直接走全表扫描；
• 即使有候选索引，若索引选择性太差（如字段值重复率高，如性别字段），MySQL也可能放弃使用索引，走全表扫描。

6. key

表示MySQL实际使用的索引（真正用于查询的索引），是判断索引是否生效的核心字段。

注意事项：

• 若key为NULL，表示MySQL没有使用任何索引，走了全表扫描；
• key的值一定是possible_keys中的一个（或多个，若使用index_merge）；
• 若使用了覆盖索引（索引包含查询所需的所有字段），key会显示该索引，且Extra字段会出现“Using index”。

7. key_len

表示MySQL实际使用的索引长度（单位：字节），用于判断索引的使用情况（如联合索引是否被部分使用）。

核心说明：

• key_len越长，说明索引使用的字段越多（联合索引中，前缀匹配的字段越多，key_len越长）；
• key_len的计算与字段类型、字符集有关（如varchar(20) utf8mb4，每个字符占4字节，key_len=20*4=80；若字段允许NULL，会额外加1字节）；
• 通过key_len可判断联合索引是否被完全使用（如联合索引(a,b,c)，若key_len对应a+b的长度，说明只使用了前两个字段）。

8. ref

表示与key字段中索引匹配的列或常量，说明索引是如何被使用的，常见取值：

• const：表示索引匹配的是一个常量（如where id=1，ref为const）；
• 表名.字段名：表示索引匹配的是另一张表的某个字段（如a join b on a.id = b.a_id，b表的ref为a.id）；
• NULL：表示索引使用的是范围查询（type为range）或全索引扫描（type为index），无需匹配具体值。

9. rows

表示MySQL预估的、需要扫描的行数（非实际扫描行数），用于判断查询的效率：rows值越小，扫描的行数越少，效率越高。

注意事项：

• rows是MySQL根据统计信息估算的值，可能与实际扫描行数有差异，但趋势一致；
• 若rows值远大于表的实际行数，说明MySQL的统计信息过时，需执行ANALYZE TABLE 表名更新统计信息。

10. Extra

表示执行计划的额外信息，包含大量关键的优化提示，是分析SQL性能的重要补充，常见取值及意义如下：

• Using index：使用了覆盖索引（索引包含查询所需的所有字段），无需回表查询数据，效率极高（最优情况）；
• Using where：MySQL在获取数据后，会对数据进行where条件过滤（说明where条件生效，但可能未使用索引）；
• Using temporary：MySQL需要创建临时表来存储查询结果（如group by、distinct、union等操作），效率较低，需优化；
• Using filesort：MySQL需要对结果集进行排序（未使用索引排序），效率较低，需优化（如添加排序索引）；
• Using join buffer (Block Nested Loop)：多表join时，MySQL使用join buffer来存储中间结果，效率较低（通常是因为没有可用的join索引）；
• Using index condition：使用了索引条件推送（ICP），MySQL会先通过索引过滤部分数据，再回表查询，减少回表次数；
• Using intersect：使用了索引交集（index_merge的一种，多个索引的交集查询）；
• Using union：使用了索引并集（index_merge的一种，多个索引的并集查询）；
• Impossible WHERE：where条件永远为false（如where 1=0），MySQL不会扫描任何数据；
• No tables used：查询不涉及任何表（如select 1+1）。

实用提示：Extra中出现Using temporary、Using filesort通常是性能瓶颈，需优先优化；出现Using index是最优状态。

补充说明

1. 执行计划的字段顺序不影响分析，核心关注type、key、rows、Extra四个字段；

2. 若执行计划中出现ALL（全表扫描）、Using temporary、Using filesort，需优先优化（如添加合适的索引、调整SQL语句）；

3. 不同MySQL版本的执行计划字段可能略有差异，但核心字段（id、type、key、rows、Extra）的意义基本一致。

案例拆解：结合具体SQL理解执行计划字段

以下选取3个高频SQL场景（单表查询、多表JOIN、子查询），模拟执行计划输出，逐一拆解各字段的实际对应关系，帮你快速关联理论与实践。

案例1：单表查询（主键+普通索引场景）

1. 准备测试表与数据

-- 创建用户表，主键id，普通索引name
CREATE TABLE `user` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `age` INT NOT NULL,
  `gender` VARCHAR(10) DEFAULT NULL,
  INDEX idx_name (`name`) -- 普通索引
);
-- 插入测试数据
INSERT INTO `user` (name, age, gender) VALUES 
('张三', 25, '男'), ('李四', 30, '女'), ('王五', 28, '男');

2. 执行SQL与模拟执行计划

执行SQL：EXPLAIN SELECT id, name FROM `user` WHERE id = 1 AND name = '张三';

模拟执行计划输出（重点字段）：

3. 字段拆解（对应前文理论）

• id=1：只有一个select子句，执行顺序唯一，从上至下执行。
• select_type=SIMPLE：简单查询，无subquery、union，仅单表查询。
• table=user：当前操作的是实际存在的user表。
• type=const：通过主键id查询，最多返回一行数据，效率极高（符合前文const是主键/唯一索引查询的定义）。
• possible_keys=PRIMARY,idx_name：MySQL认为可能适用的索引的是主键索引（PRIMARY）和普通索引（idx_name）。
• key=PRIMARY：实际使用的是主键索引（主键索引查询比普通索引更高效，MySQL优先选择）。
• key_len=4：id是INT类型（占4字节），不允许NULL，因此key_len=4（符合前文“key_len与字段类型相关”的说明）。
• ref=const：索引匹配的是常量（where id=1中的1是常量）。
• rows=1：MySQL预估扫描1行数据（与实际数据量一致，统计信息准确）。
• Extra=Using index：查询的字段（id、name）都在主键索引和idx_name索引中（覆盖索引），无需回表，效率最优。

案例2：多表JOIN查询（关联索引场景）

1. 准备测试表（新增订单表）

-- 创建订单表，外键user_id关联user表id
CREATE TABLE `order` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `order_no` VARCHAR(50) NOT NULL,
  `user_id` INT NOT NULL,
  `create_time` DATETIME NOT NULL,
  INDEX idx_user_id (`user_id`) -- 外键索引，关联user.id
);
-- 插入测试数据
INSERT INTO `order` (order_no, user_id, create_time) VALUES 
('OD20260313001', 1, '2026-03-13 10:00:00'),
('OD20260313002', 2, '2026-03-13 10:30:00');

2. 执行SQL与模拟执行计划

执行SQL：EXPLAIN SELECT u.name, o.order_no FROM `user` u JOIN `order` o ON u.id = o.user_id WHERE u.age > 20;

模拟执行计划输出（重点字段）：

3. 字段拆解（重点关注多表关联相关字段）

• id=1（两行）：id相同，执行顺序由上至下（先执行user表查询，再执行order表关联查询）。
• select_type=SIMPLE：虽为多表JOIN，但无复杂子查询、union，仍为简单查询。
• table=u、o：分别对应user表（别名u）和order表（别名o）。
• type=ALL（u表）、ref（o表）： u表：where条件是age>20，age无索引，因此type=ALL（全表扫描）；
• o表：通过idx_user_id索引关联u.id，type=ref（非唯一索引查询，匹配多行但每行主表数据对应一行从表数据）。

possible_keys与key： u表：possible_keys=PRIMARY（主键索引），但未使用（key=NULL），因查询条件不涉及主键；

o表：possible_keys=idx_user_id，实际使用该索引（key=idx_user_id）。

ref=test.u.id（o表）：o表的索引（idx_user_id）匹配的是u表的id字段（多表关联的典型ref取值）。

rows=3（u表）、1（o表）：u表全表扫描3行，o表每匹配一个u.id，预估扫描1行。

Extra=Using where（u表）：u表全表扫描后，通过where age>20过滤数据（where条件生效但未用索引）。

案例3：子查询（派生表+关联子查询场景）

1. 执行SQL与模拟执行计划

执行SQL：EXPLAIN SELECT u.name, (SELECT COUNT(*) FROM `order` o WHERE o.user_id = u.id) AS order_count FROM `user` u WHERE u.name = '张三';

模拟执行计划输出（重点字段）：

3. 字段拆解（重点关注id、select_type）

• id=1、2：id不同，id=2（子查询）优先级高于id=1（主查询），先执行子查询，再执行主查询。
• select_type=PRIMARY、DEPENDENT SUBQUERY： id=1：主查询，标记为PRIMARY；
• id=2：关联子查询（子查询o.user_id = u.id依赖主查询u.id的值），标记为DEPENDENT SUBQUERY。

type=ref（两表）： u表：通过idx_name（name索引）查询，type=ref；

o表：通过idx_user_id索引关联u.id，type=ref。

key_len=202（u表）：name是varchar(50)、utf8mb4（50*4=200字节），不允许NULL，因此key_len=200（无额外1字节）。

Extra=Using index（o表）：子查询只统计count(*)，idx_user_id索引包含user_id字段（覆盖索引），无需回表。

案例4：包含Using temporary/Using filesort的场景（分组+排序无索引）

1. 执行SQL（触发性能瓶颈）

基于前文user表（gender无索引），执行SQL：EXPLAIN SELECT gender, COUNT(*) AS count FROM `user` GROUP BY gender ORDER BY count DESC;

说明：该SQL需按gender分组统计数量，再按数量降序排序，因gender无索引、排序字段count无索引，会触发Using temporary和Using filesort。

2. 模拟执行计划输出（重点字段）

3. 字段拆解（重点关注Extra字段及性能瓶颈）

• id=1、select_type=SIMPLE：单表简单查询，无复杂子查询、JOIN，执行顺序唯一。
• table=user：操作的是user表，无临时表（临时表是MySQL内部创建，table字段不显示）。
• type=ALL、key=NULL：gender字段无索引，group by需要全表扫描所有数据，因此type=ALL（全表扫描），无可用索引（key=NULL）。
• possible_keys=NULL：MySQL认为无适用索引，无法通过索引优化分组操作。
• rows=3：预估扫描3行数据（与user表实际数据量一致）。
• Extra=Using temporary; Using filesort（核心重点）：Using temporary：因group by gender无索引，MySQL需要创建临时表，存储分组后的结果（按gender分组，统计count(*)），临时表的创建和销毁会消耗性能；Using filesort：因order by count DESC（排序字段count是聚合函数结果，无索引），MySQL无法通过索引排序，只能将分组后的结果加载到内存或磁盘中进行文件排序，效率较低。

4. 优化提示（关联前文理论）

要消除Using temporary和Using filesort，可添加单列索引（注：当前仅针对gender字段优化，无需联合索引）：CREATE INDEX idx_gender ON `user` (gender);

优化后执行计划变化：type会变为ref（若gender值有重复，也可能为range），key=idx_gender，Extra字段会消失（无临时表、无文件排序），查询效率大幅提升，对应前文“Extra中出现Using temporary、Using filesort需优先优化”的提示。

5. 优化后执行计划对比（核心重点）

添加索引后，执行相同SQL：EXPLAIN SELECT gender, COUNT(*) AS count FROM `user` GROUP BY gender ORDER BY count DESC;

优化后模拟执行计划输出（与未优化版本对比）：

6. 对比总结

通过添加idx_gender索引，实现了3个关键优化，完美呼应前文理论：

• 访问类型（type）从ALL（全表扫描）优化为ref（索引查询），符合“type优化到ref及以上”的目标；
• 索引从无（key=NULL）变为实际使用（key=idx_gender），索引生效，减少扫描开销；
• 消除了Using temporary和Using filesort两个性能瓶颈，查询效率大幅提升，印证了“添加合适索引可优化此类瓶颈”的结论。

案例总结

1. 执行计划的核心是「type（访问类型）、key（实际索引）、rows（预估行数）、Extra（优化提示）」，这四个字段直接决定查询效率；

2. 单表查询优先看type和key，尽量避免ALL（全表扫描）；多表JOIN优先看关联字段是否有索引（type=ref/eq_ref）；

3. 子查询的id和select_type能明确执行顺序和子查询类型，关联子查询（DEPENDENT SUBQUERY）效率较低，可考虑改为JOIN优化；

4. Using temporary（临时表）和Using filesort（文件排序）是常见性能瓶颈，多出现于group by、order by且无对应索引的场景，添加合适索引可有效优化。

ps：如果这篇帖子对于还在找工作和找实习的你有所帮助，可以关注我，给本贴点赞、评论、收藏并订阅专栏；同时不要吝啬您的花花