精华内容
下载资源
问答
  • MySQL索引底层

    2020-10-26 15:43:52
    MySQL索引底层一、聚簇索引和非聚簇索引是什么?他们在索引树的叶子节点上存储数据有什么区别?二、覆盖索引三、索引失效的底层原理 一、聚簇索引和非聚簇索引是什么?他们在索引树的叶子节点上存储数据有什么区别?...

    一、聚簇索引和非聚簇索引是什么?他们在索引树的叶子节点上存储数据有什么区别?

    聚簇索引:并不是一种单独的索引类型,而是一种存储数据方式,聚簇的意思就是数据行和相邻的键值紧凑的存储在一起,因为无法同时把数据行放在两个不同的地方,所以一个表只能有一个聚簇索引。
    聚簇索引的优点:数据访问的更快,因为聚簇索引将索引和数据保存在同一个索引树中,因此从聚簇索引中获取数据通常比在非聚簇索引中查找更快。
    InnoDB中的聚簇索引:InnoDB默认对主键建立聚簇索引,如果你不指定主键,InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引,InnoDB会定义一个隐藏的主键,然后对其建立聚簇索引。一般来说,InnoDB会以聚簇索引的形式来存储实际的数据,它是其他二级索引的基础。
    非聚簇索引:就是普通索引,它的索引树树的叶子节点存储的数据是主键列。因为对于非聚簇索引查询来说,他是无法通过一次扫描树然后就可以定位行记录。
    举例:

    有表 table(id,name,sex,flag);
    id为主键(聚簇索引),name是普通索引。
    
    id name sex flag
    1 shenjian m A
    3 zhangsan m A
    5 lisi m A
    9 wangwu f B

    两种索引在索引树中的数据存储如下图:
    在这里插入图片描述

    1.id为聚簇索引,叶子节点存储行记录;
    2.name为普通索引,叶子节点存储主键值,即id;
    既然普通索引无法直接定位行记录,那么普通索引的查询过程是什么?

    通常情况下要扫描两次索引树
    select * from where name = 'lisi';
    

    在这里插入图片描述
    图中的扫描过程为:
    1.先通过普通索引定位到主键的ID = 5;
    2.再通过聚簇索引定位到行记录;
    这就是所谓的回表查询,先定位到行记录,然后扫描聚簇索引的索引树。它的性能较扫描一遍索引树效率更低。

    二、覆盖索引

    覆盖索引:一个索引中包含所有需要查询字段的值。
    即Explain的输出结果Extra字段为Using index时,能触发索引覆盖。
    如何实现索引覆盖:将被查询的字段,建立到联合索引里去。
    使用覆盖索引的好处:无需回表,少了一个索引树的扫描。

    三、索引失效的底层原理

    只讨论最左前缀法则失效问题。
    联合索引排序的原理
    先对第一个字段进行排序,在第一个字段相同的情况下对第二个字段排序,以此类推。
    大多数情况下两个字段的值怎么会相同?所以很容易引发索引失效问题。

    在这里插入图片描述
    a的值是有顺序的:1 , 1, 2,2,3,3
    b的值是没有顺序的:1,2,1,4,1,2
    范围查找右边的索引会失效

    explain select * from test where a > 1 and b = 1;
    

    我们可以找到a>1的所有节点,但是此时b索引是无序的,二分查找没办法继续下去了。

    展开全文
  • MySql索引底层

    2019-08-16 16:03:56
    好久没有写博客了,前几天看了一个关于索引底层原理的学习视频,虽然是技术小白,但也是整理下自己学到或者理解的一些知识吧,不喜勿喷,欢迎交流! MySql索引的底层原理 1.索引的定义 MySQL官方对索引的定义为:...
       好久没有写博客了,前几天看了一个关于索引底层原理的学习视频,虽然是技术小白,但也是整理下自己学到或者理解的一些知识吧,不喜勿喷,欢迎交流!
    

    MySql索引的底层原理
    1.索引的定义
    MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。
    2.索引的分类
    常见的索引类型有:主键索引、唯一索引、普通索引、全文索引、组合索引
    1、主键索引:即主索引,根据主键pk_clolum(length)建立索引,不允许重复,不允许空值;
    2、唯一索引:用来建立索引的列的值必须是唯一的,允许空值
    3、普通索引:用表中的普通列构建的索引,没有任何限制
    4、全文索引:用大文本对象的列构建的索引
    5、组合索引:用多个列组合构建的索引,这多个列中的值不允许有空值
    *遵循“最左前缀”原则,把最常用作为检索或排序的列放在最左,依次递减,组合索引相当于建立了col1,col1col2,col1col2col3三个索引,而col2或者col3是不能使用索引的。
    *在使用组合索引的时候可能因为列名长度过长而导致索引的key太大,导致效率降低,在允许的情况下,可以只取col1和col2的前几个字符作为索引
    3.索引的优缺点
    **优势:**可以快速检索,减少I/O次数,加快检索速度;根据索引分组和排序,可以加快分组和排序;
    **劣势:**索引本身也是表,因此会占用存储空间,一般来说,索引表占用的空间的数据表的1.5倍;索引表的维护和创建需要时间成本,这个成本随着数据量增大而增大;构建索引会降低数据表的修改操作(删除,添加,修改)的效率,因为在修改数据表的同时还需要修改索引表;
    4.索引的实现原理
    我们知道,数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快,因此数据库系统的设计者会从查询算法的角度进行优化。如果稍微分析一下会发现,每种查找算法都只能应用于特定的数据结构之上,所以,在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。
    在这里插入图片描述
    这是正常的一张表,假设我们需要查询col2=23这条数据,
    select * from table where col2=23;
    当我们没有建任何索引的时候,数据库会从第一条开始依次查询,基于最基本的查询算法:顺序查找(linear search),然后足足经过7次查询,才找到我们需要的数据.,这样的情况就是当数据量一大,会导致我们的数据查询会非常的慢。
    这时候,索引就能帮助我们加快提高查询的效率了。
    计算机科学的发展提供了很多更优秀的查找算法,例如二叉树查询算法,B树查询算法,B+树查询算法,索引就是通过指向这些优秀的算法然后实现效率的提高。
    二叉树:
    这就是二叉树一种大概的结构:(左边<右边,根节点>子节点)
    在这里插入图片描述
    我们将col2设为索引,同样我们取col2=23这条数据,当数据库查询完第一条数据时,会将34与第一条数据的34比较,如果是小,就从左边走,如果是大,就走右边,然后查到第二条数据再次比较,直到查询到col2=23这条数据,这样只需要经过3次的查询之后就能找到所需要的数据了,跟之前的7次提高了很大的效率。但如果是极端的情况,设col1为索引,查询col1=7的数据,那怕是使用二叉树查询算法,也是没有任何的效率提升,需要足足查询7次。(同样这样的提升也是可见的,一旦数据量一大,到百万,千万级数据量的时候,也是会非常的缓慢)
    B树
    这是一种更效率的数据结构算法,上面的二叉树图,每个子节点就放一个数据,B树就是在这个基础上,我每个子节点不在只放一个数据,而是放多个数据,这样查询的次数就会再次缩减,达到优化查询的目的
    在这里插入图片描述
    那很多人会产生两个疑问:
    1.一个节点我放多个数据,那我干脆所有的数据都放在一个节点内,那是不是代表我只要查一次就直接查出来了?
    答:每个节点的是有大小的,无法所有的数据都放在一个节点里面。
    2.一个节点放两个数据,是不是代表也需要查询两次?那效率还是没有提升。
    答:mysql的数据查询是对于磁盘上的数据进行查询,而节点上的查询,是在内存上进行的查询,而内存上查询的效率是远远高于磁盘上查询的效率。
    但B树查询算法也有一定的局限,假设我查询的数据时没有规则的几个,那我每次查询到一个数据之后,我还需要回到根节点,然后重新再从根节点再查询接下来的数据。
    B+树(Mysql索引用到的底层数据结构)
    在这里插入图片描述
    B+树是对于B树的一种优化,同样是每个节点会有多个数据,在B+树的每个叶子节点增加一个指向相邻叶子节点的指针,就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能,例如图中如果要查询从18到49的所有数据记录,当找到18后,只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点,极大提到了区间查询效率。

    展开全文
  • mysql索引底层

    2020-03-23 19:25:11
    下面就写写我今天学习到的mysql索引的原理吧。 在聊mysql的索引原理之前,我觉得有必要跟大家解释一下什么是索引,索引顾名思义就是可以按照线索的指引去找到答案,比如牛津字典,在字典的前面会有目录,其实这里的...

    参考链接:掘金

    今天聊聊mysql的索引吧

    今天看了一篇掘金平台的一篇关于mysql的索引与调优的文章,我觉得作者写的特别好理解而且全面,传送链接在本文末尾哦。

    下面就写写我今天学习到的mysql索引的原理吧。

    在聊mysql的索引原理之前,我觉得有必要跟大家解释一下什么是索引,索引顾名思义就是可以按照线索的指引去找到答案,比如牛津字典,在字典的前面会有目录,其实这里的目录就相当于索引,你想找一个英文mysql的注解,你第一个肯定会先去目录里面找到m开头的目录,找到m,那就省去了25/26的时间全本逐一单词寻找的时间了,是不是感觉有了索引,整个世界都变快了。

    好,进入正题

    作为一个免费、开源又成熟的关系型数据管理系统,mysql是很受欢迎的,因为是开源的,所以很多企业都可以在此基础上开发自己的存储引擎,而Mysql现在默认的存储引擎是Innodb,在此之前默认是MySIAM,可见Innodb的性能更胜一筹。今天主要聊聊这两种存储引擎的索引结构。

    有些同学可能不太理解存储引擎是什么,给大家看下mysql的整体架构,就知道存储引擎是什么了,这里就不再深入了,想了解的可以看这个存储引擎

    B+Tree出现的背景

    InnodbMySIAM两种存储引擎用的索引结构都是B+Tree类型,但两者之间具体的使用的又稍有不同,后面会讲。

    我们都知道mysql的数据是存储在磁盘中的,那如果没有索引的话,我们正常读取数据是什么操作呢?比如我们有一些数据3,5,9,10,13,15,28,29存储在磁盘上,如果我们要找数据为29的数据,我们假设每个数据占用一个页,则系统就先通过磁盘io把3的数据读到内存,然后判断29大于3,然后又触发磁盘io接着读取下一页,直到找到数据29,这样就触发了多次磁盘io,磁盘io需要的时间是很致命,你想象一下,如果数据量是100万,那就是100万次磁盘io,那岂不是灾难级别的操作?

    那对此我们有什么解决方案呢?

    我们都知道二叉查找树吧,查找数据的时间复杂度是log(2,N),2是底数,但随着数据的增多,二叉树的高度也会变的越来越高,因为我们知道索引都是存在磁盘的,虽然二叉查找树能减少查询时间,但随着树的高度变高,那系统的磁盘io也会变多。

    这时B+Tree就诞生了,B+Tree是一个多路查找树,通过增加一个节点存储多条数据来实现降低树的高度(一般一个节点存储的数据量是一页4k),这样磁盘io操作次数也会降低很多,一般百万级别的数据,树的高度也就3,也就是只需要3次磁盘io,是不是很惊人。

    B+Tree的结构

    B+Tree数据结构是怎样的呢?直接上图。
    b+tree索引结构
    浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包含数据项17和35,包含指针P1、P2、P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。
    原文链接

    这时查找29这个数据项他的查询路径是这样的。先从磁盘中读取根节点,然后判断得29在17与35之间,所以得到指针p2,然后将指针p2指向的节点从磁盘中读取到内存,判断得29在26与30之间,所以得到p2指针,将p2指针指向的叶子节点从磁盘读取到内存,然后在内存中根据二分查找叶子节点中的数据得到29,所以整个过程下来只需要3次磁盘io就能找到数据。

    B+树性质

    1. 通过上面的分析,我们知道数据越小,数据项的数量越多,树的高度越低。这就是为什么每个数据项,即索引字段要尽量的小,比如int占4字节,要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点,一旦放到内层节点,磁盘块的数据项会大幅度下降,导致树增高。当数据项等于1时将会退化成线性表。
    2. 当b+树的数据项是复合的数据结构,比如(name,age,sex)的时候,b+数是按照从左到右的顺序来建立搜索树的,比如当(张三,20,F)这样的数据来检索的时候,b+树会优先比较name来确定下一步的所搜方向,如果name相同再依次比较age和sex,最后得到检索的数据;但当(20,F)这样的没有name的数据来的时候,b+树就不知道下一步该查哪个节点,因为建立搜索树的时候name就是第一个比较因子,必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时,b+树可以用name来指定搜索方向,但下一个字段age的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是F的数据了, 这个是非常重要的性质,即索引的最左匹配特性。
      原文链接

    MySQL 索引实现

    既然索引可以提升我们查找数据的速度,那他是怎么实现的呢?也就是B Tree是怎么实现的呢?因为索引是引擎级别的概念,所以不同引擎他对B Tree的实现是不同的,这也是针对特定引擎使用场景选择的。

    MySIAM存储引擎:

    MySIAM存储引擎采用的是B Tree数据结构实现索引,他的节点是不存储实际数据的,只是数据的指向,他的叶子节点存放的也是指向具体数据真实存放的地址,这样查找某个数据时,先是通过在树中找到这个数据所在的叶子节点,然后通过叶子节点的指向地址找到真实数据。

    主键索引

    是主键索引,假设他只有三个字段col1,col2,col3,而且col1是主键,则从上图中可以看出,索引结构上的节点只存储数据记录的地址,主键索引要求数据是唯一性的,但辅助索引数据是可以重复的,我们假设col2是辅助索引,下面是辅助索引的树状数据结构,其实跟主键是一样的。

    辅助索引

    MySIAM这种索引结构也叫非聚簇索引,为了跟INNODB的(聚簇)索引结构区分开。

    INNODB索引结构:

    其实更多人喜欢叫INNODB的索引结构为B+Tree,但与MySIAM是一样的,都是B Tree类型,那INNODBMySIAM的索引结构有哪些不同呢?

    最大的区别是INNODB的叶子节点存储的是真实的数据,并不是数据的地址,这也是为什么叫聚簇索引,因为数据都直接存在了叶子节点,不用根据叶子节点的地址再去磁盘寻找数据。

    下面是INNODB的索引结构图:

    innodb索引结构

    INNODB索引要求要按主键聚集,所以INNODB存储引擎都是要求有一个主键,而MySIAM不需要,如果没有显示指定,INNODB会隐式创建一个大小为6字节长度的主键id作为主键索引存储,从图中可以看出按主键查找数据非常快。

    我们再来看看INNODB的辅助索引结构是怎么样的

    innodb辅助索引结构

    从辅助索引可以看出,辅助索引的叶子节点存储的是这个辅助字段所在的行的主键,要通过辅助索引获取完整数据得先获取主键,然后根据主键获取完整数据,所以需要遍历两次索引数据。

    了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助,例如知道了InnoDB的索引实现后,就很容易明白为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。再例如,用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。

    如何建立合适的索引

    我想这是大多数最想了解的,这也是我们今天这篇文章的重点内容,大家在了解了索引的底层实现原理之后,就不难理解为啥要这么建立索引,这样使用索引了。

    1.最左原则
    可以说这是最重要的,也是最应该注意的原则,因为你可以建立一个组合索引,里面包括多个字段,那这些字段的排序有什么要求,这就是一个学问了,字段不是随意摆放的,比如有个组合索引(A,B,C)

    • 那么查询的时候,如果查询【A】【A,B】 【A,B,C】,那么可以通过索引查询
    • 如果查询的时候,采用【A,C】,那么C这个虽然是索引,但是由于中间缺失了B,因此C这个索引是用不到的,只能用到A索引
    • 如果查询的时候,采用【B】 【B,C】 【C】,由于没有用到第一列索引,不是最左前缀,那么后面的索引也是用不到了
      如果查询的时候,采用范围查询,并且是最左前缀,也就是第一列索引,那么可以用到索引,但是范围后面的列无法用到索引

    2.主键字段的选择
    我们都知道了INNODB索引都是要求有主键的,但该如何选择什么字段做主键索引合适呢?

    从上面的索引结构中我们了解到数据是存放在叶子节点上的,而且是按照主键从小到大排序的,我们一个叶子节点存储的是一页的数据,也就是说一个叶子节点上会存放很多的主键,而且都是排好序的,如果你的主键字段是自增的,那每次插入新数据的时候,都会顺序添加到当前索引节点的后续位置,如果叶子节点满了,会开辟一个新的叶子节点存储数据。假如你的主键不是自增的,而是随机的,比如学号或者身份证号,那你每次要新增一个数据时,先查找这个id他应该要插入的位置,找到后插入到这个位置,那原本这个位置已经有其他数据了,所以你不得不移动后面的数据,这样就增加了移动数据的开销。

    节点

    所以请尽量选择自增字段作为主键。

    建立索引的常用技巧

    • 最左匹配原则,mysql会一直向最右匹配数据,直到遇到范围查询( <, >, between, like ) 才会停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
    • =和in可以乱序,比如a=1 and b=2 and c=3,建立(a,b,c)可以乱序,mysql优化器可以帮您优化成索引可以识别的形式
    • 尽量选择区分度高的字段作为索引,区分度公式是count(distinct a) / count(*),表示字段不重复的比例,比例越接近1,说明越适合建立索引,因为他过滤的值会越多,那这个值多少才合适呢?看具体使用场景,比如join操作我们要求on后面的字段的值是0.1以上,也就是平均1条只需要扫描10条。
    • 索引列不能参与计算,比如 where a + 1 = 10,这样的查询是用不到索引a的,因为我们知道索引节点上保持的是索引的值,如果进行计算,那局势每个节点都要进行计算去对比,显然这样查找成本会变高。但如果使用where a = 10-1,这样就能使用到索引,所以要保持索引列的独立性。

    尽量扩展索引,不要新建索引,比如原本已经有一个索引(a),有业务需要在a,b上建立索引,只需要在原来的基础上扩展为(a,b),而不用再建一个(b)当然视场景而定,扩展主要是为了减少遍历多个索引,维护多个索引。

    explain语句

    不知道大家对这个语句熟不熟悉,这是非常有用的语句,可以分析你业务上跑的sql是不是有用到索引,有哪些优化空间。
    执行explain语句后会返回很多字段,每个字段所代表的意义这里解释一下。

    mysql> explain select * from servers;±—±------------±--------±-----±--------------±-----±--------±-----±-----±------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±--------±-----±--------------±-----±--------±-----±-----±------+| 1 | SIMPLE | servers | ALL | NULL | NULL | NULL | NULL | 1 | NULL |±—±------------±--------±-----±--------------±-----±--------±-----±-----±------+1 row in set (0.03 sec)

    • id:表示sql顺序执行的标识,sql从大到小执行
    • select_type:表示查询中每个select子句的类型
    • table:表示这次查询的表名
    • type:表示表中找到所需行的方式,又称”访问类型“,常用的类型有:
    类型 描述
    ALL 全表扫描
    index 索引全扫描
    range 索引范围扫描,常用语<,<=,>=,between等操作
    ref 使用非唯一索引扫描或唯一索引前缀扫描,返回单条记录,常出现在关联查询中
    eq_ref 类似ref,区别在于使用的是唯一索引,使用主键的关联查询
    const/system 单条记录,系统会把匹配行中的其他列作为常数处理,如主键或唯一索引查询
    null MySQL不访问任何表或索引,直接返回结果性能从上到下是 差 => 好
    • possible_keys:能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用
    • key:表示mysql在查找时实际使用的索引名称,如果没有则是null
    • key_len:表示索引中使用的字节数,可通过改列计算出查询时使用的索引的长度
    • rel:表示上述表的连接匹配条件,即哪些列或常量是被用在查询表索引列上的值
    • rows:表示mysql根据统计表信息及索引使用情况,在查到这些记录所需要扫描的行数,理论上值越少,代表性能越好
    • extra:该列表示mysql查找记录的其他解决信息

    总结

    首先我们需要深入理解索引的原理和实现,当理解了原理后,才能够更有助于我们建立合适的索引。然后我们建立索引的时候,不要想当然,要先想清楚业务逻辑,再建立对应的表结构和索引。需要再次强调如下几点:

    1. 索引不是越多越好
    2. 区分主键和索引
    3. 理解索引结构原理
    4. 理解查询索引规则
    展开全文
  • MySQL 索引底层

    2020-08-01 09:46:13
    MySQL官方对索引的定义:索引(Index)是帮助MySQL高效获取数据的数据结构。索引的本质:数据结构 数据库的查询是数据库最主要的功能之一,要想查询速度尽可能快,就得从查询算法上进行优化。最基本的查询算法就是...

    MySQL官方对索引的定义:索引(Index)是帮助MySQL高效获取数据的数据结构。索引的本质:数据结构

    数据库的查询是数据库最主要的功能之一,要想查询速度尽可能快,就得从查询算法上进行优化。最基本的查询算法就是顺序查找了,时间复杂度为O(n),可见效率很低。还有一些其他的常用查找算法,比如二分查找,二叉树查找,但这些算法都对应特定的结构来使用,二分查找要求数据是顺序的,二叉树查找是基于二叉查找树结构的,但是数据库本身的数据不可能完全满足于各种数据结构。所以,在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这种数据结构以某种方式引用数据,这样就可以在这些数据上实现高级查找算法。这种数据结构就是索引。

    举个例子来理解 索引 这种数据结构:

     

    左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址(注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的)。为了加快Col2的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含关键字和一个指向对应数据记录物理地址的指针(关键字可以看作是索引的具体值),这样就可以运用二叉查找在O(logn)的复杂度内获取到相应数据。

    上图只是展示了索引的一种可能的方式,但实际数据库中没有使用二叉查找树或其进化版本红黑树来实现索引结构的。

    二叉查找树

    在二叉查找树中,左节点都比父节点小,右节点都比父节点大。

    二叉查找树组织数据,用于查找时效率比较高,因为每次经过一个节点,最多可以减少一半的可能,不过极端情况会出现所有节点都位于同一侧,直观上看就是一条直线,那么这种查询的效率就比较低了,因此需要对二叉树左右子树的高度进行平衡化处理,于是就有了平衡二叉树(Balenced Binary Tree)。

    平衡二叉树:每个节点的左右子树高度差最多为1,如果不满足,就通过左旋和右旋来满足这个条件。这样就不会出现一条支路特别长的情况。于是,在这样的平衡树中进行查找时,总共比较节点的次数不超过树的高度,这就确保了查询的效率(时间复杂度为O(logn))。

    B树

    B树是平衡树的一种,是一种平衡的多叉查找树。

    (B树关于节点,深度以及分裂组合的特性不多赘述)

    B树相对于平衡二叉树的不同是,每个节点包含的关键字增多了,特别是在B树应用到数据库中的时候,数据库充分利用了磁盘块的原理(磁盘数据存储是采用块的形式存储的,每个块的大小为4K,每次IO进行数据读取时,位于同一个磁盘块中的数据会被一次性读取出来把节点大小限制在磁盘块大小范围;树的节点关键字增多后树的层级比原来的二叉树少了,减少数据查找的次数和复杂度。

    B树进行查找时存在的缺点:

    搜索有可能在非叶子节点结束,查找效率不均衡。

    范围查找时需要中序遍历。

    每个节点都带有数据,每次读取都意味着一次I/O,如果寻址遍历的次数多,就意味着更多的I/O,浪费性能。

    B+树:

    与B树的不同:

    所有的关键字全部存储在叶节点上,且叶子节点本身根据关键字自小而大顺序连接。

    非叶结点仅具有索引作用,数据均存放在叶结点中。

     innodb存储引擎中有页(Page)的概念,页是数据库管理磁盘的最小单位,innodb存储引擎中默认每个页的大小为16kb,每次读取磁盘时都将页载入内存中。

    系统一个磁盘块的大小空间往往没有16kb这么大,因此innodb每次io操作时都会将若干个地址连续的磁盘块的数据读入内存,从而实现整页读入内存。

    B+ 树中各个页之间是通过双向链表连接的(一个叶节点为一页),叶子节点中的数据是通过单向链表连接的。范围查找时可以直接通过链表查找相邻数据。

    B+树中间节点没有Data数据,所以同样大小的 页 可以容纳更多的键值

    所以数据量比较大并且数据量相同的情况下,B+树比B树更加“矮胖“,因此使用的IO查询次数更少。(数据量比较小的情况下,B+树未必比B树高度矮)

    MyISAM 中的 B+ 树索引实现与 InnoDB 中的略有不同。在 MyISAM 中,B+ 树索引的叶子节点并不存储数据,而是存储数据的文件地址

    尽量在 InnoDB 上采用自增字段做表的主键。因为 InnoDB 数据文件本身是一棵B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页

    聚集索引和非聚集索引:

    着重介绍 InnoDB 中的聚集索引和非聚集索引:(以 InnoDB 作为存储引擎的表,表中的数据都会有一个主键,即使你不创建主键,系统也会帮你创建一个隐式的主键)。

    InnoDB存储引擎表是索引组织表,即表中数据按照主键的顺序存储。聚集索引就是按照表的主键构建一棵B+树,叶节点中存放的是行记录

    每张表只有一个聚集索引,即为主键索引,其他索引为非聚集索引。

    InnoDB中,辅助索引与主索引的区别在于辅助索引的叶子节点不存储表中的数据,而是存储该列对应的主键,想要查找数据我们还需要根据主键再去主索引中进行查找,这个过程称为“回表”。

    MyISAM:主索引和辅助索引都为非聚集索引。

    MyISAM索引的叶子节点存储指针,指向行数据。

     

    利用聚集索引查找数据:

    select * from user where id>=18 and id <40

    ①一般根节点都是常驻内存的,也就是说页 1 已经在内存中了,此时不需要到磁盘中读取数据,直接从内存中读取即可。

    从内存中读取到页 1,要查找这个 id>=18 and id <40 或者范围值,首先需要找到 id=18 的键值。

    从页 1 中可以找到键值 18,此时我们需要根据指针 p2,定位到页 3。

    ②要从页 3 中查找数据,就需要拿着 p2 指针去磁盘中进行读取页 3。

    从磁盘中读取页 3 后将页 3 放入内存中,然后进行查找,我们可以找到键值 18,然后再拿到页 3 中的指针 p1,定位到页 8。

    ③同样的页 8 页不在内存中,需要再去磁盘中将页 8 读取到内存中。

    将页 8 读取到内存中后。因为页中的数据是链表进行连接的,而且键值是按照顺序存放的,此时可以根据二分查找法定位到键值 18。

    此时因为已经到数据页了,此时已经找到一条满足条件的数据了,就是键值 18 对应的数据。

    因为是范围查找,而且此时所有的数据又都存在叶子节点,并且是有序排列的,那么我们就可以对页 8 中的键值依次进行遍历查找并匹配满足条件的数据。

    我们可以一直找到键值为 22 的数据,然后页 8 中就没有数据了,此时我们需要拿着页 8 中的 p 指针去读取页 9 中的数据。

    ④因为页 9 不在内存中,就又会加载页 9 到内存中,并通过和页 8 中一样的方式进行数据的查找,直到将页 12 加载到内存中,发现 41 大于 40,此时不满足条件。那么查找到此终止。

    最终我们找到满足条件的所有数据,总共 12 条记录:

    (18,kl), (19,kl), (22,hj), (24,io), (25,vg) , (29,jk), (31,jk) , (33,rt) , (34,ty) , (35,yu) , (37,rt) , (39,rt) 

     

     

    展开全文
  • 深入理解 MySQL 索引底层原理 Mysql 作为互联网中非常热门的数据库,其底层的存储引擎和数据检索引擎的设计非常重要,尤其是 Mysql 数据的存储形式以及索引的设计,决定了 Mysql 整体的数据检索性能。 何为索引 我们...
  • mysql索引底层原理分析
  • 微信公众号:Java患者专注Java领域技术分享MySQL索引底层原理局部性与页在操作系统中,我们执行一个指令去磁盘取数据,那么他会从磁盘取出4KB数据,这个4KB就是一个局部单位,而这...
  • mysql索引底层原理mysql 引擎什么是索引数据结构什么是B树B树结构图什么是B+树两者有什么不同之处mysql索引为什么用B+树了解了mysql 索引底层有什么用 mysql 引擎 MyISAM 引擎和 InnoDb引擎 用表对比: 引擎 主...
  • MySQL索引底层实现原理:B树、B+树
  • Mysql索引底层原理分析, Mysql索引的本质 Mysql索引的底层原理 Mysql索引的实战经验 面试 问:数据库中最常见的慢查询优化方式是什么? 同学A:加索引。 问:为什么加索引能优化慢查询?同学A:…不知道同学B:因为...
  • MySQL索引底层原理

    2020-05-21 18:44:28
    Mysql索引底层数据结构选型 1、哈希表(Hash) 通过Hash算法,能快速检索数据 数据碰撞问题用链地址法 无法进行范围搜索 2、二叉查找树(BST) 解决哈希索引无法范围搜索的问题 极端情况下会退化成线性链表,...
  • 深入理解 Mysql 索引底层原理 一步一步推导出 Mysql 索引的底层数据结构。 Mysql 作为互联网中非常热门的数据库,其底层的存储引擎和数据检索引擎的设计非常重要,尤其是 Mysql 数据的存储形式以及索引的设计,...
  • 一,MySQL索引底层的实现 索引是帮助MySQL高效获取数据的排好序的数据结构; 上图中有一张表,表名为 t ,表中有7条数据;使用select * from t where t.clo2 = 89查询; 1、若表中没有创建索引,则会全表扫描,...
  • MySQL索引底层数据结构索引到底是什么联合索引结构MyISAM索引文件和数据文件是分离的主键索引普通索引InnoDB索引实现主键索引普通索引 索引到底是什么 索引是帮助MySQL高效获取数据的 排好序 的 数据结构 索引存储...
  • 深入理解Mysql索引底层原理 一步一步推导出Mysql索引的底层数据结构。 Mysql作为互联网中非常热门的数据库,其底层的存储引擎和数据检索引擎的设计非常重要,尤其是Mysql数据的存储形式以及索引的设计,决定了...
  • MySql索引底层数据结构和算法 MySql explan执行计划详解 优化原则实例sql准备 CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_name` varchar(24) NOT NULL DEFAULT '' COMMENT '用户姓名', ...
  • 由浅入深全面分析MySQL索引底层原理 1、索引基础 索引是MySQL优化中最重要的手段之一,本文从基础到原理,让你重新认识并掌握索引。 1.1 什么是索引 MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取...
  • MySQL索引底层分析观后感MySQL的索引类型什么是索引为什么用到索引B+Tree 进阶历程二叉树平衡二叉树多路平衡树(B Tree)加强版多路平衡树(B+Tree)覆盖索引聚集索引总结 MySQL的索引类型 MySQL 索引类型有:唯一...
  • 一文全面深入理解 Mysql 索引底层原理 本文旨在一步一步推导出 Mysql 索引的底层数据结构。 Mysql 作为互联网中非常热门的数据库,其底层的存储引擎和数据检索引擎的设计非常重要,尤其是 Mysql 数据的存储形式以及...
  • 深入理解 MySQL 索引底层原理 Mysql 作为互联网中非常热门的数据库,其底层的存储引擎和数据检索引擎的设计非常重要,尤其是 Mysql 数据的存储形式以及索引的设计,决定了 Mysql 整体的数据检索性能。 何为索引 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,680
精华内容 1,072
关键字:

mysql索引底层

mysql 订阅