MySQL中Index Condition Pushdown(ICP)优化

在MySQL 5.6开始支持的一种根据索引进行查询的优化方式。之前的MySQL数据库版本不支持ICP，当进行索引查询是，首先根据索引来查找记录，然后在根据WHERE条件来过滤记录。在支持ICP后，MySQL数据库会在取出索引的同时，判断是否进行WHERE条件过滤，也就是将WHERE的部分过滤操作放在存储引擎层。在某些查询下，可以大大减少上层SQL层对记录的索取(fetch)，从而提高整体性能

ICP优化支持range,ref,eq_ref,ref_or_null类型的查询，当前支持MyISAM和InnoDB存储引擎。当优化器选择ICP优化时可以看到在Extra列中有Using index condition提示

加入某张表有联合索引(zip_code,last_name,firset_name)并且查询语句如下

SELECT * FROM people

WHERE zipcode='95054'

AND lastname like '%etrunia%'

AND address LIKE '%Main Street%';

 

对于上述语句，数据库可以通过索引来定位zipcode等于95054的记录，但是索引对where条件的lastname LIKE '%etrunia%' AND address like '%Main Street%'没有任何帮助。若不支持ICP优化，数据库需要先通过索引取出所有zipcode等于95054的记录，然后在过滤WHERE之后的两个条件

若支持ICP，在索引取出时，就会进行WHERE条件的过滤，然后再去获取记录。这将大大提高查询效率。当然，WHERE可以过滤的条件时要改索引可以覆盖的范围

SELECT * FROM salaries 
WHERE(from_date between ''1986-01-01' AND '1995-01-01')
AND(salary between 38000 and 40000);

可以看到Extra有Using index condition提示，但是为什么会在这里的idx_s索引会使用到ICP优化呢？因为这张表的主键是(emp_no,from_date)联合索引。所以idx_s索引中包含了from_date的数据，故可以采用此优化方式

对比同时开启MRR后的执行时间

可以看到同时开启MRR后性能提示400%