likes
comments
collection
share

MySQL单表最大行数估算方法-500W的前世今生

作者站长头像
站长
· 阅读数 16

前言

坊间传言，MySQL单表不要超过500w条记录。但单表行数并非MySQL限制，是基于业务实操的建议值。但业务表千差万别，直接拿一个固定数值来套用未免太过武断。所以本文尝试从理论角度推断一下MySQL单表行数的估算方法。

下图截取自阿里巴巴Java开发规范。

MySQL单表最大行数估算方法-500W的前世今生

结论

日常使用，可采用如下公式计算：

Total=80003(a+4)2(a+b+30)Total =\frac {8000^3}{(a+4)^2(a+b+30)}Total=(a+4)2(a+b+30)80003​

a表示索引字段占用空间（byte） b表示非索引字段占用空间（byte）

基础概念说明

对分析单表行数直接影响的相关概念会在此处进行说明。由于版本不同会有差异，本文以MySQL5.7版本为例说明。如果对基础概念已经了解，或者不关注推导过程，可直接查看计算公式。

MySQL执行流程

MySQL单表最大行数估算方法-500W的前世今生

MySQL的架构共分为两层：Server 层和存储引擎层。Server 层负责建立连接、分析和执行 SQL；存储引擎层负责数据的存储和提取。

存储引擎

MySQL服务器把数据的存储和提取操作都封装到了一个叫存储引擎的模块里。我们知道表是由一行一行的记录组成的，但这只是一个逻辑上的概念，物理上如何表示记录，怎么从表中读取数据，怎么把数据写入具体的物理存储器上，这都是存储引擎负责的事情。为了实现不同的功能，MySQL提供了各式各样的存储引擎，不同存储引擎管理的表具体的存储结构可能不同，采用的存取算法也可能不同。MySQL支持的存储引擎有很多，最常用的就是InnoDB(默认)和MyISAM。

  • InnoDB 具备外键支持功能的事务存储引擎

  • MyISAM 主要的非事务处理存储引擎

编码规范

相信很多对字符集编码不够了解的程序员经常会出现很多乱码问题，抱有很多疑惑。希望通过如下基础概念的梳理能助你解惑。

  • 字节 这个是最基本的概念了，字节是计算存储容量的一种计量单位。我们知道计算机只能识别1和0组成的二进制位。一个数就是1位（bit），为了方便计算，我们规定8位就是一个字节。 例如 ：00001111 这个8位二进制数就占了一个字节的存储容量。

  • 字符 字符和字节不太一样，任何一个文字或符号都是一个字符，但所占字节不一定，不同的编码导致一个字符所占的字节不同。

  • 编码规范 随着时代的发展，程序员们希望在计算机中显示字符，但计算机只能识别0和1的二进制数。为了显示字符，国际组织就制定了编码规范，希望使用不同的二进制数来表示代表不同的字符，这样电脑就可以根据二进制数来显示其对应的字符。

    字库表、编码字符集（字符集）、字符编码（编码方式）是编码规范里的3个子概念。所谓字符集其实是一套编码规范中的子概念，我们通常就称呼其为XX编码，XX字符集。由于字符集、编码方式很多是同名的，日常使用无需刻意区分概念的精准，不影响互相沟通、理解即可。

    例如：Unicode是一种字符集，UTF-8就是一种编码方式

常见字符集

  • ASCII字符集 最通用的单字节编码系统。7位（bits）表示一个字符，共128字符，字符值从0到127。

  • GB2312 双字节编码系统。国标字符集，收录6763 个常用的汉字和字符。

  • GB18030 可变字节编码系统，采用单字节、双字节和四字节三种方式对字符编码。国标字符集，共收录七万多个汉字和字符。

  • Unicode 是国际标准字符集，它将世界各种语言的每个字符定义一个唯一的编码，以满足跨语言、跨平台的文本信息转换。Unicode 字符集的编码范围是 0x0000 - 0x10FFFF，因此需要 1 到 3 个字节来表示。它只规定了每个符号的二进制值，但是符号具体如何存储它并没有规定。Unicode 出现了多种存储方式，常见的有 UTF-8、UTF-16、UTF-32，它们分别用不同的二进制格式来表示 Unicode 字符。

