MySQL单表最大建议行数2000w数据怎么算出来的?

希诺双层玻璃杯商场专柜款过滤泡茶杯带盖透明防漏男士大号水杯高档生日礼物防烫水晶定制礼品杯 专柜款(6607)高挑款410ML带滤网-大容量 >> 限时秒杀¥179.00

经常都会看到这么一个标准:在MySQL中,单表最大建议行数不要超过2000w,超过的话查询性能会急剧下降。那么2000w这个值的依据是什么?怎么算出来的呢?

在MySQL的Innodb引擎中,每一张表在磁盘上都会有对应的一个.ibd文件(innodb data),这个文件叫表空间。在表空间中,数据是以数据页的形式进行存储的,每一页只有16k的大小。

数据页的结构如下:

可以先只关注User Records和Free Space。
User Records:实际存储的行记录内容
Free Space:页中尚未使用的空间

在数据插入的过程中,数据页的变化如下:

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

所有的数据以数据页的形式进行存储,为了增加查询效率,数据页与数据页之间是以B+树的形式进行关联的。

其中,叶子节点上的数据页存放的是实际存储的行记录内容,非叶子节点上存储的是索引内容(子节点中,页的第一条数据的主键id及页的地址)。B+树的每一层代表一次磁盘IO(性能损耗的点)。

了解完存储的结构,我们就可以进行数据的估算

设:
非叶子节点内指向其他数据页的指针数量为x
叶子节点内能容纳的行记录数量为y
B+树的层数为z

则:
这棵B+树能存放的行数据总量为(x ^ (z-1)) * y

代入计算:
数据页中扣除各种信息,User Records可用的空间约为15k,假设主键id为bigint类型为8byte,页号占用4byte,则:x=15*1024/12≈1280
y是数据页中能容纳的最大行记录数量,所以与实际存储的行记录的大小有关,假设一条行记录占用的空间大小为1k,则:y=15

假设B+树是两层,那z=2。则(1280 ^ (2-1)) * 15 ≈ 2w
假设B+树是三层,那z=3。则(1280 ^ (3-1)) * 15 ≈ 2.5kw

可以看到,当B+树为3层时,可存储的数据约为2500w,超出这个数据之后,层高会增加,也就意味这需要再一次的磁盘IO,故而这也就是“MySQL单表最大建议行数2000w”这一建议中2000w数据的来源。

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

参考资料:
https://www.modb.pro/db/139052
https://www.bilibili.com/read/cv15996043/
https://www.toutiao.com/article/7124938887893418530

飞利浦(PHILIPS)车载手机支架无线充电器 出风口夹 无线QI大功率15W华为/苹果DLP9102B/93-无线充 >> 限时秒杀¥199.00

觉得内容还不错?打赏个钢镚鼓励鼓励!!👍