为什么MySQL数据库单表建议最大2KW数据？_mysql单表数据量多少合适

本文链接：https://blog.csdn.net/2401_85112032/article/details/139911415

剩下的空间才是用来放行数据 record 的，如果行数据 record 行数特别多，进入到页内时会挨个遍历，效率也不太行，所以，为这些数据生成了一个页目录。具体实现细节不重要，只需要知道，它可以通过二分查找的方式将查找效率从 O(n) 变成 O(logn)。

数据页的结构：

在数据插入的过程中，数据页的变化如下：

在这里插入图片描述

（1）申请新的数据页；
（2）此时并没有User Records这个部分，当我们插入一条记录时会从Free Space也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records中用于记录的存储；
（3）当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了；
（4）继续有新的记录插入；
（5）重新申请新的页。

二、 B+ tree 结构

如果想查找一条行数据 record，可以把表空间里每一页查出来，再遍历里面的行数据 record ，挨个判断是不是我们要找的。行数小的时候，这么操作也没啥问题。行数多了，性能就慢了。于是为了加快搜索，可以在每个数据页里选出主键 id 最小的 record，而且只需要它们的主键 id 和所在页的页号，将它们组成新的 record，放入到一个新生成的一个数据页中，这个新数据页跟之前的页结构没啥区别，大小还是 16KB。但为了跟之前的数据页进行区分，数据页里加入了页层级（Page Level）信息，从 0 开始往上算，于是页与页之间就有了上下层级的概念，页跟页之间看起来就像是一棵倒过来的树，也就是我们常说的 B+ 树索引。

所有的数据以数据页的形式进行存储，为了增加查询效率，数据页与数据页之间是以 B+ 树的形式进行关联的。页的页号并不是连续的，它们在磁盘里也不一定是挨在一起的。使用二分查找，时间复杂度从 O(n) 变成 O(logn)。

在这里插入图片描述

B+ 树的叶子节点存放的是实际存储的行记录数据，数据页；
B+ 树的非叶子节点存放的是索引内容（子节点中，页的第一条数据的主键ID + 页的地址），索引页；
B+树的每一层代表一次磁盘IO（性能损耗的点）。

三、 B+ 树存放数据的行数

在这里插入图片描述

设：
非叶子结点存放的指向其他数据页的指针数量为 X ；
叶子节点存放的行数据数量为 Y ；
B+ 树的层数（高）为 Z 。

则：

B+树能存放的总行数 = (X ^ (Z-1)) * Y。

代入计算：

一个数据页 16KB ，扣除各种信息，可用的空间约为15KB。

假设主键 ID 为 bigint 类型为 8 byte，页号占用4 byte，非叶子节点里的一条数据是 12 Byte 左右，则：X = 15*1024/12 ≈ 1280，指向 1280 个新的数据页。

Y 是数据页中能容纳的最大行记录数量，所以与实际存储的行记录的大小有关，假设一条行记录占用的空间大小为1KB，则：Y = 15。

假设B+树是两层，那 Z = 2，则总行数 = (1280 ^ (2-1)) * 15 ≈ 2 W。

假设B+树是三层，那 Z = 3，则总行数 = (1280 ^ (3-1)) * 15 ≈ 2.5 KW。

这个数据是根据每条行记录的大小为 1KB 时估算而来的，而实际情况中并不一定是这个值，所以说，最大建议行数2000w这个值只是一个建议，并非一个标准。

B 树，B 树叶子结点和非叶子结点上都放数据表行数据。每个数据页 16KB，掐头去尾每页剩15KB，假设一条数据表行数据还是占 1KB，就算不考虑各种页指针的情况下，也只能放个 15 条数据，数据页的扇出明显变小了。计算可承载的总行数的公式也变成了一个等比数列。其中 Z 还是层数的意思。
15 + 15^2 +15^3 + … + 15^Z
为了能放两千万左右的数据需要 Z>=6，也就是树需要有 6 层，查一次要访问 6 个页，假设这 6 个页并不连续，为了查询其中一条数据，最坏情况需要进行 6 次磁盘 IO。而 B+ 树同样情况下放两千万数据左右，查一次最多是 3 次磁盘 IO。磁盘 IO 越多则越慢，这两者在性能上差距略大。因此，B+ 树比 B 树更适合成为 MySQL 索引。

在 InnoDB 中 B+ 树高度一般为1-3层，它就能满足千万级的数据存储。查询数据时，每加载一页（page）代表一次IO，所以通过主键索引查询通常只需要1~3次 I/O 操作即可查找到数据。

MySQL 都有缓存， B+ 树高度为 3 时，第 1 层和第 2 层的数据都在缓存中，查询只需要一次 I/O 操作，速度很快，但是当数据超出 2KW 时， B+ 树层高会增加，需要再多一次 I/O 操作，查询效率就急速下降了。所以MySQL数据库单表建议最大2KW数据。