MYSQL索引与B+树

什么是索引？

索引是一种排好序的可以快速查找数据的数据结构

索引存储位置

通常，索引本身很大，不能完全存储在内存中，因此索引通常作为索引文件存储在磁盘上。在这种情况下，在索引查找过程中会生成磁盘I/O消耗。 I/O访问消耗比内存访问高几个数量级，因此将数据结构评估为索引的最重要指标是查找过程中磁盘I/O操作数量的渐进复杂性。换句话说，索引的结构被组织为最小化查找过程期间的磁盘I/O访问的数量。

索引存储在磁盘上

磁盘由相同大小和同轴的圆盘组成，磁盘可以旋转(每个磁盘必须同步旋转)。在磁盘的一侧有一个磁头固定器，磁头固定器固定一组磁头，每个磁头负责访问一个磁盘的内容。头部不能旋转，但它可以沿着盘的半径移动(实际上是倾斜的切向运动)。每个磁头也必须同时同轴，也就是说，从右上方向下，所有磁头在任何时候都会重叠(但目前，有多种磁头独立技术，不受此限制)。

由于存储介质的特性，磁盘本身的访问速度远远低于主存储器。除机械机芯外，磁盘的访问速度通常是主存储器的百分之几。因此，为了提高效率，尽量提高效率。减少磁盘I/O.为了实现这一目标，磁盘不是严格按需读取的，但每次预读时，即使只需要一个字节，磁盘也会从这个位置开始，并向后读取一定长度的数据。记忆。预读可以提高I/O效率。预读的长度通常是页面的整数倍(页面：计算机管理存储器的逻辑块 - 通常为4k)。主存储器和磁盘以页为单位交换数据。当程序要读取的数据不在主存储器中时，将触发页面错误异常。此时，系统将向磁盘发出读信号，磁盘将找到数据的起始位置并连续向后读取一个或几个页面。加载在内存中。地方原则

这样做的理论基础是计算机科学中著名的地方性原则：当使用一个数据时，通常立即使用其附近的数据。

也就是说，程序运行期间所需的数据通常是集中的。由于磁盘的顺序读取非常有效(不需要寻道时间，只需要少量的旋转时间)，因此对于具有局部性的程序，预读可以提高I/O效率。

文件系统和数据库系统的设计者利用磁盘预读的原理将节点的大小设置为等于一页，这样每个节点只能完全加载一个I/O.为了实现这一目标，每次B + Tree创建一个新节点时，它直接请求页面空间，从而确保节点物理存储在页面中，并且计算机存储分配是页面对齐的。实现节点只需要一个I/O.

各种数据结构的索引分析

没有索引

假如我们没有使用索引。并且总数据有1000条。
当执行sql ： == select * from t_user where id = 500 ==，时，就会对id进行比较1000次，从而找到id=500的数据记录。也就是我们讲的，全局扫描。

链表

假如我们使用链表为id做索引。当执行sql ： select * from t_user where id = 500时。
就会在链表中进行比较查找，当查找到第一个id=500时表示要找的数据已经开始，当顺序查找到最后一个id=500时，表示要查找的数据已经结束（因为索引是有顺序的）。
假如，id=500的数据在1000条数据的第499,500,501位时。那此次比较就进行502次比较。
加快了查询效率。

但是链表并不适合做索引。

因为效率太低了。
我们知道链表的查询效率是O(n)。就像上面的例子，遍历了502次才找到符合条件的记录，这是很低效的。而我们知道，数组+二分查找的效率是O(lgn)，但是数组的插入元素以及删除元素的效率很低，因此使用数组做为索引结构并不合适。

另外，在选择数据库索引的结构的时候，要考虑到另一个问题。索引是存在于磁盘中，当索引非常大的时候，达到几个G的时候，无法一次加载到内存中。

考虑到上面两个因素，数据库中索引使用的是树形结构。

总结：效率低

二叉树

二叉树的查找时间复杂度可以达到O(log2(n))。

二叉树：

二叉树搜索相当于一个二分查找。二叉查找能大大提升查询的效率，但是它有一个问题：二叉树以第一个插入的数据作为根节点，如上图中，如果只看右侧，就会发现，就是一个线性链表结构

如果我们要查询的数据为4，则需要遍历所有的节点才能找到4，即，相当于全表扫描。所以二叉树也并不适合做索引。

总结：效率低，而且并不能解决磁盘IO加载到内存的问题

平衡二叉树

如果上图中平衡二叉树保存的是id索引，现在要查找id = 8的数据，过程如下：

