前言

很多人说，mysql数据单表不能超过500万条或者容量2G，才推荐分库分表。实际上每张表由于自身的字段不同、字段所占用的空间不同等原因，它们在最佳性能下可以存放的数据量也就不同。

如果要计算出具体的条数，可以通过"innodb的B+树的高度保持在三层以内"这一观点进行计算

基础说明

B+树的概念不多赘述，可以提取几个关键字：根，(非叶子)节点，叶子节点。叶子结点对应mysql的页存储，另外两个可以理解为拆分的叶子节点，也对应两页。

mysql页大小默认16KB，可以有4KB，8KB，16KB，32KB，64KB。页的下一级是行，每行最大长度略小于页一半，其中64KB的最大行是16KB，后续均以每页16KB计算，mysql5.7+

页格式

mysql需要分表的数据条数计算

innoDb会预留1/16的空间页面空闲以供将来插入和更新索引记录。如果按顺序（升序或降序）插入索引记录，则生成的页大约可用 15/16 的空间。如果以随机顺序插入记录，则页大约可用 1/2 到 15/16 的空间。

除了 User Records和Free Space 以外所占用的内存是 38+56+26+8=12838 + 56 + 26 + 8 = 128 字节，
每一页留给用户数据的空间就还剩 16×15/16×1024−128=15232字节（保留了1/16）。

行格式

MySQL5.6的默认行格式为COMPACT(紧凑)，5.7及以后的默认行格式为DYNAMIC(动态)

mysql需要分表的数据条数计算

当使用 DYNAMIC 创建表时，InnoDB 会将较长的可变长度列（比如 VARCHAR、VARBINARY、BLOB 和 TEXT 类型）的值剥离出来，存储到一个溢出页上，只在该列上保留一个 20 字节的指针指向溢出页。具体总数根据实际情况计算

非叶子节点计算

非叶子节点也叫索引页，每一条索引记录当中都包含了当前索引的值 、 一个 6字节 的指针信息 、一个 5 字节的行标头，用来指向下一层数据页的指针。

假设我们的主键id为 bigint 型，也就是8个字节，那索引页中每行数据占用的空间就等于 8+6+5=198 + 6 + 5 = 19 字节。每页可以存 15232÷19≈801条索引数据。

那算上页目录的话，按每个槽平均6条数据计算的话，至少有 801÷6 个槽，需要占用 268 字节的空间。

把存数据的空间分一点给槽的话，算出来大约可以存 787 条索引数据。

如果是主键是 int 型的话，那可以存更多，大约有 993 条索引数据。

简单记即：主键bigint=787, int = 993, 后续以常用的bigint进行计算

叶子节点计算

因为三层结构关系，第二层N，第三层即N*N。所以总的叶子节点数

  • 主键为 bigint 的表可以存放 787*787 个叶子节点
  • 主键为 int 的表可以存放 993*993 个叶子节点

最小存放计算

每页按照最大算，即每页两条，则每张表的最小存放数量  2 * 619369 = 1238738条，即一百二十多万

可以看出计算公式为  每页存放的行数 * 叶子节点个数

而每页存放行数=页大小/每行大小

示例计算

CREATE TABLE `course_schedule` (
  `id` bigint NOT NULL,
  `teacher_id` bigint NOT NULL,
  `course_id` bigint NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

算上每行记录的额外字段和数据字段，可得每行大小为8+8+8+6+7+5=42字节，算上槽位大小所以(15232-268)/42≈356条

可得该表最大行数为： *356=220495364,  两亿多条数据

当时在实际使用中，表的字段肯定较多，行大小也较大，所以一般条数会少，具体已实际计算为准。

 

 

Follow your heart ~!