Java岗大厂面试百日冲刺 – 日积月累,每日三题【Day28】—— 数据库5

云惠网小编 2021年7月28日17:18:00
评论
6399字阅读21分19秒
摘要

百日闭关修炼,每日三道高频面试题。一起冲进大厂!今天是数据库5,看什么看?快上车!

广告也精彩

1、如果建的索引顺序是 (a, b)。而查询的语句是 where b = 1 AND a = ‘陈哈哈’; 为什么还能利用到索引?

2、还有一个特殊情况说明下,下面这种类型的SQL, a 与 b 会走索引,c不会走。

  当某列满足两个条件时,我们可以创建聚集索引:

  为了提升效率,要尽量减少磁盘I/O的次数。实际过程中,磁盘并不是每次严格按需读取,而是每次都会预读。

  聚簇索引像字典,字典按字母顺序排列数据,有序。在聚集索引中,索引包含指向数据存储的块而不是数据存储地址的指针,和非聚集索引(Normal)相反。

以下三种情况却会走索引:

  • 数据存储有序(如自增)
  • key值应当唯一

作者:晓海wiley

每日小结

B-Tree借助计算机磁盘预读机制:


1、聚集索引

  我们可以通过这种方式来找到我们所需要的字,但它需要两个过程,先找到目录中的结果,然后再翻到相应页码。我们把这种目录纯粹是目录,正文纯粹是正文的排序方式(无序)称为“非聚集索引”。

MySQL 建立多列索引(联合索引)有最左匹配的原则,即最左优先:
如果有一个 2 列的索引 (a, b),则已经对 (a)、(a, b) 上建立了索引;
如果有一个 3 列索引 (a, b, c),则已经对 (a)、(a, b)、(a, b, c) 上建立了索引;

  如果想查询学分在60-90之间的学生的学分以及姓名,在学分上创建聚集索引是否是最优的呢?

在这里插入图片描述

  由上图可以看出,B+ 树的数据项是复合的数据结构,同样,对于我们这张表的联合索引 (sex,price,name)来说 ,B+ 树也是按照从左到右的顺序来建立搜索树的,当SQL如下时:

  综上,如果通过调整顺序,就可以解决问题或少维护一个索引,那么这个顺序往往就是我们DBA人员需要优先考虑采用的。



Java岗大厂面试百日冲刺 – 日积月累,每日三题【Day28】—— 数据库5
烟花台风来之前的上海,江浙沪各地的老铁们,保重!

  聚集索引是我们常用的一种索引,该索引中键值的逻辑顺序决定了表中相应行的物理顺序,我们叶子结点直接对应的实际数据,当索引值唯一(unique)时,使用聚集索引查找特定的行效率很高。例如,使用唯一店员 ID 列 emp_id 查找特定雇员的最快速的方法,是在 emp_id 列上创建聚集索引或 PRIMARY KEY 约束。可见,自增主键就是一个标准的聚集索引

2、非聚集索引

  那么这都是为什么呢?我们一起来看看其原理吧。

在这里插入图片描述

当对索引中所有列通过"=" 或 “IN” 进行精确匹配时,索引都可以被用到。

面试题1:说一下你对聚集索引与非聚集索引的理解,以及他们的区别?

在这里插入图片描述

车票

  • 面试题1:说一下你对聚集索引与非聚集索引的理解,以及他们的区别?
    • 追问1:为什么聚集索引可以创建在任何一列上,如果此表没有主键约束,即有可能存在重复行数据呢?
    • 追问2:聚集索引一定比非聚集索引性能优么?
  • 面试题2:说一说你对 B树 和 B+树 的理解吧
  • 面试题3:说一下你对最左前缀原则的理解吧
  • 每日小结

  B树是一种多路自平衡搜索树,它类似普通的二叉树,但是B书允许每个节点有更多的子节点。B树示意图如下:值得注意的是,B树的非叶子节点和叶子结点的data数据都是分开存储的,那么针对范围查询、排序等常用特性就很不友好了。

在这里插入图片描述

  为什么数据库会让我们选择字段的顺序呢?不都是三个字段的联合索引么?这里就引出了数据库索引的最重要的原则之一,最左匹配原则

