SQL执行计划

MySQL Explain执行计划 核心知识点

一、基础概述

1. 作用

EXPLAIN 用于模拟MySQL执行SQL，输出查询执行计划，分析索引使用、扫描方式、性能瓶颈，是慢SQL优化的核心工具。

2. 核心区别

  • EXPLAIN：仅预估执行计划，不真实执行SQL，无耗时、无IO开销

  • EXPLAIN ANALYZE（MySQL8.0.18+）：真实执行SQL，输出预估+真实耗时、扫描行数、循环次数

  • ⚠️ 线上使用EXPLAIN ANALYZE需谨慎，大SQL会真实执行并可能压垮数据库；轻量级SQL可酌情使用

3. 执行顺序规则

  • id值越大，越优先执行

  • id值相同，从上到下顺序执行

  • id为NULL：UNION合并结果集

二、Explain 10大字段详解（核心）

1. id 执行序号

标识当前行在查询中的执行顺序，是判断子查询、UNION、JOIN执行优先级的核心依据。

  • id值越大，越优先执行
  • id相同，从上到下顺序执行
  • id为NULL，表示该行为结果集（如UNION RESULT）

2. table 表名

标识当前行访问的是哪张表。

  • 普通表名：直接访问的物理表
  • <derivedN>：派生表（FROM子查询生成的临时表），N为对应的id
  • <unionM,N>：UNION合并结果集
  • <subqueryN>：物化子查询

3. select_type 查询类型

标识当前查询块的类型，高频知识点：

  • SIMPLE：简单查询，无子查询、无UNION

  • PRIMARY：最外层主查询

  • SUBQUERY：普通子查询，独立执行

  • DEPENDENT SUBQUERY：高危！相关子查询，外层每一行执行一次子查询，性能极差

  • DERIVED：派生表（FROM后嵌套子查询），会生成临时表

  • UNION：UNION中第二个及后续查询

  • UNION RESULT：UNION结果合并，id为NULL

4. type 访问类型（最重要指标）

SQL查询性能核心标尺，性能从优到劣排序：

system > const > eq_ref > ref > ref_or_null > range > index_merge > index > ALL

  • system/const：最优，主键/唯一索引常量匹配，仅1行数据

  • eq_ref：JOIN查询主键/唯一索引匹配，每行唯一匹配

  • ref：普通二级索引等值查询，常规最优业务状态

  • ref_or_null：索引等值查询+NULL值匹配

  • range：范围查询（>、<、BETWEEN、IN），走索引范围扫描

  • index_merge：索引合并，多个单列索引合并结果，性能弱于联合索引

  • index：索引全扫描，遍历整棵索引树，大表性能差

  • ALL：最差，全表扫描，大表绝对禁止

✅ 优化目标：业务SQL尽量保证 ref/range，杜绝大表 index/ALL

5. possible_keys / key / ref

  • possible_keys：理论上可以使用的索引

  • key：实际最终使用的索引（NULL代表未走索引）

  • ref：显示哪些列或常量被用于和索引进行比较（如 const、字段名等）

高频问题：possible_keys有值、key为NULL的5大原因

  1. 索引失效：索引列函数运算、隐式类型转换、LIKE前缀通配、OR条件失效

  2. 数据占比过高：满足条件数据占比较大（经验参考约30%，实际阈值受表大小、Buffer Pool、数据分布等因素影响），优化器认为全表扫描代价更低

  3. 统计信息失真：大批量增删改后，采样数据不准，优化器误判

  4. 索引选择性极差：如status状态字段，区分度极低

  5. SQL强制忽略索引：ignore index

6. key_len 索引长度

作用：判断联合索引最左前缀使用到第几列，仅统计索引条件字段，与SELECT字段无关。

utf8mb4 计算规则：

  • int：4字节；not null无额外开销，null+1字节
  • bigint：8字节；not null无额外开销，null+1字节
  • char(n)：n*4字节；not null无额外开销，null+1字节
  • varchar(n)：n*4 + 2（2字节长度开销），null+1字节
  • date：3字节；datetime：5字节（MySQL 5.6+）；timestamp：4字节
  • 所有类型：可为NULL字段额外+1字节

7. rows

优化器预估扫描行数，非真实行数，数值越大性能越差。

失真原因：统计信息采样偏差、数据倾斜、大批量数据变更未更新统计。

修复命令：analyze table 表名;

8. filtered 过滤比例

表示经过WHERE条件过滤后，预估剩余行数的百分比（1-100）。

  • 值越接近100，说明过滤效率越高，扫描的行数大部分都满足条件
  • 值很低时，说明大量行被过滤掉，可能需要优化索引或SQL写法

9. Extra 额外信息（坑点最多）

✅ 优质标记

  • Using index：覆盖索引，无需回表，性能最优

  • Using index condition（ICP）：索引条件下推，存储引擎层过滤数据，减少回表

❌ 高危慢SQL标记

  • Using filesort：文件排序，未利用索引有序性，需额外排序

  • Using temporary：创建临时表，常见于GROUP BY、DISTINCT、UNION

  • Using join buffer：JOIN无索引，走块嵌套循环或Hash Join，性能差

