MySQL（2.1）索引下推（Index Condition Pushdown）

介绍

查询计划中，extra部分可能出现的提示

• Using index condition 会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用WHERE 子句中的其他条件去过滤这些数据行；
• 因为MySQL的架构原因，分成了server层和引擎层，才有所谓的“下推”的说法。所以ICP（Index Condition Pushdown，索引下推）其实就是实现了index filter技术，将原来的在server层进行的table filter中可以进行index filter的部分，在引擎层面使用index filter进行处理，不再需要回表进行table filter。
• Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种 优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。

where条件分类

要想深入理解 ICP 技术，必须先理解数据库是如何处理 where 中的条件的
对 where 中过滤条件的处理，根据索引使用情况分成了三种：

index key, index filter, table filter

1. index key
用于确定SQL查询在索引中的连续范围(起始范围+结束范围)的查询条件，被称之为Index Key。由于一个范围，至少包含一个起始与一个终止，因此Index Key也被拆分为Index First Key和Index LastKey，分别用于定位索引查找的起始，以及索引查询的终止条件。也就是说根据索引来确定扫描的范围。
2. index filter
在使用 index key 确定了起始范围和介绍范围之后，在此范围之内，还有一些记录不符合where 条件，如果这些条件可以使用索引进行过滤，那么就是 index filter。也就是说用索引来进行where条件过滤。
3. table filter
where 中的条件不能使用索引进行处理的，只能访问table，进行条件过滤了。

也就是说各种各样的 where 条件，在进行处理时，分成了上面三种情况，一种条件会使用索引确定扫描的范围；一种条件可以在索引中进行过滤；一种必须回表进行过滤；

如何确定哪些where条件分别是 index key, index filter, table filter？

• 在 MySQL5.6 之前，并不区分Index Filter与Table Filter，统统将Index First Key与Index LastKey范围内的索引记录，回表读取完整记录，然后返回给MySQL Server层进行过滤。
• 而在MySQL 5.6（包含）之后，Index Filter与Table Filter分离，Index Filter下降到InnoDB的索引层面进行过滤，减少了回表与返回MySQL Server层的记录交互开销，提高了SQL的执行效率。
• 所以所谓的 ICP 技术，其实就是 index filter 技术而已。只不过因为MySQL的架构原因，分成了server层和引擎层，才有所谓的“下推”的说法。所以ICP其实就是实现了index filter技术，将原来的在server层进行的table filter中可以进行index filter的部分，在引擎层面使用index filter进行处理，不再需要回表进行table filter。

不使用ICP扫描的过程

• storage层：
  只将满足index key条件的索引记录对应的整行记录取出，返回给server层。
• server 层：
  对返回的数据，使用后面的where条件过滤，直至返回最后一行。
  
  使用ICP扫描的过程
• storage层：
  首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤
  将满足的index filter条件的索引记录才去回表取出整行记录返回server层
  不满足index filter条件的索引记录丢弃，不回表、也不会返回server层
• server 层：
  对返回的数据，使用table filter条件做最后的过滤。
  
  使用前后的成本差别
• 使用ICP前，存储层多返回了需要被index filter过滤掉的整行记录。
• 使用ICP后，直接就去掉了不满足index filter条件的记录，省去了他们回表和传递到server层的成本。

ICP的使用条件

• 只能用于二级索引(secondary index)。
• explain显示的执行计划中type值（join 类型）为range、 ref、 eq_ref或者ref_or_null。
• 且查询需要访问表的整行数据，即不能直接通过二级索引的元组数据获得查询结果(索引覆盖)。
• 对于InnnoDB表，ICP仅用于二级索引。（ICP的目的是减少全行读取的次数，从而减少IO操作），对于innodb聚集索引，完整的记录已被读入到innodb缓冲区，在这种情况下，ICP不会减少io。
• ICP可以用于MyISAM和InnnoDB存储引擎，不支持分区表（5.7将会解决这个问题）