MYSQL 单表可以放多少数据是怎么计算出来的

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MYSQL 单表可以放多少数据是怎么计算出来的

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` int NOT NULL,
  `teacher_id` int NOT NULL,
  `course_id` int NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

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

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

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

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

04 实际场景

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

CREATE TABLE `blog` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '博客id',
  `author_id` bigint unsigned NOT NULL COMMENT '作者id',
  `title` varchar(50) CHARACTER SET utf8mb4 NOT NULL COMMENT '标题',
  `description` varchar(250) CHARACTER SET utf8mb4 NOT NULL COMMENT '描述',
  `school_code` bigint unsigned DEFAULT NULL COMMENT '院校代码',
  `cover_image` char(32) DEFAULT NULL COMMENT '封面图',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `release_time` datetime DEFAULT NULL COMMENT '首次发表时间',
  `modified_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `status` tinyint unsigned NOT NULL COMMENT '发表状态',
  `is_delete` tinyint unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `author_id` (`author_id`),
  KEY `school_code` (`school_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=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 万。

05 后记

对于上述知识,其实主要是整理小白 Debug 和掘金阿杆的两篇博文。

文章的很多图片是选取「小白 Debug」的博文,数值计算和案例,是选取的「掘金阿杆」的博文。

重新整理后,整体可读性更强,数字依据更考究

该文的理论知识,以及整体计算的逻辑和思想,是可以学习接借鉴的,但是对于里面很多数值的精确度,其实我是有些质疑的,比如主键为 int 的表可以 986049 个叶子节点,个人感觉还是少了。

无论如何,这两篇文章应该是我最近看到的,关于 Mysql 存放数据大小计算的最好的两篇,不建议照搬里面的数据,但本文的理论知识、计算思路是完全可以学习和借鉴的。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
9天前
|
安全 关系型数据库 MySQL
如何将数据从MySQL同步到其他系统
【10月更文挑战第17天】如何将数据从MySQL同步到其他系统
66 0
|
15天前
|
SQL 前端开发 关系型数据库
全表数据核对 ,行数据核对,列数据核对,Mysql 8.0 实例(sample database classicmodels _No.3 )
全表数据核对 ,行数据核对,列数据核对,Mysql 8.0 实例(sample database classicmodels _No.3 )
32 0
全表数据核对 ,行数据核对,列数据核对,Mysql 8.0 实例(sample database classicmodels _No.3 )
|
20天前
|
关系型数据库 MySQL 数据库
mysql 里创建表并插入数据
【10月更文挑战第5天】
93 1
|
5天前
|
SQL Java 关系型数据库
java连接mysql查询数据(基础版,无框架)
【10月更文挑战第12天】该示例展示了如何使用Java通过JDBC连接MySQL数据库并查询数据。首先在项目中引入`mysql-connector-java`依赖,然后通过`JdbcUtil`类中的`main`方法实现数据库连接、执行SQL查询及结果处理,最后关闭相关资源。
|
2天前
|
SQL 关系型数据库 MySQL
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
10 1
|
3天前
|
SQL 关系型数据库 MySQL
mysql数据误删后的数据回滚
【11月更文挑战第1天】本文介绍了四种恢复误删数据的方法:1. 使用事务回滚,通过 `pymysql` 库在 Python 中实现;2. 使用备份恢复,通过 `mysqldump` 命令备份和恢复数据;3. 使用二进制日志恢复,通过 `mysqlbinlog` 工具恢复特定位置的事件;4. 使用延迟复制从副本恢复,通过停止和重启从库复制来恢复数据。每种方法都有详细的步骤和示例代码。
|
15天前
|
存储 关系型数据库 MySQL
面试官:MySQL一次到底插入多少条数据合适啊?
本文探讨了数据库插入操作的基础知识、批量插入的优势与挑战,以及如何确定合适的插入数据量。通过面试对话的形式,详细解析了单条插入与批量插入的区别,磁盘I/O、内存使用、事务大小和锁策略等关键因素。最后,结合MyBatis框架,提供了实际应用中的批量插入策略和优化建议。希望读者不仅能掌握技术细节,还能理解背后的原理,从而更好地优化数据库性能。
|
18天前
|
存储 SQL 关系型数据库
Mysql学习笔记(二):数据库命令行代码总结
这篇文章是关于MySQL数据库命令行操作的总结,包括登录、退出、查看时间与版本、数据库和数据表的基本操作(如创建、删除、查看)、数据的增删改查等。它还涉及了如何通过SQL语句进行条件查询、模糊查询、范围查询和限制查询,以及如何进行表结构的修改。这些内容对于初学者来说非常实用,是学习MySQL数据库管理的基础。
78 6
|
16天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
50 3
Mysql(4)—数据库索引
|
1天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
18 2