MySQL使用B+树存储索引

快来打我* 2021-12-07 10:07 216阅读 0赞

# **一、索引是什么？** #

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。它的本质就是**数据结构**，单独存储在磁盘上，用它来提高数据查询的效率。

> 适合作为索引的结构应该是尽可能少的执行磁盘IO操作，因为执行磁盘IO操作非常的耗时。

# **二、索引常见数据结构** #

## **2.1 二叉查找树（Binary Search Tree）** ##

采取二分查找的思想，O（log N）的复杂度就可以完成对数据的查找任务，查找所需的最大次数等同于二叉查找树的高度。

它具有以下特性：

*  左子树上所有结点的值均小于或等于它的根结点的值；
 *  右子树上所有结点的值均大于或等于它的根结点的值；
 *  左、右子树也分别为二叉排序树。

如下图所示：[排序工具][Link 1]

![20190716173557317.jpg][]

对该二叉树的节点进行查找发现深度为1的节点的查找次数为1，深度为2的查找次数为2，深度为n的节点的查找次数为n，因此其平均查找次数为 (1+2+2+3+3+3) / 6 = 2.3次

二叉查找树可以任意地构造，这样会出现一种极端情况，如果依次插入如下六个节点：7，6，5，4，那么就会变成下图所示：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MzQ5MTE0_size_16_color_FFFFFF_t_70][]

这样退化成线性表，导致树高度过高，从而查询效率就降低了。

那么如何解决二叉查找树多次插入新节点而导致的不平衡？这里就要引出新的定义——平衡二叉树，或称AVL树。

> 树的查找性能取决于树的高度，让树尽可能平衡，就是为了降低树的高度。

## **2.2 平衡二叉查找树（AVL Tree）** ##

平衡二叉查找树（AVL树）在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1。如下图所示，它的任何节点的两个子树的高度差<=1。

![20190716173618310.jpg][]

如果在AVL树中进行插入或删除节点，可能导致AVL树失去平衡。

## **2.3 红黑树（Red Black Tree）** ##

红黑树是一种自平衡的二叉查找树。除了符合二叉查找树的基本特性外，它还具备以下特性：

1.  节点是红色或者黑色；
2.  根节点是黑色；
3.  每个叶子的节点都是黑色的空节点（NULL）；
4.  每个红色节点的两个子节点都是黑色的；
5.  从任意节点到其每个叶子的所有路径都包含相同的黑色节点。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MzQ5MTE0_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MzQ5MTE0_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MzQ5MTE0_size_16_color_FFFFFF_t_70 3][]

[五分钟搞懂什么是红黑树（全程图解）][Link 2]

**红黑树相比于BST和AVL树有什么优点？**

> 红黑树是牺牲了严格的高度平衡的优越条件为代价，它只要求部分地达到平衡要求，降低了对旋转的要求，从而提高了性能。
> 
> 红黑树能够以O(log2 n)的时间复杂度进行搜索、插入、删除操作。此外，由于它的设计，任何不平衡都会在三次旋转之内解决。当然，还有一些更好的，但实现起来更复杂的数据结构能够做到一步旋转之内达到平衡，但红黑树能够给我们一个比较“便宜”的解决方案。
> 
> 相比于BST，因为红黑树可以能确保树的最长路径不大于两倍的最短路径的长度，所以可以看出它的查找效果是有最低保证的。在最坏的情况下也可以保证O(logN)的，这是要好于二叉查找树的。因为二叉查找树最坏情况可以让查找达到O(N)。
> 
> 红黑树的算法时间复杂度和AVL相同，但统计性能比AVL树更高，所以在插入和删除中所做的后期维护操作肯定会比红黑树要耗时好多，但是他们的查找效率都是O(logN)，所以红黑树应用还是高于AVL树的。实际上插入，AVL 树和红黑树的速度取决于你所插入的数据.如果你的数据分布较好,则比较宜于采用 AVL树(例如随机产生系列数)，但是如果你想处理比较杂乱的情况,则红黑树是比较快的。

