MySQL 之 Index Condition Pushdown(ICP)

简介

Index Condition Pushdown (ICP)是MySQL 5.6 版本中的新特性,是一种在存储引擎层使用索引过滤数据的一种优化方式。

当关闭ICP时,index 仅仅是data access 的一种访问方式，存储引擎通过索引从表获取的数据会传递到MySQL Server 层进行where条件过滤。
当打开ICP时,如果部分where条件能使用索引中的字段,MySQL Server 会把这部分下推到引擎层,可以利用index过滤的where条件在存储引擎层进行数据过滤,而非将所有通过index access的结果传递到MySQL server层进行where过滤.

优化效果:ICP能减少引擎层访问基表的次数和MySQL Server 访问存储引擎的次数,减少io次数，提高查询语句性能。

ICP的使用限制

当sql需要全表访问时,ICP的优化策略可用于range, ref, eq_ref, ref_or_null 类型的访问数据方法。
支持InnoDB和MyISAM表。
ICP只能用于二级索引，不能用于主索引。
并非全部where条件都可以用ICP筛选。如果where条件的字段不在索引列中,还是要读取整表的记录到server端做where过滤。
ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。
5.6 版本的不支持分表的ICP 功能，5.7 版本的开始支持。
当sql 使用覆盖索引时，不支持ICP 优化方法。

实践

mysql> show create table userG
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`password` varchar(50) NOT NULL,
`score` smallint(3) NOT NULL,
`mobile` char(11) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `unique_username` (`username`),
KEY `pre_idx` (`score`,`mobile`,`username`)
) ENGINE=InnoDB AUTO_INCREMENT=200003 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

开启ICP

mysql> set optimizer_switch="index_condition_pushdown=on";

mysql> explain select * from user where score=60 and mobile like '%75%' and username like '%username11%'G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
partitions: NULL
type: ref
possible_keys: pre_idx
key: pre_idx
key_len: 2
ref: const
rows: 2012
filtered: 1.23
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

从explain 着灰底色的参数可以看到，使用了复合索引 pre_idx，并且只使用了 score字段的索引（因为mobile 与 username 使用了模糊查询，无法使用索引），但Extra列并没有Using where，而是Using index condition，说明使用了ICP，在存储引擎使用了索引过滤where条件，不再把通过索引查找到的数据传输到mysql服务器层通过where条件过滤。

关闭ICP

mysql> set optimizer_switch="index_condition_pushdown=off";

从explain 着灰底色的参数可以看到，使用了复合索引 pre_idx，并且只使用了 score字段的索引（因为mobile 与 username 使用了模糊查询，无法使用索引），Extra出现Using where。

从案例可以看到，当开启ICP时查询在sending data环节时间消耗是 0.006537s，未开启时是0.329598s，现在该表的数据量为20w，差异已经非常明显了。