索引条件下推

索引条件下推(index condition pushdown ICP)

是mysql5.6新特性，减少回表查询次数，提高查询效率。下推是指将部分上层（服务层）负责做的索引条件检查，交给了（下层）引擎层去处理。

根据explain解析结果可以看出Extra的值为Using index condition，表示已经使用了索引条件下推。

ICP 限制

1. ICP 仅用于需要访问基表所有记录时使用，适用的访问方法为：range、ref、eq_ref、ref_or_null。ICP尤其是对联合索引的部分列模糊查找非常有效。
2. 适用于分区表
3. ICP的目标是减少全行记录读取，从而减少IO操作，仅用于二级索引。主键索引本身即是表数据，不存在下推操作，索引条件下推只适用于二级索引（也叫辅助索引）。对于InnoDB的聚簇索引来说，完整的行记录已经加载到缓存区了，索引条件下推也就没什么意义了。
4. 引用了子查询的条件不能下推；
5. 引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。

CREATE TABLE user (
id int(11) NOT NULL AUTO_INCREMENT COMMENT "主键",
name varchar(32)  COMMENT "姓名",
city varchar(32)  COMMENT "城市",
age int(11)  COMMENT "年龄",
primary key(id),
key idx_name_city(name, city)
)engine=InnoDB default charset=utf8;

INSERT INTO user (name, city, age)
VALUES ('ZhaoDa', 'BeiJing', 20),
	('QianEr', 'ShangHai', 21),
	('SunSan', 'GuanZhou', 22),
	('LiSi', 'ShenZhen', 24),
	('ZhouWu', 'NingBo', 25),
	('WuLiu', 'HangZhou', 26),
	('ZhengQi', 'NanNing', 27),
	('WangBa', 'YinChuan', 28),
	('LiSi', 'TianJin', 29),
	('ZhangSan', 'NanJing', 30),
	('CuiShi', 'ZhengZhou', 65),
	('LiSi', 'KunMing', 29),
	('LiSi', 'ZhengZhou', 30);

select * from user where name="LiSi" and city like "%Z%" and age > 25;

不使用索引条件下推

在不使用索引条件下推的情况下，根据联合索引最左匹配原则，只有name列能用到索引，city由于是模糊匹配的，是不能用到索引的。 执行过程：

• 存储引擎根据(name,city)联合索引，找到name值为LiSi的记录，共4条
• 然后根据4条记录的id，逐一进行回表扫描，去聚簇索引中取出完整的行记录，并把记录返回给Server层。
• Server层收到这些记录，并按条件 name="LiSi" and city like "%Z%" and age > 25 进行过滤，最终留下 ("LiSi", "ZhengZhou", 30)

使用索引条件下推

• 存储引擎根据(name,city)联合索引，找到name值为LiSi的记录，共4条
• 由于联合索引中包含city列，存储因此直接在联合索引中按city like "%Z%"进行过滤，过滤后剩下2条记录
• 然后根据2条记录的id，逐一进行回表扫描，去聚簇索引中取出完整的行记录，并把记录返回给Server层。
• Server层收到这些记录，并按条件age > 5，进行过滤，最终留下 ("LiSi", "ZhengZhou", 30)

//设置
set optimizer_switch="index_condition_pushdown=off";