>  *  AVL是严格平衡树，因此在增加或者删除节点的时候，根据不同情况，旋转的次数比红黑树要多；
>  *  而红黑是弱平衡的，用非严格的平衡来换取增删节点时候旋转次数的降低；
>  *  所以简单说，查找的次数远远大于插入和删除，那么选择AVL树；如果搜索、插入删除次数几乎差不多，应该选择RB树。

**红黑树的应用**

1.  在Java中， TreeMap和TreeSet，Java 8中HashMap中TreeNode节点都采用了红黑树实现。 
2.  C++中，STL的map和set也应用了红黑树； 
3.  Linux进程调度Completely Fair Scheduler； 
4.  用红黑树管理进程控制块epoll在内核中的实现，用红黑树管理事件块； 
5.  Nginx中，用红黑树管理timer等；

红黑树的各种操作的时间复杂度是O(lgn)，逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，IO次数多查找慢，效率低。

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

红黑树的高度虽然有一定的控制，而数据库当中一般要把索引树的高度控制在3-5层，这点红黑树显然无法做到。B-Tree是为磁盘等外存储设备设计的一种平衡查找树，是一种多路平衡搜索树，既然它是多路平衡的，那么就不在像红黑树那样只有2个子节点了，既然有多个子节点，树的高度就可以控制了，同时它也跟红黑树一样，数据是排序的，可以快速查找。

B树具有以下特点：

1.  每个节点最多含有m个孩子；
2.  根节点含有\[2,m\]个孩子；
3.  非叶子节点含有\[\[m/2\],m\]个孩子节点（向上取整的意思）；
4.  一个节点如果含有K个关键字，那么它就有k+1个孩子；
5.  所有叶子节点都在同一层；
6.  每个节点的K个关键数把节点拆成了K+1段

下面是一颗B树：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MzQ5MTE0_size_16_color_FFFFFF_t_70 4][]

B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组\[key, data\] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：   
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MzQ5MTE0_size_16_color_FFFFFF_t_70 5][]

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：

1.  根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
2.  比较关键字29在区间（17,35），找到磁盘块1的指针P2。
3.  根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
4.  比较关键字29在区间（26,30），找到磁盘块3的指针P2。
5.  根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
6.  在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次**磁盘I/O操作是影响整个B-Tree查找效率的决定因素**。

[MySQL中B+Tree索引原理][MySQL_B_Tree]

## **2.5 B+Tree** ##

B+Tree是在B-Tree（不要读成B减树，而是B树）基础上的一种优化，使其更适合实现外存储索引结构，**InnoDB存储引擎就是用B+Tree实现其索引结构**。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在**B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息**，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

1.  非叶子节点只存储键值信息。
2.  所有叶子节点之间都有一个链指针。
3.  数据记录都存放在叶子节点中。

将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MzQ5MTE0_size_16_color_FFFFFF_t_70 6][]

数据都在叶子节点上，并且增加了**顺序访问指针**，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可，**提高了区间访问性能（无需返回上层父节点重复遍历查找减少IO操作）**。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MzQ5MTE0_size_16_color_FFFFFF_t_70 7][]

# **三、为什么使用B+Tree** #

红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

这样我们对比上面的B+树和红黑树，比如查找节点21，红黑树要磁盘IO5次，而B+树只要2次，也就是说磁盘IO次数大致为树的高度，这样B+树就脱颖而出了，成为实现索引的不二选择。

实际情况中每个节点可能不能填充满，因此在数据库中，**B+Tree的高度一般都在2~4层**。MySQL的InnoDB存储引擎在设计时是将**根节点常驻内存**的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

数据库中的B+Tree索引可以分为**聚集索引**（clustered index）和辅助索引（secondary index）。上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

> **聚簇索引(聚集索引)：**并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。

**数据库索引采用B+树而不是B树的主要原因：**B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。

