Explain & 索引调优 本次实操复习知识点汇总

一、explain 核心字段重点

1. type（访问类型，性能从优到劣）
   system > const > eq_ref > ref > range > index > ALL

• ALL：全表扫描，需要优化
• ref：等值匹配普通索引
• range：范围查询（> < between in）

2. key：本次查询实际使用的索引，NULL = 没用到索引
3. key_len：索引检索阶段真正用到的索引字节长度
   • 可用来反推复合索引命中几列；前提：知道字段类型、是否允许 NULL（null 字段额外占 1 字节标记位）
   • ⚠️ 只统计索引查找使用的字段；where 里用来过滤、但没有参与索引定位的字段不计入 key_len
4. rows：预估扫描行数，越小越好
5. Extra（重点标记）
   • Using index：覆盖索引，查询需要的所有字段都在二级索引，不需要回表，性能优秀
   • Using index condition(ICP索引条件下推)：把部分 where 过滤条件下推到存储引擎，在索引页过滤数据，减少回表次数，但依旧可能回表，≠覆盖索引
   • Using filesort：额外文件排序，没有利用索引自带有序性，性能差，需要优化
   • Using temporary：创建临时表，常见 group by，性能差
   • Using where：数据拿到 server 层之后再过滤

二、复合索引：最左匹配原则

索引idx(a,b,c)

1. 必须从索引最左侧列开始连续匹配；跳过前面列，后面列无法用于索引定位
   • where a=1 ✔ 使用 a
   • where a=1 and b=2 ✔ 使用 a+b
   • where b=2 ❌ 跳过 a，无法走索引
   • where a=1 and c=3 ✔ 可以走索引，但只用到 a 列，c 只能做数据过滤，不能索引定位
2. MySQL 优化器会自动调整 where 条件书写顺序，不是 SQL 书写顺序，是索引定义顺序决定最左前缀
3. 范围截断规则：复合索引中，一旦某个字段是范围条件（> < between），该字段之后的所有列无法用于索引定位

索引设计口诀：等值条件放前面，范围条件放最后

三、索引失效高频场景

1. 在索引列做函数、运算；
2. 隐式类型转换：索引字段类型和传入参数类型不一致；
3. like %前缀通配符；like 'xxx%'可以走 range 索引；
4. or 条件中，or 一侧字段没有索引，会导致索引失效；可改用 union all 优化

四、覆盖索引

1. 定义：二级索引里包含 select、where 需要的所有字段，不需要回主键索引查数据
2. 触发：Extra 出现Using index
3. 好处：省去回表 IO，性能提升明显

五、Join 关联查询 explain 要点

1. explain 多行输出，一行对应一张表
2. 优化原则：小表驱动大表；被驱动表的关联字段一定要建索引

六、filesort 产生原因

排序字段不在索引有序序列内，MySQL 需要单独排序。优化思路：把排序字段加入索引，利用索引天然有序，避免 filesort

七、关键易混区分

• Using index：覆盖索引，无回表
• Using index condition：ICP 索引条件下推，减少回表，但仍可能回表，不属于覆盖索引