MySQL中的字符集

UTF-8是一种变长字符编码，被定义为将码点编码为 1 至 4 个字节，具体取决于码点数值中有效二进制位的数量。但是我们常用的一些字符使用1～3个字节就可以表示了。而在MySQL中字符集表示一个字符所用最大字节长度在某些方面会影响系统的存储和性能，所以MySQL定义了两个细分概念：

  • utf8mb3 MySQL中用utf8指代。阉割过的utf8字符集，只使用1～3个字节表示字符。无法表示emoji表情。

  • utf8mb4 正宗的utf8字符集，使用1～4个字节表示字符。

InnoDB存储引擎介绍

数据在文件系统的存储

MySQL的数据存放在数据目录内。每个数据库都在数据目录下有一个子目录(数据库名)，子目录内主要是包含表结构（元数据）（表名.frm）、表数据(表名.ibd)。

MySQL单表最大行数估算方法-500W的前世今生

表空间文件的结构

表空间由段（segment）、区（extent）、页（page）、行（row）组成，InnoDB存储引擎的逻辑存储结构大致如下图：

MySQL单表最大行数估算方法-500W的前世今生

1、行（row）

数据库表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构。

后面我们详细介绍 InnoDB 存储引擎的行格式，也是本文重点介绍的内容。

2、页（page）

记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。

InnoDB 的数据是按「页」为单位来读写的，默认每个页的大小为 16KB。也就是说，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。

总之知道表中的记录存储在「数据页」里面就行。后面我们会详细介绍，也是本文重点介绍的内容。

3、区（extent）

我们知道 InnoDB 存储引擎是用 B+ 树来组织数据的。

B+ 树中每一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置并不是连续的，可能离得非常远，那么磁盘查询时就会有大量的随机I/O，随机 I/O 是非常慢的。

解决这个问题也很简单，就是让链表中相邻的页的物理位置也相邻，这样就可以使用顺序 I/O 了，那么在范围查询（扫描叶子节点）的时候性能就会很高。

每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。

4、段（segment）

表空间是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等。

  • 索引段：存放 B + 树的非叶子节点的区的集合；
  • 数据段：存放 B + 树的叶子节点的区的集合；
  • 回滚段：存放的是回滚数据的区的集合。

数据页

记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。

因此，InnoDB 的数据是按「页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。

默认每个页的大小为 16KB，也就是最多能保证 16KB 的连续存储空间。

页是 InnoDB 存储引擎磁盘管理的最小单元，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。

数据页包括七个部分，结构如下图：

MySQL单表最大行数估算方法-500W的前世今生

这 7 个部分的作用如下图：

MySQL单表最大行数估算方法-500W的前世今生

在 File Header 中有两个指针，分别指向上一个数据页和下一个数据页，连接起来的页相当于一个双向的链表，如下图所示：

MySQL单表最大行数估算方法-500W的前世今生

采用链表的结构是让数据页之间不需要是物理上的连续的，而是逻辑上的连续。

数据页的主要作用是存储记录，也就是数据库的数据，所以重点说一下数据页中的 User Records 是怎么组织数据的。

数据页中的记录按照「主键」顺序组成单向链表，单向链表的特点就是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索。

因此，数据页中有一个页目录，起到记录的索引作用，就像我们书那样，针对书中内容的每个章节设立了一个目录，想看某个章节的时候，可以查看目录，快速找到对应的章节的页数，而数据页中的页目录就是为了能快速找到记录。

那 InnoDB 是如何给记录创建页目录的呢？页目录与记录的关系如下图：

MySQL单表最大行数估算方法-500W的前世今生

页目录创建的过程如下：

1.  将所有的记录划分成几个组，这些记录包括最小记录和最大记录，但不包括标记为“已删除”的记录； 1.  每个记录组的最后一条记录就是组内最大的那条记录，并且最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段（上图中粉红色字段）

1.  页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录。

从图可以看到，页目录就是由多个槽组成的，槽相当于分组记录的索引。然后，因为记录是按照「主键值」从小到大排序的，所以我们通过槽查找记录时，可以使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到对应的记录，无需从最小记录开始遍历整个页中的记录链表。

