我的编程空间,编程开发者的网络收藏夹
学习永远不晚

最左前缀有手就会,那索引下推呢?

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

最左前缀有手就会,那索引下推呢?

联合索引的最左前缀原则属于面试高频题,想必大部分同学都知道一些,但是,那些不符合最左前缀的部分,会怎么样呢(索引下推)

索引下推不算高频题,知道的同学应该不是很多(不过并不代表有啥难度哈,挺简单的),学起来装波杯

老规矩,背诵版在文末。点击阅读原文可以直达我收录整理的各大厂面试真题

引子

看下面这张用户表,包含主键 id、身份证号 id_card、姓名 name、年龄 age和性别 sex,并且在 id_card 上建立了辅助索引(普通索引/非聚集索引)

如果现在有一个高频请求,要根据市民的身份证号查询他的姓名:

  1. select name from user where id_card = xxx; 

众所周知,这会导致回表查询,通过 id_card 这棵辅助索引树只能找到主键 id,然后需要再回到主键索引(聚集索引)树上根据主键 id 查找相应的 name。

所以,这个时候,我们可以建立一个 (id_card, name) 的联合索引来进行优化,对于这条语句来说,也就是覆盖索引,在这个高频请求上用到覆盖索引,不再需要回表查整行记录,大幅减少了语句的执行时间。

不过,索引字段的维护总是有代价的,如果为每一种查询都设计一个联合索引,索引是不是太多了?反过来说,单独为一个不频繁的请求创建一个联合索引是不是有点浪费了。因此在建立冗余索引来支持覆盖索引时就需要我们去做出一些权衡考虑了。

具体来说,我们应该怎么做呢?

最左前缀原则

B+ 树这种索引结构,可以利用联合索引的 “最左前缀” 来定位记录。

何为联合索引的最左前缀原则?

从本质上来说,联合索引也是一棵 B+ 树,不同的是联合索引的键值的数量不是 1,而是大于等于 2。我们来看下两个整型列组成的联合索引,假定两个键值的名称分别为 a、b。

从图中可以看到多个键值的 B+ 树情况,键值都是排序的。通过叶子节点可以逻辑上顺序读取所有数据,就上面图中所示,即为 (1,1)、(1、2)、(2、1)、(2、4)、(3、1)、(3、2),数据是按照 (a, b) 的顺序进行存放。

?? 这里 “**键值都是排好序” 的这种说法可能会让大伙很疑惑,**似乎只有 a 列是排序的,b 列并没有排序啊。

注意!这里的排序,意思是确定了第一个键,对于第一个键相同的记录来说,查询的结果是对第二个键进行了排序。

这也是**使用联合索引的第二个好处,即已经对第二个键值进行了排序处理,可以避免多一次排序操作。**举个例子:有些应用程序都需要查询某个用户的购物情况,并按照时间进行排序,取出最近 n 次的购买记录,这时使用联合索引就可以避免多一次排序操作,因为索引本身在叶子节点已经排序了。

更进一步说,假设有联合索引(a,b,c),下列语句可以直接通过联合索引得到结果:

  • SELECT ... FROM TABLE WHERE a=xxx ORDER BY b
  • SELECT ... FROM TABLE WHERE a=xxx AND b=xxx ORDER BY c

但是对于下面的语句,联合索引不能直接得到结果,其还需要执行一次排序操作,因为索引 (a,c) 并未排序:

  • SELECT ... FROM TABLE WHERE a=xxx ORDER BY c

说了这么多,好像和最左前缀啥关系也没有啊

考虑下,对于下面这条语句,能否用到联合索引(a, b)?

  1. select * from table where a = XXX and b= XXX; 

这个当然没问题。

那对于 a 列的单独查询,能否用到联合索引(a, b)?

  1. select * from table where a = XXX; 

当然也可以,我们不是说了,a 列是已经排好序的。

但是对于 b 列的单独查询则不能使用联合索引(a, b)!

  1. select * from table where b = XXX; 

因为把叶子节点中的 b 值单独拎出来看它不是有序的:1、2、1、4、1、2,因此对于 b 的查询是使用不到 (a,b) 这个联合索引的。

同样的道理,对于(a, b, c)联合索引来说,查询 (a, b) 可以用到这个联合索引,但是查询 (b, c) 就没办法使用这个联合索引,因为 b 和 c 列的有序性都是依托于 a 列的存在的。

This,就是联合索引的最左前缀原则,只要查询的是联合索引的最左 N 个字段,就可以利用该联合索引来加速查询。

基于上面对最左前缀索引的说明以及用户表的例子,我们来讨论一个问题:在建立联合索引的时候,如何安排索引内的字段顺序?

有两点原则。

首先,第一原则,如果通过调整顺序,可以少维护一个索引,那么这个字段顺序往往就是需要优先考虑采用的

很好理解,当已经有了 (a,b) 这个联合索引后,一般就不需要单独在 a 上建立索引了。

那么,再思考一个问题:如果既有联合查询 (a,b),又有基于 a、b 各自的查询呢?

显然,如果查询条件里面只有 b 的语句,是无法使用 (a,b) 这个联合索引的,这时候你不得不维护另外一个 b 列的索引,也就是说你需要同时维护 (a,b)、(b) 这两个索引。

举个用户表例子,有这样三个高频查询需求:

  • 根据 name 查询 id:select id from user where name = xxx;
  • 根据 age 查询 id:select id from user where age= xxx;
  • 根据 name 和 age 查询 id:select id from user where name = xxx and age = xxx;

这个时候,我们有两种索引建立的选择:

  • 联合索引 (age, name) + 单字段索引 (name)
  • 联合索引 (name, age) + 单字段索引 (age)

怎么选?

