Mysql数据存储原理解析

郑重申明：该内容摘抄自网络（深度好文）https://www.xjx100.cn/news/478262.html?action=onClick

mysql使用b+tree为底层数据结构，至于为什么使用b+tree而不使用b-tree和红黑树，我们来分析一下。

红黑树

红黑树存储的数据量大的时候，红黑树的节点层数多，也就是树的高度比较高，查找的底层数据时，查找次数就比较多，即对磁盘IO使用比较频繁，还有就是树的每个节点，存放的数据很少，通过计算本来树的每一层大概需要分配16KB的数据。而红黑树所存的数据远远小于16KB，造成空间的浪费。

总结一下就是有两个缺点

1. 浪费存储空间
2. 磁盘读取太频繁（我们知道磁盘读取是很慢的，所以要想提高查询效率，就必须要尽可能的减少磁盘IO。）

那么我们可以从以下两点出发进行改进：

1. 增加树每层的节点数量，这样可以对分配的16KB充分利用，即解决上面的读取浪费的问题
2. 尽可能的让树的高度减小，使得树显得比较“矮胖”，这样可以减少读取磁盘的次数

那么怎么样才可以实现以上的方法呢？这就需要用到b-tree了。

B-Tree：

一棵m阶的B-Tree有如下特性：

1. 每个节点最多有m个孩子。
2. 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
3. 若根节点不是叶子节点，则至少有2个孩子
4. 所有叶子节点都在同一层，且不包含其它关键字信息
5. 每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
6. 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
7. ki(i=1,…n)为关键字，且关键字升序排序。
8. Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

当看完上面的特性我是崩溃的，这也太多太复杂了，所以放张图来理解一下。

模拟查找关键字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查找效率的决定因素。B-Tree相对于红黑树缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

可以看到B-Tree完美解决了这两个问题，在每个节点都有data数据，且根据特性来维持树的高度。但是每一个页的存储空间是有限的，如果data数据较大会导致每个节点能存储的key数量很小，当数据量很大的时候，同样会导致树的高度增加，从而增加磁盘IO次数，影响查询效率，这是我们不愿意看到的。

B+Tree：

B+Tree是在B-Tree基础上的一种优化，使其更适合实现存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。
在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

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

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

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

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。
我们可以看到，通过这种巧妙的设计，既减少了树的高度，还存储了更多的数据。

B+Tree 索引为什么可以支持千万级别数据量的查找
分析：
MySQL 官方对非叶子节点(如最上层 h = 1的节点，B+Tree高度为3) 的大小是有限制的，通过执行
SHOW GLOBAL STATUS like 'InnoDB_page_size'
可以得到大小为 16384，即 16k大小。
那么第二层也是16k大小。

假如：B+Tree的表都存满了。索引的节点的类型为BigInt，大小为8B，指针为6B。
最后一层，假如 存放的数据data为1k 大小，那么

1. 第一层最大节点数为： 16k / (8B + 6B) = 1170 (个)；
2. 第二层最大节点数也应为：1170个；
3. 第三层最大节点数为：16k / 1k = 16 (个)。

则，一张B+Tree的表最多存放 1170 * 1170 * 16 ≈ 2千万。
所以，通过分析，我们可以得出，B+Tree结构的表可以容纳千万数据量的查询。

插入原理请参考

【MySQL进阶】深入理解B+树索引底层原理_mysql b+树原理-CSDN博客