select sex,price,name from LOL where sex = 2 and price = 6300 and name = 'JJJ疾风剑豪';
主键id  sex(a)   price(b)      name(c)
(1)     1         1350         AAA安妮
(2)     2         6300         MMM盲僧
(3)     1         3150         NNN奈德丽
(4)     2         6300         CCC锤石
(5)     1         6300         LLL龙女
(6)     2         3150         EEE伊泽瑞尔
(7)     2         6300         III艾克
(8)     1         6300         BBB暴走萝莉
(9)     1         4800         FFF发条魔灵
(10)    2         3150         KKK卡牌大师
(11)    1         450          HHH寒冰射手
(12)    2         450          GGG盖伦
(13)    2         3150         OOO小提莫
(14)    2         3150         DDD刀锋之影
(15)    2         6300         JJJ疾风剑豪
(16)    2         450          JJJ剑圣

  这就是MySQL非常重要的原则,即索引的最左匹配原则。

从图中也可以看到,B+树与B树的不同在于:

  通常我们在建立联合索引的时候,相信建立过索引的同学们会发现,无论是Oracle还是 MySQL 都会让我们选择索引的顺序,比如我们想在a,b,c三个字段上建立一个联合索引,我们可以选择自己想要的优先级,(a、b、c),或是 (b、a、c) 或者是(c、a、b) 等顺序。

  非聚集索引就是索引类型为Normal的普通索引啦,我们在《聊聊MySQL索引“B+Tree”的前世今生》这篇文章中提到,B+Tree(这里是索引类型是Normal)所有关键字存储在叶子节点,但不存储真正的data,叶子结点存的是一个指向磁盘data的指针,需要到磁盘数据页中取。

  B+ 树会优先比较 sex 来确定下一步的指针所搜方向,如果 sex 相同再依次比较 price 和 name,最后得到检索的数据;

  其实,这种场景可以通过修改索引顺序为 abc_index:(a,c,b),就可以使三个索引字段都用到索引,建议小伙伴们不要有问题就想着新增索引哦,浪费资源还增加服务器压力。

  要知道的是,你每次创建表,系统会为你自动创建一个基于ID的聚集索引(上述B+树),存储全部数据;你每次增加索引,数据库就会为你创建一个附加索引(上述B+树),索引选取的字段个数就是每个节点存储数据索引的个数,注意该索引并不存储全部数据。

  从上面两个例子大家有木有看出点眉目呢?

二、违背最左原则导致索引失效的情况
(下面以联合索引 abc_index:(a,b,c) 来进行讲解,便于理解)

说索引像一个汉语字典,聚集索引是根据拼音查询,而非聚集索引是根据偏旁部首查询,你想想哪个查的快?

  非聚集索引的数据存储在一个位置,索引存储在另一位置。由于数据和非聚集索引是分开存储的,因此在一个表中可以有多个非聚集索引。

2、查询条件中,缺失优先级居中的索引 “b”
  当 where a =1 and c =“JJJ疾风剑豪” 这样的数据来检索时;B+ 树可以用 a 来指定第一步搜索方向,但由于下一个字段 b 的缺失,所以只能把 a = 1 的数据主键ID都找到,通过查到的主键ID回表查询相关行,再去匹配 c = ‘JJJ疾风剑豪’ 的数据了,当然,这至少把 a = 1 的数据筛选出来了,总比直接全表扫描好多了。

三、查询优化器偷偷干了哪些事儿

2、B+ Tree (B+树是B树的变体,也是一种多路搜索树)

1、查询条件中,缺失优先级最高的索引 “a”
  当 where b = 6300 and c = 'JJJ疾风剑豪' 这种没有以 a 为条件来检索时;B+树就不知道第一步该查哪个节点,从而需要去全表扫描了(即不走索引)。因为建立搜索树的时候 a 就是第一个比较因子,必须要先根据 a 来搜索,进而才能往后继续查询b 和 c,这点我们通过上面的存储结构图可以看明白。



  这时候,就得用“偏旁部首”查了吧,然后根据这个偏旁后的页码来找字。这种结合“部首目录”和“检字表”查到的字的排序并不是真正的正文的排序方法,比如查“张”字,我们可以看到在查部首之后的检字表中“张”的页码是672页,检字表中“张”的上面是“驰”字,但页码却是63页,“张”的下面是“弩”字,页面是390页。很显然,这些字并不是真正的分别位于“张”字的上下方,现在看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序,是字典正文中的字在非聚集索引中的映射。

  并不是。既然只输出两列,我们可以在学分以及学生姓名上创建联合非聚集索引,此时的索引就形成了覆盖索引,即索引所存储的内容就是最终输出的数据,这种索引当然比以学分为聚集索引做查询性能好,算是相当于联合聚集索引~~灵活运用即可。

  在我们开发中经常会遇到这种问题,明明这个字段建了联合索引,但是SQL查询该字段时却不会使用这个索引。难道这索引是假的?白嫖老子资源?!

