MySQL单表数据不能超过2千万条？

MySQL单表数据不能超过2千万条？

为什么现实中有些单表数据明明很大了，但查询还很快？
为什么大家都说MySQL单表数据不能超过2千万条？那2千万这个值又是怎么来的？
底层的原理是怎么样的？

注意：本文是基于InnoDB引擎所写。

一、页（数据页）

当下我有一张 user** 表，表结构如下：

表内部有如下数据：

在服务器MySQL的 data 目录下可以看到 user.ibd 文件，也就是 表空间。
虽然在服务器中只有一个 user.ibd 文件，但是实际上在文件里面是被分成很多小份 数据页，每份大小16k。
我们可以这样理解：

每个页可以存放的数据不大，只有16k，但我们数据那么多，一页肯定放不下，所以就会分出很多页，每个页都有自己的 页号(也就是表空间的地址偏移量)。

但是光有页号还不够，我们还要把页和页之间关联起来，于是引入了 前后指针，用于指向前一页和后一页。这些都会被加到 页头 里面。

日常中我们是要对页里面的数据进行读写的，可以想象，如果在读取数据到一半时，发生了断电，我们该怎么保证数据的正确性，这时就引入了 校验码。这个就被加到了 页尾。

那剩下的空间就可以放我们的数据了，那我们要查找数据时就要挨个遍历了，这时复杂度是 O(n)，但是如果我们的数据有很多行呢？

其实我们可以根据数据生成一个 页目录，这时候就可以使用二分查找了，复杂度直接从 O(n) 变成 O(lgn)。

二、索引

例如：我们要查找一条数据，我们可以把表空间里面每一页都捞出来，把每一页里面的数据挨个进行判断是不是我们要的数据。

在数据量少的时候，只有几个数据页的时候，这么做貌似也没啥问题。

但是当数据量上去了，那性能就慢了，无法接受。

为了加速搜索，我们可以在每个数据页里面选出主键id最小的数据，并且只需要它的主键id和它所在的页号，把它们组成一条新的数据，放入一个新生成的数据页中，这个新数据页跟之前的页在结构上没有区别，大小也还是16Kb。

为了跟之前的数据页进行区分，数据页里加入了层级信息，从0开始往上数，从而构成了页与页之间的上下层级的概念，

如下图，就是我们常说的B+树索引了：

如上图，最下面一层的层级为0，也就是叶子节点，其余的都叫非叶子节点。

叶子节点存放就是我们具体的数据了，而非叶子节点存放的是索引数据，也即指向具体数据所在页的页号。

如果数据变多了，还可以往上在构建一层，形成三层的树结构。

如下图：

以如上3级B+树为例，我们要查找数据id=5：

1. 首先我们要从顶层页开始入手，由于5大于等于1，小于10，那么如果id=5这条数据存在，那么必定在左边，顺着数据行里面的记录就可以找到页6；
2. 在页6里面同样进行比较，由于5大于等于1，且大于等于4，且小于7，那么同样的，我可以从id=4的数据行中可以找到页60；
3. 最后我们可以在数据页60里面找到我们要的id=5的数据。

如下图：
注意：如上图，所有的页号都不是连续，并且在磁盘里面它们页不一定是挨在一起的。

整个过程中查询了3个页，如果三个页都在磁盘中，没有被加载到内存中，那么整个过程最多需要三次磁盘IO查询，它们才能被加载到内存中。

三、B+树可以承载多少记录数量？

从上面我可以知道，B+树的最末级叶子节点存放的是具体的数据，非叶子节点则是用来存放加速查询的索引数据。

那么，我们可以这样认为，同样的一个16Kb的页，非叶子节点里面的每一天数据都是指向一个新的页的指针，被指向的新的页有两种可能：

• 如果是末级叶子节点，那么存放就是具体的数据了；
• 如果是非叶子节点，那么就会循环指向一个新的页。

这时候我们可以假设3个变量：

• 假设非叶子节点内指向新的页的指针数量为X
• 假设叶子节点能存放的数据量为Y
• 假设B+树的层数为Z

那么，我们可以得出这棵 B+树的总行数 = (X ^ (Z-1)) * Y。

有了公式，接下来我们得知道X、Y、Z具体的值要怎么取得，这样才能套入公式中进行计算。

1、X怎么计算

