M有SQL(八) 索引下推

一.什么是索引下推

索引下推（ICP）：是从MySQL5.6开始引入的一个特性，通过减少回表的次数，来提高数据库的查询效率。

注意：来复习一下什么是回表吧

对于非主键索引，我们搜索了两颗B+树，第一次先从查询项的B+树找到主键的值，第二次通过主键的值找到主键索引的B+树，然后获得我们想要的数据。

1.1 添加表和数据

CREATE TABLE `user2` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `username` (`username`(191),`age`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;
1
2
3
4
5
6
7
8
9

添加一张表，再添加几条数据如下

二.索引下推小例子

MySQL 5.5 中SQL执行流程

执行一条SQL
select * from user2 where username='1' and age=99;

执行流程如下：

1. 首先 MySQL 的 server 层调用存储引擎获取 username=‘1’ 的第一条记录。
2. 存储引擎找到 username=‘1’ 的第一条记录后，在 B+Tree 的叶子结点中保存着主键 id，此时通过回表操作，去主键索引中找到该条记录的完整数据，并返回给 server 层。
3. server 层拿到数据之后，判断该条记录的 age 是否为 99，如果 age=99，就把该条记录返回给客户端，如果 age!=99，那就就丢弃该记录。
4. 由于 username+age 组成的复合索引只是一个普通索引，并不是唯一索引(如果是唯一索引，那么这个查询就到此结束了），所以还需要继续去搜索有没有满足条件的记录。
   即：此时我们还要去查找MySQL的age的数据，但是由于我们的username+age是符合索引，故在整个表中，username被视为一个普通索引，而age则并不被视为索引，所以age的字段的存储是无需的，于是我们在通过username索引找到username的数据之后，还要去搜索整个数据库找到我们age=99的这一条数据。

MySQL 5.7中SQL执行流程

select * from user2 where username like 'j%' and age=99;

那有了索引下推ICP之后的执行流程是怎样的呢

1. MySQL 的 server 层首先调用存储引擎定位到第一个以 j 开头的
   username。
2. 找到记录后，存储引擎并不急着回表，而是继续判断这条记录的 age 是否等
   于 99，如果 age=99，再去回表，如果 age 不等于 99，就不去回表了，直接继续读取下一条记录。
3. 存储引擎将读取到的数据行返回给 server 层，此时如果还有其他非索引的查询条件，server 层再去继续过滤，在我们上面的案例中，此时没有其他查询条件了。假设 server 层还有其他的过滤条件，并且这个过滤条件把刚刚查到的记录过滤掉了，那么就会通过记录的 next_record 属性读取下一条记录，然后重复第二步。

这就是我们说的索引下推ICP,有效的减少了回表次数，提高了查询效率，