目录
问题场景
Online DDL 简介
场景复现
DBdoctor快速识别 Online DDL
总结
问题场景
在MySQL数据库中,DDL变更可以通过两种算法实现:Copy算法和In-Place算法。Copy算法会复制整个表,这可能导致长时间的写入阻塞,从而严重影响业务运行。相比之下,In-Place算法直接在现有表上进行结构修改,通常锁定时间较短,因此对业务的干扰也较小。由于In-Place算法几乎不需要锁定表,它通常被称为Online DDL。判断哪些SQL不支持Online DDL,成为DBA 面临的一大挑战。
大家看下面这条SQL在大表DDL变更时,会不会长时间阻塞写入?
alter table test_tb modify name varchar(128) default '' not null comment '创建人';
表test_tb 的charset为utf8mb4,字段name原类型为varchar(32)。官方文档中扩展varchar长度是支持Online DDL的,但是最近开发同学却遇到了问题。
某个版本上线一段时间后,发现一个表的两个varchar字段的长度不够用,开发紧急上线数据库脚本扩展字段长度,而在执行过程中,修改第一个字段很快就执行完了,修改第二个字段时执行时间较长,执行时间段内业务有告警写入失败,对应功能界面全部转圈,收到用户投诉。
数据库版本为MySQL 5.7,表结构如下:
create table k8s_auth_server.paas_role_list
(
id bigint not null primary key,
creator varchar(16) default '' not null comment '创建人',
role_name varchar(32) not null comment '角色英文名称',
...
)
comment '平台角色表' collate = utf8mb4_general_ci;
扩展字段长度SQL如下:
alter table k8s_auth_server.paas_role_list modify creator varchar(32) default '' not null
comment '创建人';
alter table k8s_auth_server.paas_role_list modify role_name varchar(128) not null comment'角色英文名称';
同样是修改字段长度,为什么修改字段creator很快,而修改字段role_name 特别慢呢?
Online DDL 简介
在MySQL 5.6 版本之前,所有的DDL变更都需要锁表,导致大量线程处于“Waiting for meta data lock”的状态,5.6版本引入Online DDL新特性,部分DDL 变更不需要锁表,引入算法In-Place和Copy(8.0增加Instant),其中 Instant,In-Place可以实现无锁变更。
-
Copy:建一张新表,并将表数据逐行从原始表复制到新表,复制阶段全程不允许并发DML。
-
In-Place:尽量避免复制表数据,但可能会在原地重建表。在操作的准备和执行阶段,可以短暂地对表进行独占元数据锁定。通常支持并发DML(全文索引和空间索引例外)。
-
Instant:操作仅修改数据字典中的元数据。在操作的执行阶段,可以短暂地对表进行独占元数据锁定。表数据不受影响,允许并发DML。
Copy算法和In-Place算法各阶段加锁情况对比如下,可以看到In-Place算法仅在开始和结束时短时间阻塞写入,而Copy算法在变更阶段全程阻塞写入。
Online DDL的变更原理这里不再详细描述,在MySQL 5.7 官方文档中,对于字段相关的Online DDL支持如下:
可以看到扩展varchar字段长度支持In-Place算法,但实际表现为什么和官方文档中不一致呢?
继续往下看,文档中对扩展varchar字段长度有额外说明:
-
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)
翻译下就是只有256 字节内的变更才支持In-Place算法,超过这个长度只能使用Copy算法,换言之需要锁表。
文档中有提示:
-
The byte length of a VARCHAR column is dependant on the byte length of the character set
也就是使用In-Place算法扩展varchar类型的长度限制,和字段的字符集有关。
mysql> show charset where Charset in ('latin1', 'utf8', 'utf8mb4');
+---------+----------------------+--------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+----------------------+--------------------+--------+
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
+---------+----------------------+--------------------+--------+
3 rows in set (0.00 sec)
常见的字符集utf8一个字符需要3个字节,utf8mb4需要4个字节,即 utf8 的varchar(86)及以上,utf8mb4 的varchar(64)及以上不支持In-Place算法,不支持 Online DDL。
场景复现
一张utf8mb4的测试表
create table testmb4
(
id bigint not null primary key,
creator varchar(16) default '' not null comment '创建人',
role_name varchar(32) not null comment '角色英文名称'
) charset = utf8mb4 collate = utf8mb4_general_ci;
变更字段 creator
mysql> alter table testmb4 modify creator varchar(32) default '' not null comment '创建人', ALGORITHM = INPLACE;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
变更字段 role_name
mysql> alter table testmb4 modify role_name varchar(128) default '' not null comment '角色英文名称', ALGORITHM = INPLACE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
果然,utf8mb4的字段将varchar(32)变至varchar(128)不再支持In-Place算法。
测试支持In-Place算法的边界:
mysql> alter table testmb4 modify role_name varchar(64) default '' not null comment '角色英文名称', ALGORITHM = INPLACE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
mysql> alter table testmb4 modify role_name varchar(63) default '' not null comment '角色英文名称', ALGORITHM = INPLACE;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
经验证,utf8mb4的varchar类型字段,变更长度大于等于64时,不再支持In-Place算法。
DBdoctor快速识别 Online DDL
Online DDL包含表、字段、索引等多个场景,DBA也很难全部识别。线上DDL变更更是个挑战。但是DBdoctor提供的免费SQL审核工具可以简化这一过程。只需将多条DDL变更的SQL放入审核窗口内,就可快速识别不支持Online DDL的SQL语句。
上传SQL
点击审核
审核详情中出现:DDL语句不支持Online DDL
总结
扩展varchar字段长度虽然支持In-Place算法,但是有一定限制,长度若大于等于256 byte则不支持Online DDL,utf8对应varchar(86),utf8mb4对应varchar(64)。面对Online DDL的众多场景,DBdoctor免费的SQL审核功能可以快速识别Online DDL,支撑线上DDL变更,有效预防锁表问题,欢迎小伙伴们下载体验!
*************************************************************************************************************
免*费下载,一键部署:DBdoctor-数据库性能诊断