如何估算 InnDB 最大记录总数

一七令
148 阅读3分钟

一 前言

有不少情况下需要通过一些已有的条件来计算出一张 InnoDB 表能够存储多少记录数。

特别是面试官问你的时候...

二 知识预备

在讲解具体的计算方法前，需要先了解一些知识点。

假如有一张学生表 students：

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `gender` enum('female','male') DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

在 datadir 配置目录下有使用独立表空间对应的 students.ibd 文件，里面就存放着这张表的数据记录。

这些记录被分为很多相同大小的数据页，默认每页大小 16 k。大致如下图所示：

在这里插入图片描述

其中每个数据页大致结构为下图所示：

在这里插入图片描述

  1. 页头中页号标识数据页（地址偏移量）；前后指针将数据页关联起来。
  2. 页尾中记录校验码，用于检查数据页是否完整。

为了提示查找效率，引入了索引。B+tree 作为 Innodb 的索引实现数据结构，以主键索引为例数据页的层次结构如下图所示：

在这里插入图片描述 从上图的结构里可以看出 B+树 的最末级叶子结点里放了实际的数据记录。而非叶子结点里则放了用来加速查询的索引数据。

三 计算总量

针对 B+tree 的结构作以下的假设：

  • 非叶子结点内指向其他内存页的指针数量为x
  • 叶子节点内能容纳的数据记录数量为y
  • B+树的层数为z

在这里插入图片描述

3.1 计算 X

非叶子结点主要存储主键和页号。

上面创建的表中主键使用 int(4 字节)，页号（FIL_PAGE_OFFSET）4 字节。则非叶子结点中一条数据大约是 8 字节。

一个数据页页头页尾大概 128 字节，加上页目录粗略认为占用 1Kb。以 MySQL 默认数据页大小 16K 为例，则剩下的 15k 则大概认为是用于存放数据的。

则一个非叶子结点能够存储的数据页指向：15K / 8 Byte = 1920。

3.2 计算 Y

叶子结点的页头、页尾、页目录等和非叶子结点一样，也是认为占用 1Kb。则数据行大小影响每个数据页能存储的记录数。

假设一条记录 0.5k，则能存储的数据记录数：15K / 0.5K = 30 。

3.3 计算总数

B+树数据记录数总量等于 (x ^ (z-1)) * y , 已知x = 1920，y = 3。

假如层高 Z:

  1. Z = 2： 则能存储的记录总数 (1920 ^ (2-1)) * 30 = 57600 行。
  2. Z = 3：则能存储的记录总数 (1920 ^ (3-1)) * 30 = 110592000 行。

所以一般情况下 B+tree 的层高2～3层就能存储很多的数据了。

四 总结

  • B+ 树叶子和非叶子结点的数据页都是16k，且数据结构一致，区别在于叶子节点放的是真实的行数据，而非叶子结点放的是主键和下一个页的地址。
  • B+树一般有两到三层，由于其高扇出，三层就能支持千万以上的数据，且一次查询最多1~3次磁盘IO。
  • 数据记录大小决定每个叶子结点能最大存储行数。
avatar
文章
阅读
粉丝