MYSQL索引之B+tree

01、分析

MySql底层的数据结构主要是基于Hash 和 B+Tree

01-01、二叉查找树

数据结构动态操作网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html java版本下载：https://www.cs.usfca.edu/~galles/visualization/java/visualization.jar

下载以后执行：java -jar visualization.jar

01-02、二叉树数据存储的结构

 为了加快数据的查找,可以维护二叉查找树, 每个节点分别包含索引键值和一个指向对应数据记录的物理地址的指针,这样就可以运用二叉查找在一定的复杂度内获取相应的数据,从而快速的检索出符合条件 的记录。从二叉树的查找过程了来看，最坏的情况下磁盘IO的次数由树的高度来决定。从前面分析情况来看，减少磁盘IO的次数就必须要压缩树的高度，让瘦高的树尽量变成矮胖的树，所以引出B-Tree强势登场

特点： 1、任意节点的左子树不空，则左子树的键值小于根的键值（小于root的放左边） 2、任意节点的右子树不空，右子树的键值大于根的键值（大于root的放右边）

缺点： 1、二叉树是一种偏向树，会造成层级过深，给查询其实并没用提高性能。 2、二叉树也只能存两个节点，层级越来越大越来越深，发生磁盘的IO会越频繁。

01-03、平衡二叉树数据存储的结构

参考：https://www.cs.usfca.edu/~galles/visualization/AVLtree.html

特点：

1、平衡二叉树本质是：二叉树，只不过是二叉树变体 2、平衡二叉树每个节点的左右子节点数的高度之差的绝对值最多为1（保持绝对的平衡）。

缺点

1、平衡二叉树虽然具有层级减少的好处，但是问题也很明显。

2、每次改变节点时候，为了达到平衡会不停的动态调整和旋转他们之间的位置（也就是block的位置）。如果用这种数据结构去做索引存储可能一样效率不高。

01-04、Btree

Btree 和 B+tree 都是通过最原始的数据的结构：二叉树 构建出来一种形态。

Btree是一种多路自平衡搜索树，它类似普通的二叉树，但是Btree允许每个节点有更多的子节点。每个节点包含key和data。B树示意图如下：

参考： https://www.cs.usfca.edu/~galles/visualization/BTree.html

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

BTree 的特性：

为了描述BTree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除以key外的数据。那么BTree是满足下列条件的数据结构：

1、d 为大于1的一个正整数，称为BTree的度；

2、h为一个正整数，称为BTree的高度；

3、key和指针互相间隔，节点两端是指针；

4、会占用更大的内存空间。

5、一个节点中的key从左到右非递减排序

6、每个指针要么为null，要么指向另外一个节点；每个非叶子节点由 n-1 个key 和 n个指针组成，其中

d <= n <= 2d;

7、每个叶子节点最少包含一个key和两个指针，最多包含 2d-1个key 和 2d个指针，叶节点的指针均为null

8、所有叶子节点必须处于同一级别（从下往上构建）也就是说：父节点的推荐和选择是由子类决定。这样我们讲数据先全部存储到叶子节点中，减少了磁盘的交换

总结：

• 叶节点具有相同的深度

• 叶节点的指针为空

• 节点的数据索引从左到右递增排列

缺点：

• 单节点存储的key过少。

• Btree数据是存储到每个节点自己，所以每次查询的和维护的时候就会维护索引值又维护了数据，这样会就是造成内存的浪费和性能的消耗。这也是B+TREE优化的地方。

• 为了提升key的数量，还需要对这种数据结构进行优化，所以它的升华版B+Tree诞生了

但是BTree仍然存在一些问题，比如执行下面的语句，查找col1 > 20 的值

 select *from t where col1 > 20;

那么不但需要叶子节点>20的值，也需要非叶子节点在右边节点的值。即下图圈的两部分：

BTree似乎在范围查找没有更简便的方法，为了解决这一问题。我们可以用B+Tree。

存在的问题

问题1：btree 还是具体：添加数据，删除数据，修改数据都会去调整索引--造成lock

问题2：btree的每个节点的数据，都是存在自己的节点上，如果我为一个表创建多个索引，那么久需要维护多份数据。就需要更大的内存。

问题3：如果说你要进行范围查询：就造成查询折回过程，这个非常的消耗性能

