面试不要再问我数据库索引了!!!

Dog_Lee

索引

索引这个问题在面试中被问到的几率还是挺大的,比如常见的几种问题:索引有哪些类型?索引是不是越多越好?索引底层用的是什么数据结构?为什么用B+树而不用B树?什么是最左前缀原则?密集索引和稀疏索引?为什么最好使用主键自增的索引?如果你只回答索引就像字典一样,能快速的根据目录查找数据,可能面试官听着就不太过瘾,或者说认为你理解的不够深刻。通过这篇文章,你将会从各个方面了解索引,无论是从实战还是面试角度都会有很大的帮助。

什么是索引

官方的定义是:索引是帮助MySQL高效获取数据的数据结构,简单点说索引就是一种数据结构。为什么要用数据结构存储呢?两个字:高效的存储数据。

索引为什么高效

image.png
现在有一张表,想要找到col2的值为89所在的那一行,方法有很多,最简单的就是从头开始扫描,一行一行的向下遍历,经过6次扫描后就可以找到了,显然这样效率是不高的,如果用二分搜索树存储呢?
image.png

可以看到下图将col2作为索引列,其实就是把clo2的所有值存到二叉树中,通过这颗树找到89只需要1次I/O。同时每一行数据在内存中都会存在一个地址值(最左侧16进制表示),通过这个地址值就可以找到这行所对应的所有数据,找到了89也就能拿到0X77,拿到0X77就能获取到这一行其他列的数据,这就是索引。

索引用的是什么数据结构?

可以看到经过上面的优化,找到89只需要一次,但不是每次都这么"幸运"。同样是二分搜索树的情况下,因为元素进入的顺序不一样,会导致"树形"也不一样,下图中左边是比较理想的,找到89只需要一次IO,但是在极端的情况下,二分搜索树就会退化成线性数据结构,像右图这样找到89就会经过6次IO。如果要将col1这个列所有的元素存储到二分搜索树,那也一定是按照线性结构的,可以看到查找一个元素效率很低。
image.png

如果树变成平衡二叉树(AVL)或者红黑树就解决了问题呢?这两种数据结构是可以解决退化成链表的问题,但随着数据的增加,树的深度也会不断增加,IO次数同样会变多,性能同样会下降。
image.png

那索引到底是什么数据结构呢?不装了,我是亿万富翁,我摊牌了。那就是B树的改良版B+树。
说B+树之前要说一说B树,说B树之前先说一说哈希,为什么不用哈希存储呢?Hash存取多快啊,哈希存取是快,但哈希有一个最大的弊端,就是不适合范围查询,而MySQL中使用范围查询又比较多,所以目前大多数还是用B+树的比较多,同时哈希不在本文讨论的范围之内。说完哈希,说一说B树,上面不是说数据量大的情况下,树的深度会很深,导致IO次数过多影响效率嘛,那么B树其实就很好的解决了这个问题。
image.png

从上图来看,B树就是把每一个存储位置做了横向扩展,原来只能存储一个,现在能存储3个甚至更多,从而来减少树的深度。在MySQL使用的B树中,定义一个最基本的存储单位为[Key,Data],Key就是索引列对应的值,Data就是除了Key以外的数据,找到了Key就找到了其他数据。关于B树过多的底层实现和复杂度分析不在这里赘述。
image.png

  • 每个节点能存储的最大元素个数,称之为B树的度(MaxDegree)
  • 元素从左到右递增排列(有序存储)
  • 所有叶子节点都具有相同的深度(平衡)
  • 一个节点指针要么指向另一个节点,要么指向null(叶子节点的指针为空)

既然可以设置度的大小,那么度约大,里面可以存放的元素就越多,树的深度就越浅,IO次数就越少,甚至度足够大,树的深度可以达到一层,那是否度的取值越大越好呢?这样不就经过一次IO就可以了?答案是不可以的,一次性是读不出来的,效率很慢,因为cpu从硬盘取数据时,一次只能取一部分。至于这个度取多少合适?MySQL会自动根据机器配置帮你优化。如果B树作为索引的底层数据结构,效率是非常高的,B+树是B树的改良版,其实B树改良的数据结构很多,B+树只是其中一种,而MySQL用的就是B+树。
image.png

