MySQL笔记(六)MySQL 存储引擎的索引为什么使用B+树?
为什么 MySQL 使用 B+树 - 知乎 (zhihu.com)
首先需要澄清的一点是,MySQL 跟 B+ 树没有直接的关系,真正与 B+ 树有关系的是 MySQL 的默认存储引擎 InnoDB
,MySQL 中存储引擎的主要作用是负责数据的存储和提取
(从我们的磁盘中),除了 InnoDB 之外,MySQL 中也支持 MyISAM 作为表的底层存储引擎。
![image-20220306120713501](https://bearbrick0.oss-cn-qingdao.aliyuncs.com/images/img/image-20220306120713501.png)
先思考一下,为什么不用平衡二叉搜索树或者红黑树,而选择B+树?
- 二叉树相比于顺序查找的确减少了查找次数,但是在最坏情况下,二叉树有可能退化为顺序查找。而且就二叉树本身来说,当数据库的数据量特别大时,其层数也将特别大。二叉树的高度一般是
log_2^n
,B 树的高度是log_t^((n+1)/2) + 1
,其高度约比 B 树大lgt
倍。n 是节点总数,t 是树的最小度数。 - B 树在提高 IO 性能的同时,并没与解决元素遍历时效率低下的问题,正是为了解决这个问题,B+树应运而生。B+树只需遍历叶子节点即可实现整棵树的遍历,而 B 树必须使用中序遍历按序扫库,B+树支持范围查询非常方便。这才是数据库选用 B+树的主要原因。另外,最后说一下,并不是说 B+树就比 B 树好,有很多基于频率的搜索是选用 B树,越频繁 query 的结点越往根上走,前提是需要对 query 做统计,而且要对 key做一些变化。无论是 B 树还是 B+树由于前边几层反复 query,因此早已被加载入内存,不会出现读磁盘 IO。一般启动的时候,就会主动换入内存。在内存中 B+树并没有优势,只有在磁盘中 B+树的威力才能显现。
- B+树的高度一般为 2-4 层,所以查找记录时最多只需要 2-4 次 IO,相对二叉平衡树已经大大降低了。 范围查找时,能通过叶子节点的指针获取数据。例如查找大于等于 3 的数据,当在叶子节点中查到 3 时,通过 3 的尾指针便能获取所有数据,而不需要再像二叉树一样再获取到 3 的父节点。
总结一下:
- 二叉查找树(BST):解决了排序的基本问题,但是由于无法保证平衡,可能退化为链表
- 平衡二叉树(AVⅥL):通过旋转解决了平衡的问题,但是旋转操作效率太低
- 红黑树:通过舍弃严格的平衡和引入红黑节点,解决了 AⅥ旋转效率过低的问题,但是在磁盘等场景下,树仍然太高,IO 次数太多
- B 树:通过将二叉树改为多路平衡查找树,解决了树过高的问题
- B+树:在 B 树的基础上,将非叶节点改造为不存储数据的纯索引节点,进一步降低了树的高度;此外将叶节点使用指针连接成链表,范围查询更加高效。B+树叶节点之间只是逻辑相邻,而不是物理相邻,甚至在物理位置相邻很远的情况下,依然会产生很多的随机IO。B+树减少随机IO的关键在于,利用叶节点逻辑相邻的特性,尽可能地做到物理相邻(数据被分配到连续的页中),使得在读取叶节点中的大量记录时可以使用顺序IO。这点很重要!
我们今天最终将要分析的问题其实还是,为什么 MySQL 默认的存储引擎 InnoDB 会使用 B+ 树来存储数据,相信对 MySQL 稍微有些了解的人都知道,无论是表中的数据(主键索引)还是辅助索引最终都会使用 B+ 树来存储数据,其中前者在表中会以 <id, row> 的方式存储,而后者会以 <index, id> 的方式进行存储,这其实也比较好理解:
- 在主键索引中,id 是主键,我们能够通过 id 找到该行的全部列;
- 在辅助索引中,索引中的几个列构成了键,我们能够通过索引中的列找到 id,如果有需要的话,可以再通过 id 找到当前数据行的全部内容;
对于 InnoDB 来说,所有的数据都是以键值对的方式存储的,主键索引和辅助索引在存储数据时会将 id 和 index 作为键,将所有列和 id 作为键对应的值。
![image-20220306122202136](https://bearbrick0.oss-cn-qingdao.aliyuncs.com/images/img/image-20220306122202136.png)
到了这里我们已经明确了今天待讨论的问题,也就是为什么 MySQL 的 InnoDB 存储引擎会选择 B+ 树作为底层的数据结构,而不选择 B 树或者哈希?在这一节中,我们将通过以下的两个方面介绍 InnoDB 这样选择的原因。
- InnoDB 需要支持的场景和功能需要在特定查询上拥有较强的性能;
- CPU 将磁盘上的数据加载到内存中需要花费大量的时间,这使得 B+ 树成为了非常好的选择;
数据的持久化以及持久化数据的查询其实是一个常见的需求,而数据的持久化就需要我们与磁盘、内存和 CPU 打交道;MySQL 作为 OLTP 的数据库不仅需要具备事务的处理能力,而且要保证数据的持久化并且能够有一定的实时数据查询能力,这些需求共同决定了 B+ 树的选择,接下来我们会详细分析上述两个原因背后的逻辑。
- 读写性能
很多人对 OLTP 这个词可能不是特别了解,我们帮助各位读者快速理解一下,与 OLTP 相比的还有 OLAP,它们分别是 Online Transaction Processing
和 Online Analytical Processing
,从这两个名字中我们就可以看出,前者指的就是传统的关系型数据库,主要用于处理基本的、日常的事务处理,而后者主要在数据仓库中使用,用于支持一些复杂的分析和决策。
作为支撑 OLTP 数据库的存储引擎,我们经常会使用 InnoDB 完成以下的一些工作:
- 通过 INSERT、UPDATE 和 DELETE 语句对表中的数据进行增加、修改和删除;
- 通过 UPDATE 和 DELETE 语句对符合条件的数据进行批量的删除;
- 通过 SELECT 语句和主键查询某条记录的全部列;
- 通过 SELECT 语句在表中查询符合某些条件的记录并根据某些字段排序;
- 通过 SELECT 语句查询表中数据的行数;
- 通过唯一索引保证表中某个字段或者某几个字段的唯一性;
如果我们使用 B+ 树作为底层的数据结构,那么所有只会访问或者修改一条数据的 SQL 的时间复杂度都是 O(log n),也就是树的高度,但是使用哈希却有可能达到 O(1) 的时间复杂度,看起来是不是特别的美好。但是当我们使用如下所示的 SQL 时,哈希的表现就不会这么好了:
select * from posts where author = 'draven' order by created_at desc;
select * from posts where comments_count > 10;
update posts set github = 'github.com/BearBrick0' where author = 'BearBrick0';
delete from posts where author = 'BearBrick0';
如果我们使用哈希作为底层的数据结构,遇到上述的场景时,使用哈希构成的主键索引或者辅助索引可能就没有办法快速处理了,它对于处理范围查询或者排序性能会非常差,只能进行全表扫描并依次判断是否满足条件。全表扫描对于数据库来说是一个非常糟糕的结果,这其实也就意味着我们使用的数据结构对于这些查询没有其他任何效果,最终的性能可能都不如从日志中顺序进行匹配。
使用 B+ 树其实能够保证数据按照键的顺序进行存储,也就是相邻的所有数据其实都是按照自然顺序排列的,使用哈希却无法达到这样的效果,因为哈希函数的目的就是让数据尽可能被分散到不同的桶中进行存储,所以在遇到可能存在相同键 author = 'BearBrick0 或者排序以及范围查询 comments_count > 10 时,由哈希作为底层数据结构的表可能就会面对数据库查询的噩梦 —— 全表扫描。
B 树和 B+ 树在数据结构上其实有一些类似,它们都可以按照某些顺序对索引中的内容进行遍历,对于排序和范围查询等操作,B 树和 B+ 树相比于哈希会带来更好的性能,当然如果索引建立不够好或者 SQL 查询非常复杂,依然会导致全表扫描。
与 B 树和 B+ 树相比,哈希作为底层的数据结构的表能够以 O(1) 的速度处理单个数据行的增删改查,但是面对范围查询或者排序时就会导致全表扫描的结果,而 B 树和 B+ 树虽然在单数据行的增删查改上需要 O(log n) 的时间,但是它会将索引列相近的数据按顺序存储,所以能够避免全表扫描。
- 数据加载
既然使用哈希无法应对我们常见的 SQL 中排序和范围查询等操作,而 B 树和 B 树和 B+ 树都可以相对高效地执行这些查询,那么为什么我们不选择 B 树呢?这个原因其实非常简单 —— 计算机在读写文件时会以页(page)为单位将数据加载到内存中。页的大小可能会根据操作系统的不同而发生变化,不过在大多数的操作系统中,页的大小都是 4KB,你可以通过如下的命令获取操作系统上的页大小:
插入个问题:如果我们的在B+树中去查找4-9之间的数,需要几次磁盘I/O呢?
如果4-9在同一页中,除了从根节点到对应页的过程,就一次,不在同一页中,多次看页数。访问一个page,就意味着需要读一次磁盘,然后在内存里缓存成一个page,下一次再访问这个page,就不需要读磁盘了。B+树特点就是以page为单位读写磁盘,来减少磁盘IO次数。MySQL把一个page定义为16KB,是根据经验主义,发现16KB是一个通用的最优配置。
getconf PAGE_SIZE
4096
当我们需要在数据库中查询数据时,CPU 会发现当前数据位于磁盘而不是内存中,这时就会触发 I/O 操作将数据加载到内存中进行访问,数据的加载都是以页的维度进行加载的,然而将数据从磁盘读取到内存中所需要的成本是非常大的,普通磁盘(非 SSD)加载数据需要经过队列、寻道、旋转以及传输的这些过程,大概要花费 10ms 左右的时间。
![preview](https://pic3.zhimg.com/v2-2a03b27d8d73401661dcf1bb4c8751de_r.jpg)
我们在估算 MySQL 的查询时就可以使用 10ms 这个数量级对随机 I/O 占用的时间进行估算,这里想要说的是随机 I/O 对于 MySQL 的查询性能影响会非常大,而顺序(IO)读取磁盘中的数据时速度可以达到 40MB/s,这两者的性能差距有几个数量级,由此我们也应该尽量减少随机 I/O 的次数,这样才能提高性能。
B 树与 B+ 树的最大区别就是,B 树可以在非叶结点中存储数据,但是 B+ 树的所有数据其实都存储在叶子节点中,当一个表底层的数据结构是 B 树时,假设我们需要访问所有『大于 4,并且小于 9 的数据』:
![img](https://bearbrick0.oss-cn-qingdao.aliyuncs.com/images/img/202203262011188.jpg)
如果不考虑任何优化,在上面的简单 B 树中我们需要进行 4 次磁盘的随机 I/O 才能找到所有满足条件的数据行:
- 加载根节点所在的页,发现根节点的第一个元素是 6,大于 4;
- 通过根节点的指针加载左子节点所在的页,遍历页面中的数据,找到 5;
- 重新加载根节点所在的页,发现根节点不包含第二个元素;
- 通过根节点的指针加载右子节点所在的页,遍历页面中的数据,找到 7 和 8;
当然我们可以通过各种方式来对上述的过程进行优化,不过 B 树能做的优化 B+ 树基本都可以,所以我们不需要考虑优化 B 树而带来的收益,直接来看看什么样的优化 B+ 树可以做,而 B 树不行。
由于所有的节点都可能包含目标数据,我们总是要从根节点向下遍历子树查找满足条件的数据行,这个特点带来了大量的随机 I/O,也是 B 树最大的性能问题。
B+ 树中就不存在这个问题了,因为所有的数据行都存储在叶节点中,而这些叶节点可以通过『指针』依次按顺序连接,当我们在如下所示的 B+ 树遍历数据时可以直接在多个子节点之间进行跳转,这样能够节省大量的磁盘 I/O 时间,也不需要在不同层级的节点之间对数据进行拼接和排序;通过一个 B+ 树最左侧的叶子节点,我们可以像链表一样遍历整个树中的全部数据,我们也可以引入双向链表保证倒序遍历时的性能。
![preview](https://bearbrick0.oss-cn-qingdao.aliyuncs.com/images/img/202203262011358.jpg)
有些读者可能会认为使用 B+ 树这种数据结构会增加树的高度从而增加整体的耗时,然而高度为 3 的 B+ 树就能够存储千万级别的数据,实践中 B+ 树的高度最多也就 4 或者 5,所以这并不是影响性能的根本问题。
- 总结
我们在这里重新回顾一下 MySQL 默认的存储引擎选择 B+ 树而不是哈希或者 B 树的原因:
- 哈希虽然能够提供 O(1) 的单数据行操作性能,但是对于范围查询和排序却无法很好地支持,最终导致全表扫描;
- B 树能够在非叶节点中存储数据,但是这也导致在查询连续数据时可能会带来更多的随机 I/O,而 B+ 树的所有叶节点可以通过指针相互连接,能够减少顺序遍历时产生的额外随机 I/O,是一个顺序 I/O;
如果想要追求各方面的极致性能也不是没有可能,只是会带来更高的复杂度,我们可以为一张表同时建 B+ 树和哈希构成的存储结构,这样不同类型的查询就可以选择相对更快的数据结构,但是会导致更新和删除时需要操作多份数据。
从今天的角度来看,B+ 树可能不是 InnoDB 的最优选择,但是它一定是能够满足当时设计场景的需要,从 B+ 树作为数据库底层的存储结构到今天已经过了几十年的时间,我们不得不说优秀的工程设计确实有足够的生命力。而我们作为工程师,在选择数据库时也应该非常清楚地知道不同数据库适合的场景,因为软件工程中没有银弹。
本文来自博客园,作者:{BearBrick0},转载请注明原文链接:{https://www.cnblogs.com/bearbrick0}