这时我们要回归到页的结构图中

和叶子节点页结构的差异就是非叶子节点存的不是具体数据，而是主键id和索引数据，也就是指向的页号。
假设主键数据类型是bigint（8Byte），页号在源码里叫FIL_PAGE_OFFSET（4Byte），那么非叶子节点里的一条数据是12Byte左右。

已知一个页的大小为16Kb，页头加上页尾大概128Byte，加上目录预估它1Kb，那么剩下15KB，除以12Byte，等于1280，也即X=1280。

我们知道二叉树指的是1个节点可以指向2个新的节点，同理，m叉树指的一个节点可以指向m个新的节点。这个指向新节点的操作就是扇出。
通过上面我们可以知道，Mysql中B+树的一个节点可以指向1280个新的节点，这个扇出就非常恐怖了。

2、Y怎么计算

这个就要回归到叶子节点的页结构图了，和非叶子节点的差异就是叶子节点存储的是具体数据，但除去页头、页尾、页目录，我们同样可以假设其剩余15Kb。

我们假设具体的一条数据大小为1Kb，那么我们可以知道一页中可以存储的数据行数Y=15。

3、总行数计算

已知公式：B+树的总行数 = (X ^ (Z-1)) * Y，

已知：X=1280，Y=15，

假设B+树是2层结构，即**Z=2**，那么 (1280 ^ (2-1)) * 15 ≈ 2W；

假设B+树是3层结构，即**Z=3**，那么 (1280 ^ (3-1)) * 15 ≈ 2.5kW。

这就是大家常常建议单表不能超过2千万的原因了。

这时有同学就会说是不是可以上到4层结构？

其实一旦上到4层结构，那单表就可以上到百亿级别了，有点离谱了。

并且如果采用的是三层结构，那么磁盘IO最多也就3次，相对更合理些。

四、单表数据有没有可能上到1亿行？上到1亿行查询是不是会很慢？

其实是有可能上到1亿行数据的，我们前面假设单条数据大小是1Kb，那么单页可以存储的数据行数Y=15。

但是如果我们单条数据没有1Kb那么大呢，假设单条数据大小就250Byte，那么单页可以存储的数据行数Y=60。

在3层B+树结构下，套入公式 (1280 ^ (3-1)) * 60 ≈ 9.8kW，近乎1个亿，如果单条数据再小点，那就妥妥上1个亿了。

同时在3层B+树结构下，磁盘IO最多也就3次，可以看到查询速度并不慢。

五、B树 与 B+树

我们知道B树(B-树)与B+树非常像，但MySQL为什么选择了B+树？

让我们来看看B树(B-树)的结构，它和B+树的区别在于，它会在叶子节点和非叶子节点上都放入具体数据。

我们还是假设每个页大小16Kb，去掉头尾和目录剩余15Kb，假设单条数据还是1Kb，不考虑指针的情况下，每页可以存放15行数据，扇出明显减少了，B+树中非叶子节点扇出是可以达到1280的。

那么这时候的公式就变成了一个等比数列了：15 + 15^2 + 15^3 + ...... + 15^Z，其中Z还是代表层数。

假设我要存放2kW左右的数据，这时层数需要达到6层，也即 Z ≥ 6，那么我要查询一条数据，最坏情况下要进行6次磁盘IO。

而B+树最多3次磁盘IO，磁盘IO越多次，查询的越慢，性能差距就显现出来了，因此，B+树更加适合做 MySQL 索引。

六、总结

• B+树叶子和非叶子结点的数据页都是16Kb，且数据结构一致，区别在于叶子节点放的是真实的行数据，而非叶子结点放的是主键和下一个页的地址。
• B+树一般有两到三层，由于其高扇出，三层就能支持2kW以上的数据，且一次查询最多1~3次磁盘IO，性能是可观的。
• 存储同样量级的数据，B树(B-树)比B+树层级更高，因此磁盘IO也更多，所以B+树更适合成为 MySQL 索引
• 索引结构不会影响单表最大行数，2kW也只是推荐值，超过了这个值可能会导致B+树层级更高，影响查询性能。
• 单表最大值还受主键大小和磁盘大小限制。
• 单表最大行数还受单条数据大小影响。

【参考文章】
为什么大家说mysql数据库单表最大两千万？依据是啥？