2020-09-01

索引下推

索引下推（ICP）

mysql5.6开始推出，主要用于优化查询

• 在不使用ICP的情况下，在使用二级索引查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。
• 在使用ICP的情况下，如果where条件存在索引列的判断条件时，mysql-server将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

作用

索引下推下个可以：
1、减少回表次数
2、减少返回的数据量（开启profiling中，sending data那一步）

实践

CREATE TABLE `student` (
  `studentId` int(10) NOT NULL,
  `studentName` varchar(20) NOT NULL,
  `phoneNum` int(11) DEFAULT NULL,
  `hobby` varchar(100) DEFAULT NULL,
  `sex` varchar(10) DEFAULT 'mail',
  `age` int(10) DEFAULT NULL,
  PRIMARY KEY (`studentId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

mysql> select * from student;
+-----------+-------------+------------+------------+--------+------+
| studentId | studentName | phoneNum   | hobby      | sex    | age  |
+-----------+-------------+------------+------------+--------+------+
|      1000 | acex        | 2147483647 | reading    | female |   18 |
|      1001 | alex        | 1329519519 | basketball | male   |   20 |
|      1002 | bob         | 1329519516 | football   | male   |   17 |
|      1003 | clear       | 1329512516 | girl       | male   |   17 |
|      1004 | daming      | 1329222516 | boy        | male   |   17 |
|      1006 | ellen       |  124356666 | LOL        | male   |   17 |
|      1007 | fandrule    | 2147483647 | game       | male   |   18 |
+-----------+-------------+------------+------------+--------+------+
7 rows in set (0.00 sec)

建立联合索引

alter table student add index (studentName,age)

• 假设有一个需求，要求匹配name第一个为a的所有用户，sql语句如下：

  SELECT * from user where studentNamelike ‘a%’

• 根据 “最佳左前缀” 的原则，这里使用了联合索引（studentName，age）进行了查询，性能要比全表扫描肯定要高。

• 问题来了，如果有其他的条件呢？假设又有一个需求，要求匹配名字第一个字为陈，年龄为20岁的用户，此时的sql语句如下：

  SELECT * from user where studentName like ‘a%’ and age=20

Mysql5.6之前的版本
5.6之前的版本是没有索引下推这个优化的，因此执行的过程如下图：(图片出处见水印)

会忽略age这个字段，直接通过name进行查询，在(name,age)这课树上查找到了两个结果，id分别为2,1，然后拿着取到的id值一次次的回表查询，因此这个过程需要回表两次。

Mysql5.6及之后版本
5.6版本添加了索引下推这个优化，执行的过程如下图：(图片出处见水印)

InnoDB并没有忽略age这个字段，而是在索引内部就判断了age是否等于20，对于不等于17的记录直接跳过，因此在(name,age)这棵索引树中只匹配到了一个记录，此时拿着这个id去主键索引树中回表查询全部数据，这个过程只需要回表一次。

操作

explain select studentName age from student where studentName like "a%" and age =20;
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | student | NULL       | range | studentName   | studentName | 27      | NULL |    1 |    14.29 | Using index condition    |
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

当Extra值为：Using index condition.表示使用索引下推。

• 通过索引下推对于非主键索引进行优化，可有效减少回表次数，从而提高效率。

• 关闭索引下推命令

  set optimizer_switch=‘index_condition_pushdown=off’;