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

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

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据