Mysql：索引下推
**索引下推Index Condition Pushdown简称 ICP**是一种减少回表次数的优化它的核心思想是在读取完整数据行之前先利用索引中已有的列判断一部分WHERE条件只有满足条件的索引记录才继续回表读取完整行MySQL 官方文档将它描述为存储引擎先检查索引元组只有索引条件满足时才读取完整表记录一、 没有索引下推时假设有表CREATE TABLE people ( id BIGINT PRIMARY KEY, zipcode CHAR(5), lastname VARCHAR(50), firstname VARCHAR(50), address VARCHAR(255), KEY idx_zip_last_first (zipcode, lastname, firstname) );执行SELECT * FROM people WHERE zipcode 95054 AND lastname LIKE %etrunia% AND address LIKE %Main Street%;联合索引是(zipcode, lastname, firstname)没有 ICP 时大致过程是1. 使用索引找到 zipcode 95054 的索引记录 2. 对每一条索引记录回表读取完整行 3. 回到 MySQL Server 层判断 lastname LIKE %etrunia% address LIKE %Main Street% 4. 不满足条件的行丢弃过程可以表示为索引记录 1 → 回表读完整行 → 判断条件 → 丢弃 索引记录 2 → 回表读完整行 → 判断条件 → 丢弃 索引记录 3 → 回表读完整行 → 判断条件 → 保留问题在于很多记录最终会被过滤掉但已经发生了回表和完整行读取二、有索引下推时启用 ICP 后过程变成1. 使用索引找到 zipcode 95054 的索引记录 2. 直接从索引中判断 lastname LIKE %etrunia% 3. 不满足的索引记录直接跳过 4. 满足的记录才回表读取完整行 5. 回表后再判断 address LIKE %Main Street%过程变成索引记录 1 → 判断 lastname → 不满足 → 不回表 索引记录 2 → 判断 lastname → 不满足 → 不回表 索引记录 3 → 判断 lastname → 满足 → 回表 → 判断 address所以 ICP 主要减少的是回表次数完整数据行读取次数存储引擎与 Server 层之间的数据传递相关磁盘 I/O三、为什么lastname可以被下推因为lastname在联合索引中(zipcode, lastname, firstname)虽然查询条件lastname LIKE %etrunia%因为以%开头通常不能用来直接定位 B 树的起始范围但索引记录中确实包含lastname所以可以先扫描 zipcode 95054 的索引记录 再在索引内部判断 lastname这就是 ICP 的典型场景某个条件不能帮助缩小索引扫描范围 但可以帮助减少后续回表。而address不在索引中(zipcode, lastname, firstname)因此必须回表读取完整行后才能判断address LIKE %Main Street%四、 “索引条件”和“下推条件”的区别还是以索引(zipcode, lastname, firstname)为例。WHERE zipcode 95054 AND lastname LIKE %etrunia% AND address LIKE %Main Street%大致可以这样理解条件作用zipcode 95054用于定位和扫描索引范围lastname LIKE %etrunia%可以在索引中判断适合索引下推address LIKE %Main Street%不在索引中只能回表后判断也就是说索引访问条件 决定“扫描哪些索引记录” 索引下推条件 决定“哪些索引记录值得回表”两者不是完全一回事五、ICP 和覆盖索引的区别覆盖索引如果查询需要的字段都在索引中CREATE INDEX idx_zip_last_first ON people(zipcode, lastname, firstname); SELECT zipcode, lastname, firstname FROM people WHERE zipcode 95054 AND lastname LIKE %etrunia%;数据库可以直接从索引返回结果不需要回表这叫覆盖索引索引 → 直接返回索引下推如果查询还需要索引之外的列SELECT * FROM people WHERE zipcode 95054 AND lastname LIKE %etrunia% AND address LIKE %Main Street%;则仍然需要回表但 ICP 会尽量减少回表数量索引过滤 → 满足条件的记录回表可以简单记忆覆盖索引完全不回表 索引下推尽量少回表六、 什么情况下收益明显ICP 的收益通常在以下场景比较明显使用的是 InnoDB 二级索引索引扫描出来的候选记录较多额外过滤条件的选择性较高完整行比较宽读取成本较高回表需要较多随机 I/O例如索引扫描 100 万条 最终只有 1000 条符合 lastname 条件没有 ICP可能需要回表 100 万次有 ICP先在索引中筛选可能只回表约 1000 次实际次数由执行计划和数据分布决定但优化方向就是减少无效回表七、 什么情况下不能使用ICP 不是所有条件都能下推。常见限制包括条件使用了不在索引中的列条件包含子查询条件调用存储函数某些触发条件无法下推查询不需要读取完整行时ICP 本身没有太大意义InnoDB 聚簇索引通常不使用 ICP因为读取聚簇索引记录时完整行已经被读入 Buffer Pool对于 InnoDBICP 主要针对二级索引官方文档也说明它适用于range、ref、eq_ref和ref_or_null等访问方式并且前提是查询需要读取完整表行
)
