mysql执行计划

📝 记录一下 MySql 执行计划。

⚡ 实战优化技巧

快速定位问题的三步法：

先看 type：是否全表扫描
再看 Extra：是否有文件排序
最后看 rows：扫描行数是否合理

1. 看 type 字段决定优化方向

ALL/index → 必须优化
range → 检查范围是否过大
ref → 理想状态
const/eq_ref → 完美

2. Extra 字段的关键信号

Using filesort → 排序性能杀手，优先优化
Using temporary → 内存/磁盘消耗大
Using index → 最佳状态，覆盖索引

mysql 执行计划

一、什么是执行计划

执行计划（Execution Plan / Query Plan）是 MySQL 优化器决定如何执行一条 SQL 的详细步骤说明。
当你写下一个 SELECT，MySQL 内部会经历 解析 → 语义检查 → 优化器 → 执行器。其中 优化器 的任务，就是选择最优的索引、连接顺序和访问方式。

我们用命令查看执行计划：

-- 基本执行计划
EXPLAIN SELECT * FROM users WHERE age > 25;

-- 详细执行计划（MySQL 5.6+）
EXPLAIN EXTENDED SELECT * FROM users WHERE age > 25;
SHOW WARNINGS;

-- JSON格式执行计划（MySQL 5.6+）
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 25;

-- 分析实际执行情况（MySQL 8.0+）
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;

-- 查看执行计划和实际统计信息
EXPLAIN FORMAT=TREE SELECT * FROM users WHERE age > 25;

二、EXPLAIN 输出字段详解

共 12 个字段

字段	含义
`id`	执行顺序标识，越大优先执行；相同 id 表示同层次，嵌套子查询会有不同 id。
`select_type`	查询类型：SIMPLE、PRIMARY、SUBQUERY、DERIVED 等。
`table`	当前访问的表。
`partitions`	访问的分区。
`type`	连接类型（重要，性能指标）。
`possible_keys`	优化器认为可用的索引。
`key`	实际使用的索引。
`key_len`	索引使用长度（字节数，越短越精确）。
`ref`	与索引列比较的对象（如常量/字段）。
`rows`	预估扫描行数。
`filtered`	过滤后剩余行数百分比（估算）。
`Extra`	额外信息（是否回表、是否排序、是否临时表等）。

1. `id`：查询序列号

表示查询中每个 SELECT 子句的执行顺序，有三种情况：

id 相同：执行顺序由上至下（如多表 JOIN 时，先执行的表在上方）；
id 不同：id 越大，优先级越高，先执行（如子查询，内层查询 id 更大）；
id 有相同有不同：大 id 先执行，相同 id 按顺序执行。

示例：
子查询的 id 大于外层查询，优先执行内层：

+----+-------------+-------+...+
| id | select_type | table |...|
+----+-------------+-------+...+
|  1 | PRIMARY     | o     |...|  -- 外层查询，后执行
|  2 | SUBQUERY    | u     |...|  -- 内层子查询，先执行
+----+-------------+-------+...+

2. `select_type`：查询类型

表示 SELECT 语句的类型，反映查询的复杂程度（如简单查询、子查询、联合查询等），常见值：

值	含义	优化建议
`SIMPLE`	简单查询（无子查询、无 `UNION`）	无需优化
`PRIMARY`	最外层的查询（包含子查询或 `UNION` 时，外层查询标记为 `PRIMARY`）	关注连接顺序
`SUBQUERY`	子查询中的第一个 `SELECT`（不依赖外层结果）	考虑改为 JOIN
`DEPENDENT SUBQUERY`	子查询依赖外层查询的结果（外层每一行都会触发一次子查询）
`DERIVED`	派生表的 SELECT（FROM 子句中的子查询）	可能导致临时表
`UNION`	`UNION` 中第二个及以后的 `SELECT`	检查是否必要
`UNION RESULT`	`UNION` 的结果集（临时表，用于合并 `UNION` 结果）	关注去重成本

关键关注点：DEPENDENT SUBQUERY 可能导致性能问题（外层有 1000 行，子查询就执行 1000 次），需优化为 JOIN。

3. `table`：查询的表名

