【mysql】完整的推算Mysql单表可存2000万数据的过程

一个16KB的数据页，B+树叶子节点里放了行数据，如下所示的叶子节点上的数据页结构：

真正存储数据的是User Records + Free Space这两块区域空间，现在我们使用掐头去尾的方式预估User Records + Free Space所占的空间的大小，预估的依据如下所示：

（1）文件头、页头、存储页面最大最小记录以及页的校验页大小是固定的，其占用的空间是38 + 56 +26 + 8 = 128B

（2）page Directory大小是不固定的，假设其占用空间为896B

这样我们可估算出来User Records + Free Space大小约为15KB，如下所示：

假设一条数据占用的空间大小是1KB，那么一个叶子节点的数据页可以存储的数据就15条了。

索引页的数据页和叶子节点上的数据页其实没有什么区别，只是索引页中不存储具体的数据，如下所示的的索引页：

在索引页中存储的数据是叶子节点中数据的主键和数据页号，如下所示的索引页存储的数据：

假设数据的主键是bigint类型，那么其占用的空间是8字节，数据页的页号在源码中定义了其为4字节，如下图所示：

那么在索引页中15kb大小的存储空间可以存储多少的索引数据便可以估算出来了，约是（15 * 1024）/ （8 + 4）= 1280条。

根据如下的计算公式我们来预估计算单表可以存储的数据量，公式如下所示：

函数中各个变量的含义如下表所示：

在实际B+树中各个变量的代表的含义如下所示：

（a）若X=1280，y=15，Z=2，根据上述的公式可以计算的单表存储的数据量为20000条数据

（b）若X=1280，y=15，Z=3，根据上述的公式可以计算的单表存储的数据量为25000000条数据

这就是单表建议最大行数2,000万的一个由来，通过上面的分析我们发现B+树从2层变成3层，数据就激增；如果再增加一个层，也就是B+树从3层变成4层，那么数据量就更加大了。一般来讲3层的B+树理论上只需要3次的IO便可以完成数据的加载和查询工作了，也是比较的合理。

我们假设的是y（叶子节点）上的数据页中每条数据是1KB大小，那么现在我们假设叶子节点的数据页中数据大小是500KB，那么我们的3层的B+树可以存储的数据量大约为原先的2倍，也就是可以存储2500000 * 2 = 5千万的数据量；假设数据页中数据大小是250KB，那么3层的B+树可以存储的数据量大约为原先的4倍，也就是可以存储1亿的数据量。

在一定的条件下，单表的1亿数据使用3层B+树也是可以的存放的，那么查询的时候3次IO也可以实现数据的查询。