这种场景下,我们要考虑的原则就是空间。

显然,name 字段是要比 age 字段大的,所以,第二种选择占用的空间要小于第一种选择,推荐大伙儿使用第二种选择:联合索引 (name, age) + 单字段索引 (age)

索引下推

最左前缀可以用于在索引中定位记录,那么,那些不符合最左前缀的部分,会怎么样呢?

以用户表的联合索引(name, age)为例,假设现在有一个需求,找出所有姓 “张” 并且 20 岁的男性:

  1. select * from tuser where name like '张%' and age = 20 and sex = male 

《高性能 MySQL》 书中提到:对于联合索引,如果查询中有某个列的范围查询,则其右边所有列都无法使用索引进行快速定位。

所以对于这条语句来说,其实并不能完全踩中 (name, age) 这个联合索引,他只能踩到 name。

具体来说,这个语句在搜索(name,age)的联合索引树的时候,并不会去看 age 的值,只是按顺序把 “name 第一个字是张” 的记录一条条取出来,然后开始回表,到主键索引上找出数据行,再一个一个判断其他条件是否满足。从下图可以看出来,需要回表 3 次。

这是 MySQL 5.6 之前的做法,简单总结,当进行索引查询时,首先根据索引来查找记录,然后再根据 where 条件来过滤记录。

而 MySQL 5.6 开始,数据库在取出索引的同时,会根据 where 条件直接过滤掉不满足条件的记录,减少回表次数。这就是 索引下推 (Index Condition Pushdown,ICP) ,一种根据索引进行查询的优化方式。

从图中可以看出来,InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 20,对于不等于 20 的记录,直接判断并跳过,所以只需要对 ID1 这条记录进行回表判断就可以了。

面试官:讲一下联合索引的最左前缀原则,为什么得最左匹配,不按照这个来为什么失效?

小牛肉:最左前缀原则就是只要查询的是联合索引的最左 N 个字段,就可以利用该联合索引来加速查询。

不按照最左匹配来为什么失效,其原因就在于联合索引的 B+ 树中的键值是排好序的。不过,这里指的排好序,其实是相对的,举个例子,有 (a, b, c) 联合索引,a 首先是排序好的,而 b 列是在 a 列排序的基础上做的排序,同样的 c 是在 a,b 有序的基础上做的排序。所以说,如果有 where a = xxx order by b = xxx 这种请求的话,是可以直接在这颗联合索引树上查出来的,不用对 b 列进行额外的排序;而如果是 where a = xxx order by c = xxx 这种请求的话,还需要额外对 c 列进行一次排序才行。

另外,如果有对 a,b,c 的联合条件查询的话,并且 a 是模糊匹配或者说是范围查询的话,其实并不能完全踩中联合索引(a,b,c),a 列右边的所有列都无法使用索引进行快速定位了。所以这个时候就需要进行回表判断。也就是说数据库会首先根据索引来查找记录,然后再根据 where 条件来过滤记录。

不过在 MySQL 5.6 中支持了索引下推 ICP,数据库在取出索引的同时,会根据 where 条件直接过滤掉不满足条件的记录,减少回表次数。

 

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

最左前缀有手就会,那索引下推呢?

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

最左前缀有手就会,那索引下推呢?

联合索引的最左前缀原则属于面试高频题,想必大部分同学都知道一些,但是,那些不符合最左前缀的部分,会怎么样呢(索引下推)?

MySQL索引 索引分类 最左前缀原则 覆盖索引 索引下推 联合索引顺序 - G

MySQL索引 索引分类 最左前缀原则 覆盖索引 索引下推 联合索引顺序 What"s Index ?索引就是帮助RDBMS高效获取数据的数据结构。索引可以让我们避免一行一行进行全表扫描。它的价值就是可以帮助你对数据进行快速定位。 索引分类按照功能逻辑来分普通
MySQL索引 索引分类 最左前缀原则 覆盖索引 索引下推 联合索引顺序 - G
2018-09-14

编程热搜

  • Python 学习之路 - Python
    一、安装Python34Windows在Python官网(https://www.python.org/downloads/)下载安装包并安装。Python的默认安装路径是:C:\Python34配置环境变量:【右键计算机】--》【属性】-
    Python 学习之路 - Python
  • chatgpt的中文全称是什么
    chatgpt的中文全称是生成型预训练变换模型。ChatGPT是什么ChatGPT是美国人工智能研究实验室OpenAI开发的一种全新聊天机器人模型,它能够通过学习和理解人类的语言来进行对话,还能根据聊天的上下文进行互动,并协助人类完成一系列
    chatgpt的中文全称是什么
  • C/C++中extern函数使用详解
  • C/C++可变参数的使用
    可变参数的使用方法远远不止以下几种,不过在C,C++中使用可变参数时要小心,在使用printf()等函数时传入的参数个数一定不能比前面的格式化字符串中的’%’符号个数少,否则会产生访问越界,运气不好的话还会导致程序崩溃
    C/C++可变参数的使用
  • css样式文件该放在哪里
  • php中数组下标必须是连续的吗
  • Python 3 教程
    Python 3 教程 Python 的 3.0 版本,常被称为 Python 3000,或简称 Py3k。相对于 Python 的早期版本,这是一个较大的升级。为了不带入过多的累赘,Python 3.0 在设计的时候没有考虑向下兼容。 Python
    Python 3 教程
  • Python pip包管理
    一、前言    在Python中, 安装第三方模块是通过 setuptools 这个工具完成的。 Python有两个封装了 setuptools的包管理工具: easy_install  和  pip , 目前官方推荐使用 pip。    
    Python pip包管理
  • ubuntu如何重新编译内核
  • 改善Java代码之慎用java动态编译

目录