MYSQL--基础--4.2--索引--数据结构--B+树

MYSQL–基础–4.2–索引–数据结构–B+树

---

## 数据结构在线演示地址
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

1、B+Tree(B-Tree变种)

• 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
• 叶子节点包含所有索引字段
• 叶子节点用指针连接，提高区间访问的性能
• 每层节点元素：从左到右，依次递增

1.1、和B树区别

• 叶子节点用指针连接，提高区间访问的性能
  • 叶子节点中：18 和 20 使用指针连接
• 非叶子节点做了冗余索引
  • 叶子节点15和上层节点：使用冗余索引15

1.2、演示

1.2.1、新增有序数据

1，2，3，4，5，6，7，8，9

1.3、B+Tree索引的数据结构(细节)

浅蓝色的块

我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示)，如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。

深蓝色的块(非叶子节点)

不存储真实的数据，只存储指引搜索方向的数据项，如17、35

黄色的块

指向下一个磁盘块的指针

深蓝色的块(叶子节点)

存真实的数据，即3、5、9、10、13、15、28、29、36、60、75、79、90、99。

2、公式

2.1、高度为N层的B+树，能存储多少个索引

2.1.1、通过案例来总结公式

计算高度为3层的B+树，能存储多少个索引

2.1.1.1、页面(非叶子节点)

2.1.1.2、假设字段类型是bigint，每一页大概能存储多少索引？

16 × 1024 ÷ (8+6) =1170.2857 

也就是说每个页面可以存储1170个索引和1170个下层节点的元素地址

2.1.1.3、每个叶子节点可以保存多少元素

16 ÷1 =16

2.1.1.4、高度为3层的B+树，能存储多少个索引

1170 × 1170 × (16 ÷1)=21902400=2190W

能存储2190W个索引，对应能存储2190W行数据。

2.1.1.5、高度为4层的B+树，能存储多少个索引

1170 ^(4-1) × (16 ÷1)=25625808000=2562580W=256.2亿

能存储256.2亿个索引，对应能存储256.2亿行数据。

2.1.2、总结

存储索引的数量=存储行数的数量= (页大小/(数据项大小+6B))的(h-1)次幂*(页大小/每行数据的大小)
 
页大小：默认16KB
数据项大小：就是索引对应字段的数据类型，比如int占4字节，bigint占8字节
每行数据的大小：一般不会超过1KB
h：高度

2.1.2.1、假设索引的字段是bigint类型，深度是3，能存储多少个索引

存储索引的数量=存储行数的数量= (16KB÷(8B+6B))^(3-1)*(16KB÷1KB) ≈ 2191W

2.2、表数据行数为N，索引字段类型是bigint，请问B+数的高度是多少？

 
 
h = log(m+1)N 
h：b+树的高度
N：当前数据表的数据，也就是整个表的数据
m： 每个磁盘块的数据项的数量 
m = 磁盘块的大小 / 数据项的大小
一个磁盘块的大小 等于 一个数据页的大小，msyql中是16KB  
数据项大小：就是索引对应字段的数据类型，比如int占4字节，bigint占8字节

注意：m+1是底数

总结：
h = log((页大小÷数据项的大小)+1)N 

2.2.1、当N不变场景

数据项越大，m越小，h越大，B+树深度越高， IO次数约大(次数和h正相关)
数据项越小，m越大，h越小，B+树深度越低， IO次数约小(次数和h正相关)

2.2.2、数据项设计建议

每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。

这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项的大小变大(真实的数据比索引的数据来的大)，m变小，h变高。当(m+1)趋近于1时将会退化成线性表。

3、索引原理

3.1、索引的目的

提高查询效率，与我们查字典一个道理：先定位首字母拼音，然后定位到该首字母拼音下的第二个字母的拼音，通过这个方式最后找到字。

3.2、索引的本质

通过不断地缩小想要获取数据的范围来筛选出最终想要的结果。

数据库也是一样，把数据分成段，然后分段查询，打个比方如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段…这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。

3.3、磁盘IO与预读

访问磁盘的成本大概是访问内存的十万倍左右，考虑到访问磁盘IO是非常费时的操作，操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据读取到内存缓冲区内，还把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。

每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为16KB，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

4、b+树的查找过程

如图所示，怎么查找数据项29

• 第1步：把磁盘块1从磁盘加载到内存，此时发生1次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短(相比磁盘的IO)可以忽略不计。
• 第2步：通过磁盘块1的P2指针的磁盘地址把磁盘块3从磁盘加载到内存，发生第2次IO，29在26和30之间，锁定磁盘块3的P2指针。
• 第3步：通过磁盘块3的P2指针加载磁盘块8到内存，发生第3次IO，同时内存中做二分查找找到29，结束查询，总计3次IO。

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

5、索引的最左匹配特性(即从左往右匹配)

当b+树的数据项(索引)是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的。

会根据name创建索引
会根据name,age创建索引
会根据name,age,sex创建索引

索引的匹配优先级：先匹配name，再匹配age，再匹配sex

比如当(张三，20，F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；

比如当(20，F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，索引失效，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。

比如当(张三，F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了。

上面讲的就是：索引的最左匹配特性。