【SQL】索引条件下推ICP

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a'; 	# Using index condition

表中有二级索引key1，假设有100万条数据，先用索引过滤 key1>‘z’ 的，假设过滤完是100条，再通过每一条的id回表找到完整记录，与 ‘%a’ 一一比对。但查询优化器对其做了优化，我们可以看到，这里and前后的字段都是key1，于是先过滤完100条数据，直接在索引中对这100条进行 and 后的条件比对，在索引中过滤一遍；假设过滤完后剩10条数据满足，再回表，这时只要回表10次，性能比之前好很多，也减少了随机io。

EXPLAIN SELECT * FROM people
WHERE zipcode = '000001'
AND lastname LIKE '%张%'
AND address LIKE '%北京市%';	# Using index condition, Using where

有索引 zip_last_first(zipcode,lastname,firstname)，对于where后的zipcode，使用索引过滤数据，再过滤like，由于最后的字段address没有索引，所以回表，找满足最后条件的记录。减少了回表次数。using where 就是address的筛选。
注意，要有回表行为，才能构成索引下推

关闭索引下推：set optimizer_switch = 'index_condition_pushdown = off';
打开索引下推：set optimizer_switch = 'index_condition_pushdown = on';
也可以在sql中直接写，不使用索引下推：
select /*+ no_icp (people) */ * from people where zipcode = '00001' and lastname like '%张%';

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优化方法。