Mysql中到底什么是索引下推，一分钟看懂

• 索引条件下推(Index Condition Pushdown),简称ICP。MySQL5.6新添加，用于优化数据的查询。

理论

索引下推简单来说就是把索引当中的所有数据全部用到减少回表的次数

假如现在我们表中有a b c三个字段的联合索引，那么该索引再b+tree中的存储如下

此时根据最佳做匹配原则

where a = 1a and b = 1b and c = 1c; 会完全用到索引

where a = 1a and c = 1c; 此时会用到a索引，因为b索引断了，索引c索引也会浪费

那么如果没有索引下推，此时mysql会获取到 （1a + 1b + 1c）(1a + 2b + 2c)两个索引

此时会进行回表获取id=1 和 id = 2 的数据，然后判断c 是否等于1c

最终结果返回id=1的这条数据

但是有了索引下推之后

mysql会获取到 （1a + 1b + 1c）(1a + 2b + 2c)两个索引

接着会判断c 是否等于1c因为c也在索引结构中，此时只会留下（1a + 1b + 1c）这一个索引

这样就减少了回表次数，提升了查询效率 

验证

#创建表 建立name, city, age三个字段的复合索引，sex是为了避免索引覆盖
CREATE TABLE user_index (
                      id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
                      name varchar(32)  COMMENT '姓名',
                      city varchar(32)  COMMENT '城市',
                      age int(11)  COMMENT '年龄',
                      sex int(1) default 0,
                      primary key(id),
                      key idx_name_city(name, city, age)
)engine=InnoDB default charset=utf8;

#插入数据
insert into user_index(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);

 此时关闭索引下推，执行计划

#关闭索引下推
set optimizer_switch='index_condition_pushdown=off';
#查看执行计划
explain select * from user_index where name = 'ZhaoDa' and age = 20;

#打开索引下推  默认是开启
set optimizer_switch='index_condition_pushdown=on';
#查看执行计划
explain select * from user_index where name = 'ZhaoDa' and age = 20;

 

 索引还是遵循最左匹配的，如果直接从第二个字段查询就会索引失效全表扫描

explain select * from user_index where city = 'BeiJing' and age = 20;