我要评论核心观点：explain 是索引优化的"眼睛"。不会看 explain，索引知识就只能靠背——比如"联合索引用了几列"，背下来也验证不了。
一、explain 是什么
一句话：它不执行 sql，只让优化器把"打算怎么执行这条 sql"打印出来。
相当于执行前的"作战计划"。
能解决什么问题：
- 验证索引有没有生效
- 看联合索引实际用到了第几列
- 找出全表扫描、回表、额外排序、临时表
- 对比优化前后，量化收益
基本用法：
explain select user_id, amount from orders where user_id = 13;
四种输出格式：
| 格式 | 命令 | 用途 |
|---|---|---|
| 传统表格 | explain | 日常快速看 |
| json | explain format=json | 看成本估算 cost，信息最全 |
| tree | explain format=tree | 树状展示执行顺序 |
| 真实执行 | explain analyze | 真跑一遍，给实际耗时（8.0.18+） |
二、看 explain 的正确顺序（30 秒定位问题）
不要从左往右逐字段读，按这个顺序扫：
- type 走没走索引？ ← 先看有没有大问题
- key 走的哪个索引？
- key_len 用了几列？ ← 联合索引的关键
- rows×filtered 大概要处理多少行？
- extra 有没有回表/排序/临时表？
口诀：先看 type 定生死，再看 key_len 定列数，最后看 extra 定细节。
三、逐字段详解
先给一张完整字段总表（8.0 传统格式）：
| 字段 | 含义 |
|---|---|
id | 查询序号，越大越先执行；相同则从上往下 |
select_type | 查询类型：simple / primary / subquery / derived / union |
table | 正在访问的表 |
partitions | 命中哪些分区（分区表才有意义） |
type | 访问类型（索引使用等级）★ |
possible_keys | 候选索引 |
key | 实际使用的索引 |
key_len | 实际用到的索引字节数 ★★ |
ref | 与索引列比较的对象（const / 列名） |
rows | 预估扫描行数 |
filtered | 过滤后剩余百分比 |
extra | 额外执行信息 ★ |
3.1 select_type
| 值 | 含义 |
|---|---|
simple | 简单查询，不含子查询 / union |
primary | 最外层查询 |
subquery | 子查询（不在 from 里） |
derived | from 里的子查询（派生表） |
union | union 中第二个及以后的 select |
union result | union 的结果集 |
出现
derived说明"from 里套了子查询"，mysql 会建临时表——能用 join 改写就改写。
3.2 type：访问类型（等级从好到坏）★
| type | 含义 | 典型场景 |
|---|---|---|
system | 表只有一行 | 系统表 |
const | 主键 / 唯一索引等值，最多 1 行 | where id = 1 |
eq_ref | join 时被驱动表用主键 / 唯一索引 | 关联字段是主键 |
ref | 普通索引等值查询 | where user_id = 13 |
range | 索引范围扫描 | between / > / < / in |
index | 扫整棵索引树 | 覆盖索引但全扫 |
all | 全表扫描 | 无索引 / 索引失效 |
记忆要点：
ref/range是目标all必须优化index是"伪装成索引的全表扫描"——扫的是索引树，但仍遍历所有节点，数据量大时一样慢
关键区分：index 和 all 都是全扫，区别只是"扫索引树"还是"扫数据表"。若 extra = using index，说明走了覆盖索引，只需扫索引即可返回，比 all 好，但仍不如 ref / range。
3.3 key_len：联合索引"用了几列"的唯一证据 ★★
定义：这条 sql 实际用到的索引列的总字节数。
为什么重要：type=ref 只说明"走了索引"，但联合索引 (a,b,c) 走到了第几列？只有 key_len 能回答。这是判断"最左前缀用到哪、后面列有没有白建"的唯一量化依据。
计算公式（三个加项）
| 加项 | 规则 |
|---|---|
| 类型基础长度 | 见下表 |
| 变长类型 | varchar 额外 +2（存长度） |
| 可空列 | 额外 +1 |
类型基础长度：
| 类型 | 字节 |
|---|---|
tinyint | 1 |
smallint | 2 |
mediumint | 3 |
int | 4 |
bigint | 8 |
float | 4 |
double | 8 |
date | 3 |
time | 3 |
datetime | 5（5.6+） |
timestamp | 4 |
char(n) | n × 字符集单字符字节数 |
varchar(n) | n × 字符集单字符字节数 + 2 |
字符集单字符最大字节：utf8mb4 = 4，utf8(utf8mb3) = 3，gbk = 2，latin1 = 1。
手算示例
create table t ( a int not null, b int null, c varchar(10) not null, index idx_abc (a, b, c) ) engine=innodb default charset=utf8mb4;
逐列算：
| 列 | 计算过程 | 字节 |
|---|---|---|
| a | int not null | 4 |
| b | int null → 4 + 1 | 5 |
| c | varchar(10) utf8mb4 → 10×4 + 2 | 42 |
所以：
| 实际用到的列 | key_len |
|---|---|
| 只用 a | 4 |
| 用 a + b | 9 |
| 用 a + b + c | 51 |
反向读法：看到 key_len = 4 → 只用了 a；看到 9 → 用了 a、b；看到 51 → 三列全用上。
实战读法（key_len 的真正价值）
拿到一条 sql，先算"全部列都用上应该是多少"，再对比实际 key_len，差值就是"没用上的列"。
注意：key_len 只反映"用于定位（索引查找）的列"。范围查询之后的列不参与定位（key_len 不增加），但如果满足 icp 条件，仍可用于索引层过滤——这时 extra 会出现 using index condition。所以"key_len 没变"不代表后面的列完全没用。
3.4 key / possible_keys
possible_keys：候选索引（优化器认为"可能用得上"的）key：最终选中的索引
三种典型情况：
| 现象 | 含义 | 处理 |
|---|---|---|
possible_keys 有值，key 有值 | 正常用了索引 | 检查是不是你期望的那个 |
possible_keys 有值，key = null | 优化器算完成本后主动放弃 | 不是失效！ 通常是"回表代价 > 全表扫描"，考虑覆盖索引 |
possible_keys = null | 没有可用索引 | 缺索引，或索引失效（函数 / 类型转换） |
3.5 ref
显示"索引列和谁比较"：
const：和常量比（等值查询）库名.表名.列名：和另一张表的列比（join）null：不是等值比较
3.6 rows / filtered
rows：预估扫描行数（基于统计信息，不是精确值）filtered：扫描后剩余百分比
真实代价 ≈ rows × filtered%——这才是要处理的有效行数。
rows 是估算值，可能严重失真。统计信息过期时，explain 会明显偏离实际——这时用 explain analyze 看真实值，或先 analyze table 表名; 刷新统计。
3.7 extra：信息量最大的一列 ★
| extra 值 | 含义 | 好坏 |
|---|---|---|
using index | 覆盖索引，免回表 | 好 |
using index condition | 索引下推 icp（5.6+） | 好 |
using mrr | 多范围读优化 | 好 |
using index for group-by | 分组也走索引 | 好 |
using where | 拿到数据后还要过滤 | 中性 |
using join buffer | join 无索引，用内存缓冲 | 差 |
using filesort | 需要额外排序 | 差 |
using temporary | 需要建临时表 | 差 |
null | 没有额外信息 | — |
重点解释三个"坏"值：
using filesort：索引不能提供 order by 需要的顺序，需额外排序。解决：把排序列放进联合索引（条件列之后），且排序方向一致（8.0 可用降序索引解决混排）。using temporary：常见于 group by / distinct / union 无合适索引。解决：给分组列建索引。using join buffer：被驱动表的关联列没索引。解决：给关联列建索引。
using index 和 using where 可以同时出现：前者说"索引覆盖了需要的列"，后者说"仍有过滤条件"。这是常见组合，不是矛盾。
四、实战：用 explain 回答"联合索引用了几列"
场景：a int not null, b int not null, c int not null，联合索引 (a, b, c)。三列都是 int not null，每列 4 字节。
四条 sql 的 explain 结果对比：
| # | 查询条件 | type | key | key_len | extra | 生效列 |
|---|---|---|---|---|---|---|
| 1 | a=1 and b=2 and c=3 | ref | idx_abc | 12 | — | 3 列 |
| 2 | a=1 and c=3 | ref | idx_abc | 4 | — | 只用 a |
| 3 | b=2 and c=3 | all | null | null | using where | 0 列 |
| 4 | a between 1 and 5 and b=2 and c=3 | range | idx_abc | 4 | using index condition | 只用 a（b、c 仅过滤） |
从这张表能读出三条核心结论：
- key_len 从 12 掉到 4 = 从"3 列"掉到"1 列"。这是最左前缀"跳列截断"的直接证据。
- 第 3 条
type = all、key = null= 跳过最左列，整个索引作废（不是"部分生效"）。 - 第 4 条 key_len = 4 但
extra = using index condition= 范围查询让 a 之后的 b、c 无法用于定位，但 icp 让它们在索引层完成过滤——这就是"范围后失效 ≠ 完全没用"的实证。
动手练习：建一张这样的表，把上面 4 条 sql 各 explain 一次，亲眼看 key_len 从 12 变 4、type 从 ref 变 all。explain 是"看"会的，不是"读"会的。
五、进阶用法
5.1 explain format=json（看成本）
explain format=json select ...;
关键看 cost_info：
"cost_info": {
"query_cost": "12.35",
"read_cost": "4.20",
"eval_cost": "0.85"
}对比两条 sql 的 query_cost，能量化"优化到底有没有用"。也能看到优化器为什么选某个索引（成本估算过程）。
5.2 explain analyze（8.0.18+，看真实耗时）
explain analyze select ...;
会真正执行 sql，输出每个步骤的实际耗时和实际行数：
-> index lookup on orders using idx_user (user_id=13) (cost=0.35 rows=1) (actual time=0.05..0.06 rows=1 loops=1)
关键对比：rows=1（估算）vs rows=1（实际）——估算和实际差距大，说明统计信息失真，这是优化器选错索引的常见原因。
注意：explain analyze 会真的执行 sql，不要在写库 / 大表上乱跑。
5.3 explain format=tree（8.0+）
树状展示执行顺序，直观看到"先做什么、后做什么"。
六、常见"索引失效"场景速查（配合 explain 验证）
| 场景 | explain 表现 | 解法 |
|---|---|---|
条件列用函数 where year(create_time)=2026 | type=all，key=null | 改成范围 create_time >= '2026-01-01' |
隐式类型转换 where phone=13800138000（phone 是 varchar） | type=all | 加引号 phone='13800138000' |
前导模糊 like '%王' | type=all | 改后缀匹配，或上 es |
| or 两侧有一侧无索引 | type=all | 给两侧都建索引，或改 union |
跳最左列 where b=2（索引是 a,b） | type=all | 补最左列，或另建索引 |
| 范围查询之后的列 | key_len 不增加 | 把等值列放前面 |
索引列参与运算 where id+1=5 | type=all | 改成 where id=4 |
注意：!= / not in / is not null 不必然失效——数据量占比小时优化器可能仍走索引。以 explain 实测为准，不要背结论。
七、面试速记卡
| # | 核心要点 | 一句话记忆 |
|---|---|---|
| 1 | 看 explain 的顺序 | type → key → key_len → rows → extra |
| 2 | type 目标值 | ref / range 是目标，all 要修，index 是伪装的全扫 |
| 3 | key_len 作用 | 判断联合索引实际用了几列 |
| 4 | key_len 算法 | 类型字节 +（varchar +2）+（可空 +1） |
| 5 | possible_keys 有值但 key=null | 优化器算完成本主动放弃，不是失效 |
| 6 | extra 三个坏值 | filesort（排序）、temporary（临时表）、join buffer（无索引） |
| 7 | 覆盖索引 | extra = using index，免回表 |
| 8 | 索引下推 | extra = using index condition，范围后的列仍可过滤 |
| 9 | 估算失真怎么办 | analyze table 刷新统计，或 explain analyze 看实际 |
| 10 | 核心原则 | 索引失效不要背结论，以 explain 实测为准 |
以上就是mysql索引优化之explain的零基础完全精通指南的详细内容，更多关于mysql explain用法的资料请关注代码网其它相关文章！
发表评论