一、最左匹配原则的原理

  其原因是:如果未使用 UNIQUE 属性创建聚集索引,数据库引擎将向表自动添加一个四字节 uniqueifier列。必要时,数据库引擎 将向行自动添加一个 uniqueifier 值,使每个键唯一。此列和列值供内部使用,用户不能查看或访问。

select * from table where c = '1';
select * from table where b ='1' and c ='2';

  是的,索引abc_index:(a,b,c),只会在where条件中带有(a)、(a,b)、(a,b,c)的三种类型的查询中使用。其实这里说的有一点歧义,其实当where条件只有(a,c)时也会走,但是只走a字段索引,不会走c字段。

作者:對你何止一句钟意

比如索引abc_index:(a,b,c)是a,b,c三个字段的联合索引,下列sql执行时都无法命中索引abc_index;

在这里插入图片描述

  • B+树的磁盘读写代价更低
  • B+树的查询效率更加稳定

  磁盘读取完需要的数据后,会按顺序再多读一部分数据到内存中,这样做的理论依据是计算机科学中注明的局部性原理:

  本栏目Java开发岗高频面试题主要出自以下各技术栈:Java基础知识集合容器并发编程JVMSpring全家桶MyBatis等ORMapping框架MySQL数据库Redis缓存RabbitMQ消息队列Linux操作技巧等。

聚集索引 和 非聚集索引的区别:

B树的特点:

追问1:为什么聚集索引可以创建在任何一列上,如果此表没有主键约束,即有可能存在重复行数据呢?

Java岗大厂面试百日冲刺 – 日积月累,每日三题【Day28】—— 数据库5
  这是一张来自思否上的图片,层次感很清晰,小伙伴可以看到,对于B+树中的联合索引,每级索引都是排好序的。联合索引 bcd_index:(b,c,d) , 在索引树中的样子如图 , 在比较的过程中 ,先判断 b 再判断 c 然后是 d 。

在这里插入图片描述

  1. 所有键值分布在整个树中
  2. 任何关键字出现且只出现在一个节点中
  3. 搜索有可能在非叶子节点结束
  4. 在关键字全集内做一次查找,性能逼近二分查找算法

因此,B+Tree可以对<,<=,=,>,>=,BETWEEN,IN,以及不以通配符开始的LIKE使用索引。且如果用到了该索引,排序功能的消耗大大减少。

  理论上索引对顺序是敏感的,但是由于 MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引,所以 MySQL 不存在 where 子句的顺序问题而造成索引失效。当然了,SQL书写的好习惯要保持,这也能让其他同事更好地理解你的SQL。

  乍一看,这还真是和聚集索引的约束相背,但实际情况真可以创建聚集索引。

假设数据 表 LOL (id,sex,price,name) 的物理位置(表中的无序数据)如下:
(注:下面数据是测试少量数据选用的,只为了方便大家看清楚。实际操作中,应按照使用频率、数据区分度来综合设定索引顺序~)

  汉语字典的正文本身就是一个聚集索引。比如,我们要查“啊”字,拼音是“a”,按照拼音排序是以“a”开头“z”结尾的,那么“啊”字就自然地排在字典的前部。如果翻完了所有以“a”开头的内容仍然找不到这个字,那么就说明字典中就没有这个字。我们知道,其实字典的正文部分本身就是一个目录,不需要再去查其他目录来找到我们需要找的内容。我们把这种正文内容本身就按照一定规则排列(有序)的目录称为“聚集索引”。

  1. 所有关键字存储在叶子节点,非叶子节点不存储真正的data,从而可以快速定位到叶子结点。
  2. 为所有叶子节点增加了一个链指针意味着所有的值都是按顺序存储的,并且每一个叶子页到根的距离相同,很适合查找范围数据。说明支持范围查询和天然排序。
  • 由于磁盘顺序读取的效率很高(不需要寻址时间,只需很少的旋转时间),因此对于具有局部性的程序来说,预读可以提高I/O效率.预读的长度一般为页(page)的整倍数。
  • MySQL(默认使用InnoDB引擎),将记录按照页的方式进行管理,每页大小默认为16K(可以修改)。

