前言

索引下推(ICP)是针对MySQL应用索引从表中检索数据行的状况的优化。

在没有索引下推的状况下，MySQL通过存储引擎遍历索引来定位表中的数据行并将它们返回给MySQl服务器，服务器再进行WHERE条件的判断，确认是否将数据行退出后果集。
开启索引下推，且WHERE条件局部能够仅应用索引中的列来评估，这时MySQL服务器会将这部分WHERE条件下推到存储引擎，接着存储引擎应用索引条目评估推送的索引条件，仅当满足该条件时才从表中进行读取。

索引下推能够缩小存储引擎拜访数据表的次数以及MySQL服务器拜访存储引擎的次数。

注释

看完下面这段官网文档的解释，你当初是不是这样的

是的话，那就对了，毫无疑问下面这段话了解起来相当吃力，但请不要灰心，我将用最通俗易懂的语言来带你理解索引下推。

在此之前我先像你介绍两个敌人

最左前缀准则
回表

最左前缀准则

MySQL在建设联结索引时会遵循最左前缀准则，比方当初User表建设了联结索引（id，name，age）依据最左前缀准则只有在SQL的条件局部命中(id)、(id，name)或者(id, name, age)时能力应用到这个联结索引。

能应用该索引的状况如下：
SELECT * FROM USER WHERE id = 1
SELECT * FROM USER WHERE id = 1 and name = 'zhangsan'
SELECT * FROM USER WHERE id = 1 and name = 'zhangsan' and age = 18
不能应用该索引的状况如下:
SELECT * FROM USER WHERE name = 'zhangsan'
SELECT * FROM USER WHERE age = 18
SELECT * FROM USER WHERE name = 'zhangsan' and age = 18

对于联结索引mysql会始终向右匹配直到遇到范畴查问(>、<、between、like)就进行匹配。

回表

MySQL在InnoDB引擎下反对两种索引

汇集索引：索引里（B+树的叶子结点上）存储的是数据行（实在的数据）
一般索引：索引里（B+树的叶子结点上）存储的是主键

这里着重说一下汇集索引，官网文档有以下形容

在有主键的表，InnoDB将主键作为汇集索引
没有主键的表，InnoDB应用第一个惟一索引作为汇集索引
即没有主键也没有惟一索引时，MySQL将生成一个暗藏的6字节大小的row ID字段作为汇集索引

MySQL通过一般索引没法一次性将数据拿全的状况下，通过一般索引获取主键值，再通过主键值到汇集索引中定位到记录，这个过程就叫回表。能够通过建设笼罩索引来缩小回表，比方当初要通过身份证号查姓名，那就建设身份证号和姓名的联结索引（id，name），当查问时能够通过这个索引间接拿到姓名name得值，不再须要去汇集索引里查找了，这就是笼罩索引。

索引下推

首先创立一个用户表

CREATE TABLE `student` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(255)  DEFAULT NULL,  `age` int  DEFAULT 0,  `class` varchar(255)  DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `index_two` (`name`,`age`)) ENGINE=InnoDB;//这张表减少一个复合索引 (`name`,`age`)

给表插入数据

INSERT INTO  `student` (`name`, `age`, `class`) VALUES  ('pengpeng', 21, '1');INSERT INTO  `student` (`name`, `age`, `class`) VALUES  ('pengpeng', 22, '2');INSERT INTO  `student` (`name`, `age`, `class`) VALUES  ('pengpeng', 23, '3');INSERT INTO  `student` (`name`, `age`, `class`) VALUES  ('pengpeng', 24, '4');INSERT INTO  `student` (`name`, `age`, `class`) VALUES  ('pengpeng', 25, '5');

查问插入的数据如下

接下来explain上面这个SQL

explain select * from student where name like 'peng%' and age = 23;

能够看到Extra字段显示为USING INDEX CONDITION，这就表明这个SQL应用了索引下推，咱们剖析下下面这个SQL语句：

在MySQL5.6之前，只能从name字段中找出符合条件的行而后开始回表，到汇集索引上找出数据行，再对age字段进行比照，把符合条件的数据退出到后果集中。
在MySQL5.6引入了索引下推优化，在索引的遍历过程中，对索引中蕴含字段先做判断，这里对age字段进行判断。间接将age字段不满足的数据行排除，从而缩小回表的次数。

问答区

问题1 当复合索引列为（name，age，address）时以下SQL能应用索引吗？

select * from student where name like 'peng%' and age = 23;

能够，遇到like会中断后续元素的匹配，但只能应用name这个字段，mysql会始终向右匹配直到遇到范畴查问（>、<、between、like）就进行匹配。范畴列能够用到索引，然而范畴列前面的列无奈用到索引。即索引最多用于一个范畴列，因而如果查问条件中有两个范畴列则无奈全用到索引。

问题2 索引下推只能存在联结索引里吗？

是的，非联结索引无奈应用索引下推。

问题3 索引下推在哪些状况下无奈应用？

下推条件遇到子查问
下推条件遇到函数
非InnoDB表和MyISAM表

问题4 索引下推如何开启和敞开？

// 索引下推默认是开启的set optimizer_switch='index_condition_pushdown=off'; // 敞开set optimizer_switch='index_condition_pushdown=on'; // 开启

总结

索引下推在非主键索引上的优化，能够无效缩小回表的次数，大大晋升了查问的效率，在平时工作中能够依据业务状况通过优化索引来达到应用索引下推，进步业务吞吐量。

❤️ 谢谢反对

以上便是本次分享的全部内容，心愿对你有所帮忙^_^

喜爱的话别忘了分享、点赞、珍藏三连哦~。

欢送关注公众号程序员巴士，一辆乏味、有范儿、有温度的程序员巴士，涉猎大厂面经、程序员生存、实战教程、技术前沿等内容，关注我，交个敌人。