mysql online ddl更改varchar长度的锁表情况

时间:2024-02-22 17:51:52

我们只看5.7及其之后的版本,官方文档:https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.html

mysql online ddl的特性是指,支持以instant(8.0版本开始支持)和in-place的方式修改表,同时允许在修改表的过程中,并发地进行dml操作。

mysql ddl操作中,修改varchar长度是一个比较常见的操作。我们需要关注的是修改varchar长度过程中是否是以instant或in-place的方式进行。

官方文档是这么描述的:

图片

图片

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:
ALTER TABLE tbl_name ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256); ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
.....
Decreasing VARCHAR size using in-place ALTER TABLE is not supported. Decreasing VARCHAR size requires a table copy (ALGORITHM=COPY).

由官方文档可梳理出下面几点:

1.对于varchar这种存储可变长度字符串的类型来说,需要使用1或2个额外字节记录字符串的长度:如果列的最大长度小于或等于255字节,则只需要使用1个字节表示,否则使用2个字节。
2.如果想要支持in-place的方式修改varchar的长度,varchar列的长度字节数需要保持不变。也就是说in-place的方式只支持varchar列的大小在0~255之间或者256~256+之间变动,不支持从小于等于255的值增大到255以上,比如varchar(20)-->varchar(500)。因为这个时候长度字节数从1变成了2。
3.当长度字节数发生改变时,仅支持以copy的方式进行。
4.缩减varchar长度,只能使用copy方法。

这里需要注意的是,这里我们提到的字节数并不等于字符数,也即varchar(n)中的n。

varchar实际占用的字节数会根据字符的编码和存储格式来计算VARCHAR列的实际字节数。例如,对于UTF-8编码的字符,utf8mb3占3个字节,utf8mb4占4个字节。

接下来我们实战验证一下。有张表的表结构如下:

 CREATE TABLE `t` (
  `d` datetime DEFAULT CURRENT_TIMESTAMP,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

表t的字符集是utf8mb4,所以name字段,varchar(20)所占字节数为20*4=80。

我们先试试使用inplace算法将varchar(20)改为varchar(63)。

图片

可见,20*4(80)-->63*4(252),255字节以内的变动,支持使用inplace。
再试试使用inplace算法将varchar(63)改为varchar(64):

图片

可以看到,63*4(252)-->64*4(256),这个时候不支持inplace,执行报错:ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

使用copy算法将n修改为64后,再将n修改为200(256-->800),则又支持inplace了,如下图所示:

图片

此外,减少varchar字段的长度,也不能使用inplace,需要使用copy算法。

图片

注:copy 模式会有持续性的锁(DDL 的整个过程期间无法向该表写入任何数据),要格外关注。

总结

  • 缩小字段长度不能使用inpalce,只能使用copy方式,会锁表。

  • 放大字段长度:in-place的方式只支持varchar列的字节数在0~255之间或者256~256+之间变动,不支持从小于等于255的值增大到255以上。以UTF8mb4编码为例,一个字符占4个字节。如:

    • varchar(20)--》varchar(63),inplace方式,不锁表。

    • varchar(64)--》varchar(120),inplace方式,不锁表。

    • varchar(20)--》vachar(64),copy方式,会锁表(20*4=80个字节,64*4=256字节);

参考:http://mysql.taobao.org/monthly/2021/03/06/
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.html
https://blog.csdn.net/yuanlairuci1992/article/details/126886422

图片

点个“赞 or 在看” 你最好看!

喜欢,就关注我吧!

图片