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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 索引下推(Index Condition Pushdown) ICP 是Mysql5.6之后新增的功能,主要的核心点就在于把数据筛选的过程放在了存储引擎层去处理,而不是像之前一样放到Server层去做过滤。虽然这是一个比较简单的概念,但是可能很多不细心的同学对于索引下推会存在一个小小的误区,至于是什么,请看下文。

什么是索引下推

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

a48f51fbcd8a828480afb8272439823d.jpg

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

102e5e463bcf9ffeb51e914a5e432906.jpg

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

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

df383878854040da1e3eaccafe0873ad.jpg

在没有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一次查询都只能走一个索引,我们来修改上述的表结构,把联合索引改为两个单独的索引,数据保持不变

194a8ab59c92e954a842db84dbe1d076.jpg

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

0657711e70e797a21888663c774ff2bd.jpg

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

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

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

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

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

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


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
6月前
|
SQL 存储 关系型数据库
对线面试官 - 如何理解MySQL的索引覆盖和索引下推
索引下推是MySQL 5.6引入的优化,允许部分WHERE条件在索引中处理,减少回表次数。例如,对于索引(zipcode, lastname, firstname),查询`WHERE zipcode='95054' AND lastname LIKE '%etrunia%'`时,索引下推先过滤zipcode,然后在索引中应用lastname条件,降低回表需求。索引下推可在EXPLAIN的`Using index condition`中看到。
对线面试官 - 如何理解MySQL的索引覆盖和索引下推
|
19天前
|
数据库 索引
深入探索数据库索引技术:回表与索引下推解析
【10月更文挑战第15天】在数据库查询优化的领域中,回表和索引下推是两个核心概念,它们对于提高查询性能至关重要。本文将详细解释这两个术语,并探讨它们在数据库操作中的作用和影响。
42 3
|
19天前
|
数据库 索引
深入理解数据库索引技术:回表与索引下推详解
【10月更文挑战第23天】 在数据库查询性能优化中,索引的使用是提升查询效率的关键。然而,并非所有的索引都能直接加速查询。本文将深入探讨两个重要的数据库索引技术:回表和索引下推,解释它们的概念、工作原理以及对性能的影响。
36 3
|
5月前
|
关系型数据库 MySQL 数据库
MySQL索引优化:深入理解索引合并
MySQL索引优化:深入理解索引合并
|
5月前
|
存储 SQL 关系型数据库
MySQL索引下推:原理与实践
MySQL索引下推:原理与实践
|
6月前
|
SQL 存储 关系型数据库
MySQL索引原理(索引、约束、索引实现、索引失效、索引原则)以及SQL优化
MySQL索引原理(索引、约束、索引实现、索引失效、索引原则)以及SQL优化
176 1
|
SQL 存储 关系型数据库
你不得不知的MYSQL优化——索引下推
你不得不知的MYSQL优化——索引下推
233 0
你不得不知的MYSQL优化——索引下推
|
SQL 算法 关系型数据库
MySQL索引优化(为排序)
MySQL索引优化(为排序)
89 0
MySQL索引优化(为排序)
|
存储 SQL 关系型数据库
|
存储 SQL 关系型数据库
索引到底能提升多少查询效率?何时该使用索引?一文快速搞懂数据库索引及合理使用它
索引到底能提升多少查询效率?何时该使用索引?一文快速搞懂数据库索引及合理使用它
663 0
索引到底能提升多少查询效率?何时该使用索引?一文快速搞懂数据库索引及合理使用它