为什么会有“建议MySQL单表数据控制在2000W”的说法？

前言

经常听到“建议MySQL单表数据控制在2000W”这样的说法，这个说法的背后是因为表数据量过大会导致查询性能低下。那么2000W是怎么来的？

行记录是如何存储的？

这得从MySQL存储设计说起，以InnoDB存储引擎为例：

1. InnoDB是以页为单位进行存储和管理数据的，目的是为了减少磁盘IO的次数，‌提高数据的读写速度。‌
2. 为了提高查询效率，InnoDB以索引组织表数据，且每张表至少会有一个索引（聚簇/主键索引）。

所以，在组织表数据时会存在索引页和数据页。不论是哪种页，其结构是一样的，大概是这样的：

更详细的说明请移步至《执行器调用存储引擎后，InnoDB做了什么事？》

图中的「用户数据」可以存放多少行记录是至关重要的，因为它关系到“单表数据控制在2000W”的这个说法是否成立。

那么，索引页和数据页可以存放多少行记录？

数据页可以存放多少行记录？

InnoDB页大小默认为16KB，「文件头」、「页头」、「页目录」等占用了1/16的空间，剩下的15KB就用来存行记录。

索引页存放的数据是索引和指针，数据页则存放的是完整的行数据。

通常我们的主键都是数值类型（int、bigint…），以bigint为例，一行索引记录会占用8B加上指针占用的空间6B，也就是14B。那么索引页就可以存放15*1024/14≈1098行记录。

数据页能存放多少行记录则需要看每个字段占用多少空间来估算，这里假设一行记录是1KB，那么数据页可以存放15行记录。

基于以上的情况，InnoDB是如何以索引组织2000W数据的？

InnoDB如何以索引组织2000W数据？

以B+tree索引结构为例，其叶子结点是数据页，非叶子结点为索引页。

• 如果树的高度为2，1层索引页和数据页，那么可以存放1098*15≈2W行记录。
• 如果树的高度为3，2层索引页和1层数据页，那么可以存放1098*1098*15≈2000W行记录。
• 如果树的高度为4，3层索引页和1层数据页，可以存放1098*1098*1098*15≈200000W行记录。

如此看来，“建议MySQL单表数据控制在2000W”并不是没有道理的。因为树高度过高时，会有以下两个原因导致查询速度变慢：

1. 树高度过高，意味着在进行数据检索时需要进行更多的磁盘I/O操作。
2. 在这样大的数据量下，索引的体积也会很大，维护索引结构的复杂度也会相应提高。

总结

综上所述，“建议MySQL单表数据控制在2000W”是根据每行记录大小为1KB估算的，并非一个标准，还是要根据实际情况去决定单表的数据量。