前言：

索引下推(ICP)是针对MySQL5.6使用索引从表中检索数据行的情况的优化

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

索引下推可以减少存储引擎访问数据表的次数以及MySQL服务器访问存储引擎的次数。

总结一下：

  • 最左前缀原则

  • 回表

    1、最左前缀原则

    MySQL在建立联合索引时会遵循最左前缀原则，比如现在User表建立了联合索引（id，name，age）根据最左前缀原则只有在SQL的条件部分命中(id)、(id，name)或者(id, name, age)时才能使用到这个联合索引。
    能使用该索引的情况如下：

    1. SELECT* FROM USER WHERE id = 1
    2. SELECT* FROM USER WHERE id = 1 and name= 'zhangsan'
    3. SELECT* FROM USER WHERE id = 1 and name= 'zhangsan'and age = 18

    不能使用该索引的情况如下:

    1. SELECT* FROM USER WHERE name= 'zhangsan'
    2. SELECT* FROM USER WHERE age = 18
    3. SELECT* FROM USER WHERE name= 'zhangsan'and age = 18

    对于联合索引mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。

    2、回表

    MySQL在InnoDB引擎下支持两种索引

  • 聚集索引 ：索引里（B+树的叶子结点上）存储的是数据行（真实的数据）

  • 普通索引 ：索引里（B+树的叶子结点上）存储的是主键

这里着重说一下聚集索引，官方文档有以下描述

  • 在有主键的表，InnoDB将主键作为聚集索引
  • 没有主键的表，InnoDB使用第一个唯一索引作为聚集索引
  • 即没有主键也没有唯一索引时，MySQL将生成一个隐藏的6字节大小的row ID字段作为聚集索引

MySQL通过普通索引没法一次性将数据拿全的情况下，通过普通索引获取主键值，再通过主键值到聚集索引中定位到记录，这个过程就叫回表。可以通过建立覆盖索引来减少回表，比如现在要通过身份证号查姓名，那就建立身份证号和姓名的联合索引（id，name），当查询时可以通过这个索引直接拿到姓名name得值，不再需要去聚集索引里查找了，这就是覆盖索引。

3、索引下推

首先创建一个用户表

  1. CREATETABLE`student` (
  2.     `id` int(11) NOTNULLAUTO_INCREMENT,
  3.   `name` varchar(255)  DEFAULTNULL,
  4.   `age` int  DEFAULT0,
  5.   `class` varchar(255)  DEFAULTNULL,
  6.   PRIMARYKEY(`id`),
  7.   KEY`index_two` (`name`,`age`)
  8. ) ENGINE=InnoDB;
  10. INSERTINTO  `student` (`name`, `age`, `class`) VALUES  ('pengpeng', 21, '1');
  11. INSERTINTO  `student` (`name`, `age`, `class`) VALUES  ('pengpeng', 22, '2');
  12. INSERTINTO  `student` (`name`, `age`, `class`) VALUES  ('pengpeng', 23, '3');
  13. INSERTINTO  `student` (`name`, `age`, `class`) VALUES  ('pengpeng', 24, '4');
  14. INSERTINTO  `student` (`name`, `age`, `class`) VALUES  ('pengpeng', 25, '5');

接下来explain下面这个SQL

  1. explain select* fromstudent wherenamelike'peng%'andage = 23;

MySQL索引下推 - 图1

可以看到Extra字段显示为USING INDEX CONDITION，这就表明这个SQL使用了索引下推，我们分析下上面这个SQL语句：
在MySQL5.6之前，只能从name字段中找出符合条件的行然后开始回表，到聚集索引上找出数据行，再对age字段进行对比，把符合条件的数据加入到结果集中。
在MySQL5.6引入了索引下推优化，在索引的遍历过程中，对索引中包含字段先做判断，这里对age字段进行判断。直接将age字段不满足的数据行排除，从而减少回表的次数。
问答区
问题1 当复合索引列为（name，age，address）时 以下SQL能使用索引吗？

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

可以，遇到like会中断后续元素的匹配，但只能使用name这个字段，mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。范围列可以用到索引，但是范围列后面的列无法用到索引。即索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

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

是的，非联合索引无法使用索引下推。

问题3 索引下推在哪些情况下无法使用？

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

问题4 索引下推如何开启和关闭？
// 索引下推默认是开启的

  1. setoptimizer_switch='index_condition_pushdown=off'; // 关闭
  2. setoptimizer_switch='index_condition_pushdown=on'; // 开启

总结

索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率，在平时工作中可以根据业务情况通过优化索引来达到使用索引下推，提高业务吞吐量。