文件与数据库都是需要较大的存储，也就是说，它们都不可能全部存储在内存中，故需要存储到磁盘上。而所谓索引，则为了数据的快速定位与查找，那么索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数，因此B+树相比B树更为合适。数据库系统巧妙利用了局部性原理与磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入，而红黑树这种结构，高度明显要深的多，并且由于逻辑上很近的节点(父子)物理上可能很远，无法利用局部性。最重要的是，B+树还有一个最大的好处：方便扫库。B树必须用中序遍历的方法按序扫库，而B+树直接从叶子结点挨个扫一遍就完了，B+树支持range-query非常方便，而B树不支持，这是数据库选用B+树的最主要原因。

# **四、问题** #

**问：为什么索引结构默认使用B-Tree，而不是hash，二叉树，红黑树？**

hash：虽然可以快速定位，但是没有顺序，IO复杂度高。

二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

红黑树：树的高度随着数据量增加而增加，IO代价高。

> 如果只选一个数据，那确实是hash更快。但是数据库中经常会选择多条，这时候由于B+树索引有序，并且又有链表相连，它的查询效率比hash就快很多了。
> 
> 而且数据库中的索引一般是在磁盘上，数据量大的情况可能无法一次装入内存，B+树的设计可以允许数据分批加载，同时树的高度较低，提高查找效率。

**问：为什么官方建议使用自增长主键作为索引。**

结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。

### UUID ###

好处就是本地生成，不要基于数据库来了；不好之处就是，UUID 太长了、占用空间大，**作为主键性能太差**了；更重要的是，UUID 不具有有序性，会导致 B+ 树索引在写的时候有过多的随机写操作（连续的 ID 可以产生部分顺序写），还有，由于在写的时候不能产生有顺序的 append 操作，而需要进行 insert 操作，将会读取整个 B+ 树节点到内存，在插入这条记录后会将整个节点写回磁盘，这种操作在记录占用空间比较大的情况下，性能下降明显。

源：[https://my.oschina.net/lienson/blog/2987474][https_my.oschina.net_lienson_blog_2987474]

[Link 1]: https://www.cs.usfca.edu/~galles/visualization/flash.html
[20190716173557317.jpg]: /images/20211207/e422e4c280ea4343bf3b67755bac5262.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MzQ5MTE0_size_16_color_FFFFFF_t_70]: /images/20211207/3c9d52f1c2fb4eb0920e51efc54802ad.png
[20190716173618310.jpg]: /images/20211207/970aae4737c44c8ba4104806ef9532be.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MzQ5MTE0_size_16_color_FFFFFF_t_70 1]: /images/20211207/9874a4abf5754ce9845de61b0d464384.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MzQ5MTE0_size_16_color_FFFFFF_t_70 2]: /images/20211207/9f9b287641714c8e82704f9e342c9fe4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MzQ5MTE0_size_16_color_FFFFFF_t_70 3]: /images/20211207/07e38a5131de45e9ae22d61c6d86d203.png
[Link 2]: https://www.toutiao.com/i6584714397543825927/?tt_from=weixin_moments&utm_campaign=client_share&wxshare_count=2&from=timeline&share_type=original&timestamp=1544587349&app=news_article&utm_source=weixin_moments&iid=53509037357&utm_medium=toutiao_android&group_id=6584714397543825927&pbid=6633976754150000142
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MzQ5MTE0_size_16_color_FFFFFF_t_70 4]: /images/20211207/85f9059d259e443aac9b0a0f708bd586.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MzQ5MTE0_size_16_color_FFFFFF_t_70 5]: /images/20211207/baa6047d2cbe4098bba62a27fdac2bbc.png
[MySQL_B_Tree]: https://blog.csdn.net/ifollowrivers/article/details/73614549
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MzQ5MTE0_size_16_color_FFFFFF_t_70 6]: /images/20211207/c532f69657744935b2c0416341f375c1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MzQ5MTE0_size_16_color_FFFFFF_t_70 7]: /images/20211207/31498903583749fdae3d5e8d0b02634e.png
[https_my.oschina.net_lienson_blog_2987474]: https://my.oschina.net/lienson/blog/2987474