MySQL 索引下推

使用前后对比

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

• 如果没有ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给MySQL服务器，由MySQL服务器评估WHERE后面的条件是否保留行。
• 启用ICP后，如果部分WHERE条件可以仅使用索引中的列进行筛选，则MySQL服务器会把这部分WHERE条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。
• 好处:ICP可以减少存储引擎必须访问基表的次数和MysQL服务器必须访问存储引擎的次数。
• 但是，ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。
•   

ICP的开启/关闭

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

#关闭索引下推
SET optimizer_switch = 'index_condition_pushdown=off' ;
#开启索引下推
SET optimizer_switch = 'index_condition_pushdown=on ';

当使用索引条件下推时，EXPLAIN语句输出结果中Extra列内容显示为Using index condition。

ICP使用案例

建表

CREATE TABLE people`(
'id` int NOT NULL AUTO_INCREMENT,
'zipcode' varchar(20)COLLATE utf8_bin DEFAULT NULL,
'firstname ' varchar(20)COLLATE utf8_bin DEFAULT NULL,
'lastname` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`address' varchar( 50)COLLATE utf8_bin DEFAULT NULL,PRIMARY KEY ( id `),
KEY‘zip_last_first’( zipcode `, 'lastname ' , `firstname ')
)ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;

插入数据

 

INSERT INTO 'people` VALUES
( '1', '008801', '三'，'张','北京市'),
( '2', '000002', '四'，'李','南京市'),
( '3', '888003', '五'，'王','上海市'),
( '4', '000001', '六', '赵','天津市');

为该表定义联合索引zip_last_first (zipcode，lastname，firstname)。如果我们知道了一个人的邮编，但是不确定这个人的姓氏，我们可以进行如下检索:
 

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

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

ICP的使用条件

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