InnoDB中一颗B+书可以存放多少行数据

最新推荐文章于 2024-09-15 20:24:46 发布

叫我杨哥5240

最新推荐文章于 2024-09-15 20:24:46 发布

阅读量281

点赞数

文章标签： mysql 索引数据结构

本文链接：https://blog.csdn.net/qq_43207114/article/details/115753774

版权

本文深入探讨了InnoDB存储引擎中B+树的结构及其在数据存储和索引查找中的应用。通过计算，得出一个高度为2的B+树可存放约18720条数据，而高度为3的B+树可存约2190万条。主键索引B+树的高度可通过根页的pagelevel确定，通常为1-3层。通过实例分析，验证了不同数据量下B+树高度与查询效率的关系。

摘要由CSDN通过智能技术生成

面试官：InnoDB中一颗B+书可以存放多少行数据

InnoDB一颗B+树可以存放多少行数据？ 这个问题的简单回答是：约2千万。为什么是这么多呢？因为这是可以算出来的，要搞清楚这个问题，我们先从InnoDB索引数据结构、数据组织方式说起。

我们都知道计算机在存储数据的时候，有最小存储单元，这就好比我们进行现金的流通最小单位是一毛。在计算机中磁盘中存储数据最小单元是扇区，一个扇区的大小是512字节，而文件系统（例如XFS/EXT4）他的最小单元是块，一个块的大小是4k，而对于我们InnoDB的存储引擎也有自己最小的存储单元–页，一个页的大小是16k

各存储层面的最小单元

数据表中的数据都是存储在页中的，所以一个页中能存储多少行数据呢？假设一行数据的大小是1k，那么一页可以存储16行这样的数据。

那么我们都知道数据库的底层使用的是B+树，那么B+树包含非叶子节点与叶子节点，对于数据库动辄就是存储成千上万的数据，那么他又是如何对着庞大的数据量进行查找的呢？

查找数据的流程

这里为了方便大家理解就只在一页存放了3条记录，实际可以放很多，除了存放数据的页还有存放剪纸+指针的页，如图Pagenumber = 3的页，这样的页由N个键值+指针组成。当然它也是排好序的，这样的结构我们称为索引组织表，那么它是如何查找数据的呢？

id我们设为主键，那么我们首先需要找到根页，那么我们如何二得治它的根页在哪呢？其实每张表的根页在表空间中是固定的，即page number = 3的页（待会我们再来进一步证明），找到根页之后会通过二分查找法，定位到指定主键的指针是指向哪一页，那么进一步去对应的page number = x的页中去查找

小结：
  1、InnoDB的存储引擎的中最小存储单元是页，页可以用于存放数据也可以存放键值+指针，在B+树中叶子节点存放数据，非叶子节点存放键值+指针。
  2、索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页超找到需要的数据。

那么继续回到开始时的问题，通常一颗B+树可以存放多少行数据？

我们先假设B+树高为2，即存在一个根节点和若干个叶子节点，那么这颗B+树存放总记录数为：根结点指针数*单个叶子节点记录行数。

前面我们假设单个叶子节点的记录大小是一行1k，那么一页的记录数就是16k/1k = 16。

那么现在我们需要计算出非叶子节点能存放多少指针，其实也非常好算，我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码的设置为6字节，这样的话一共就是14字节，我们一页中能存放多少这样的单元，其实就代表有多少指针，即16384（16k）/14 = 1170.那么可以算出一颗高度为2的B+树，能存放1170*16 = 18720条这样的数据记录。根据同样的原理现在我们可以算出一个高度为3的B+树可以存放：1170*1170*16 = 21902400条这样的记录。所以一般InnoDB中B+树的高度也就是1-3层就能满足千万级数据的存储。在查找数据时一次页的查找就代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

怎么得到InnoDB主键索引B+树的高度？

上面我们推断得出B+树的高度一般为1~3层，下面我们从另外一个侧面证明这个结论。在InnoDB的表空间文件中，约定page number为3的代表主键索引的根页，而在根页偏移量为4的地方存放了该B+树的page level。如果page level为1，树高为2，page level为2，则树高为3，所以B+树的高度为page level + 1；下面将在实际环境中去寻找page level

在实际操作前我们能可以通过InnoDB元数据表确认主键索引根页的page number为3，也可以参考《InnoDB存储引擎》

SELECT
b.name, a.name, index_id, type, a.space, a.PAGE_NO
FROM
information_schema.INNODB_SYS_INDEXES a,
information_schema.INNODB_SYS_TABLES b
WHERE
a.table_id = b.table_id AND a.space <> 0;

执行结果

元数据查询执行结果
可以看出dbt3下的customer表、lineitem表主键索引根页的page number均为3，而其他的耳机索引page number为4.关于二级索引与主键索引的区别请参考相关书籍。

因为主键索引B+树的根页在整个表空间文件中的第三个页开始，所以可以算出它在文件中的偏移量：16384*3=49152.

另外根据《InnoDB存储引擎》中描述在根页的64偏移位置前2个字节中，保存了page level的值，因此我们小样的page level的值，因此我们想要page level的值在整个文件中的偏移量为：16384*3+64 = 49152+64 = 49216，前两个字节中。

接下来我们用hexdump工具，查看表空间文件指定偏移量上的数据：
在这里插入图片描述
linetem表的page level为2，B+树高度为page level+1 = 3；
region表的page level为0，B+树的高度为page level + 1 = 1；

那么这三张表的数据量如下：

在这里插入图片描述

  总结：
      Lineitem表的数据行数为600多万，B+树高度为3，customer表数据行数只有15万，B+树的高度为3，可以看出景观数据量差异较大，这两个表树的高度都是3，换句话说这两个表通过索引查询效率斌没有太大差异，因为都只需要做3次IO。那么如果有一张表行数是一千万，那么他的B+树的高度依旧时3，查询效率任然不会相差太大。
      region表只有5行数据，当然他的B+树高度为1.