阅读全文

⭐⭐⭐ Spring Boot 项目实战	⭐⭐⭐ Spring Cloud 项目实战
《Dubbo 实现原理与源码解析 —— 精品合集》	《Netty 实现原理与源码解析 —— 精品合集》
《Spring 实现原理与源码解析 —— 精品合集》	《MyBatis 实现原理与源码解析 —— 精品合集》
《Spring MVC 实现原理与源码解析 —— 精品合集》	《数据库实体设计合集》
《Spring Boot 实现原理与源码解析 —— 精品合集》	《Java 面试题 + Java 学习指南》

摘要: 原创出处 楼仔 「楼仔」欢迎转载，保留摘要，谢谢！

• 01 理论知识
  • B+ 树
  • 页存储
  • 行存储
• 02 叶子节点计算
  • 3 层 B+ 树最大数据量
  • 叶子节点总数 x ^ 2 计算
• 03 总记录数计算
  • 溢出页
  • 最少总记录数
  • 最多总记录数
• 04 实际场景

---

🙂🙂🙂关注微信公众号：【芋道源码】有福利：

1. RocketMQ / MyCAT / Sharding-JDBC 所有源码分析文章列表
2. RocketMQ / MyCAT / Sharding-JDBC 中文注释源码 GitHub 地址
3. 您对于源码的疑问每条留言都将得到认真回复。甚至不知道如何读源码也可以请教噢。
4. 新的源码解析文章实时收到通知。每周更新一篇左右。
5. 认真的源码交流微信群。

---

关于单表能存多少数据，阿里 JAVA 开发手册提出，建议最大 2000 万。

然后也看过一篇文章，可以往单表塞 1 亿。

当然，以上其实都有一些理论支撑，但是都不全面，也没有结合具体实际的场景。

这篇文章，结合之前学习的知识，进行一个整体汇总，并贴合实际场景展开。

不 BB，上文章目录。

01 理论知识

B+ 树

MySQL 的底层结构用 B+ 树存储，这个估计地球人都知道。

为了便于后续讲解，先普及几个概念：

• 对于非聚集索引，B+ 树的叶子节点和非叶子节点存储的都是索引指针；
• 对于聚集索引，B+ 树的非叶子节点存储的是索引指针，叶子节点存储的是数据，顺序排列；
• InnoDB 中的 B+ 树的高度一般会保持在 3 层以内，我们就以 3 层来定。

下图是聚集索引，3 层 B+ 树的结构：

虚线部分，可以找到对应页码的数据，这里很基础，不去过多解读。

页存储

B+ 树节点的存储结构是 “页”，一页的大小 16 KB。

下面是页结构示意图：

再看看对页结构的解读：

名称	空间	含义和作用等
File Header	38字节	文件头：包括校验页号、前后节点的两个指针、页类型、表空间等。
Page Header	56字节	页头：用来记录页的状态信息，包括页目录的槽数、空闲空间的地址等。
Infimum & supremum	26字节	用来限定当前页记录的边界值，包含一个最小值和一个最大值。
User Records	不固定	用户记录，我们插入的数据就存储在这里。
Free Space	不固定	空闲空间，用户记录增加的时候从这里取空间。
Page Directort	不固定	页目录，用来存储页当中用户数据的位置信息。
File Trailer	8字节	文件结尾信息，校验页面完整性。

那一页能留多少存储空间呢？

除了 User Records 和 Free Space 以外所占用的存储是 38 + 56 + 26 + 8 = 128。

当新记录插入到 InnoDB 聚集索引中时，InnoDB 会尝试留出 1/16 的页面空闲以供将来插入和更新索引记录，所以就只剩下 15/16。

可存储空间 = 15/16 * 1024 - 128 = 15232 字节。

行存储

MySQL 的数据是行存储，MySQL 5.6 默认行格式为 COMPACT(紧凑)，5.7 及以后的默认行为 DYNAMIC(动态)。

下面是行结构示意图：

再看看对行结构的解读：

名称	空间	含义和作用等
行记录头信息	5字节	包含标志位、数据类型等。
可变长度字段列表	不固定	保存可变长度的字段占用字节数，比如 varchar、text、blob等。
null值列表	不固定	存储可以为 null 的字段，每个可为 null 的字段在这里占用一个 bit。
事务ID和指针字段	6+7字节	包含了一个6字节的事务ID和一个7字节的回滚指针。
实际数据	不固定	真实存储数据。

02 叶子节点计算

3 层 B+ 树最大数据量

前面说了，我们的 B+ 树是 3 层，第一层就一个根节点，能存放 X 个指针。

第二层的每个节点，也能存放 X 个指针，指向第三层 X 个节点。

第三层的每个节点，存放 Y 个数据。

3 层 B+ 树最大数据量 = x ^ 2 * y。

叶子节点总数 x ^ 2 计算

我们先看一页能存储多少个指针索引。

每一条索引记录当中都包含了当前索引的值 、一个 6 字节的指针信息 、一个 5 字节的行标头，用来指向下一层数据页的指针。

索引记录当中的指针占用空间我没在官方文档里找到，这个 6 字节是我参考其他博文，他们说源码里写的是 6 字节。

假设我们的主键 id 为 bigint 型，也就是 8 字节。

索引指针大小：8 + 6 + 5 = 19 字节。

前面已经算出，每页可存储空间 15232 字节。

单页可存储索引指针：15232 / 19 ≈ 801 条。

