我要评论前言
**索引下推（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
对于 innodb，icp 主要针对二级索引；官方文档也说明，它适用于 range、ref、eq_ref 和 ref_or_null 等访问方式，并且前提是查询需要读取完整表行
到此这篇关于mysql数据库索引下推的文章就介绍到这了,更多相关mysql索引下推内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论