MySQL索引数据结构二叉树、红黑树、B-Tree、B+Tree、Hash

Seventeen117

已于 2022-09-15 14:17:45 修改

阅读量630

点赞数 3

分类专栏： MySQL 文章标签：数据结构 mysql 数据库

于 2022-09-15 00:47:21 首次发布

本文链接：https://blog.csdn.net/Soil_Three_66/article/details/126843882

版权

MySQL 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

索引：帮助MySQL高效获取数据的有序的数据结构。

假设我们有一张表table，包含Clo1和Clo2两个字段

内存地址	Clo1	Clo2
0x07	1	36
0x5A	2	20
0x7A	3	80
0xF3	4	8
0x77	5	28
0xD1	6	66
0x89	7	88

在没有索引情况下的执行下面SQL

select * from table where Clo2 = 66;

过程如下：

在没有索引的情况下要找到Clo2=66的数据，要经过6次的I/O，6次RAM内存比较，在内存中比较的时间会快，完全可以忽略不计，但是6次的I/O时间会很长（导致慢SQL主要原因之一）。

通过索引进行SQL效率优化，可以认为是尽可能的减少I/O的次数，就是后面讲的降低树的高度。如何减少I/O的次数，这就涉及到了数据结构。接下来介绍一下二叉树、红黑树（二叉平衡树）、B-Tree、B+Tree、Hash的数据结构模型。

数据结构可视化学习网站：

Data Structure Visualizationhttps://www.cs.usfca.edu/~galles/visualization/Algorithms.html

1.二叉树

右侧子元素大于父元素数据，左侧子元素小于父元素数据

下面我们通过上面学习网站看一下Clo1建立二叉树的数据结构和Clo2建立二叉树数据结构

Clo1是单边增长的列，生成的二叉树结构与链表一样，树的高度是7，Clo2生成的二叉树树的高度是3。模拟一下查询Clo1=6,Clo2=66这一条数据：

使用Clo1进行查询

找到Clo1=6的数据一共经历了6次I/O和6次比较，在树的第6层拿到数据。

使用Clo2进行查询

找到Clo2=66的数据，进行了3次I/O和比较，在树的第3层拿到数据

由已上例子可以看出进行的IO次数就是数据所在树的层数

2.红黑树（二叉平衡树）

红黑树的结构与二叉树结构一样，区别在于生成的过程中红黑树会自动平衡节点。

再模拟一次查询Clo1=6的数据：经历了3次I/O和比较，在树的第3层拿到数据。同样的使用Clo=6查找数据，二叉树用了6次I/O，红黑树用了3次I/O，由此可看出红黑树要比二叉树更适合创建索引。但是数据量大了，到了几百万的时候树的高度依然不可控。

红黑树的根节点只有一个数据，我们将根节点的内存扩大多存放一些数据就可以降低树的高度，接下来的B-Tree就是这种结构。

3.B-Tree

叶节点具有相同的深度，叶节点的指针为空
所有索引元素不重复
节点中的数据索引从左到右递增排列
节点中存储data数据

当索引结构以B-Tree数据结构创建时，再次查找Clo2 = 66 的数据：

1.将树的第一层所有数据load到内存，使用比对算法（如：二分查找）进行比对

a.如果有66则将对应的data数据从内存中读取出来

b.如果没有，则会找66对应的区间地址（35-80）

2.将35-80这一页的所有数据再拿出来与66做比较

a.如果有66则将对应的data数据从内存中读取出来

b.如果没有，则会找66对应的区间地址

以此类推，直到找到66或者这一系列的数据比对完毕

使用了B-Tree数据结构后，我们查询Clo2=66的数据只做了两次的I/O，比红黑树的效率更高一些。

但是MySQL底层用的依然不是B-Tree，而是B-Tree的变种B+Tree

4.B+Tree

非叶子节点不存储data，只存储索引（冗余），目的是可以放更多的索引
叶子节点包含所有索引字段
叶子节点使用指针连接，提高区间访问的性能

1.为什么非叶子节点不存储data，可以放放更多的索引，也是MySQL索引数据结构使用B+Tree不使用B-Tree的主要原因。

通过SHOW GLOBAL STATUS like 'Innodb_page_size';可以看到mysql数据库中定义文件页大小是16384B（16KB）。

如果叶子节点不存储data，假设字段类型是BigInt，字段值占用8B，地址值占用6B，一层16KB大概可以存储1170个索引值。

如果叶子节点存储data，一般的data+字段值+地址值不会大于1KB，我们就按照平均1KB计算，一层16KB也就只能存储16个索引值。

2.为什么MySQL页文件默认是16KB?

由问题1知道第一层一页数据可以存1170个索引值，如果再加一层，第一层的每一个地址值指向第二层的一页数据，我们就可以存储 1170X1170 个索引值，第二层每个地址值指向第三层的一页数据，第三层的叶子节点要带数据存储每一页只存16个数据，三层高的树可以存储 1170 X 1170 X 16 大约2千万左右的数据。

3.叶子节点指针的作用