01-05、B+Tree

在B-Tree基础上进行优化，使其更适合实现外存储索引结构。InnoDB就是存储引擎就是用B+Tree。 在B-Tree中每一个页存储空间有限，如果data数据较大，会导致每个节点key太小，当数据量很大同一会导致B_Tree深度较大，增大查询的磁盘IO次数，影响查询效率。

在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层叶子节点上，而非叶子节点上只存储key值信息，可以大大增大每个节点存储的key值的数量，降低B+Tree的高度。

参考网站： https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

B+树是Btree 的变体，也是一种多路平衡查找树，B+树的示意图为：

特点

• 非叶子节点不存储data,只存储key,以存放更多索引

• 叶子节点不存储指针,所有的数据都存储在叶子节点上

• 顺序访问指针，提高区间访问能力

B+Tree索引的性能分析

1、一般使用磁盘I/O次数评价索引结构的优劣

2、预读：磁盘一般会顺序向后读取一定长度的数据(页的整数倍)放入内存

3、局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用

4、B+Tree的度一般会超过10，因此h非常小 (一般为3到5之间)，性能就会非常稳定

5、B+Tree叶子节点有顺序指针，更容易做范围查询

6、存储器读取数据按 磁盘块读取

7、每个磁盘块的大小为 扇区（页）的2的N次方

8、每个扇区的最小单位 512B 或 4096B 不同的生产厂家不同

01-06、B+Tree 索引为什么可以支持千万级别数据量的查找

分析：

从磁盘中 --user - 100w ---id suoyin + zhij/ 16KB ---

MySQL 官方对非叶子节点(如最上层 h = 1的节点，B+Tree高度为3) 的大小是有限制的，通过执行

 SHOW GLOBAL STATUS like 'InnoDB_page_size';

可以得到大小为 16384，即 16k大小。x

那么第二层也是16k大小。

假如：B+Tree的表都存满了。索引的节点的类型为BigInt，大小为8B，指针为6B。

最后一层，假如 存放的数据data为1k 大小，那么

1. 第一层最大节点数为： 16k / (8B + 6B) = 1170 (个block)；

2. 第二层最大节点数也应为：1170个block；

3. 第三层最大节点数为：16k / 1k = 16 (个block)。

则，一张B+Tree的表最多存放 1170 * 1170 * 16 ≈ 2千万。

所以，通过分析，我们可以得出，B+Tree结构的表可以容纳千万数据量的查询。

而且一般来说，MySQL会把 B+Tree 根节点放在内存中，那只需要两次磁盘IO就行

 01-07、 B+Tree解决范围查找

在上述的图中，我们可以看到B+Tree 还有一个顺序访问指针，这样一来，当我们会到上面的范围查找

 select *from t where col1 >= 20;

时，B+Tree可以通过该指针把20 后面的直接找到，非常方便。

 

总结

索引列：最好不要经常的更新和删除，索引重建过程中，如果数量很大，会产生lock。这一时刻数据是无法写入和操作。

索引是一种数据结构：B+Tree

btree

1、btree 是一种多路平衡数，深度是固定

2、btree 是一种多路平衡数，数据+索引值（id主键）+指针（十六进制的数字，执行磁盘空间data数据的存储扇区block）。每个叶子节点都存储了数据索引和指针

3：叶子节点（没有孩子的都称之为叶子节点）。叶子节点是没有指针的。

4：遵循二叉树，比非叶子节点大的放右边，小的放左边

5、btree所有叶子节点必须处于同一级别（从下往上构建）也就是说：父节点的推荐和选举是由子节点决定。这样我们将数据先全部存储到叶子节点中，减少了磁盘的交换

b+tree

1、b+tree 是一种多路平衡数，深度是固定

2、b+tree 是一种多路平衡数，数据+索引值（id主键）+指针（十六进制的数字，执行磁盘空间data数据的存储扇区block）。只有叶子节点存储了数据索引

3、所有的节点都会下沉到叶子节点中，进行排序和列表连接。目的就是为了：提高查询速度，提升范围查询的速度

4、b+tree所有叶子节点必须处于同一级别（从下往上构建）也就是说：父节点的推荐和选举是由子节点决定。这样我们将数据先全部存储到叶子节点中，减少了磁盘的交换