彻底理解数据库何时需要分表问题

在阿里巴巴开发手册中写道：

【推荐】单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。
说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

大家在网上肯定看到过很多关于分库分表的情况，很多说到当数据量达到2000W行的时候就需要分库分表了，但是这种单纯抛开容量只谈行数的话肯定是不对。

下面通过一个3层的B+最多可以存储多少数据量来说明不对的原因。

一.B+树知识

首先要知道一个B+数(如果想更仔细了解B+数，可以查看《多路查找树》文档)是由一个一个的磁盘页组成的，每一页的大小可以通过sql语句进行查询(show GLOBAL STATUS like 'innodb_page_size)，默认是16k。

二.一页可以存多少数据

1.每一页存储结构如下：

每个页面16KB，应该减去页头和页尾的开销(约200字节)，剩下的才是用于存储实际数据。

有效数据空间：16384-200=16184字节。

2.假设目前有一个张表：

CREATE TABLE TEST(
    a int(11) PRIMARY KEY,
    b int(11),
    c int(11),
    d int(11),
    e varchar(20) CHARACTER SET utf8mb4
) ENGINE = InnoDB;

分析：

a列：int(11)，占4字节；

b列：int(11)，占4字节；

c列：int(11)，占4字节；

d列：int(11)，占4字节；

e列：Varchar(20)，占4n+2字节；

总计：4 + 4 + 4 + 4 + (4x20+2) = 100字节。

此时就可以算出一页存储的数据了，这个表比较小，只是用作举例，实际开发的表肯定比这个大，通过计算可以知道 一页存储的数据 = 16183 / 100 ≈ 161条数据。 

三.3层B+树可以放多少数据

上面说的161条数据，只是一个叶子节点的数据，如果想得到3层B+树能存多少数据，还需知道一共都多少个叶子节点；

非叶子节点是不会存数据的，所以第一层B+数只会存索引(我这里是int类型，占4字节)和执行下一个节点的指针(占6字节)，所以在一个索引页里面就会占到10个字节，就可以计算出第一层数据 = 16184 / 10 ≈ 1618个索引加指针；

接着，第二层B+树节点，每个节点也会有1618个索引，根节点是1618个索引加指针，所以也会发散出1618分子节点，所以第二层存储的数据就 = 1618 * 1618 ≈ 2617924个索引加指针；

最后3层B+树总共存储的数据= 2617924 x 161  ≈ 421485764条数据。

如下图：

也就是说一个3层的B+树最多可以存放4亿多条100字节的数据。 

如果说按照每条数据1KB来计算，大约可以存4200W行数据。

总结：

所以说网上流行的2000W条数据就不是标准答案，实际能存多少，是需要根据数据库每行所占大小来计算得出来的。

对于一个B+树来说，3层是一个比较理想的范围，因为只需要经过3次磁盘的IO就可以定位到数据。

所以说2000W只是一个参考值，比如阿里巴巴的500W，可能要是考虑到服务器的性能，以前的磁盘都是机械硬盘，现在都是固态硬盘了，所以说这个数量其实也可以往上进行浮动一些，还有可能也是考虑到数据要进行备份恢复的话，如果单表太大，想要备份恢复的风险也比较大，也不利于备份和恢复。