索引下推,这个点你肯定不知道!

索引下推(Index Condition Pushdown) ICP 是Mysql5.6之后新增的功能,主要的核心点就在于把数据筛选的过程放在了存储引擎层去处理,而不是像之前一样放到Server层去做过滤。

虽然这是一个比较简单的概念,但是可能很多不细心的同学对于索引下推会存在一个小小的误区,至于是什么,请看下文。

什么是索引下推

首先,我们创建一张user表,同时建立age_name的联合索引,同时插入3条测试数据。

img

然后,我们执行查询explain SELECT * from user where age >10 and name = 'a',如下图所示,就会看见Extra中显示了Using index condition,你可能就知道了,这表示出现了索引下推了。

img

没错,针对这个查询场景就是索引下推,那到底什么是索引下推呢?

按照我们上述的场景,实际上就存在两个索引树,一个是主键索引,存储了具体的数据的信息,另外则是age_name的联合索引,保存了主键的ID。

img

在没有ICP索引下推的时候,这个查询的流程应该是这样(略过无关的细节):

  1. Mysql Server层调用API查询存储引擎数据
  2. 存储引擎根据联合索引首先通过条件找到所有age>10的数据
  3. 找到的每一条数据都根据主键索引进行回表查询,直到找到不符合条件的结果
  4. 返回数据给Server层,Server根据条件对结果进行过滤,流程结束

而有了ICP之后的流程则是这样:

  1. Mysql Server层调用API查询存储引擎数据
  2. 存储引擎根据联合索引首先通过条件找到所有age>10的数据,根据联合索引中已经存在的name数据进行过滤,找到符合条件的数据
  3. 根据找到符合条件的数据,回表查询
  4. 返回数据给Server层,流程结束

对比这两个流程就会很明显的发现,使用ICP之后我们就是简单的通过联合索引中本来就有的数据直接过滤了,不需要再查到一堆无用的数据去Server层进行过滤,这样的话减少了回表的次数和返回的数据,IO次数减少了,对性能有很好的提升。

按照官方文档所说,ICP其实也存在一定的使用限制场景,只说关键的,乱七八糟的不说。

  1. 首先,ICP适用于range、ref、eq_ref和ref_or_null的场景下
  2. InnoDB和MyISAM都支持ICP,Mysql partition分表的话也可以使用
  3. 对于InndoDB而言,ICP只支持二级索引,因为主键索引它用不上不是吗?
  4. 子查询不支持

现在我们基本都使用的5.6以上的版本了,默认就是开启ICP的,想关闭的话可以通过命令SET optimizer_switch = 'index_condition_pushdown=off';

一个小小的误区

一般来说,正常情况下Mysql一次查询都只能走一个索引,我们来修改上述的表结构,把联合索引改为两个单独的索引,数据保持不变

img

然后我们执行查询explain SELECT * from user where age >10 and name like 'a%',结果如下图。

img

你会发现,我靠,怎么还有索引下推?这不科学对不对,好像无法解释嘛,难道这一次索引下推还能先查出age再下推到name索引吗,这完全不合理啊。

其实不然,真实的情况是,Using index condition并不代表一定是使用了索引下推,只是代表可以使用,但是不一定用了。。。

这个就有点坑爹,可能会对我们判断的时候造成误解啊。

如果你去网上搜很多人举例子这样建索引,然后告诉你这就是索引下推的时候,你可以尽情的喷他了,我们说索引下推一定是在联合索引的情况下,根据联合索引本身就有的数据直接做一次过滤,而不用再进行多次无用的回表再到Server层进行过滤,这一点你要很明确才行。

好了,今天的话题就到这里结束,我是艾小仙,我们下期见。

(本来我想多画两张图的,不过好像觉得这个概念实在太简单了,画的花里胡哨的反而没有意义,就像你说覆盖索引、回表还画好几张图给你解释吗,没有必要对不对,肯定不是因为我懒。。。)


192 声望
68 粉丝
0 条评论
推荐阅读
RabbitMQ、RocketMQ、Kafka延迟队列实现
延迟队列在实际项目中有非常多的应用场景,最常见的比如订单未支付,超时取消订单,在创建订单的时候发送一条延迟消息,达到延迟时间之后消费者收到消息,如果订单没有支付的话,那么就取消订单。

艾小仙阅读 917

从零搭建 Node.js 企业级 Web 服务器(十五):总结与展望
总结截止到本章 “从零搭建 Node.js 企业级 Web 服务器” 主题共计 16 章内容就更新完毕了,回顾第零章曾写道:搭建一个 Node.js 企业级 Web 服务器并非难事,只是必须做好几个关键事项这几件必须做好的关键事项就...

乌柏木75阅读 7.1k评论 16

从零搭建 Node.js 企业级 Web 服务器(一):接口与分层
分层规范从本章起,正式进入企业级 Web 服务器核心内容。通常,一块完整的业务逻辑是由视图层、控制层、服务层、模型层共同定义与实现的,如下图:从上至下,抽象层次逐渐加深。从下至上,业务细节逐渐清晰。视图...

乌柏木45阅读 8.5k评论 6

从零搭建 Node.js 企业级 Web 服务器(二):校验
校验就是对输入条件的约束,避免无效的输入引起异常。Web 系统的用户输入主要为编辑与提交各类表单,一方面校验要做在编辑表单字段与提交的时候,另一方面接收表单的接口也要做足校验行为,通过前后端共同控制输...

乌柏木35阅读 6.7k评论 10

一文搞懂秒杀系统,欢迎参与开源,提交PR,提高竞争力。早日上岸,升职加薪。
前言秒杀和高并发是面试的高频考点,也是我们做电商项目必知必会的场景。欢迎大家参与我们的开源项目,提交PR,提高竞争力。早日上岸,升职加薪。知识点详解秒杀系统架构图秒杀流程图秒杀系统设计这篇文章一万多...

王中阳Go33阅读 2.5k评论 1

封面图
从零搭建 Node.js 企业级 Web 服务器(五):数据库访问
回顾 从零搭建 Node.js 企业级 Web 服务器(一):接口与分层,一块完整的业务逻辑是由视图层、控制层、服务层、模型层共同定义与实现的,控制层与服务层实现了业务处理过程,模型层定义了业务实体并以 对象-关系...

乌柏木34阅读 5k评论 9

从零搭建 Node.js 企业级 Web 服务器(十三):断点调试与性能分析
Node.js 官方提供了断点调试机制,出于安全性考虑默认为关闭状态,可以通过 node 参数 --inspect 或 --inspect-brk 开启,配合 IDE 能够非常方便地调试代码,本章就上一章已完成的项目 licg9999/nodejs-server-ex...

乌柏木31阅读 4.2k评论 9

192 声望
68 粉丝
宣传栏