为啥MySQL的InnoDB在一页(page)中最少要存储两条记录?

关于这个问题,村长我查过很多信息,有说来自官方,有自己分析的,莫衷一是,要么太发散,要么有几分牵强,现在村长试解答一下。

这个问题其实挺有意思,理论上来说,可以存一条数据,那为啥要有这么个规则呢?

我们知道,一页的大小默认为 16*1024=16384 字节,但是,MySQL 允许的一行数据的存储上限却为 65535!
当然了,这 65535 个字节除了列本身的数据外,还包括一些其他数据(storage overhead),比如 变长字段长度列表、NULL值列表、记录头信息 等,但归根结底,还是远大于 16384 的。
如果至少要存储两条记录,简单做个除法,16384/2=8192,这远小于上限值啊!

有意思吧?

如果我们设置行类型为 text, 理论上会出现一页可能存不下一行数据的情况誒。
比如,超过 一页大小的二分之一,8192,那怎么办?

这种情况称之为 行溢出,和内存溢出类似

MySQL 是怎么处理行溢出的呢?

这涉及到 行格式 问题,MySQL 支持4种不同类型的行格式:Compact、Redundant(比较老)、Dynamic、Compressed。
Compact和Redundant行格式中,记录的真实数据处会存储该列的一部分数据(前768个字节), 剩余数据存储在其他页(溢出页),再使用20个字节存储指向溢出页的地址;
Dynamic(MySQL默认)和Compressed行格式中,不会在记录的真实数据处存放前768个字节,而是将所有字节都存储在其它页面中,自身只存储一个指向溢出页的地址;略有不同的是,Compressed 行格式会采用压缩算法对页面进行压缩,以节省空间。

言而总之,要么存 前缀+溢出页地址,要么只存 溢出页地址。

现在问题来了,我们假设一个场景:

某一行数据,占 10000 个字节,加上额外信息,一页也是可以存下的,为啥非得搞个溢出页存放,这不给自己找事儿吗?
我们再继续假设,如果允许存放一行,且所有数据都是 10000 个字节,那就变成有多少行就有多少页了。

这种情况有啥问题呢?

我们要知道,MySQL 是以页作为磁盘和内存之间交互的基本单位的,也就是,一般情况下一次最少从磁盘中读取16KB的内容到内存中;
如果我们执行 select * from tbl, 连个筛选条件也不加,而且是读取全量字段,感觉一页存几条都没啥区别,就是可劲儿造呗;
但实际情况中,千万量级的表,不可能出现这么傻叉的查询逻辑,别问村长为啥,你自己试试就知道多么痛的领悟了。

通常来讲,大量级的表,我们查询数据,一般要设定筛选条件,也就是根据索引来缩小查询范围,以达到只取必要数据的目的;
而造成行溢出的,基本都是 text 或 超长的 varchar 字段,这类字段一般是不会被高频次查询的;
假如有一个学生表,里边有学生ID、名称、班级、专业等信息,还有一个兴趣爱好的字段,被设定为 text,且大家为了交朋友,都很踊跃填写,兴趣爱好都超过了 10000 个字节,但小于一页数据大小;

现在问题又来了:
MySQL 允许一页存一行数据,一共存了 2000 条学生记录,也就是 2000 页;
大领导来视察了,想查询学生基本信息,不用带上 兴趣爱好 描述啥的,领导没兴趣看,就学生自己玩儿的;
假设一次从磁盘读取16kb,也就是一页数据,那就得从磁盘读取 2000 次吧?
如果记录数扩展为 2000万 呢?那就得读取 2000万 次吧?这效率可想而知。。。
但如果限制了一页至少存两条记录呢?你会发现,读取次数直接降了一半誒。

这就是二分逻辑的神奇之处,类似经济学中的"荷塘效应", 假设池塘的荷叶一天增长一倍,那在在它铺满池塘之前的前一天,才只占了池塘的一半面积呢。

分析到这里,都有些心灵鸡汤的玄学味道了;
这个限制,大约就是为了提早分离长文本数据,以实现更高效的查询吧;

因为这个限制的存在,保证了极端情况的查询效率可以至少提升一倍

附:
行格式 小知识延伸
MySQL 中,若一张表里面不存在varchar、text以及其变形、blob以及其变形的字段的话,比如只有 int、char 字段,那么这张表其实也叫静态表,即该表的row_format是 FIXED;
其特点是:每条记录所占用的字节一样,读取快,但浪费额外一部分空间;
反之如果存在,这张表就叫动态表,即该表的row_format是 DYNAMIC(当然还包括上面提到的COMPRESSED、COMPACT等),就是说每条记录所占用的字节是动态的;
其特点是:节省空间,但增加读取的时间开销;
所以,具有频繁检索场景的表,一般都以空间换时间,将其设计成静态表


参考文档:
https://blog.csdn.net/weixin_39644325/article/details/110587235

作者:后厂村村长原文地址:https://segmentfault.com/a/1190000043851633

%s 个评论

要回复文章请先登录注册