平时做技术实践时，很多问题不是概念不会，而是细节没串起来。拿“MySQL索引优化之EXPLAIN的零基础完全精通指南”来说，它看着像小点，放到项目里常会牵出环境、配置、兼容性和维护成本。下面按实际采用顺序，把思路、关键写法和容易踩坑的地方讲清楚，便于大家直接对照操作。
核心观点：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用法适合结合实际项目边做边理解。先抓住核心思路，再逐步补上细节和边界处理，最后效果会更稳定，也更容易复用。