表示当前行对应的表（或临时表、衍生表）：

直接显示表名（如 orders）；
衍生表显示为 <derivedN>（N 是子查询的 id）；
UNION 结果显示为 <unionM,N>（M、N 是 UNION 中查询的 id）。

4. `partitions` - 匹配的分区

显示查询将访问的分区
非分区表为 NULL

5. type - 访问类型（非常重要-核心字段）

表示 MySQL 扫描表的方式，直接决定查询性能，从优到差的常见值，性能从好到坏排序：

值	含义	性能等级
`system`	表中只有一行数据（如系统表），是 `const` 的特例	最优
`const`	通过主键或唯一索引查询，最多返回一行（如 `WHERE id = 1`）	优秀
`eq_ref`	多表 `JOIN` 时，被驱动表通过主键或唯一索引匹配，每行只匹配一次（如 `JOIN ON 主键`）	优秀
`ref`	通过普通索引（非唯一索引）查询，可能返回多行（如 `WHERE status = 1`，`status` 有非唯一索引）	良好
`range`	索引范围查询（如 `>`, `<`, `BETWEEN`, `IN`），这个还是利用到了索引	较好
`index`	扫描整个索引（全索引扫描），比 `ALL` 快（索引体积小于表数据），也利用了索引	一般
`ALL`	全表扫描（未使用索引）	最差

优化目标：至少达到 range 级别，最好是 ref 或 const。若为 ALL（全表扫描），需检查是否缺少索引。

6. possible_keys - 可能使用的索引

优化器考虑使用的索引
如果为 NULL，考虑添加合适索引

7. key - 实际使用的索引

实际选择的索引
为 NULL 表示未使用索引

8. key_len - 使用的索引长度

索引中使用的字节数
可判断是否使用索引的全部部分
规则：字段类型长度 + 是否为 NULL（1 字节） + 字符编码（如 utf8mb4 每个字符占 4 字节）。
示例：联合索引 (status, create_time)，status 是 tinyint NOT NULL（1 字节），create_time 是 datetime（8 字节）：
- 若 key_len=1：仅使用了 status 字段；
- 若 key_len=9：使用了 status + create_time 字段（1+8=9）。
key_len 计算规则：
- 数值类型：TINYINT(1), INT(4), BIGINT(8)
- 字符类型：CHAR(n)=n字符集字节数, VARCHAR(n)=n字符集字节数+2
- 日期类型：DATE(3), DATETIME(8), TIMESTAMP(4)
- NULL字段：额外占用 1 字节
- 变长字段：额外占用 2 字节存储长度

9. ref - 索引比较的列或常量

表示索引字段与哪些值进行比较，可能是：

常量（如 const，表示与固定值比较，如 WHERE id=1）；
字段名（如 users.id，表示与其他表的字段比较，如 JOIN ON orders.user_id = users.id）。
func：函数值
NULL：范围扫描

10. `rows`：估算扫描行数

MySQL 估算的需要扫描的行数（非精确值），值越小越好。

若 type=ALL 且 rows 接近表总行数，说明全表扫描，需优化；
若 type=ref 但 rows 很大，可能索引字段选择性差（如低基数字段，如 status 只有 0/1 两个值）。

11. filtered - 过滤百分比

表示按 WHERE 条件过滤后剩余记录的百分比

rows：预估要扫多少行。
filtered：估计条件过滤后还剩多少比例。
rows × filtered/100 ≈ 传递给上层的行数。

12. `Extra`：额外信息（非常重要-核心字段）

包含优化器的关键执行细节，是判断查询效率的重要依据，常见值：

