为什么MySQL要用B+Tree来做索引

0. 预备知识

1. 红黑树数据结构
2. BTree数据结构
3. B+Tree数据结构

建议稍微了解一下这三种数据结构,不然下文可能看不懂

1. 存储介质

这段主要目的是让大家对机械硬盘读取速度有个感性的认识!!!

1.1 机械硬盘名词解释

这里介绍一下常用的最廉价的存储介质机械硬盘

下面我们要对磁盘存储区域进行一下划分

1.1.1 盘面

• 硬盘最基本的组成部分是由坚硬金属材料制成的涂以磁性介质的盘片，不同容量硬盘的盘片数不等
• 我们的数据就存储在这个圆盘的盘面上
• 绿色区域
  

1.1.2 磁道

• 磁盘上的同心圆
• 这里各种颜色都是不同的磁道
• 实际上磁盘上的磁道远远多于图上画的,图只是个示意图
  

1.1.3 扇面

• 磁盘上的扇形区域
• 

1.1.4 扇区

• 磁盘存取的最小单位, 512B
• 从磁盘的
• 红色区域
  

1.1.5 簇(linux叫块)

• 操作系统对磁盘存取的最小单位
• 簇(块)就是多个连续的扇区,一般为2的N次方个扇区
• 常见的簇(块)大小为4KB,那么就是8个扇区
• 这个图略微粗糙了点,表达的意思就是连续的扇区
  

1.2 为什么操作系统操作磁盘的最小单位是簇

• 举个例子: FAT32文件系统
• FAT32文件系统中有一张文件分配表FAT,表中存放了每个簇的位置信息
• FAT32表占据2^32位,理论上支持4294967296个簇
• linux也是类似的
• 如果磁盘的大小一定,簇越大,表就越小,簇越小FAT表就越大,可以管理的磁盘空间就越多
• 当然这个簇不能太大,太大浪费的空间就很多,再小的文件,也要消耗至少1簇的物理
• 这个簇也不能太小,如果太小,我们可管理的硬盘空间就会变小,磁盘文件碎片化程度结汇越厉害

1.3 寻道时间

什么是寻道时间?

• 我们知道机械硬盘,有一个机械臂,可以移动磁头的位置
• 那么寻道时间可以表示为: 磁头从开始移动到数据所在磁道所需要的时间
• 注意这里仅仅只是移动到磁道,还没找到扇区呢

1.4 读取一个簇的时间 Ta

• 设读取一个簇的时间为Ta, 寻道时间为Ts, 磁盘旋转延迟时间 Tr,传输数据时间Tt,那么Ta=Ts+Tr+Tt(先排除其他因素,比如数据传输速度等)
• 寻道时间Ts: Ts=m*n+s(s是启动磁臂时间,n表示磁盘上磁道的条数,m是常数与转速有关)
• 旋转延迟时间Tr: Tr=((60s)/rpm)*0.5 rpm是磁盘转速,以7200转为例,转一圈所需时间为60×1000÷7200=8.33毫秒,乘以0.5意思是,每次数据读取的位置在磁道上都是平均分布
• 读取数据时间Tt:Tt=(b/N)/(60s/rpm)
• 所以读取一个簇总共所花时间为:Ta=Ts+Tr+Tt
• Ta=Ts+Tr+Tt=(m*n+s)+((60s)/rpm)*0.5+(b/N)/(60s/rpm)
• 总之这个Ta是一个毫秒级的操作,各位可以自己计算一下

1.5 磁盘读取时间总结:

非常重要 总结: 机械硬盘读写一个簇是一个毫秒级的操作

---

2. 数据库存储 数据结构选择

2.1 无序数据结构

2.1.1 哈希表

毫无疑问如果没有排序和范围查询的需求哈希表性能最好O(1),但是很多情况下排序是非常强的需求,所以我们暂时不讨论

2.2 有序数据结构

2.2.1 红黑树

优点:

• 假设有N条数据
• 空间复杂度为: O(n),有几条数据,就有几个节点,完全没有冗余,空间利用率非常高效
• 查找时间复杂度: O(lgn),实际上查找的时间复杂度与红黑树的树高是一回事
• 插入时间复杂度: O(lgn),插入之前先查找,然后配合上左旋右旋操作,耗时还是 O(lgn)
• 删除时间复杂度: O(lgn),和插入同理
• 总的来说再时间复杂度的角度来分析,红黑树是个十分理想的数据结构

缺点:

• 磁盘IO较多: 树高增长的很快,因为每个节点最多只有两个子节点,树高增长的快就意味着,有许多查询可能需要查的树比较深,每读一个节点,就会发生一次磁盘IO(毫秒级操作)
  如果有N
