MySQL为什么建议单表不要超过2000万行

MySQL为什么建议单表不要超过2000万行

前言：

在工作中或者面试中，经常会说mysql单表牛不要超过2000w行，超过两千万行建议分表，否则查询效率会急剧下降。

那么一张表最多可以存多少数据呢，今天我们从底层技术进行刨析，分析下根本原因。

底层存储：

我们看到的表，都是一行一行存储，实际上在mysql底层文件中是以b+树的结构存储，b+ 树的数据类型这块就不多做描述。

b+ 树：

b+ 树是一个多路平衡树，非叶子节点不存储数据只存储索引，叶子节点会去存储索引和具体数据行，同时在我们的每个节点上，都会去存储多个索引id，当我们需要检索id的适合，会去根据先在当前节点上查找，定位到下一层的节点位置，再去下一层查找，直到找到叶子节点，拿到需要查找的数据行。所以我们的每次查询，都是需要遍历到叶子节点才可以拿到数据。

例如：

我们需要查找索引为8的数据，就需要经过3层逐层查找，按照层级时间复杂度是O(logn)。

页：

如上图，我们的每个节点其实就是一个页，InnoDB存储引擎中的页是其内存管理和磁盘存储的基本单位。

在页中包括页头、数据列、页目录、页尾四部分，在查找数据的时候，会通过页目录进行检索，使用二分查找法，时间复杂度为O(logn)。

计算验证三层B+树可以存储多少数据？

我们每个页默认的大小是16kB，抛去页头、页目录、页尾，剩余的数据区大概有15kB的大小。

总行数计算公式：
$$\text{maxRow}=X^{Z-1}\times Y$$
其中B+树 层数为Z，单个叶子节点数据行数为Y，单页的索引指针数为X。

非叶子节点数据区，包括索引指针以及页号。

按照我们主键索引为bigint类型，占用8字节，页号占用4字节，一条数据占用12字节。那么我们计算：

单页的索引指针数：
$$X=15KB/12B=1280$$

叶子节点数据区，包含索引、页号以及具体数据行。

假设，我们一行的数据包括索引和页号算1KB，那么我们计算：

单个叶子节点的数据行数：
$$Y=15KB/1KB=15$$
那么我们如果有三层B+树，总存储量就是：

$$\text{maxRow}=X^{Z-1}\times Y=1280^2\ast 15=24576000$$
所以我们可以计算出，三层结构B+树，就可以存储2400wan数据了。

这个也就是我们常说的单表数据2000万的一个由来了。

文章到这里也就结束了，如果有问题，欢迎大家在下面讨论。