索引下推

索引下推是从 MySQL5.6 开始引入一个特性,英文是 index condition pushdown,一般简称为 ICP,索引下推通过减少回表的次数,来提高数据库的查询效率。

在 MySQL5.5 中,由于没有索引下推,所以上面这个 SQL 的执行流程是这样的:

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


但是注意第四步的搜索方式,不是直接去 B+Tree 中搜索了。由于在 username 索引中,username 字段的存储是有序的,即 username=‘1’ 的记录都是挨着的,而 B+Tree 的叶子结点之间通过双向链表关联,通过一个叶子结点就能找到下一个叶子结点(或者上一个叶子结点),第二步返回的数据中有一个 next_record 属性,该属性就直接指向二级索引的下一条记录,找到下一条记录后,回表拿到所有数据并返回给 server 层,然后重复 3、4 步。

 

前面两个 SQL,由于查询的时候是 select *,所以都是需要回表操作的,虽然是复合索引,索引中既有 username 又有 age,但是查询条件中只能传入 username 到存储引擎中,从存储引擎中回表拿到一行数据的完整记录后,再返回给 server 层,再在 server 层判断 age 是否满足条件。我们肉眼其实都能看到这样查询效率比较低,明明索引中有 age 的值,但是却不在索引中比较 age,而是要回表,取一行的完整记录出来,返回给 server 层,再去和 age 比较,要是比较不通过,这条记录就被丢掉了。如果我们能够把 age 直接传入存储引擎,在存储引擎中直接去判断 age 是否满足条件,满足条件了,再去回表,不满足条件就到此结束,这样就可以减少回表的次数,进而提高查询效率。


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

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

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


这就是索引下推(index condition pushdown,ICP),有效的减少了回表次数,提高了查询效率。

 

 


__EOF__

本文作者liuliu的小家
本文链接https://www.cnblogs.com/liu-jin/p/17258498.html
关于博主:hello~好久不见,喜欢的话点个赞吧
版权声明:本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!
声援博主:如果您觉得文章对您有帮助,可以点击文章右下角推荐一下。您的鼓励是博主的最大动力!
posted @   Bepowerful  阅读(261)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
· 上周热点回顾(2.24-3.2)
点击右上角即可分享
微信分享提示