• 浪费空间: 每个节点占用空间小,一个节点,只有一个数据和两个子节点的地址,对于操作系统每次读取一簇来说,实在是太浪费了.
• 查询时间不稳定: 树高很高,就意味着,有时候查询会很快,有时候查询会很慢

1. 假设n=1000,那么树高假设为h=lg1000=9.967=10,这个树高增长的趋势还是非常快得
2. 假设平均每次读取的节点高度为10(二叉树很容易就达到),那么就意味着平均每次读取,需要磁盘10次io才能找到目标
3. 那么如果一次硬盘读取为10ms,读取10次就需要100ms来找到期望的节点
4. 综上,红黑树其实不太适合在磁盘这种载体上使用,内存读取一次耗时100ns级别,而硬盘读取一次耗时是10ms这个级别的慢了100倍

2.2.2 BTree

前面说了那么多红黑树不合适硬盘使用的问题,那么怎么解决呢?
BTree为我们提供了一些思路

• BTree和红黑树最大的区别是结点可以有很多个孩子,有兴趣的同学可以看看算法导论中的定义
• BTree不对每个节点的孩子数量做严格限制
• 每个结点可以存放多条数据,至少t/2-1（取上整）和至多t-1个关键字；

设BTree子节点个数为t,t为常数
树高<=logt((n+1) /2)      以t为底的log函数
设t=2^x,x为常数
查找复杂度=O(logt((n+1) /2))=O((1/n)lg((n+1)/2))=O(lgn)
查找复杂度还是O(lgn)

推荐一个网站: http://algo.bjtu.edu.cn/static/visual/usfca/Algorithms.html
这里有个非常形象的BTree结构的操作,建议大家都去看看

举个例子,看看查询效率

例子不严谨
设我们有1000个结点,设t=1000(假设每个结点可以有1000个关键数据)
设1000个键数据,占据磁盘空间<=4kb,一个簇(块)可以容纳得下
根据公式h<= logt((n+1) /2)  = 1.00045 = 2,树高为2
即我们查到指定的数据只需要读取两个簇
而1000条数据读入内存之后,进行比较操作,耗时跟磁盘io相比耗时可以忽略不计,不是一个数量级的
设每个结点比较耗时100ns,如果进行了1000次比较来找到指定结点.1,000,000纳秒=1毫秒,那么耗时为0.001ms
也就是说,我们查到指定数据,最多只需要两次磁盘IO,查询时间约等于20ms

上面这个例子可以看出:

• 相比红黑树,BTree的树深度增长得非常缓慢
• 即使一次性将较多的数据加载到内存来处理,cpu和内存的处理速度也要比磁盘io块太多

给大家看个反映数量级的表格,不代表实际的速度,
(cpu缓存速度和cpu频率强相关,可以上intel官网查查然后计算一下,每个时钟周期能传送的数据是固定的,比如64kb)

	延迟	顺序读写速度	4k随机读写速度
机械硬盘	2500000ns	100MB/s	1MB/s
固态硬盘	15000ns	1000MB/s	10MB/s
内存	100ns	10000MB/s	500MB/s
L3 cache	1ns	>100000MB/s	>100000MB/s

(1,000,000纳秒=1毫秒)

• 由此可见CPU和内存操作,与硬盘操作相比几乎可以忽略不记

2.2.3 B+Tree

B+Tree优点:

上面分析了红黑树和BTree之间的比较
那么为什么MySQL要用B+Tree呢?与B+Tree相比还有什么优势呢?

• B+Tree把所有卫星数据都存储在叶节点中,内部结点只存放关键字和孩子结点的地址
• BTree最大的问题是无法高效率的范围查询,而B+Tree可以轻松解决

http://algo.bjtu.edu.cn/static/visual/usfca/BPlusTree.html

• B+Tree的每个叶子结点都会指向下一个叶子结点
• 如果想要范围查询,我们查到指定结点之后,只需要顺着往下查即可

B+Tree缺点:

1. 因为结点中只保存了关键数据,想要获取完整的数据,就一定要查到叶节点,
2. 但其实也可以认为,每次都要查到叶节点,是让查询速度更加稳定了
3. 空间占用相对BTree多一些,因为除了叶节点,其他父节点全部是为了排序冗余的

---

3.总结

通过以上分析,我们可以看出
B+Tree的设计,正好适合数据库通过操作系统读取操作硬盘的特性

• 复杂度低
• 树高增长慢
• IO次数少
• 不浪费每次读取
• 范围查找效率高
• 查询耗时稳定

MySQL就是各方权衡,最终选择B+Tree来作为存储的数据结构