• 把根节点加载进内存，用8和10进行比较，发现8比10小，继续加载10的左子树。
• 把5加载进内存，用8和5比较，同理，加载5节点的右子树。
• 此时发现命中，则读取id为8的索引对应的数据。

索引保存数据的方式一般有两种：

• 数据区保存id 对应行数据的所有数据具体内容。
• 数据区保存的是真正保存数据的磁盘地址。

平衡二叉树很好的解决了二叉树的线性链表结构问题。
但是平衡二叉树也并不合适做索引。
1.因为结构问题，当数据量很大时候，就会造成树的深度是很大，如果查找的数据在根节点还好，如果在叶子结点，就会造成多次IO。而IO是比较耗时的。
2.每个节点存储的数据内容太少，没有很好的利用操作系统和磁盘的数据交换特性。也没有利用好磁盘IO的预读能力。操作系统和磁盘之间的一次数据交换是以页为单位的。一页大小为4K，即一次IO操作系统会将4K（整倍数）的数据加载到内存中。但是二叉树每个节点值保存了一个关键字，一个数据区，两个子节点的引用，并不能填满4K的内容。辛辛苦苦的一次IO操作，只加载了一个关键字。
所以平衡二叉树也并不适合做索引。

总结：解决了线性链表结构，但是树深度大，不能很好利用IO缓存。影响效率。

多路平衡查找树（B-Tree）

上图为一个2-3树（每个节点存储2个关键字，有3路），多路平衡查找树也就是多叉的意思，从上图中可以看出，每个节点保存的关键字的个数和路数关系为：关键字个数 = 路数 – 1。

假设要从上图中查找id = X的数据，B TREE 搜索过程如下：

• 取出根磁盘块，加载40和60两个关键字。
• 如果X等于40，则命中；如果X小于40走P1；如果40 < X < 60走P2；如果X = 60，则命中；如果X > 60走P3。
• 根据以上规则命中后，接下来加载对应的数据， 数据区中存储的是具体的数据或者是指向数据的指针。

为什么说这种结构能够解决平衡二叉树存在的问题呢？
B Tree 能够很好的利用操作系统和磁盘的交互特性， MySQL为了很好的利用磁盘的预读能力，将页大小设置为16K，即将一个节点（磁盘块）的大小设置为16K，一次IO将一个节点（16K）内容加载进内存。这里，假设关键字类型为 int，即4字节，若每个关键字对应的数据区也为4字节，不考虑子节点引用的情况下，则上图中的每个节点大约能够存储（16 * 1000）/ 8 = 2000个关键字，共2001个路数。对于二叉树，三层高度，最多可以保存7个关键字，而对于这种有2001路的B树，三层高度能够搜索的关键字个数远远的大于二叉树。

这里顺便说一下：在B Tree保证树的平衡的过程中，每次关键字的变化，都会导致结构发生很大的变化，这个过程是特别浪费时间的，所以创建索引一定要创建合适的索引，而不是把所有的字段都创建索引，创建冗余索引只会在对数据进行新增，删除，修改时增加性能消耗。

总结：很好的解决了平衡二叉树的缺点。但是相较于B+Tree还是各有优缺点的
平衡二叉树主要发生在磁盘IO读取，B-Tree主要是内存读取。这两种耗时差距很大。

B+Tree

如果上图中是用ID做的索引，如果是搜索X = 1的数据，搜索规则如下：

1. 取出根磁盘块，加载1，28，66三个关键字。
2. X <= 1 走P1，取出磁盘块，加载1，10，20三个关键字。
3. X <= 1 走P1，取出磁盘块，加载1，8，9三个关键字。已经到达叶子节点，命中1，接下来加载对应的数据，图中数据区中存储的是具体的数据。

B+树的关键字全部存放在叶子节点中，非叶子节点用来做索引，而叶子节点中有一个指针指向一下个叶子节点。做这个优化的目的是为了提高区间访问的性能。而正是这个特性决定了B+树更适合用来存储外部数据。

B+树叶子节点（也就是最下面一层的没有子节点的节点）有一个双向链表，左右是为了方便范围查找（ 假如我找前100条数据，那么我找到第一条叶子节点的数据就可以从叶子节点直接向后取100个数据即可，不用再从根节点向下寻找）

MySQL为什么最终要去选择B+Tree？

1. B+Tree是B TREE的变种，B -TREE能解决的问题，B+TREE也能够解决（ 降低树的高度，增大节点存储数据量）

2. B+Tree扫库和扫表能力更强。如果我们要根据索引去进行数据表的扫描，对B TREE进行扫描，需要把整棵树遍历一遍，而B+TREE只需要遍历他的所有叶子节点即可（叶子节点之间有引用）。