那算上页目录的话，按每个槽平均 6 条数据计算的话，至少有 801 / 6 ≈ 134 个槽，需要占用 268 字节的空间。

把存数据的空间分一点给槽的话，我算出来大约可以存 787 条索引数据。

单页数据存储索引指针：

• 最终单页可存储 bigint 型索引指针：（15232 - 268）/ 19 ≈ 787 条；
• 最终单页可存储 int 型索引指针 993 条。

叶子节点总数：

• 主键为 bigint 的表可以存放 787 ^ 2 = 619369 个叶子节点；
• 主键为 int 的表可以存放 993 ^ 2 = 986049 个叶子节点。

说明：以上的数据计算，仅供参考，因为有的文章说，在主键为 bigint 的情况下，可存放 160 万叶子节点，整整多出 65 万。

03 总记录数计算

溢出页

前面提到过，MySQL 行存储格式包括 COMPACT 和 DYNAMIC，我们这里只看 DYNAMIC。

DYNAMIC 怎么理解？

在一行数据中，当某列太长时，叶子节点无需将该数据直接存储 ，而是存储指向该数据的指针，真实数据全部存储在溢出页。

使用 DYNAMIC 格式，较短的列会尽可能保留在 B+ 树节点中，从而最大限度地减少给定行所需的溢出页数。

那 COMPACT 呢？

COMPACT 行格式则是将前 768 个字节和 20 字节的指针存储在 B+ 树节点的记录中，其余部分存储在溢出页上。

这里我们只讨论 DYNAMIC 情况。

最少总记录数

前面我们提到，最大行长度略小于数据库页面的一半，之所以是略小于一半，是由于每个页面还留了点空间给页格式的其他内容，所以我们可以认为每个页面最少能放两条数据，每条数据略小于 8 KB。

如果某行的数据长度超过这个值，那 InnoDB 肯定会分一些数据到 溢出页当中去了，所以我们不考虑。

那每条数据 8 KB 的话，每个叶子节点就只能存放 2 条数据。

在主键为 int 的情况下，最少总记录数：2 × 986049 ≈ 124 万。

最多总记录数

假设我们的表是这样的：

CREATE TABLE`course_schedule` (
`id`intNOTNULL,
`teacher_id`intNOTNULL,
`course_id`intNOTNULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDBDEFAULTCHARSET=utf8;

先来分析一下这张表的行数据：无 null 值列表，无可变长字段列表，需要算上事务 ID 和指针字段，需要算上行记录头。

每行数据占用空间：4 + 4 + 4 + 6 + 7 + 5 = 30。

每个叶子节点存放：15232 ÷ 30 ≈ 507。

算上页目录槽位所占空间，每个叶子节点可存放 502 条。

在主键为 int 的情况下，最多总记录数：502 × 986049 ≈ 5 亿。

04 实际场景

上面的场景是两个极端， 我们看一个具体的示例。

CREATE TABLE`blog` (
`id`bigintunsignedNOTNULL AUTO_INCREMENT COMMENT'博客id',
`author_id`bigintunsignedNOTNULLCOMMENT'作者id',
`title`varchar(50) CHARACTERSET utf8mb4 NOTNULLCOMMENT'标题',
`description`varchar(250) CHARACTERSET utf8mb4 NOTNULLCOMMENT'描述',
`school_code`bigintunsignedDEFAULTNULLCOMMENT'院校代码',
`cover_image`char(32) DEFAULTNULLCOMMENT'封面图',
`create_time` datetime NOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',
`release_time` datetime DEFAULTNULLCOMMENT'首次发表时间',
`modified_time` datetime NOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'修改时间',
`status`tinyintunsignedNOTNULLCOMMENT'发表状态',
`is_delete`tinyintunsignedNOTNULLDEFAULT0,
  PRIMARY KEY (`id`),
KEY`author_id` (`author_id`),
KEY`school_code` (`school_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1DEFAULTCHARSET=utf8 COLLATE=utf8_general_mysql500_ci ROW_FORMAT=DYNAMIC;

分析一下这张表的行记录：

• 行记录头信息：肯定得有，占用 5 字节；
• 可变长度字段列表：表中 title 占用 1 字节，description 占用 2 字节，共 3 字节；
• null 值列表：表中仅 school_code、cover_image、release_time 3 个字段可为 null，故仅占用 1 字节；
• 事务 ID 和指针字段：两个都得有，占用 13 字节。

再看看字段内容信息：

• id、author_id、school_code 均为 bigint 型，各占用 8 字节，共 24 字节；
• create_time、release_time、modified_time 均为 datetime 类型，各占 8 字节，共 24 字节；
• status、is_delete 为 tinyint 类型，各占用 1 字节，共 2 字节；
• cover_image 为char(32)，字符编码为表默认值 utf8，占用 32 字节；
• title、description 分别为 varchar(50)、varchar(250)，这两个应该都不会产生溢出页（不太确定），字符编码均为 utf8mb4，实际生产中 70% 以上都是存的中文( 3 字节)，25% 为英文(1 字节)，还有 5% 为 4 字节的表情，则存满的情况下将占用 (50+250)×(0.7×3+0.25×1+0.05×4) = 765 字节。

统计上面的所有分析，共占用 869 字节，则每个叶子节点可以存放 15232 ÷ 869 ≈ 17 条，算上页目录，仍然能放 17 条。

主键为 bigint，最大总记录数：17× 619369=10,529,273 ≈ 1053 万。