mysql数据库数据结构查询_mysql索引的数据结构推导

介绍

我们知道索引的作用是做数据的快速检索，而快速检束的实现本质是数据结构。通过不同数据结构的选择，实现各种数据的快速检索。所以在数据库中，高效的查找算法是非常重要的，因为数据库中存储了大量的数据，一个高效的索引能节省巨大的时间。如下面的数据表，如果mysql没有实现索引算法，那么查找ID=7这个数据，只能采取遍历的方式查找，找到id=7需要比较7次，如果数据库存储的过多有1000W个数据，就需要遍历1000w次去查找，这样的速度是绝不能接受的。

一 二叉查找树(BST)

二叉查找树是一种支持数据快速查找的数据结构，如下图所示

针对上面这个二叉树结构，我们需要计算比较3次就可以检索到id=7的数据，相对于直接遍历查询省了一半时间，从检索效率上看来是能找到检索。

二叉树的叶子节点都是按序排列的，从左到右依次升序排列，如果找id>5的数据，那么取出节点id=6以及其右边的id=5即可，查找范围也比较好实现。

但是普通的二叉查找树有个致命缺点：极端情况下会退化为线性链表，二分查找也会退化到遍历查找，检索性能急剧下降。比如以下这个情况，二叉树已经极度不平衡，退化为链表，检索速度大大降低。此时检索ID=7的数据所需要的次数就为7了。

出现原因：数据的自增长，从id=1开始，那么该二叉树的生成就以ID=1为头且不可变，那么每一次出现的数据都会偏向右增长则出现了一个类似线性关系的链表。而在数据库中id主键的自增长是默认的，所以简单的二叉树存在不平衡导致的检索性能降低的问题，是不能直接用于mysql底层索引的。

二 AVL树和红黑树

二叉查找树存在不平衡的问题，因此学者提出通过树节点的自动旋转和调整，让二叉树始终保持基本平衡的状态，就能保持二叉查找树的最佳查找性能了。基于这种思路的自调整平衡状态的有AVL树和红黑树。

1 红黑树

红黑树是一颗会自动调整形态的树结构，比如当二叉树处于一个不平衡状态时，红黑树就会自动左旋右旋节点以及节点变色，调整树的形态，使其保持基本的平衡状态，也就保证了查找效率=不会明显减低。比如从1到7升序插入数据节点，如果是普通的二叉树则会退化成链表，但是红黑树则会不断调整树的形态，使其保持基本平衡状态，如下图所示。下面这个红黑树下查找 id=7 的所要比较的节点数为 4，依然保持二叉树不错的查找效率。

但是当按顺序插入足够多的节点时，树的形态是一直处于“右倾”的趋势。从根本上来看，红黑树并没有彻底解决二叉查找树的问题，虽然这个“右倾”趋势没有像二叉查找树退化成线性链表那样夸张，但是数据库主键基本上是自增操作且其数量级都是数百万数千万，如果红黑树存在这个问题。对于查找性而言也是巨大的消耗，数据库不可能忍受这种无意义的等待，如下图查找id=16，需要比较节点数为6次。

2 AVL树

AVL树是一种更为严格的自平衡二叉树。因为AVL树是个绝对平衡的二叉树，因此它在调整二叉树的形态上消耗的性能会更多。

AVL 树顺序插入 1~16 个节点，通过大量的自动的左旋右旋调整形态且保证左右的高度差不超过1，成为下图的数据结构，查找 id=16 需要比较的节点数为 4。从查找效率而言，AVL 树查找的速度要高于红黑树的查找效率(AVL 树是 4 次比较，红黑树是 6 次比较)。从树的形态看来，AVL 树不存在红黑树的“右倾”问题。也就是说，大量的顺序插入不会导致查询性能的降低，这从根本上解决了红黑树的问题

总结一下 AVL 树的优点：

不错的查找性能(O(logn))，不存在极端的低效查找的情况。

可以实现范围查找、数据排序。

看起来 AVL 树作为数据查找的数据结构确实很不错，但是 AVL 树并不适合做 Mysql 数据库的索引数据结构，因为考虑一下这个问题：

数据库查询数据的瓶颈在于磁盘 IO，如果使用的是 AVL 树，我们每一个树节点只存储了一个数据，我们一次磁盘 IO 只能取出来一个节点上的数据加载到内存里，那比如查询 id=7 这个数据我们就要进行磁盘 IO 三次，这是多么消耗时间的。所以我们设计数据库索引时需要首先考虑怎么尽可能减少磁盘 IO 的次数。

磁盘 IO 有个有个特点，就是从磁盘读取 1B 数据和 1KB 数据所消耗的时间是基本一样的，我们就可以根据这个思路，我们可以在一个树节点上尽可能多地存储数据，一次磁盘 IO 就多加载点数据到内存，这就是 B 树，B+树的的设计原理了。

三 B树

下面这个 B 树，设置每个节点的数据页只能储存一个数据项，查询 id=16 这个数据需要查询比较 4 个节点，也就是经过 4 次磁盘 IO。看起来查询性能与 AVL 树一样。

当我们把单个节点限制的每个数据页中数据项的数量设置为4时，一个存储了 16 个数据的 B 树，查询 id=7 这个数据所要进行的磁盘 IO 为 2 次。相对于 AVL 树而言磁盘 IO 次数降低为一半。

所以数据库索引数据结构的选型而言，B 树是一个很不错的选择。

四 B+树

B 树和 B+树有什么不同呢？

第一，B 树一个节点里存的是数据，而 B+树存储的是索引(地址)，所以 B 树里一个节点存不了很多个数据，但是 B+树一个节点能存很多索引，B+树叶子节点存所有的数据。

第二，B+树的叶子节点是数据阶段用了一个链表串联起来，便于范围查找。

通过 B 树和 B+树的对比我们看出，B+树节点存储的是索引，在单个节点存储容量有限的情况下，单节点也能存储大量索引，使得整个 B+树高度降低，减少了磁盘 IO。其次，B+树的叶子节点是真正数据存储的地方，叶子节点用了链表连接起来，这个链表本身就是有序的，在数据范围查找时，更具备效率。因此 Mysql 的索引用的就是 B+树，B+树在查找效率、范围查找中都有着非常不错的性能。

五 总结

总结mysql索引选择B+树的原因：

​1.二叉查找树对于按顺序自增长的主键会彻底右倾形成一个线性的链表。

​2.红黑树虽然可以通过标记和左旋右旋的方式没有退化为线性表，但是避免不了

​总体的趋势是向右的，对比数据库千万级的主键ID号对于查找性还是巨大的消耗。

​3.AVL树是严格意义上的自平衡二叉树，但是每个节点只能放一个ID及其对应数据，

​ 而每一个节点都会遇到一次磁盘IO，那么IO操作还是过多。

​4.B树的每个节点内存储的是数据，因此每个节点能容纳的存储分支有限。

​5.B+节点存储的是索引+指针(引用指向下一个节点)，可以存储大量索引，同时最终数据存储在叶子节点，并且有引用横向链接，可以在2-3次的IO操作内完成千万级别的表操作。