为什么大家说 MySQL 数据库单表最大两千万？依据是啥？

vlambda
2022-04-27

为什么大家说 MySQL 数据库单表最大两千万？依据是啥？

故事从好多年前说起。

想必大家也听说过数据库单表建议最大两千万条数据这个说法。如果超过了，性能就会下降得比较厉害。

巧了。我也听说过。

但我不接受它的建议，硬是单表装了 1 亿条数据。

这时候，我们组里新来的实习生看到了之后，天真无邪地问我："单表不是建议最大两千万吗？为什么这个表都放了 1 个亿还不分库分表"？

我能说我是因为懒吗？我当初设计时哪里想到这表竟然能涨这么快……

我不能。

说了等于承认自己是开发组里的毒瘤，虽然我确实是，但我不能承认。

我如坐针毡，如芒刺背，如鲠在喉。

开始了一波骚操作。

"我这么做是有道理的。"

"虽然这个表很大，但你有没有发现它查询其实还是很快。"

"这个两千万是个建议值，我们要来看下这个两千万是怎么来的。"

数据库单表行数最大多大？

我们先看下单表行数理论最大值是多少。

建表的 SQL 是这么写的，

CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', `name` varchar(100) NOT NULL DEFAULT '' COMMENT '名字', `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄', PRIMARY KEY (`id`), KEY `idx_age` (`age`)) ENGINE=InnoDB AUTO_INCREMENT=100037 DEFAULT CHARSET=utf8;

其中 id 就是主键。主键本身唯一，也就是说主键的大小可以限制表的上限。

如果主键声明为 int 大小，也就是 32 位。那么能支持 2^32-1，也就是 21 个亿左右。

如果是 bigint，那就是 2^64-1。但这个数字太大，一般还没到这个限制之前，磁盘先受不了。

搞离谱点。

如果我把主键声明为 tinyint 一个字节， 8位。最大 2^8-1，也就是 255。

CREATE TABLE `user` ( `id` tinyint(2) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', `name` varchar(100) NOT NULL DEFAULT '' COMMENT '名字', `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄', PRIMARY KEY (`id`), KEY `idx_age` (`age`)) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

如果我想插入一个 id=256 的数据，那就会报错。

mysql> INSERT INTO `tmp` (`id`, `name`, `age`) VALUES (256, '', 60);ERROR 1264 (22003): Out of range value for column 'id' at row 1

也就是说，tinyint 主键限制表内最多 255 条数据。

除了主键，还有哪些因素会影响行数？

索引的结构

索引内部是用的 B+ 树，这个也是八股文老股了，大家估计也背得很熟了。

为了不让大家有过于强烈的审丑疲劳，今天我尝试从另外一个角度给大家讲讲这玩意。

页的结构

假设我们有这么一张 user 数据表。

user 表

其中 id 是唯一主键。

这看起来的一行行数据，为了方便，我们后面就叫它们 record 吧。

这张表看起来就跟个 Excel 表格一样。Excel 的数据在硬盘上是一个 xx.xlsx 文件。

而上面 user 表数据，在硬盘上其实也是类似，放在了 user.ibd 文件下。含义是 user 表的 innodb data 文件，专业说法又叫表空间。

虽然在数据表里，它们看起来是挨在一起的。但实际上在 user.ibd 里他们被分成很多小数据页，每份大小16K。

类似于下面这样：

为什么大家说 MySQL 数据库单表最大两千万？依据是啥？ — ibd 文件内部有大量的页

我们把视角聚焦到页上面。

整个页大小为 16K，不大。但 record 这么多，一页肯定放不下，所以会分开放到很多页里。并且这 16K 也不可能全用来放 record，对吧。

页需要支持读写，16K 说小也不小，写一半电源线被拔了也是有可能发生的。所以，为了保证数据页的正确性，还引入了校验码。这个被加到了页尾。

那剩下的空间才被用来放 record。如果 record 行数特别多，进入到页内时会挨个遍历效率也不太行。所以，为这些数据生成了一个页目录。具体实现细节不重要，只需要知道，它可以通过二分查找的方式将查找效率从 O(n) 变成 O(logn)。

从页到索引

如果想查一条 record，可以把表空间里每一页查出来，再把里面的 record 挨个判断是不是我们要找的。

行数小的时候，这么操作也没啥问题。行数多了，性能就慢了。

于是为了加快搜索，可以在每个数据页里选出主键 id 最小的 record，而且只需要它们的主键 id 和所在页的页号。将它们组成新的 record，放入到一个新生成的一个数据页中。这个新数据页跟之前的页结构没啥区别，大小还是 16K。

但为了跟之前的数据页进行区分，数据页里加入了页层级（Page Level）信息，从 0 开始往上算。于是页与页之间就有了上下层级的概念，就像下面这样。