关于mysql优化:索引下推yyds

1次阅读

共计 3430 个字符，预计需要花费 9 分钟才能阅读完成。

索引的问题，曾经跟大家聊了两篇文章了～明天再聊一个索引下推问题，也是十分有意思！

索引下推是从 MySQL5.6 开始引入一个个性，英文是 index condition pushdown，个别简称为 ICP，索引下推通过缩小回表的次数，来进步数据库的查问效率。

有的小伙伴可能也看过一些对于 ICP 的概念，然而我感觉，概念比较简单，说一下很容易懂，然而在理论利用中，各种各样的状况十分多。所以接下来的内容我想通过几个具体的查问剖析来和大家分享 ICP 到底是怎么一回事。

为了给大家演示索引下推，我用 docker 装置了两个 MySQL，一个是 MySQL5.5.62，另一个是 5.7.26，因为索引下推是 MySQL5.6 中开始引入的新个性，所以这两个版本就能够给大家演示出索引下推的特点（不懂 docker 的小伙伴能够在公众号后盾回复 docker，有松哥写的入门教程）。

首先我有如下一张表：

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;

我在 MySQL5.5 和 MySQL5.7 中别离执行如上 SQL，确保两个 MySQL 中都有这样一张表。这张表中有一个由 username 和 age 组成的复合索引，索引名字就叫 username，在本文接下来的内容中，我说 username 索引就是指该复合索引。

表创立胜利后，各自增加一些模仿数据，这个我就不演示了，通过存储过程或者 Java 代码都能增加模仿数据，这个小伙伴们自行解决即可。

OK，这样咱们的筹备工作就算实现了。

先来给小伙伴们演示一个 MySQL5.5 中的查问案例。

为了不便后文的表述，我给每一条 SQL 都取一个标记：

来看如下 SQL（SQL1）：

select * from user2 where username='1' and age=99;

依据 username 和 age 查问一条记录，咱们来看看这条 SQL 的执行打算（为了小伙伴们浏览不便，我加了 \G 把数据用列的模式展现）：

大抵瞅一眼，咱们发现这个是用了索引的，然而具体是怎么用的，我来和大家说道说道！

在 MySQL5.5 中，因为没有索引下推，所以下面这个 SQL 的执行流程是这样的：

首先 MySQL 的 server 层调用存储引擎获取 username=’1′ 的第一条记录。
存储引擎找到 username=’1′ 的第一条记录后，在 B+Tree 的叶子结点中保留着主键 id，此时通过回表操作，去主键索引中找到该条记录的残缺数据，并返回给 server 层。
server 层拿到数据之后，判断该条记录的 age 是否为 99，如果 age=99，就把该条记录返回给客户端，如果 age!=99，那就就抛弃该记录。
因为 username+age 组成的复合索引只是一个一般索引，并不是惟一索引（如果是惟一索引，那么这个查问就到此结束了），所以还须要持续去搜寻有没有满足条件的记录。

然而留神第四步的搜寻形式，不是间接去 B+Tree 中搜寻了。因为在 username 索引中，username 字段的存储是有序的，即 username=’1′ 的记录都是挨着的，而 B+Tree 的叶子结点之间通过双向链表关联，通过一个叶子结点就能找到下一个叶子结点（或者上一个叶子结点），第二步返回的数据中有一个 next_record 属性，该属性就间接指向二级索引的下一条记录，找到下一条记录后，回表拿到所有数据并返回给 server 层，而后反复 3、4 步。

咱们看看下面的执行打算，和咱们的剖析是统一的：

后面的 type 为 ref 示意通过索引查找数据，个别呈现等值匹配的时候，type 会为 ref。
最初的 Extra 为 Using where 示意数据在 server 层还进行了过滤操作。

再来看一个 SQL（SQL2）：

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

这跟后面那个 SQL1 其实很像，惟一的差异在于 username 用了含糊匹配 'j%'，在上篇文章中松哥曾经和大家分享过了，这种状况其实也是能用上索引的，具体大家能够参考：其实 MySQL 中的 like 关键字也能用索引！。

这条 SQL 的执行流程，跟第一条 SQL1 的执行流程也基本上是统一的，我这里就不赘述了，咱们来看看这条 SQL 的执行打算：

跟下面的执行打算相比，次要是 type 变为 range 了，示意依照范畴搜寻，因为 'j%' 其实就代表了一个扫描区间，不懂 'j%' 代表扫描区间的小伙伴，戳上篇文章。

后面两个 SQL，因为查问的时候是 select *，所以都是须要回表操作的，尽管是复合索引，索引中既有 username 又有 age，然而查问条件中只能传入 username 到存储引擎中，从存储引擎中回表拿到一行数据的残缺记录后，再返回给 server 层，再在 server 层判断 age 是否满足条件。咱们肉眼其实都能看到这样查问效率比拟低，明明索引中有 age 的值，然而却不在索引中比拟 age，而是要回表，取一行的残缺记录进去，返回给 server 层，再去和 age 比拟，要是比拟不通过，这条记录就被丢掉了。如果咱们可能把 age 间接传入存储引擎，在存储引擎中间接去判断 age 是否满足条件，满足条件了，再去回表，不满足条件就到此结束，这样就能够缩小回表的次数，进而进步查问效率。

从 MySQL5.6 开始引进的索引下推技术，做的就是这事。

咱们在 MySQL5.7 中也来看下下面两条 SQL 的执行，先来看第一个（SQL3）：

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

来看下查问打算：

能够看到，这个查问打算和 SQL2 的查问打算相比，次要是最初的 Extra 为 Using index condition，这是啥意思呢？

这就是从 MySQL5.6 开始引入了索引下推 ICP，咱们一起来看下具体操作流程：

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

这就是索引下推（index condition pushdown，ICP），无效的缩小了回表次数，进步了查问效率。

有时候咱们看一下老版本的 MySQL，会感觉特地莫名其妙，索引下推，如许理所应当牵强附会的性能呀，惋惜过后就是没有！还好，该有的最终都会有。

咱们再来看一个非凡状况，来看如下 SQL（SQL4）：

select * from user2 where username='1' and age=99;

和后面的相比，这里的查问条件都变成等值比拟了，来看看它的执行打算，如下：

能够看到，这个查问打算和 SQL1 的查问打算相比，次要是最初的 Extra 为 null，没有额定操作了，其实这只是一个非凡解决而已，利用搜寻条件 username='1' and age=99 从存储引擎中找到数据之后，没有再去反复判断了而已（SQL3 中索引下推的时候不仅判断 age 的值也判断 username 的值）。

好啦，通过 MySQL5.5 和 MySQL5.7 的比照，当初大家明确什么是索引下推了吧？其实一句话：在搜索引擎中提前判断对应的搜寻条件是否满足，满足了再去回表，通过缩小回表次数进而进步查问效率。

正文完