Q：如果某个槽内的记录很多，然后因为记录都是单向链表串起来的，那这样在槽内查找某个记录的时间复杂度不就是 O(n) 了吗？

留给有心人去探索答案。

行（row）

行格式（row_format），就是一条记录的存储结构。

InnoDB 提供了 4 种行格式。

  • Redundant 是很古老的行格式了，MySQL 5.0 版本之前用的行格式，现在基本没人用了。
  • Compact 是一种紧凑的行格式，设计的初衷就是为了让一个数据页中可以存放更多的行记录，从 MySQL 5.1 版本之后，行格式默认设置成 Compact。
  • Dynamic 从MySQL5.7 版本之后，默认使用 Dynamic 行格式。和 Compact 差不多，主要是对溢出页处理不同。
  • Compressed 与Dynamic行格式的区别是会采用压缩算法对页面进行压缩。

这次重点介绍 Compact 行格式，因为 Dynamic 和 Compressed 这两个行格式跟 Compact 非常像。

Compact 行格式

MySQL单表最大行数估算方法-500W的前世今生

记录的额外信息

  • 变长字段长度列表 比如varchar\text\blob等变长字段的实际数据长度信息，按照字段逆序存放。

  • NULL值列表 表中的某些列可能会存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，所以 Compact 行格式把这些值为 NULL 的列存储到 NULL值列表中。如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。

  • 记录头信息 它是由固定的5个字节组成。5个字节也就是40个二进制位，不同的位代表不同的意思，如图：

    MySQL单表最大行数估算方法-500W的前世今生

记录的真实数据

  • 隐藏列 每张表会默认添加如下列信息，称为隐藏列。

    列名是否必须占用空间描述
    row_id否6字节行ID，唯一标识一条记录。如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。
    transaction_id是6字节事务ID，表示这个数据是由哪个事务生成的
    roll_pointer是7字节回滚指针，这条记录上一个版本的指针

  • 业务列 NULL值存储不占用真实数据存储空间，在NULL值列表中占用1字节。

    varchar(n) 中n是指字符长度，并非字节长度。要根据字符集来估算字节长度。

    MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息，即所有业务列+变长字段+NULL值列表）占用的字节长度加起来不能超过 65535 个字节。

Q: 大对象如何存储？

B+树

InnoDB 里的 B+ 树中的每个节点都是一个数据页，结构示意图如下：

MySQL单表最大行数估算方法-500W的前世今生

通过上图，我们看出 B+ 树的特点：

  • 只有叶子节点（最底层的节点）才存放了数据，非叶子节点（其他上层节）仅用来存放目录项作为索引。
  • 非叶子节点分为不同层次，通过分层来降低每一层的搜索量；
  • 所有节点按照索引键大小排序，构成一个双向链表，便于范围查询；

估算模型

InnoDB采用B+树存储索引与数据。由于IO操作比较耗时，为了保证数据库效率，通常认为B+数不超过3层。B+树不超过3层作为本文推论的基础，不对该结论进行论证。

假设

  • 非叶子节点内指向其他页的数量为 x
  • 叶子节点内能容纳的数据行数为 y
  • B+ 数的层数为 z

如下图中所示，Total=x(z−1)×yTotal =x^{(z-1)}\times yTotal=x(z−1)×y 也就是说总数会等于 x 的 z-1 次方 与 Y 的乘积。

MySQL单表最大行数估算方法-500W的前世今生

Total=x(z−1)×yTotal =x^{(z-1)}\times yTotal=x(z−1)×y

X（非叶子节点索引）计算方法

前面章节介绍了页的结构，索引也也不例外，都会有 File Header (38 byte)、Page Header (56 byte)、Infimum + Supermum（26 byte）、File Trailer（8byte）, 再加上页目录（不固定，一个卡槽1-2byte），占用空间128+byte。

当新记录插入到 InnoDB 聚簇索引中时，InnoDB 会尝试留出 1/16 的页面空闲以供将来插入和更新索引记录。如果按顺序（升序或降序）插入索引记录，则生成的页大约可用 15/16 的空间。如果以随机顺序插入记录，则页大约可用 1/2 到 15/16 的空间。详见(MySQL :: MySQL 5.7 Reference Manual :: 14.6.2.2 The Physical Structure of an InnoDB Index)

