索引下推(Index Condition Pushdown,简称ICP)是MySQL5.6中的新技术,是一种数据库查询优化技术,它利用了数据库引擎中的索引和过滤条件,将部分过滤工作下推到存储引擎层面进行处理,从而减少不必要的数据读取和传输。
在传统的查询执行过程中,数据库引擎首先根据索引定位到符合过滤条件的数据行,并将这些数据行读取到内存中,然后再进一步进行过滤操作。而索引下推则再这一步骤中尽可能将过滤操作下推到存储引擎层面,避免将不符合条件的数据行读取到内存中,减少了IO次数。
1.1 使用索引下推前后的扫描过程
在不使用ICP索引扫描的过程:
storage层:只将满足index key条件的索引记录对应的整行记录取出,返回给server层
server 层:对返回的数据,使用后面的where条件过滤,直至返回最后一行。
使用ICP扫描的过程:
- storage层:
首先将index key条件满足的索引记录区间确定,然后在索引上使用index filter进行过滤。将满足的index filter条件的索引记录才去回表取出整行记录返回server层。不满足index filter条件的索引记录丢弃,不回表、也不会返回server层。
- server 层:
对返回的数据,使用table filter条件做最后的过滤。
使用前后的成本差别
使用前,存储层多返回了需要被index filter过滤掉的整行记录
使用ICP后,直接就去掉了不满足index filter条件的记录,省去了他们回表和传递到server层的成本。
ICP的 加速效果 取决于在存储引擎内通过 ICP筛选 掉的数据的比例。
1.2 ICP的使用条件
① 只能用于二级索引(secondary index)
②explain显示的执行计划中type值(join 类型)为 range 、 ref 、 eq_ref 或者 ref_or_null 。
③ 并非全部where条件都可以用ICP筛选,如果where条件的字段不在索引列中,还是要读取整表的记录到server端做where过滤。
④ ICP可以用于MyISAM和InnnoDB存储引擎
⑤ MySQL 5.6版本的不支持分区表的ICP功能,5.7版本的开始支持。
⑥ 当SQL使用覆盖索引时,不支持ICP优化方法