麒麟v10 上部署 TiDB v5.1.2 生产环境优化实践
1274
2023-04-22
面试官:MySQL 中 varchar(n) 中 n 最大取值为多少?
大家好,我是小林。
上周发了一篇字节一面:MySQL 的 NULL 值是怎么存放的?,文章里面有提及这个问题:「varchar(n) 中 n 最大取值为多少?」
当时这部分内容写的不够严谨,所以我重写了这部分内容。
所以,这次就聊聊这个问题。
前置知识
要回答这个问题,首先我们得先知道 MySQL 存储一条记录的格式长什么样子。
以 Compact 行格式作为例子,它长这样:
可以看到,一条完整的记录分为「记录的额外信息」和「记录的真实数据」两个部分。
这里重点讲讲记录的额外信息,它包含 3 个部分:变长字段长度列表、NULL 值列表、记录头信息。
变长字段长度列表
用于存储一行记录中每个变长字段的长度。
「变长字段长度列表」所占用的字节数 = 所有「变长字段长度」占用的字节数之和。
举个例子,假设数据库表中有 2 个 varchar(10) 类型的字段,分别为 a 和 b,且数据库表的字符集为 ascii 字符集(1 个字符占用 1 字节)。
那么a和b字段的数据值的长度分别只需要用1字节表示就行了,因为1字节能表示最大的字节数是 255,而 varchar(10) 类型的字段最大允许存储的字节数是 10 字节,所以只需要用 1 字节表示变长字段的长度就行。
那么这种情况下的 「变长字段长度列表」所占用的字节数 = 1 字节 + 1字节 = 2 字节。
「变长字段长度列表」不是必须的,如果数据库表没有变长字段,比如字段类型都是int,那么行格式中就不需要「变长字段长度列表」。
NULL 值列表
用于标记一行记录中字段值为 NULL 的字段,二进制位的值为 1 时,代表该字段的值为NULL,二进制位的值为 0 时,代表该字段的值不为 NULL。
另外,NULL 值列表必须用整数个字节的位表示(1字节8位),如果使用的二进制位个数不足整数个字节,则在字节的高位补 0。
如果表中允许为 NULL 值的记录的个数小于等于 8 个,那么 NULL 值列表就会用 1 字节表示。
如果如果表中允许为 NULL 值的记录的个数大于8 并且小于等于 16,那么 NULL 值列表就会用 2 字节表示,以此类推。
因此,如果表中有字段允许为 NULL,那么「NULL 值列表」至少占用 1 字节空间。
「NULL 值列表」不是必须的,如果数据库表中的字段都定义成 NOT NULL,那么行格式中就不需要「NULL 值列表」。
记录头信息
记录头信息中包含的内容很多,比如记录的删除标记位,指向下一条记录的指针等等,不是本文问题的重点,所以我就不细讲了。
varchar(n) 中 n 最大取值为多少?
我们要清楚一点,MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外,其他所有的列(不包括隐藏列和记录头信息)占用的字节长度加起来不能超过 65535 个字节。
也就是说,一行记录除了 TEXT、BLOBs 类型的列,限制最大为 65535 字节,注意是一行的总长度,不是一列。
知道了这个前提之后,我们再来看看这个问题:「varchar(n) 中 n 最大取值为多少?」
varchar(n) 字段类型的 n 代表的是最多存储的字符数量,并不是字节大小哦。
要算 varchar(n) 最大能允许存储的字节数,还要看数据库表的字符集,因为字符集代表着,1个字符要占用多少字节。
比如 ascii 字符集, 1 个字符占用 1 字节,那么 varchar(100) 意味着最大能允许存储 100 字节的数据。
单字段的情况
前面我们知道了,一行记录最大只能存储 65535 字节的数据。
那假设数据库表只有一个 varchar(n) 类型的列且字符集是 ascii,在这种情况下, varchar(n) 中 n 最大取值是 65535 吗?
不着急说结论,我们先来做个实验验证一下。
我们定义一个 varchar(65535) 类型的字段,字符集为 ascii 的数据库表。
CREATE TABLE test ( `name` VARCHAR(65535) NULL) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;
看能不能成功创建一张表:
结果显示,创建失败了。
从报错信息就可以知道一行数据的最大字节数是 65535(不包含 TEXT、BLOBs 这种大对象类型),其中包含了 storage overhead。
问题来了,这个 storage overhead 是什么呢?其实就是「变长字段长度列表」和 「NULL 值列表」。
也就是说一行数据的最大字节数 65535,其实是包含「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。
所以, 我们在算 varchar(n) 中 n 最大值时,需要减去 storage overhead 占用的字节数。
这是因为我们存储字段类型为 varchar(n) 的数据时,其实分成了三个部分来存储:
真实数据真实数据占用的字节数NULL 标识,如果不允许为NULL,这部分不需要
本次案例中,「NULL 值列表」所占用的字节数是多少?
前面我创建表的时候,字段是允许为 NULL 的,所以会用 1 字节来表示「NULL 值列表」。
本次案例中,「变长字段长度列表」所占用的字节数是多少?
「变长字段长度列表」所占用的字节数 = 所有「变长字段长度」占用的字节数之和。
所以,我们要先知道每个变长字段的「变长字段长度」需要用多少字节表示?具体情况分为:
条件一:如果变长字段允许存储的最大字节数小于等于 255 字节,就会用 1 字节表示「变长字段长度」;条件二:如果变长字段允许存储的最大字节数大于 255 字节,就会用 2 字节表示「变长字段长度」;
我们这里字段类型是 varchar(65535) ,字符集是 ascii,所以代表着变长字段允许存储的最大字节数是 65535,符合条件二,所以会用 2 字节来表示「变长字段长度」。
因为我们这个案例是只有 1 个变长字段,所以「变长字段长度列表」= 1 个「变长字段长度」占用的字节数,也就是 2 字节。
因为我们在算 varchar(n) 中 n 最大值时,需要减去 「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。
所以,在数据库表只有一个 varchar(n) 字段且字符集是 ascii 的情况下,varchar(n) 中 n 最大值 = 65535 - 2 - 1 = 65532。
我们先来测试看看 varchar(65533) 是否可行?
可以看到,还是不行,接下来看看 varchar(65532) 是否可行?
可以看到,创建成功了。说明我们的推论是正确的,在算 varchar(n) 中 n 最大值时,需要减去 「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。
当然,我上面这个例子是针对字符集为 ascii 情况,如果采用的是 UTF-8,varchar(n) 最多能存储的数据计算方式就不一样了:
在 UTF-8 字符集下,一个字符串最多需要三个字节,varchar(n) 的 n 最大取值就是 65532/3 = 21844。
上面所说的只是针对于一个字段的计算方式。
多字段的情况
如果有多个字段的话,要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535。
这里举个多字段的情况的例子。
实验结果:
总结
varchar(n) 中 n 最大取值为多少?
一行记录最大能存储 65535 字节的数据,但是这个是包含「变长字段字节数列表所占用的字节数」和「NULL值列表所占用的字节数」。所以, 我们在算 varchar(n) 中 n 最大值时,需要减去这两个列表所占用的字节数。
如果一张表只有一个 varchar(n) 字段,且允许为 NULL,字符集为 ascii。varchar(n) 中 n 最大取值为 65532。
计算公式:65535 - 变长字段字节数列表所占用的字节数 - NULL值列表所占用的字节数 = 65535 - 2 - 1 = 65532。
如果有多个字段的话,要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535。
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。