3. B+TREE磁盘读写能力更强。他的根节点和支节点不保存数据区 ，所以根节点和支节点同样大小的情况下，保存的关键字要比B TREE要多。而叶子节点不保存子节点引用，能用于保存更多的关键字和数据。所以，B+TREE读写一次磁盘加载的关键字比B TREE更多。

4. B+Tree排序能力更强。上面的图中可以看出，B+Tree天然具有排序功能。

5. B+Tree查询性能稳定。B+Tree数据只保存在叶子节点，每次查询数据，查询IO次数一定是稳定的。当然这个每个人的理解都不同，因为在B TREE如果根节点命中直接返回，确实效率更高。

数据库索引采用B+树的主要原因是B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。

所以数据库索引采用了B+Tree作为了索引。

补充

聚集索引和非聚集索引

面试官：嗯。那你简单说一下聚集索引和非聚集索引是什么意思

我：那我就由浅入深的简单说一下吧。
聚集索引：首先所谓聚集的含义是索引与data数据是否相邻，就是我找到索引以后在它附近就可以找到想要的data数据这就是聚集索引。
非聚集索引：非聚集索引就是我找到索引后，在它的附近找不到data数据。
这里我们根据之前的图想一下，之前的图叶子节点下面紧挨着就是data数据，这里肯定是聚集索引啊，那么什么情况下是非聚集索引呢。
在索引的字段是非主键的时候就是非聚集索引。

这里我来举个例子，如果一个student表中有主键id，姓名name，年龄age，住址add。这个时候我们给name字段建立一个索引，给add字段建立了一个索引，那么这个时候是不是有两个B+树的索引结构，那么意味着这两个索引结构的叶子节点都需要有data数据，那岂不是需要将name索引中的data数据复制一份出来给add索引。
那假如有100个字段都建立了索引，岂不是data要复制100遍。这个时候我就想了一个办法（这个办法不是我想的。。。我快要想出来了，被别人提前答出来了）能不能只让一个索引的子节点有data数据，其他索引的子节点没有data数据而是放有data数据的索引的地址呢。
这个时候就让哪个索引作为唯一拥有data数据的索引呢，这里很明显可以用主键嘛，因为主键正好是唯一的，其他字段都可以为多个，所以主键所建立的索引就是拥有data数据的聚集索引，而其他非主键字段建立的索引就是非聚集索引。

为什么B+树还要把其他节点的data数据去掉，只留叶子节点的data数据呢

面试官：嗯，每一个节点都有data数据不是更好吗，不需要到达叶子节点就可以获取数据返回了，为什么B+树还要把其他节点的data数据去掉，只留叶子节点的data数据呢

我：因为这里涉及到计算机中的IO操作，计算机IO一次只能拿一数据页的数据（姑且认为大小为64KB吧），如果每一个节点都有data数据，那么计算机IO一次可能只够拿一个节点出来，这样，可能IO一百次才能找到结果，如果其他节点不存储data数据，那么这个索引占用空间就少，IO一个可以拿出多个节点来，这样IO的次数就大大降低了，IO一次是比较耗费性能的，所以使用B+树就提高了性能。

MySQL索引下推

面试官：嗯。非常不错，那你再说一下什么是索引下推吧

我：（我特么。。。精通MySQL就这么被问啊，早知道不写精通MySQL了，累死我了。。。可以来个赞给我续续命吗家人们）
首先索引下推是MySQL5.6版本引入的一种优化手段，说白了就是优化了一下，具体优化后有了哪些效果呢，切听帅气的小奇给你娓娓道来。

关键点：
1、第一个字段为非等值字段。
2、查询的字段建立了联合索引。
前提：
name和age建立了联合索引

例如 select name,age from student where name like ‘%李*%’ and age=20；

这个时候如果在5.6之前，我们会在联合索引中先找到所有name为李开头的数据id（主键），然后再去主键索引（聚集索引）中找age为20的数据的id拿回来，然后将最后合并的数据根据id再去聚集索引中找，这样其实是两次回表查询。

而在5.6之后，我们在联合索引中就直接将name为李开头的和age等于20的数据id筛选出来了，然后再去聚集索引中查询，这样就只进行了一次回表查询。

总结：5.6之前如果查询字段为非等值字段，那么后面的查询条件就回去聚集索引中进行判断，5.6之后非等值字段后面的查询条件在当前非聚集索引中也可以进行判断。

文献参考：
什么是B+树？.
深入理解MySQL索引之B+Tree.
一步步分析为什么B+树适合作为索引的结构.
面试官：MySQL索引底层数据结构原理与性能调优，你能回答多少？.