导读
本文将从Innodb存储结构来分析 varchar 字段长度变化在哪些情况使用哪种online DDL算法
注: 本文提到的online DDL是指官方实现的, 而非使用pt-osc,gh-ost之类的第三方软件或者自定义脚本来是实现(基于binlog来实现的话, 限制就少很多了…)
对于减小字段长度, 顶多就只能使用copy算法的,甚至会报错 ERROR 1406 (22001): Data too long for column 'name' at row 1, 所以本文主要探讨扩展字段长度的情况.
online ddl算法
先来简单介绍下官方的几种online ddl算法吧.
- copy copy就是创建一张临时表来变更. 5.5版本ddl期间不能执行dml, 5.7版本可以执行dml()– example ALTER TABLE tbl_name DROP PRIMARY KEY, ALGORITHM=COPY;
- inplace inplace算法就是在现有表的基础上直接修改,不会产生额外表空间/数据文件.(相对经济实惠的), 有些情况会重建表(表空间内重建, 不需要额外的空间)
- instant 看名字就知道这个算法最快–立即. 因为它只修改元数据信息, 这个算法只能在特定条件下才支持, 比如新增字段, 删除字段之类的. 本文讲的varchar长度修改就不支持. 原因后面再讲.
先看官方结论
官方给出的是 增加字段长度(Extending VARCHAR column size), 无法只修改元数据信息(instant, 立即), 可以原地修改(in place)
注: instant算法是8.0.29之后默认优先选择的算法. 之前默认是inplace
而在给出例子的时候, 还写了一大段话.
The number of length bytes required by a VARCHAR column must remain the same. For VARCHAR columns of 0 to 255 bytes in size, one length byte is required to encode the value. For VARCHAR columns of 256 bytes in size or more, two length bytes are required. As a result, in-place ALTER TABLE only supports increasing VARCHAR column size from 0 to 255 bytes, or from 256 bytes to a greater size. In-place ALTER TABLE does not support increasing the size of a VARCHAR column from less than 256 bytes to a size equal to or greater than 256 bytes. In this case, the number of required length bytes changes from 1 to 2, which is only supported by a table copy (ALGORITHM=COPY). For example, attempting to change VARCHAR column size for a single byte character set from VARCHAR(255) to VARCHAR(256) using in-place ALTER TABLE returns this error: ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
大意就是varchar如果是从255字节以内的修改到256字节及其以上的时候, 就不能使用inplace算法了,只能使用copy算法(期间可以执行DML). 那么为啥呢? 先来验证下吧.
验证
创建测试表
create table t20241121(id int primary key, name varchar(62)) character set utf8mb4;
insert into t20241121 values(1,'ddcw');
insert into t20241121 values(2,'https://github.com/ddcw');
alter table t20241121 modify column name varchar(63); -- 256以内,
alter table t20241121 modify column name varchar(64); -- 255以内变到256及其以上
alter table t20241121 modify column name varchar(65); -- 256以上增加
验证了确实是 从255以内飞升到256及其以上时会使用copy算法( 2 rows affected)
当然也可以直接指定inplace算法,会看到如下报错 ERROR 1846 (0A000), 这也能说明突破255极限时只能使用copy
分析
在回答这个问题之前呢, 我们先来看看数据字段的存储格式, 大概如下样子
我们主要关注记录变量长度的那个字段. 每个可变长度的字段使用 1-2字节来表示, 能表示的范围就能达到0-64K.
那么什么时候使用1字节来表示, 什么时候使用2字节来表示呢?
假设1:
第1字节达到255时,再读取第2字节: 这样的话, 0-255的时候能正常表示(使用1字节), 但超过255字节的部分就只能使用1字节来表示了, 也就是 255+255, 即表示范围为0-512 不符合我们对varchar的认识.
假设2
如果要使范围达到64K的话, 那就还是得有高位来表示, 即255*255 , 由于是从右往左读取的, 所以右边得是高位. 也就是2位255进制来表示. 这样0-64k都能表示了. 比如 ‘0x01 0x02’ 就能表示 1+255*2, 但这样的话, 就永远使用2字节了, 对于开发写的 uuid varchar(500) 这种会浪费会多空间.
假设3
在假设2的基础上优化一下, 当第1字节达到128时,才使用2字节表示. 超过128的部分表示高位(2的7次方), 第2字节表示低位. 那么当小于128字节时候就只使用1字节, 大于等于128时才使用2字节. 能表示的范围就算0-128*256 (简单点说就是使用第1字节的第1bit来判断)
比如:
1 表示1
127 表示127
233 128 就表示 (128-128)*256+233 = 233
233 129 就表示 (129-128)*256+255 = 510
看起来没少问题了, 但…
这和我们知道的varchar支持65535字节不符啊
额外补充
别忘了我们一个PAGE只有16KB啊(不考虑PAGE_SIZE!=16K的情况), 所以这32KB足够了. 超过的部分就是溢出页了(和blob类似, 其实blob能存储在一个页的时候就是放在页内的, innodb不区分char,varchar,blob之类的. 都会记录其长度)
总结和其它
开头还留了一个问题: 为啥修改字段长度不支持instant算法?
我们上面讲的都是varchar超过255字节的情况, 那么如果varchar是255字节以内的呢? 使用1字节来表示完全足够的. 但由于innodb为了节省空间采用了上面的假设3. 就需要知道元varchar能否超过255字节. 也就是根据元数据信息来决定是读1字节, 还是1-2字节的长度. 如果只修改元数据信息的话, 对于之前存储的数据就可能有问题. 所以修改字段长度就无法使用instant算法.
其实可以改进为: 255字节以内支持, 256字节及其以上也支持, 就是不能跨这范围.否则再走instant算法就不错了.
缩小范围就不要想了, 不可能!
简单总结下吧: 255字节以内或者256字节以外字段扩展长度支持inplace算法, 其它情况的字段长度修改均只能使用copy算法.
innodb的存储设计主要是考虑的空间问题. 其实数据库存储基本上都是这么考虑的, 主要是存储贵. 如果现在设计一款数据库的话, 存储方面可能就不会考虑那么细了,性能和可扩展性估计会优先级高点.
假设3的验证
这一步非常枯燥,不感兴趣的建议跳过.
-- 数据准备
create table t20241121_3(id int primary key, name varchar(1000));
insert into t20241121_3 values(1,repeat('x',1));
insert into t20241121_3 values(2,repeat('x',127));
insert into t20241121_3 values(3,repeat('x',233));
insert into t20241121_3 values(4,repeat('x',510));
我们只有这么几行数据, 所以肯定是放在第4页的. 我就直接使用python验证了.
第一行数据
import struct
f = open('/data/mysql_3314/mysqldata/db1/t20241121_3.ibd','rb')
f.seek(4*16384,0)
data = f.read(16384)
offset = 99
first_offset = offset + struct.unpack('>h',data[offset-2:offset])[0]
rec_header = data[first_offset-5:first_offset]
nullable = data[first_offset-5-1:first_offset-5]
var = data[first_offset-5-1-2:first_offset-5-1]
我们得到变量长度是1字节(\x01小于128所以就使用1字节). 符合预期
第二行数据
second_offset = first_offset + struct.unpack('>h',data[first_offset-2:first_offset])[0]
rec_header = data[second_offset-5:second_offset]
nullable = data[second_offset-5-1:second_offset-5]
var = data[second_offset-5-1-2:second_offset-5-1]
我们得到变量长度是127(127小于128) 符合预期.
第三行数据
third_offset = second_offset + struct.unpack('>h',data[second_offset-2:second_offset])[0]
rec_header = data[third_offset-5:third_offset]
nullable = data[third_offset-5-1:third_offset-5]
var = data[third_offset-5-1-2:third_offset-5-1]
我们得到第1字节(右边)是128, 所以还要读1字节, 即233+(128-128)*256 = 233 符合预期
第4行数据
fourth_offset = third_offset + struct.unpack('>h',data[third_offset-2:third_offset])[0]
rec_header = data[fourth_offset-5:fourth_offset]
nullable = data[fourth_offset-5-1:fourth_offset-5]
var = data[fourth_offset-5-1-2:fourth_offset-5-1]
我们得到第1字节为129, 所以还要读1字节. 即254+(129-128)*256 = 510 符合预期.
说明我们对于varchar的各种长度存储是猜测正确的, 假设3成立.
溢出页的情况就不验证了, 太复杂了.
参考:
https://dev.mysql.com/doc/refman/8.0/en/char.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html