mysql pushdown_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 user\G

*************************** 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";

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 where

1 row in set, 1 warning (0.00 sec)

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

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