① 什么是ICP

Index Condition Pushdown(ICP)是MySQL5.6中新特性，是一种在存储引擎层使用索引过滤数据的优化方式。

MySQL的执行计划（Explain）中，我们有时会在Extra列中看到 Using index condition。其就是表示索引条件下推（ICP）。

如果没有ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给MySQL服务器，由MySQL服务器评估where后面的条件是否保留行。

启用ICP后，如果部分 where 条件可以仅使用索引中的列进行筛选，则MySQL服务器会把这部分 where 条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。

  • 好处：ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。
  • 但是，ICP的 加速效果 取决于在存储引擎内通过ICP筛选掉的数据的比例。

② ICP的开启和关闭

默认情况下启用索引条件下推。可以通过设置系统变量 optimizer_switch 来控制index_condition_pushdown：

index_merge=on,index_merge_union=on,
index_merge_sort_union=on,index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,mrr_cost_based=on,block_nested_loop=on,
batched_key_access=off,materialization=on,
semijoin=on,loosescan=on,firstmatch=on,
duplicateweedout=on,subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,derived_merge=on

开启和关闭：

# 开启
set optimizer_switch='index_condition_pushdown=on';

# 关闭
set optimizer_switch='index_condition_pushdown=off';

③ ICP实例

如下所示，表people中有联合索引KEYzip_last_first (zipcode,lastname,firstname) ，当我们执行如下SQL时，就会触发ICP。

EXPLAIN SELECT * FROM people
WHERE zipcode='000001'
AND lastname LIKE '%张%'
AND address LIKE '%北京市%';

索引trim_索引trim


执行查看SQL的查询计划，Extra中显示了Using index condition ,这表示使用了索引下推。另外，Using where表示条件中包含需要过滤的非索引列的数据，即 address like ‘%北京市%’ 这个标记并不是索引列，需要在服务端过滤掉。

索引trim_MySQL_02

这个如何理解呢？

按照我们的认知，其首先使用索引zipcode='000001'查找，然后回表到主键索引（聚簇索引）中进行查找。假设有1000条，那么我们需要回表1000次并将数据读取到内存中再进行 lastname LIKE '%张%' 和 address LIKE '%北京市%'的过滤。毕竟 lastname LIKE '%张%'按照我们前面的学习，是用不到索引的。

如果MySQL服务器用了ICP，联合索引中包含了lastname，那么就可以在二级索引查找的时候就同时进行zipcode='000001 AND lastname LIKE '%张%'的过滤，假设这时得到100条数据。那么就只需要回表100次，再进行address LIKE '%北京市%' 的过滤。

也就是ICP减少了回表次数，也同时减少了回表读取数据导致的随机IO次数。

那么对于可以正常使用联合索引的SQL呢？如下所示

EXPLAIN SELECT * FROM people
WHERE zipcode='000001'
AND lastname LIKE '张%'
AND firstname LIKE '三%';

按照我们的理解，是可以正常使用到索引的。但是查看执行计划，其Extra仍然存在Using index condition。不过查看此时的key_len能够发现确实是用到了三个字段索引。

索引trim_MySQL调优_03

可以自己做一下测试，数据量比较大的时候分别在两种情况下（是否使用ICP）验证查询性能，如下图所示没有ICP的查询明显要慢一些。

索引trim_MySQL调优_04

④ ICP的使用条件

  1. 如果表访问的类型为range、ref、eq_ref 和 ref_or_null 可以使用ICP
  2. ICP可以用于InnoDB 和 MyISAM表，包括分区表InnoDB和MyISAM表
  3. 对于InnoDB表，ICP仅用于二级索引。ICP的目标是减少全行读取次数，从而减少IO操作。
  4. 当SQL使用覆盖索引时，不支持ICP。因为这种情况下使用ICP不会减少IO。
  5. 相关子查询的条件不能使用ICP。