我们就当做它就是 1K, 那整个页的大小是 16K, 剩下 15k 用于存数据，在索引页中主要记录的是主键与页号，主键我们假设是 Bigint (8 byte), 而页号也是固定的（4Byte）, 那么索引页中的一条数据也就是 12byte。

所以 x=15*1024/12≈1280 行。

Y（叶子节点数据）计算方法

叶子节点和非叶子节点的结构是一样的，同理，能放数据的空间也是 15k。

但是叶子节点中存放的是真正的行数据，这个影响的因素就会多很多，比如，字段的类型，字段的数量。每行数据占用空间越大，页中所放的行数量就会越少。

这边我们暂时按一条行数据 1k 来算，那一页就能存下 15 条，Y = 15*1024/1024 =15。

估算结果

根据上述的公式，Total =x^(z-1) *y，已知 x=1280，y=15：

  • 假设 B+ 树是三层，那就是 z = 3， Total = （1280 ^2） *15 = 24576000 （约 2.45kw）

详细计算公式

首先对估算模型进行拆解

x=15×1024a+4x=\frac {15\times 1024}{a+4} x=a+415×1024​

y=15×1024a+b+c+d+5+13y=\frac {15\times 1024}{a+b+c+d+5+13}y=a+b+c+d+5+1315×1024​

a表示索引字段占用空间（byte） b表示非索引字段占用空间（byte） c表示变长字段长度列表占用空间（byte） d表示NULL值列表列表占用空间（byte） 页号占用4byte 记录头占用5byte 隐藏列（不包含row_id）13byte

  • 计算总公式如下
Total=(15×1024a+4)(z−1)×15×1024a+b+c+d+5+13Total ={(\frac {15\times 1024}{a+4})}^{(z-1)}\times {\frac {15\times 1024}{a+b+c+d+5+13}}Total=(a+415×1024​)(z−1)×a+b+c+d+5+1315×1024​
  • 简化计算公式如下 日常估算，其实不需要做如此精确的推算，而且很多场景也没必要，基于模糊的假设做精确的计算本质上也没意义。所以可以对公式做一定简化。
Total=150003(a+4)2(a+b+30)Total =\frac {15000^3}{(a+4)^2(a+b+30)}Total=(a+4)2(a+b+30)150003​
  • 保守的计算公式如下 由于15/16的页全部填充数据太过理想，上述计算公式给出的理论上限值，实际应用会打折扣。保守估计方案，可按照每页8k来计算。日常建议使用该公式进行推算。
Total=80003(a+4)2(a+b+30)Total =\frac {8000^3}{(a+4)^2(a+b+30)}Total=(a+4)2(a+b+30)80003​

实际估算案例

计算

样例表结构

CREATE TABLE `xxx`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `bike_id` int(11) NOT NULL COMMENT '车辆id',
  `bike_status` tinyint(2) NOT NULL COMMENT '车辆状态（1停放 2骑行 3维修 4异常 5其它）',
  `lng` decimal(11, 8) NOT NULL COMMENT '经度',
  `lat` decimal(11, 8) NOT NULL COMMENT '纬度',
  `location_time` bigint(13) NOT NULL COMMENT '定位时间',
  `division` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '区域编码',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `id_time`(`bike_id`, `location_time`) USING BTREE COMMENT '车辆时间索引（轨迹查询）'
) ENGINE = InnoDB AUTO_INCREMENT = 14169478 CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = 'xxx' ROW_FORMAT = COMPACT;

a=8+4+8=20a=8+4+8=20a=8+4+8=20 b=4+1+6+6+6=25b=4+1+6+6+6=25b=4+1+6+6+6=25 c=1c=1c=1 d=1d=1d=1

精确计算结果

Total=(15×102420+4)(3−1)×15×102420+25+1+1+5+13=96791630Total ={(\frac {15\times 1024}{20+4})}^{(3-1)}\times {\frac {15\times 1024}{20+25+1+1+5+13}}=96791630Total=(20+415×1024​)(3−1)×20+25+1+1+5+1315×1024​=96791630

