mysql索引为什么使用B+树

关注

原创 2022-10-19 17:41:02 824 阅读 AI 写同款· GEO 优化 ›

B+树的叶子节点到底是双向链表还是单向链表刚使用mysql的前几年，没有深入地了解mysql的内部实现，关于B+树的叶子节点的了解仅仅是：B+树的叶子节点按照自增id的大小自左至右排序，新增的元素排在右边。innodb的B+树的叶子节点是页(Page)，页之间是双向链表，页内部有一个单向链表，这个单向链表是用来存储记录的。后来突然某一天在网上看到一篇文章，说B+树的叶子节点是双向链表，这让我很是疑惑，因为我一直以为B+树的叶子节点是单向链表，这篇文章是不是写错了呢？Innodb的页(Page)是存储数据的最小单位，页的大小默认为16KB。阅读详情

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

mysql慢查询如何优化

`优化慢查询方法有很多，比如分库分表、提升系统硬件配置、去除不需要用的select字段。但是最常用也是最有用的方法是添加索引。

一、合理得添加索引后能给sql带来多大的速度提升？

答案是几千几万甚至几百万倍的提升。所以为什么添加索引能给慢sql带来如此巨大的速度提升。首先，索引是什么。索引是一组排好序的数据结构，用于快速查找。不用索引的时候，查询数据库中的信息时，会先将这些信息从硬盘移动到磁盘上按页读取，Innodb读取数据时一页16KB，如果一条数据是100字节，相当于一页能存储16*1024/100=160条数据。如果这张表中有10万条数据，相当于要和磁盘做625次 I/O交互。而I/O交互是很慢的。磁盘毫秒级别的时间单位比内存慢了上千倍。由上面的例子可以看到，不用索引的话，查询的速度，不仅仅和表数据量大小成反比，还和每一条数据大小成反比。速度相当得慢。

二、mysql用了什么数据结构存储索引

上面说了不用索引的话是按页一页一页去找，时间复杂度n，速度是很慢的，所以要使用索引以加快其查询速度。索引可以理解为书签，使用了索引就不用一页页去翻了。但是书签亦有优劣，所以，索引用了哪种数据结构实现了快速查找的目的。

1.为什么不用二叉树

二叉树的优势是显而易见的，二分查找时间复杂度是logn。但它存在退化问题，每次加入的数据都比上一条小，它会退化成线性结构。不巧的是，mysql又是推荐使用主键自增的，而主键自增在实际项目中也很常用，也就是说按常用的主键自增，二叉树结构的索引必然会退化，退化后时间复杂度是logn。
在这里插入图片描述

2.为什么不用红黑树

红黑树可以理解为二叉树的一个升级，红黑树的自旋操作解决了二叉树的退化问题。使得查找复杂度稳定在logn。但是二叉树一个节点只能有两个子节点。8层二叉树也仅能存255条数据。树度过高使得依然要做几十次磁盘I/O。而且一个节点只能存一条数据的设计，使得页资源巨大得浪费。因为一条数据最多也就几百byte，而一页有16KB。

在这里插入图片描述

3.为什么不用B树

B树可以理解为红黑树的一个升级。它舍弃了一个节点只能存一条数据的设定，使用了一个节点能存储多条数据。一个节点具体存储多少条数据是页大小除以一条数据大小。即，一个存储了多条数据的节点，占满整个页，不再造成页资源浪费。同时，B树也舍弃了一个节点只能有两个子节点的设定，使得它的树度远低于红黑树。那为什么最后也没有使用B树呢，主要是mysql中范围查找是非常常见的，而B树的范围查找能力并不快。B树做范围查找的话首先要定位到上界节点或下界节点，然后再做遍历，而每次遍历都要从头结点往下找，速度较慢。其次，B树在设计时数据和索引是捆绑在一起的，使得它的树度和每条数据大小成反比，这种设计使得搜索的节点在头节点会很快查询到，在叶子节点的话相对会慢一点。也就是它的查询速度不稳定。
在这里插入图片描述

4.为什么不用散列表

显而易见无序的散列表压根没法做范围查询。

5.为什么用B+树

B+树可以理解为在B树上的一个升级。B+树解决了B树范围查询过慢的问题。B+树的叶子节点由大到小排列形成了一个双向有序的链表，这条链表包含了所有索引，也就是说，除了叶子节点，其余节点都是冗余节点，这些冗余节点的作用就是为了生成由叶子节点组成的双向有序链表。有了这条链表，范围查询将非常方便。仅需定位到上界或下界节点在链表中的位置，然后向前或向后取值即可，直到超出范围，停止。
在这里插入图片描述

三、B+树和B树技术选型

由于B+树范围查找远快于B树，所以经常用到范围查询的关系型数据库更适合用B+树。那么如果是非关系型数据库呢，一般非关系型数据库不太注重范围查找。所以，B树和B+树单点查询速度，哪个更快，是接下来要阐述的问题。
B+树由于叶子节点已经包含了所有主键及主键所对应的数据，所以除了最底层的叶子节点，其他几层的节点是不包含数据的，只有主键。而一个不包含数据的节点能存多少主键呢。主键假设int型则为8字节，加上6字节的指针。而一个节点即一页的大小是16KB。16*1024/14=1170。总共能存1170个主键。第一层就一个节点能存1170个主键。第二层能有1170个节点，每个节点存1170个主键。也就是第二层能存1170乘以1170个主键。第三层如果是叶子节点包含了数据，假设数据1KB，等于一个节点包含16条数据。也就是第三层能包含1170乘以1170乘以16条数据。也就是说，三层B+树能存两千多万条数据。而每个节点都存数据的B树显然会比B+树的树度更高。但是也正是因为B树每个节点都存了数据，使得B树最快查询速度能达到O（1），当然仅限于查询的数据正好在头节点。如果是尾节点，速度是0（logn）。如果是倒数第二层，则是0（logn-1）。而B+树则是稳定的0（logn）。
所以在树度相同的情况下，B+树的查询速度和B树最慢情况下的查询速度持平，拿什么比。但是B+树非叶子节点不存数据的设定又使得在相同数据量下，B+树的高度又必然低于B树。所以两者的单点查询速度只能说不分伯仲，各有千秋。
但是，维护一条B+树更容易还是B树更容易呢。明显是B树更容易。B+树不但比B树多了很多冗余节点，还要维护最底端的双向链表。所以，对于不怎么注重条件查询的非关系型数据库，在B树和B+树单点查询速度差不多的情况下，自然是选择更易维护的B树。