那B+树对B树做了哪些改良呢?

  • 非叶子节点不存储数据,只存储Key(在度同样的情况下,可以存储更多的Key)
  • 叶子节点不存储指针,用于存储数据
  • 叶子节点增加顺序访问指针,提高区间访问的性能。(select id from table where id > 20 and id < 50)查找到id>20后,顺着指针直接查询id<50,无须从根节点从新遍历比较
  • 通常度一般会超过100,因此树的深度也很浅(一般为3到5层之间)

看到这就可以解释,为什么主键推荐用整型自增了,用UUID不也挺好嘛?首先整型作为索引在插入到B+树的时候,由于树的排序特性,会将元素和其他元素比较,插入到合适的位置,整型很好比较,而UUID很长,比较起来比较繁琐,效率低。自增是为了元素在插入的时候,很快的可以按左小右大的顺序排到最后,不需要多次比较插入到其他位置,然后还需要维护树的结构。

到这里就介绍完索引的基本原理了,明天会更新MyISAM以及InnoDB在使用索引时底层的区别以及更多索引问题。

MyISAM和InnoDB在实现索引的区别

通过上面的介绍,你已经知道了索引的概念,以及MySQL的底层如何用B+树这种数据结构存储索引和数据,但要是具体来看,这里的索引用的是主键索引嘛?还是非主键索引?数据具体指的是数据对应的地址值还是真实数据?由于不同的存储引擎在实现索引的时候,底层实现是不一样的。目前,MyISAM和InnoDB这两种存储引擎使用的最广泛,下面也主要介绍这两种。

MyISAM的数据和索引分离存储的,也就是数据存到一个文件,索引存到一个文件中,下图中MYD就是存储数据的文件,MYI就是存储索引的文件,至于那个sdi是MySQL8.0的新特性,用于代替原来的frm(表结构定义文件),不在本文的范畴内。
image.png

对于MyISAM,MYI存储索引,MYD存储数据,所以说要想查询一条数据,要先根据索引从MYI文件中查到数据对应的地址值,在根据地址值去MYD文件中去找,要经过两次查询。下图是MyISAM存储引擎,使用主键作为索引。
image.png
非主键索引结构也是一样的,除了使用col2(非主键列)作为索引外,其他的没有任何变化。
image.png

再来看InnoDB,InnoDB用的虽然也是B+树,结构和MyISAM一样,但是具体的存储方式却截然不同,使用InnoDB存储引擎,数据和索引都存储在一个IDB文件中。
image.png

和MyISAM不同的是,InnoDB使用主键作为索引时,叶子节点存储的是对应的完整数据。
image.png
InnoDB使用非主键索引的时候,与MyISAM区别也比较大,MyISAM无论使用主键还是非主键做为索引,叶子节点存储的都是对应数据的地址值。而InnoDB使用主键索引时,叶子节点存储的是索引对应完整的数据,使用非主键索引时,叶子节点的值存储的是主键索引。

image.png
什么?存储的是主键的索引?那如果我建表的时候,没有创建主键呢,也不会报错啊?MySQL已经帮你想好了,如果你建表的时候没有创建索引,MySQL系统会自动选择一个可以作为唯一标识的列当作索引,如果没有合适的列, MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。这样做的好处是什么呢?第一是保持一致性,始终维护一份数据,不容易乱,第二就是节省空间。 知道了InnoDB的索引实现后,就很容易明白为什么不建议使用过长的字段作为主键,除了上面分析问题外,还有就是因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大下图中非主键是按照ASCII码来做比较的。
image.png

明天会更新关于索引和SQL优化方面更多知识!
点赞关注不迷路!

阅读 2.7k
46 声望
4 粉丝
0 条评论
46 声望
4 粉丝
文章目录
宣传栏