MySQL ICP 索引条件下推优化

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%';
复制

最后推荐个 MySQL 实战专栏

欢迎关注我的微信公众号: coderbee笔记，可以更及时回复你的讨论。