Index Condition Pushdown(ICP)

MySQL索引下推

MySQL 联合索引仅支持按「最左匹配」原则使用索引。在遇到范围查询情况时，会停止利用后面的索引字段。本文针对这一问题对联合索引原理进行说明，并引出官方对这种情况下的优化方案：ICP 索引下推机制。

注：联合索引，又称复合索引，英文为Multiple-Column Indexes或Composite Indexes

联合索引存储原理

index (a,b)的联合索引结构如上图（出自《MySQL技术内幕-InnoDB存储引擎》），观察到每个节点中均同时包含a,b两个字段信息，且字段a全局有序，字段b局部有序（仅在字段a值相同时，字段b是有序的）。

查询 SQL 如下：

select * from tableA where a>1 and b=2;

此时，查询过程如下：

• 根据a>1定位到最小值(2,1)，最大值为∞，这一过程，利用Index Filter

• 此时 a 有2、3两种情况，也就是二者都有可能包含b=2的记录

• 所以只能遍历a>1下的所有索引，才能确定b=2的记录项有哪些：

• 这一过程因为字段b局部有序，非全局有序，必须遍历所有索引

• 所以这一操作并未充分利用到索引的特性

• 这也就是我们常说的，最左匹配，遇到非等值判断时匹配停止

• 在未开启 ICP 的情况下，存储引擎并未利用索引上的b值进行判断。而是进行回表查询，将a>1的所有数据读出、返回至 MySQL Server 层，由 Server 通过Using where根据b=2筛选目标记录。
  

• 这一过程，利用Table Filter

可见，未开启 ICP 时，不能完全利用索引树及索引上存储的信息，而是愚笨的通过回表取数据（b 字段数据已在索引树上存在），通过Using where进行数据过滤。显著的改进：利用索引树上的字段信息，进行查找过滤，减少回表IO数据。

索引下推 ICP 原理

看下官方的这个例子：

联合索引包含zipcode, lastname, firstname三个字段，查询下述 SQL：

INDEX (zipcode, lastname, firstname)
SELECT * FROM people  
WHERE zipcode='95054'  
AND lastname LIKE '%etrunia%'  
AND address LIKE '%Main Street%';

• 
  在未开启 ICP 优化时，按「最左匹配原则」，上述查询 SQL 仅能利用联合索引中的zipcode 字段，剩余lastname和address仅能回表后通过 where 过滤数据。此时通过 explain 查看执行计划，Extra 字段为Using where。

• 我们通常认为，这种情况下并未充分发挥索引的利用率，因为所需三字段信息在索引树上均包含全部信息，但却仅利用了索引树上的zipcode一个字段；其他两个字段是通过回表后，过滤的数据。既然，索引树已包含全部三个字段的信息，那为何不直接通过索引树的三个字段来完成查询，避免发生回表呢？原因是，联合索引原理中只能保证局部有序，一旦有非等值查询后，后续字段无法直接通过索引树确定范围。

• 但是，索引树上已包含所有字段信息，是否可在回表前进行过滤，确定或减少回表数据范围呢？答案是可以的，这也是 MySQL 在5.6版本后加入的功能。

未开启 ICP 时的查询：

开启 ICP 时的查询，仅图中「红色箭头」+「对勾√」标识部分发生回表，减少 IO 操作：

开启 ICP，查看执行计划时，Extra 字段会有Using index condition说明，表示 ICP 生效，减少了回表数据。这会改善 IO 操作数，提升处理效率。

ICP 注意事项

• ICP 适用于range, ref, eq_ref, and ref_or_null的回表操作前过滤数据

• 支持InnoDB和MyISAM引擎

• ICP 目的是减少回表读操作数（reduce the number of full-row reads），从而减少 I/O 操作

• InnoDB中 ICP 仅支持二级索引，不支持聚簇索引。因InnoDB引擎下，聚簇索引的字段信息已全部在索引中。

• 指向子查询的查询条件无法利用 ICP

• 函数或触发器无法利用 ICP

参考文献

• MySQL: ICP, https://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html

• MariaDB: index-condition-pushdown, https://mariadb.com/kb/en/index-condition-pushdown/

• 【MySQL】性能优化之 Index Condition Pushdown, https://developer.aliyun.com/article/27751

• SQL中的where条件，在数据库中提取与应用浅析, http://www.hedengcheng.com/?p=577

• https://www.zkii.net/tech/php/2917.html