ICP 优化的全称是 Index Condition Pushdown Optimization 。
ICP 优化适用于 MySQL 利用索引从表里检索数据的场景。
ICP 适用的场景
- ICP 用于访问方法是
range/ref/eq_ref/ref_or_null，且需要访问表的完整行记录。 - ICP适用于 InnoDB 和 MyISAM 的表，包括分区的表。
- 对于 InnoDB 表，ICP只适用于二级索引。ICP 的目标是减少访问表的完整行的读数量从而减少 I/O 操作。对于 InnoDB 的聚簇索引，完整的记录已经读进 InnoDB 的缓存，使用 ICP 不能减少 I/O 。
- ICP 不支持建立在虚拟列上的二级索引。InnoDB 支持在虚拟列上建立二级索引。
- 引用子查询、存储函数的条件没法下推。
- Triggered conditions 也没法下推。
ICP 原理
以 InnoDB 表为例。
在不启用 ICP 的情况下利用二级索引查找数据的过程：
1. 用二级索引查找数据的主键；
2. 用主键回表读取完整的行记录；
3. 利用 where 语句的条件对行记录进行过滤。
启用 ICP 的情况下利用二级索引查找数据的过程为：
1. 用二级索引查找数据的主键；
2. 如果二级索引记录的元组里的列出现在 where 条件里，那么对元组进行过滤；
3. 对索引元组的主键回表读取完整的行记录；
4. 利用 where 语句的剩余条件对行记录进行过滤；
ICP 适用的一个隐含前提是二级索引必须是组合索引、且在使用索引进行扫描时只能采用最左前缀匹配原则。组合索引后面的列出现在 where 条件里，因此可以先过滤索引元组、从而减少回表读的数量。
举例
对于组合索引 INDEX (zipcode, lastname, firstname)，下面的 SQL 根据最左前缀原则，只能使用到索引的第一列 zipcode，索引的另一列 lastname 出现在 where 条件里，可以采用 ICP 对索引的元组进行过滤，即应用 lastname LIKE '%etrunia%' 条件；然后再回表读取完成的行记录，再对行记录应用 address LIKE '%Main Street%' 条件。
SELECT * FROM people
WHERE zipcode='95054'
AND lastname LIKE '%etrunia%'
AND address LIKE '%Main Street%';
欢迎关注我的微信公众号: coderbee笔记，可以更及时回复你的讨论。
