深入剖析 MySQL 索引底层数据结构

深入理解 MySQL 索引底层原理

Mysql 作为互联网中非常热门的数据库，其底层的存储引擎和数据检索引擎的设计非常重要，尤其是 Mysql 数据的存储形式以及索引的设计，决定了 Mysql 整体的数据检索性能。

何为索引

我们知道，索引的作用是做数据的快速检索，而快速检索的实现的本质是数据结构。通过不同数据结构的选择，实现各种数据快速检索。在数据库中，高效的查找算法是非常重要的，因为数据库中存储了大量数据，一个高效的索引能节省巨大的时间。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

如何选择Mysql 索引底层数据结构

哈希表

哈希表是做数据快速检索的有效利器。
哈希算法：也叫散列算法，就是把任意值(key)通过哈希函数变换为固定长度的 key 地址，通过这个地址进行具体数据的数据结构。
考虑这个数据库表 user，表中一共有 7 个数据，我们需要检索 id=7 的数据，SQL 语法是：

select \* from user where id=7;

哈希算法首先计算存储 id=7 的数据的物理地址 addr=hash(7)=4231，而 4231 映射的物理地址是 0x77，0x77 就是 id=7 存储的额数据的物理地址，通过该独立地址可以找到对应 user_name='g’这个数据。这就是哈希算法快速检索数据的计算过程。
解决哈希算法数据碰撞问题
解决碰撞问题的一个常见处理方式就是链地址法，即用链表把碰撞的数据接连起来。计算哈希值之后，还需要检查该哈希值是否存在碰撞数据链表，有则一直遍历到链表尾，直达找到真正的 key 对应的数据为止。
哈希算法的时间复杂度为O(1),检索速度非常快，但是MySQL并没有选择作为底层算法，为何？
因为考虑到数据检索有一个常用手段就是范围查找，比如以下这个 SQL 语句：

select \* from user where id \>3;

如果使用哈希算法实现的索引，范围查找的思路就是一次把所有数据找出来加载到内存，然后再在内存里筛选目标范围内的数据。但是这个范围查找的方法也太笨重了，没有一点效率而言，因此哈希索引是不适合作为 Mysql 的底层索引的数据结构。

二叉查找树(BST)

二叉查找树是一种支持数据快速查找的数据结构，时间复杂度是 O(lgn)，从检索效率上看来是能做到高速检索的。此外二叉树的结构也可以解决哈希索引不能提供的范围查找功能，二叉树的叶子节点都是按序排列的，从左到右依次升序排列，比如说需要找节点上 id>5 的数据，那我们取出节点为 6 的节点以及其右子树就可以了，范围查找也算是比较容易实现。
但是普通的二叉查找树有个致命缺点：极端情况下会退化为线性链表，二分查找也会退化为遍历查找，时间复杂退化为 O（N），检索性能急剧下降。在数据库中主键字增是比较常见的形式，如果采取二叉树这种数据结构作为索引，那上面介绍到的不平衡状态导致的线性查找的问题必然出现。因此，简单的二叉查找树也不能直接用于实现 Mysql 底层索引的。

AVL 树和红黑树

二叉查找树存在不平衡问题，可以通过树节点的自动旋转和调整，让二叉树始终保持基本平衡的状态，就能保持二叉查找树的最佳查找性能了，因此也可以联想到AVL树和红黑树。
红黑树是一颗会自动调整树形态的树结构，比如当二叉树处于一个不平衡状态时，红黑树就会自动左旋右旋节点以及节点变色，调整树的形态，使其保持基本的平衡状态（时间复杂度为 O（logn）），也就保证了查找效率不会明显减低，红黑树拥有不错的平均查找效率，也不存在极端的 O(n)情况，但是如果将红黑树作为Mysql 底层索引实现也是存在一些问题。
红黑树顺序插入 1~7 个节点，查找 id=7 时需要计算的节点数为 4。顺序插入 1~16 个节点，查找 id=16 需要比较的节点数为 6 次。当数据是顺序插入时，树的形态一直处于“右倾”的趋势，因此红黑树并没有完全解决二叉查找树“右倾”趋势，即使不是一个线性链表，因为这种现象对于查找性能而言是巨大的消耗，数据库不可能忍受这种无意义的等待。
AVL树相较于红黑树拥有更为严格的自平衡性，因此他在调整二叉树的形态上消耗的性能会更多，并且参考红黑树的例子，查找id为7和16的比较次数更少，并且没有明显的“右倾”，也就是说，大量的顺序插入不会导致查询性能的降低，这从根本上解决了红黑树的问题。但是AVL树也并不能作为 Mysql 数据库的索引数据结构，因为数据库查询数据的瓶颈在于磁盘 IO，如果使用的是 AVL 树，我们每一个树节点只存储了一个数据，我们一次磁盘 IO 只能取出来一个节点上的数据加载到内存里比如查询 id=7 这个数据我们就要进行磁盘 IO 三次。所以我们设计数据库索引时需要考虑的问题就是减少磁盘 IO 的次数。

磁盘IO特点

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

B树

假设B 树每个节点限制最多存储两个 key，一个节点如果超过两个 key 就会自动分裂，那么在查询id=7的节点时只需要查询两次就可以得到，优于AVL树。如果每一次尽可能在一次磁盘 IO 中多读一点数据到内存，即每个节点存储的key适当增加，效果是否会更好？答案是肯定的。当我们把单个节点限制的 key 个数设置为 6 之后，一个存储了 7 个数据的 B 树，查询 id=7 这个数据所要进行的磁盘 IO 为 2 次，由此我们可以考虑B+树。

B+树

B+树与B树的不同在于B 树一个节点里存的是数据，而 B+树存储的是索引（地址），所以 B 树里一个节点存不了很多个数据，但是 B+树一个节点能存很多索引，B+树叶子节点存所有的数据，并且B+树的叶子节点是数据阶段用了一个链表串联起来，这就解决了范围查找问题。

选择B+树的理由

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

资料来源：公众号数据库开发