简化计算结果

Total=150003(20+4)2(20+25+30)=78125000Total =\frac {15000^3}{(20+4)^2(20+25+30)}=78125000Total=(20+4)2(20+25+30)150003​=78125000

保守计算结果

Total=80003(20+4)2(20+25+30)=11851851Total =\frac {8000^3}{(20+4)^2(20+25+30)}=11851851Total=(20+4)2(20+25+30)80003​=11851851

占用空间与性能分析

行数占用空间（idb）DATA_LENGTHINDEX_LENGTHcount(1)耗时query（索引）耗时
9M884MB538MB213MB3.316s0.389s
50M4620MB2765MB1334MB11.979s0.303s
90M8248MB5326MB2511MB252.720s0.964s
129.6M21760MB12035MB9256MB339.3746s1.034s

注：query走索引，大致返回3000条记录。

在精确估算环节，单条记录占用89byte，折算占用文件空间89*16/15=95。与实际占用空间基本相当。 随着数据量增加，query并未随数据规模指数级增加，count耗时随数据规模等比例增加。

idb文件分析

利用py_innodb_page_info分析idb文件。此处分析的是9M行记录的idb文件，汇总结果如下：

Total number of page: 51664:
Insert Buffer Bitmap: 4
Freshly Allocated Page: 2374
File Segment inode: 1
B-tree Node: 49281
File Space Header: 1
extend description page: 3

分析结果显示页总数51664，计算公式分析页总数38196~84365，实际结果与理论分析吻合。理论计算见以下章节。

精确计算

X=15×102420+4=640Y=15×102420+25+1+1+5+13=236X = \frac{15\times 1024}{20+4}=640\newline Y = \frac {15\times 1024}{20+25+1+1+5+13}=236X=20+415×1024​=640Y=20+25+1+1+5+1315×1024​=236

可得

索引页数=rowsX×Y=9000000640×236=60数据页数=rowsY=9000000236=38136总页数=索引页数+数据页数=rowsX×Y+rowsY=38196索引页数 = \frac{rows}{X\times Y}=\frac{9000000}{640\times 236}=60\newline 数据页数 = \frac{rows}{Y}=\frac{9000000}{236}=38136\newline 总页数= 索引页数+数据页数=\frac{rows}{X\times Y}+\frac{rows}{Y}=38196索引页数=X×Yrows​=640×2369000000​=60数据页数=Yrows​=2369000000​=38136总页数=索引页数+数据页数=X×Yrows​+Yrows​=38196

保守计算

X=800020+4=333Y=800020+25+30=107X = \frac{8000}{20+4}=333\newline Y = \frac {8000}{20+25+30}=107X=20+48000​=333Y=20+25+308000​=107

可得

总页数=rowsX×Y+rowsY=84365总页数= \frac{rows}{X\times Y}+\frac{rows}{Y}=84365总页数=X×Yrows​+Yrows​=84365

500W逆推

阿里的规范提到单表500W行或者2G容量为限，根据上述公式，尝试倒推一下这个数值来源依据。

单条记录平均容量 2G/500M=400byte 页中15/16存储业务数据，单条业务数据容量 400*15/16=375byte

根据简化计算公式，单条业务数据 a+4+a+b+30=375a+4+a+b+30=375a+4+a+b+30=375; 按照10%的字段建立了索引，a=0.1b，代入公式计算可得a=28,b=275

简化计算结果

Total=150003(20+28)2(28+275+30)=4398930Total =\frac {15000^3}{(20+28)^2(28+275+30)}=4398930Total=(20+28)2(28+275+30)150003​=4398930

计算结果439W与500W建议值基本吻合，索引占比低该值会提高。阿里规范给的数值还是比较保守的，绝大部分表字段占用未达到该估算值。

参考资料

MySQL 是怎样运行的：从根儿上理解 MySQL 图解MySQL介绍 我说MySQL每张表最好不超过2000万数据，面试官让我回去等通知？ MySQL :: MySQL 5.7 Reference Manual GitHub - happieme/py_innodb_page_info: python tool for innodb page info