值	含义	优化建议
`Using index`	使用覆盖索引（索引包含查询所需的所有字段），无需回表，性能极佳	理想状态，无需优化
`Using where`	使用 `WHERE` 条件过滤，但未使用索引（或索引未覆盖过滤字段）	检查是否缺少索引
`Using index condition`	先通过索引过滤，再用 `WHERE` 条件进一步过滤（索引下推，InnoDB 特性）	性能较好，无需优化
`Using filesort`	需要额外排序（未利用索引的有序性），大数据量时极慢	优化 `ORDER BY` 字段，使其与索引顺序一致
`Using temporary`	需要创建临时表存储中间结果（如 `GROUP BY` `INNER JOIN`无索引时），性能差	为 `GROUP BY, INNER JOIN` 字段建索引
`Using join buffer`	多表 `JOIN` 未使用索引，用连接缓冲区存储中间结果	为关联字段（`JOIN ON`）建索引
`Impossible WHERE`	WHERE 条件永远为假	检查业务逻辑
`Range checked for each record (index map: N)`	索引失效，每行都需检查是否有合适索引，性能极差	修复索引失效问题（如函数、类型转换）
`Select tables optimized away`	优化器已优化掉表访问	最佳情况

关键关注点：Using filesort、Using temporary、Using join buffer 是性能杀手，必须优化。

12.1、过滤 / 索引相关

Using where

含义：对读取到的行还要再用 WHERE 条件过滤（说明索引筛选不完全或仍需额外检查）。
常见场景：索引无法完全匹配或查询包含非索引表达式（如 WHERE col + 1 = 5、类型不匹配）。
优化建议：检查索引是否能覆盖查询条件；避免隐式类型转换；把可索引的表达式提到列上或增加合适索引。 Using index（覆盖索引 / index-only scan）
含义：MySQL 使用了索引中的所有所需列，无需回表读取主数据行。通常表示查询只读取被索引的列（SELECT 列均为索引列）。
优点：I/O 少、很快。
优化建议：设计复合索引以覆盖频繁的 SELECT（注意最左前缀规则）。 Using where; Using index
含义：既有覆盖索引（不用回表），但仍需 WHERE 进一步过滤（例如索引列包含多项，但仍需额外条件判断）。
提示：仍然比回表好，但注意 filtered 比例（rows × filtered）。 Using index condition（Index Condition Pushdown，ICP）
含义：InnoDB 在索引层尝试尽量用 WHERE 条件过滤（在走索引时先过滤一部分），只有满足条件的索引条目才读回主表。显示 ICP 时通常还会有 Using where。
优点：减少回表次数（对范围查询、复合索引很有用）。
优化建议：确认 MySQL 版本支持 ICP（5.6+），设计索引使得能在索引层过滤尽量多。

12.2、排序 / 聚合 / 临时表

Using filesort

含义：需要额外排序（并非“文件”一定是磁盘，filesort 是 MySQL 的排序算法名称）。通常因为 ORDER BY 的列没有被合适索引覆盖，或者排序列顺序和索引不匹配。
性能影响：当排序量大或内存不足时会写临时文件到磁盘，严重影响性能。
优化建议： - 为 ORDER BY 的列建立合适复合索引（列顺序、升降序注意匹配）。 - 尽量 LIMIT 限制返回量。 - 调整 sort_buffer_size（谨慎，影响内存），或改写查询使之可利用索引顺序。 Using temporary
含义：查询在执行过程中使用了临时表（通常用于 GROUP BY、DISTINCT、复杂 UNION、某些 ORDER BY 场景）。
性能影响：如果临时表被创建为磁盘临时表，会非常慢；内存临时表容量也有限。
优化建议： - 优化 GROUP BY 语句或用覆盖索引避免临时表（Using index for group-by 的话可避免）。 - 增加 tmp_table_size / max_heap_table_size（注意内存消耗）。 - 将复杂查询拆成更小的子查询或预聚合（物化中间结果）。 Using index for group-by / Using index for order-by
含义：索引被用来避免临时表或 filesort，MySQL 能直接按索引顺序完成 GROUP BY 或 ORDER BY。这是理想状态。
优化建议：保证索引列顺序严格匹配 GROUP BY / ORDER BY 的列顺序和方向。

12.3、连接（join）相关

Using join buffer（或出现 join buffer 相关说明）

