为什么会有MySQL单表最大两千万的说法？
date
May 24, 2024
slug
why-is-there-a-saying-that-the-maximum-size-of-a-single-MySQL-table-is-20-million
status
Published
tags
编程开发
数据库
summary
我们有时会在一些大公司的数据库规范里看到说MySQL单表的数据量最大不能超过两千万，超过了就要进行分库分表，不然就影响查询性能。这种说法是否有依据，为什么是两千万，而不是五百万、五千万，有没什么场景下即使存储一亿行数据也不会有性能问题。这篇文章就是想要来解答这个疑问的。
type
Post
我们有时会在一些大公司的数据库规范里看到说MySQL单表的数据量最大不能超过两千万，超过了就要进行分库分表，不然就影响查询性能。这种说法是否有依据，为什么是两千万，而不是五百万、五千万，有没什么场景下即使存储一亿行数据也不会有性能问题。这篇文章就是想要来解答这个疑问的，当然这里的存储引擎指的是InnoDB。
在理解mysql单表能存储多少数据量前，需要先明白数据是如何在mysql存储的。
在InnoDB存储引擎里，数据是以索引的方式来组织的，并且采用的是
B+树作为索引的数据结构，也就是说每一个索引在InnoDB里都会对应一颗B+树。B+树是一颗多叉平衡树，平衡树是一种特殊的树形数据结构，它能够自动保持树的平衡，使得树的高度始终保持在对数级别，从而保证操作查找、插入和删除操作的时间复杂度都是O(logn)。而多叉的特点，则是能大幅度的降低树的高度。而到了存储时，数据会被放到一个以
.ibd作为后缀的文件中（例如user.ibd），这个文件叫表空间文件。在该文件中，数据则会根据索引的定义被分割到不同的数据页，每个数据页存储一定量的数据。接下来我们以一个简单的例子看下索引是如何利用B+树将数据页组织起来，我们假设这里的B+树是一颗二叉树。
位于最底层的数据页节点被称为叶子节点，而叶子节点中存储的数据则会因为索引类型的不同存在差异。我们知道在InnoDB里，索引有主键索引和非主键索引，也被称为聚集索引和非聚集索引（或二级索引），因此叶子节点存储的内容则有如下区别：
- 主键索引中，叶子节点存储的是主键和行数据。
- 在非主键索引中，叶子节点存储的是索引字段和主键值。
不同的叶子节点之间会通过一个双向指针连接起来，这使得在进行范围查询时，可以快速地遍历叶子节点。
除了叶子节点之外的节点，则称之为非叶子节点。在非叶子节点中，则会记录索引字段以及指向下一层级节点的数据页号。由于是二叉树，因此非叶子节点最多存储2条数据，以数据页100为例子，说明了数据页106的索引最小值为1，数据页108中索引的最小值为4。
我们的示例中假设了每个叶子节点最多存储3行数据，接下来我们来看下如果要查找索引值为5的数据行时，并且这里的索引为主键索引的情况下，查找的流程是怎样的。
- 先从磁盘的ibd文件中把第一个数据页90读取到内存中，发现索引值5应该出现在数据页100中。
- 从磁盘中把数据页100也读取到内存，发现索引值应该出现在数据页108中。
- 最后从磁盘中把数据页108读取到内存，从该数据页的第一行数据开始遍历直到获取到索引值5的数据。
整个查找的过程其实就是一次二分查找，而且磁盘中的数据页一旦被加载到内存，那么下一次查找的时候则可以直接从内存中的数据页中查询。
相信从图中也可以看到，数据页并不是连续的，甚至相邻的数据页在磁盘中也不是连续紧挨着的。也就是每次需要从磁盘中读取数据页时就是一次随机读取的过程，而读取的次数则是由树高决定。为了降低树高，则需要增加数据页中所存储的数据，将二叉树转化为多叉树。例如我们可以将示例中的数据页100和数据页104合并。
原来二叉树的树高为3，这里采用四叉树后，树高则降低为2。当我们同样需要查找索引为5的数据行时，则由原先的三次磁盘查找降低为了两次磁盘查找。
正因为如此，B+树的高度通常非常低（一般是3），每个数据页节点都存储了大量的数据，配合二分查找，极大地提高了查询效率。
接下来我们就可以来看看数据页的结构。
数据页的大小默认为16KB，主要包含如下几部分：
- 页头（Page Header）。包含了两个指向前后数据页的指针，还有用来唯一标识数据页的页号。
- 用户数据（User Records）。数据页的主体部分，记录的则是实际的数据行。
- 页目录（Page Directory）。用于快速定位到数据页内特定行的位置。当数据页中的行数很多时，页目录可以加速数据的查找。
- 页尾（Page Footer）。包含数据页的校验信息，用于异常情况下的数据一致性校验。
接下来则要进入本文的正题，两千万是如何被计算出来的。
我们先计算非叶子节点中能存储的数据行的大小。假设数据页去除页头和页尾等结构之后，留给数据行的空间有15KB。由于每一行数据大小=索引字段大小+数据页号大小，其中索引字段假设采用的是bigint，大小为8byte，而页号在mysql里固定为4byte，因此每一行记录的总大小则为12byte，这样一个非叶子节点的数据页可以存储 15 * 1024 / 12 = 1280 行记录。
接下来计算叶子节点能存储的数据行大小。同理假设数据页中留给数据行的空间为15KB，我们再假设每一行数据大小为1KB，那么一个叶子节点数据页中则可以存储15行数据。
如果此时这颗B+树的高度为2，那么此时所有叶子节点的数据行总和为 * 15 = 19200 行，差不多有2万行。
而如果此时树的高度为3，那么此时的总行数将为 * 15 ，约等于2.4千万，和我们说的2千万差不多。
而我们前面的假设中，假定了叶子节点里每行数据大小为1KB，但如果我们一行数据不需要用到这么多，例如每行数据只需要256byte，此时每个数据页便可以存储60行数据。那当树高为3时，总行数将约为9.8千万，差不多有1亿行。也就是说此时我们单表存储一亿行数据，MySQL的读写性能也不会存在问题。
同样的道理，如果每行数据大于1KB，例如5KB，此时总行数将缩减为只有285万行。
到这里我们总算明白MySQL所谓的单表不能超过2千万的说法怎么来的了。具体问题具体分析，下一次如果看到超过千万行的表，也不要着急优化分库分表。而当我们单行数据的大小过大时，也不要盲目的认为MySQL单表可以支持多大的数据。
到这里我们就可以思考一个新的问题了。
- 超过两千万后数据库表怎么办？
- 单表两千万是不是非得分表？
第一个问题很好回答，超过了自然就是增加树高，从3层树高变为4层，最大就可以容纳数百亿的数据。然而我们这里的两千万是以一行数据大小只有1kb作为假设的，前面说到当每行数据大小为5kb时，3层树高就只能存储两百多万了，继续增加行数也就会增加树高。
至于第二个问题，就得看情况了。理论上来说，增加一层树高，无非就是多一次IO查询，配合mysql的缓存buffer pool，树高增加带来的性能损耗几乎可以忽略不计。
但是这种情况通常来说我们还是建议分表的。首先我们需要知道MySQL每次从磁盘获取数据都是一整页获取，即使你只是查询一条数据，也会把该数据所在的数据页都带出来，这样下次需要查询这条数据附近的数据时，就不要再发起IO了。从前面的计算也知道，数据行数的大小越小，每个数据页可以存储的数据就越多，一次查询可以被带出来的数据就越多，这就是数据页的局部性原则。
但如果此时我们的查询需要获取多行，就可能会带出多个数据页，如果单行数据过大，被带出来的数据页就越多，此时引发磁盘IO次数数量级规模的上升，进而导致单行数据过大导致表读取速度性能下降。所以在单表行数过大的场景下，在达到层数最大数据瓶颈时，最好还是做横向分表的。
当然要不要分表还会受到很多因素影响，前面我们假设的都是每个数据页大小都是一致的场景，现实肯定不是这样，这里就举几个常见的例子。
- 我们经常会使用varchar来定义字符串，不定长的字段，导致每个数据页能存储的行数变得不一致。
- 时常会遇到数据的删除和更新，页与页之间的双向链表和不同层级页之间单向指针需要频繁变化。
- 随着时间增长，ibd文件数据碎片过多。
所以何时需要分表其实也没有一个好的标准，不过总的来说还是能不分就不要分，能通过索引的方式或加内存解决就不要分表，毕竟分表了，业务代码必然产生影响。