比较的次数均衡,减少了I/O次数,提高了查找速度,查找也更稳定。

  1. 单表中只能有一个聚集索引,而非聚集索引单表可以存在多个。
  2. 聚集索引,索引中键值的逻辑顺序决定了表中相应行的物理顺序;非聚集索引,索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同
  3. 索引是通过二叉树的数据结构来描述的,我们可以这么理解聚簇索引:索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点,只不过有一个指针指向对应的数据块
  4. 聚集索引:物理存储按照索引排序;非聚集索引:物理存储不按照索引排序;

  问题来了,遇到不认识的字,不知道它的发音,怎么办?

  小伙伴儿们有没有发现B+树联合索引的规律?感觉还有点模糊的话,那咱们再来看一张索引存储数据的结构图,或许更明了一些。

  今天我们复习了面试中常考的数据库相关的三个问题,你做到心中有数了么?对了,如果你的朋友也在准备面试,请将这个系列扔给他,如果他认真对待,肯定会感谢你的!!好了,今天就到这里,学废了的同学,记得在评论区留言:打卡。,给同学们以激励。

  对于上面这种类型的sql语句;mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(包括like '陈%'这种)。在a、b走完索引后,c已经是无序了,所以c就没法走索引,优化器会认为还不如全表扫描c字段来的快。所以只使用了(a,b)两个索引,影响了执行效率。

  当你在LOL表创建一个联合索引 abc_index:(sex,price,name)时,生成的索引文件逻辑上等同于下表内容(分级排序)

课间休息,又来秀一下来自咱们群里同学的搬砖工地,坐标:深圳

select * from LOL where a = 2 and b > 1000  and c='JJJ疾风剑豪';

1、B树(Balanced Tree)多路平衡查找树 多叉

面试题2:说一说你对 B树 和 B+树 的理解吧

  大家好,我是陈哈哈,北漂五年。相信大家和我一样,都有一个大厂梦,作为一名资深Java选手,深知面试重要性,接下来我准备用100天时间,基于Java岗面试中的高频面试题,以每日3题的形式,带你过一遍热门面试题及恰如其分的解答。

  一路走来,随着问题加深,发现不会的也愈来愈多。但底气着实足了不少,相信不少朋友和我一样,日积月累才是最有效的学习方式!想起高三时一个同学的座右铭:只有沉下去,才能浮上来。共勉(juan)。

select * from table where a = '1';
select * from table where a = '1' and b = '2';
select * from table where a = '1' and b = '2'  and c='3';

B+树的优点:

在这里插入图片描述

  每次新建节点的时候,都是申请一个页的空间,所以每查找一个节点只需要一次I/O;因为实际应用当中,节点深度会很少,所以查找效率很高.

在这里插入图片描述

追问2:聚集索引一定比非聚集索引性能优么?

首先解释一下,什么是聚集索引和非聚集索引。这里我想起网上看到的一个典型的例子:

陈小哈,一个爱睡懒觉的崽子。工作日的它却总爱发呆~

sex(a)   price(b)       name(c)         主键id
1        450            HHH寒冰射手      (11)
1        1350           AAA安妮          (1)
1        3150           NNN奈德丽        (3)
1        4800           FFF发条魔灵       (9)
1        6300           BBB暴走萝莉       (8)
1        6300           LLL龙女          (5)
2        450            GGG盖伦          (12)
2        450            JJJ剑圣          (16)
2        3150           DDD刀锋之影       (14)
2        3150           EEE伊泽瑞尔       (6)
2        3150           KKK卡牌大师       (10)
2        3150           OOO小提莫         (13)
2        6300           CCC锤石          (4)
2        6300           III艾克          (7)
2        6300           JJJ疾风剑豪       (15)
2        6300           MMM盲僧          (2)

面试题3:说一下你对最左前缀原则的理解吧

本文转自 https://blog.csdn.net/qq_39390545/article/details/119112228

腾讯云618
云惠网小编
Java—代码块详解 java

Java—代码块详解

代码块概念代码块分类普通代码块构造代码块静态代码块同步代码块代码块概念在java中,{ } 括起来的代码被称为代码块代码块分类普通代码块类中方法的方法体public class M...
腾讯云618

发表评论