Mysql单表存多少数据合适

如题是一个我们经常会遇到的问题，它关乎我们请求调用的耗时与并发，关乎什么时候要做数据清理归档，关乎什么时候要做分库分表。有人会根据自己的经验回答说：2000W，这数字是怎么一拍脑袋就得出的？是否有各种限制条件？今天，让我们好好探寻一番！

首先，我们知道Mysql的数据，包括索引，终归都是存在磁盘上的，只是需要的时候才从磁盘读到内存中使用。要搞明白题目，前提是要先理解磁盘的构造以及我们是如何使用磁盘的。

我们以传统的机械磁盘为例：

一个盘面是由多个同心圆构成，每个圆就是一根磁道，而磁道上最小的存储单位就是一个扇区（sector），就是上图中那绿色的一小撮圆弧段，可存储的数据大小通常为512字节（也有更大的），扇区就是磁盘最小的物理存储单位。盘面当然不止一个，我们的大容量磁盘就是由多个盘面组成的，机械磁盘会使用机械臂上的磁头定位到数据所在的磁道，再旋转磁盘定位到具体的扇区后读取数据。

磁盘读取响应时间

• 寻道时间：磁头从开始移动到数据所在磁道所需时间，为了后续方便计算，当作10ms，实际更短

• 旋转延迟：盘片旋转将请求数据所在扇区移至读写磁头下方所需时间。旋转延迟取决于磁盘转速，普通硬盘一般都是7200rpm，也就是120rps（转每秒）

• 数据传输时间：完成传输请求的数据所需时间

• 上面大体说明了磁盘的构成与运作方式，那我们的操作系统是如何使用磁盘的呢？那就不得不先提及“块”和“页”这两个概念。

块（block）

磁盘块是我们人为虚拟的概念，是操作系统中最小的逻辑存储单位，那磁盘块和扇区有什么关系呢？我们知道一个扇区能存的数据很小不好用，所以操作系统与磁盘打交道至少是>=1个的整数个扇区，根据就近原则和二进制特性，磁盘块通常包括2、4、8、16、32、64个扇区。

页（page）

另一方面，操作系统还需要和内存打交道，类似于“块”，页就是操作系统对内存操作的最小单位，也是一个逻辑概念，页的大小通常为磁盘块大小的 2^n 倍，最常见的就是4096字节，也就是4K。

简单总结一下：

扇区（物理）<=块（逻辑）<=页（逻辑）

好，有了这些基础概念后，我们进入正题，以mysql innodb为例，innodb引擎定制化的页大小默认为16K

我们可以通过命令查询

SHOW VARIABLES LIKE 'innodb_page_size';

这个innodb_page_size=16384字节=16KB，就是innodb中最小的存储单位。

假设我们一条数据是1K大小，理想情况下innodb中的一页能存16条数据（实际能用于存储数据的不足16K），数据库中的每条记录都是以页的组织方式进行管理的。

通常来说我们还会使用主键对单表进行排序，而主键索引对应的B+Tree数据结构大概长这样：

老生常谈，B+Tree由非叶子节点和叶子节点组成，非叶子节点不存储实际数据，只存储主键列+索引号（其实就是页号）；叶子节点存储实际的数据，且前后节点之间还会双向关联。

由于每个非叶子节点能存储的索引记录很多，树整体的高度就矮，高度矮意味着调用磁盘IO的次数少，毕竟磁盘查询一次10ms的开销是非常昂贵的。

B+Tree的数据存储量

当我们有一张按主键排序的单表，主键为BIGINT（业务最常用），且假设一条记录1KB，innodb引擎默认页16KB

设B+Tree的高度为H

非叶子节点每页能存储的索引量为I

叶子节点的每页能存储的数据量为Y

B+Tree能存储的数据总量为N（最底层叶子节点层）

我们知道：数据总量 N = (I ^ (H-1)) * Y

其中，Y就按最理想的16条来算（实际更少）。

且由于BIGINT字段占8个字节，innodb索引号（页号）通常设为4字节，两者加起来12字节，就能算出

每页索引存储量 I = 16 * 1024 / (8+4) = 1365

套入公式，可以得到：

如果B+Tree的高度为2 N = (1365^(2-1))*16 = 21840

如果B+Tree的高度为3 N = (1365^(3-1))*16 = 29811600，2980w条记录——28G

如果B+Tree的高度为4 N = (1365^(4-1))*16 = 40692834000，406亿条记录——38T

当然，以上的数据总量N是最大理想情况，实际存储量肯定更小。

再来，按业务常见的100qps并发请求为例，查询表记录的IO次数取决于树的高度H，当H=3，一次db查询可能耗时10ms*3=30ms，那一块磁盘能支撑的qps = 1000 / 30 = 33，如果高峰期所有请求同时都打到这个磁盘上，那么磁盘就已经满负荷了，最后一条记录的延迟至少有100 * 30ms = 3s。

当H=4情况会更加糟糕，且存满数据时，单机的磁盘也无法负载。

因此，通常情况下H=3是一个没得选的选择，所以就会有人说单表2000w这么一个玄学数字。当然，实际情况需要我们根据业务qps，单条记录平均大小，主键类型，数据库规格，操作系统配置等多个因素综合考量单表存储的数据上限。

转载：https://zhuanlan.zhihu.com/p/679284081