⚠️ 易错标记

  • Using where：不代表索引失效！仅表示MySQL服务层二次过滤数据。只有 type=ALL+Using where 才是全表扫描低效。

三、执行计划进阶核心知识点

1. ICP 索引条件下推

MySQL5.6+默认开启，核心作用：减少回表次数

原理：将索引列的WHERE过滤条件，下推到InnoDB存储引擎层执行，提前过滤无效数据，避免所有匹配索引前缀的数据都回表查询。

2. 索引合并 index_merge

原理：没有合适联合索引，MySQL 就帮你把多个单列索引拼起来用

结论：出现 index_merge 通常意味着索引设计不合理，缺少合适的联合索引

场景：单条SQL使用多个单列索引，合并结果集（常见OR查询）。

缺点：多次索引扫描、额外合并去重开销大、无法利用索引有序性、性能远不如联合索引。应优先建立联合索引，而非依赖索引合并。

3. Hash Join 特性（MySQL8.0+）

无索引大表JOIN替代块嵌套循环，性能大幅提升。

说明：普通EXPLAIN的Extra列会显示Hash Join标记（如Using join buffer (hash join)），可识别算法类型；EXPLAIN ANALYZE在此基础上额外提供真实耗时、实际行数等运行时统计信息。

4. 优化器重写规则

用户编写的SQL不等于实际执行SQL，优化器会自动改写：

  • IN子查询转JOIN

  • 派生表合并（derived_merge）

  • OUTER JOIN转INNER JOIN

  • 常量折叠、条件简化

查看真实执行SQL：EXPLAIN后执行 show warnings;

5. Explain 致命局限性（高频）

  • 仅为预估计划，rows为估算值，统计信息不准会误导判断

  • 无法识别锁、事务、死锁、长事务、IO并发问题（很多SQL计划完美，并发卡死）

  • 对LIMIT截断优化效果体现有限（仅部分场景可通过Extra信息间接判断）

四、常见慢SQL执行计划特征+优化方案

慢SQL现象 执行计划特征 优化方案
全表扫描 type=ALL、key=NULL、rows巨大 建立合适索引，修复索引失效写法，更新统计信息
排序慢 Extra: Using filesort 建立 WHERE+ORDER BY 联合索引，利用索引有序性
分组/去重慢 Extra: Using temporary 索引覆盖分组字段，拆分大SQL，减少DISTINCT
JOIN关联慢 Extra: Using join buffer 关联字段建索引，小表驱动大表，减少JOIN表数量
大量回表查询 无Using index，仅普通ref/range 建立覆盖索引，禁止SELECT *

五、重点知识点

1. Using where 代表索引失效吗？

不代表。Using where 是MySQL服务层二次过滤数据。只有同时满足 type=ALL + Using where 才是全表扫描、索引失效。正常走索引的SQL也会出现Using where，属于正常现象。

2. key_len 的作用和计算方式？

作用：判断联合索引最左前缀的使用列数。utf8mb4下int占4字节，bigint占8字节，char(n)占4n字节，varchar(n)占4n+2字节，可为NULL字段额外+1字节。

3. explain的rows为什么不准？怎么修复？

rows是优化器采样估算值，非真实行数。大批量增删改、数据倾斜会导致统计信息失真。执行 analyze table 表名 更新统计信息即可修复。

4. DEPENDENT SUBQUERY 有什么问题？

属于相关子查询，外层查询每遍历一行，子查询就执行一次，大表场景性能爆炸。新版本优化器会自动改写为JOIN，推荐手动改写JOIN替代子查询。

5. ICP索引条件下推的作用？

将索引列的过滤条件下推到存储引擎层，在索引内部提前过滤无效数据，大幅减少回表查询次数，提升查询性能。

6. explain和explain analyze的区别？线上能用吗？

explain仅预估计划、不执行SQL；explain analyze真实执行SQL，输出真实耗时和行数。线上对大SQL禁止使用，会真实执行并可能压垮数据库；轻量级SQL可酌情使用，仅测试环境使用为佳。

7. 索引合并和联合索引哪个好？

优先使用联合索引。索引合并是多个单列索引拼接结果，开销大、效率低，是优化器兜底方案，不如联合索引高效稳定。

8. explain能排查锁、死锁问题吗？

不能。explain仅分析SQL执行逻辑和索引使用，无法感知事务、锁等待、死锁、IO并发问题，并发性能问题需要通过 show engine innodb status 排查。

六、慢SQL标准分析流程（模板）

1. 从慢查询日志抓取慢SQL，确认耗时、扫描行数；

2. 使用EXPLAIN分析执行计划，通过id确认执行顺序；

3. 优先查看type，杜绝大表index/ALL全表扫描；

4. 核对key字段，确认索引是否生效，排查索引失效、优化器选错索引问题；

5. 通过key_len判断联合索引是否完全利用；

6. 查看rows预估扫描量和filtered过滤比例，判断数据扫描效率；

7. 重点排查Extra中的filesort、temporary、join buffer等高危标记；

8. 执行show warnings查看优化器重写后的真实SQL；

9. 结合锁、事务、统计信息排查隐性性能问题；

10. 优化索引、改写SQL后，重新explain验证效果。

posted @ 2026-09-03 22:47  灰马非马  阅读(15)  评论(0)    收藏  举报