含义：MySQL 在做 join 时未用到合适索引，需要把一个表的行放入 join buffer，并对另一张表做全表扫描/探查。常见于没有在 join 列上建立索引。
性能影响：大量内存/IO，用 CPU 多，慢。
优化建议：在连接列上加索引（外键或经常 join 的列），避免全表扫描导致 join buffer 使用。也可调整 join_buffer_size（临时），但更推荐建索引。 Range checked for each record
含义：对于每一行要用范围条件再次检查索引（常见于复杂的索引合并、子查询或 OR 条件）。
优化建议：改写 SQL 避免会触发该行为的模式（例如把 OR 替换为 UNION，或给每个分支建立合适索引）。 Using where; Using join buffer 组合
表示既有额外过滤又使用了 join buffer。通常意味着需要索引优化。

12.4、特殊 / 优化器提示

Impossible WHERE noticed after reading const tables / Impossible WHERE

含义：优化器发现 WHERE 条件不可能满足（如矛盾条件），优化器短路掉了表的扫描（会在警告/EXPLAIN 中显示）。这说明查询结果为空或查询条件写错。
优化建议：检查逻辑错误，或如果这是预期，可让查询更早短路。 Select tables optimized away
含义：优化器把某些查询整体“优化掉”了（例如对某些 MyISAM 表的 COUNT(*)，或对只涉及常量表达式的 SELECT）。表示不需要读表。
提示：通常是好事，表示优化器很聪明。 Using union / Using union(…)
出现在 EXPLAIN 的 Extra 时，提示 UNION 被用（可能和临时表/filesort 一起出现）。优化 UNION 为 UNION ALL（如果允许重复）通常能省去去重开销。 Using MRR（Multi-Range Read）
含义：InnoDB 的多范围读取优化，批量读取多个范围以提高 IO 效率。一般为正面优化提示。
优化建议：无须变动，但可以通过适配索引使其更有效。

三、如何通过执行计划优化 SQL？

Using index → 覆盖索引（好）
Using where → 还有额外过滤（正常）
Using temporary / Using filesort → 可能很慢（警惕）
Using join buffer / Range checked for each record → join 或索引使用不理想（一般通过加索引改进）

判断索引是否生效：
- 若 type 为 ALL 且 key 为 NULL：缺少索引，为 WHERE 字段建索引；
- 若 possible_keys 有值但 key 为 NULL：索引失效，检查是否有函数、类型转换等问题。
优化排序和分组：
- 出现 Using filesort：让 ORDER BY 字段与索引顺序一致（如联合索引 (a,b)，ORDER BY a,b 可避免排序）；
- 出现 Using temporary：为 GROUP BY 字段建索引（如 GROUP BY a,b 建索引 (a,b)）。
优化多表关联：
- 出现 Using join buffer：为 JOIN ON 的关联字段建索引（如 a JOIN b ON a.id = b.a_id，为 b.a_id 建索引）；
- 确保驱动表是小表（rows 小的表），减少被驱动表的扫描次数。
利用覆盖索引：
- 若查询字段较少，将其加入索引（如 SELECT a,b FROM t WHERE a=? 建索引 (a,b)），触发 Using index。

EXPLAIN 仅提供估算值，EXPLAIN ANALYZE 会实际执行 SQL 并返回真实执行时间和扫描行数，更精准：

四、核心关注

type：判断访问类型（是否全表扫描）；
key：确认索引是否被使用；
rows：估算扫描行数（越小越好）；
Extra：识别 Using filesort、Using temporary 等性能问题。

⚡ 实战优化技巧​

快速定位问题的三步法：​

1. 看 type 字段决定优化方向​

2. Extra 字段的关键信号​

一、什么是执行计划​

二、EXPLAIN 输出字段详解​

1. id：查询序列号​

2. select_type：查询类型​

3. table：查询的表名​

4. partitions - 匹配的分区​

5. type - 访问类型（非常重要-核心字段）​

6. possible_keys - 可能使用的索引​

7. key - 实际使用的索引​

8. key_len - 使用的索引长度​

9. ref - 索引比较的列或常量​

10. rows：估算扫描行数​

11. filtered - 过滤百分比​

12. Extra：额外信息（非常重要-核心字段）​

12.1、过滤 / 索引相关​

12.2、排序 / 聚合 / 临时表​

12.3、连接（join）相关​

12.4、特殊 / 优化器提示​

三、如何通过执行计划优化 SQL？​

四、核心关注​