应用系统中数据库的DDL变更操作是一个非常常见的场景,但是对于一些DDL变更的原理以及变更影响评估在理解上似乎有些不足。本文梳理DDL实现算法、Varchar字段扩长影响以及大表DDL变更的优化方案等,以加深理解。
1、数据库中的DDL变更
随着业务的发展,应用上线以后需要对数据库表进行添加字段、修改字段类型或者默认值等DDL变更操作。不同数据库在不同的DDL执行的时候有在线和非在线等类型,在线DDL指的是DDL执行过程中不影响业务的读写操作,非在线DDL则会堵塞业务的写操作。因此在数据库的DDL变更中,尽可能减少非在线DDL操作,或者将非在线转换为在线DDL、缩短业务影响窗口。本文主要以MySQL为例介绍数据库中DDL实现的算法。
1.1 MySQL数据库DDL实现算法
MySQL数据库中DDL实现算法主要有Copy、Inplace和Instant三种,COPY算法是MySQL 5.5以及之前版本的默认算法,随着MySQL版本的迭代,之后的版本引入了Inplace和Instant算法,以减少DDL变更期间的业务影响。不过不是所有的DDL操作都能在线完成或者秒级完成,尤其是一些字段类型的DDL变更,而是默认情况下由数据库自己去选择哪种最优的算法实现。
1.1.1 COPY算法
COPY算法的基本原理是通过Server层创建临时表,将原表数据逐行复制到新表结构,最终替换原表。该过程会阻塞读以外的DML操作,且需要大量I/O和磁盘空间。因此使用COPY算法会锁表,属于非在线DDL。COPY算法执行流程如下:
- 准备阶段:
- 对表加DML元数据共享锁,读取元数据
- 升级DML元数据为排他锁,禁止其它写操作
- 按照原表定义创建一个新的临时表,生成对应ibd,frm文件
- 执行阶段
- 对临时表进行DDL,修改临时表元数据
- 将原表中的数据copy到临时表(最耗时)
- 将临时表重命名为原表
- 删除原表和文件
- 提交阶段:
- 释放原表的写锁
1.1.2 Inplace算法
与COPY算法不同,INPLACE算法直接在原始表上进行修改,无需创建临时表和拷贝数据。另外copy是在Server层处理的,INPLACE是在innodb引擎层处理的。分为两种模式:
- No-Rebuild:仅修改元数据(如添加二级索引),无需重建表,速度极快。
- Rebuild:需重建表(如修改主键、增减列),期间生成临时排序文件,允许DML并发,但需记录变更日志(row log)并最终应用
Rebuild是在线完成的,只是对元数据锁的操作会短暂锁表,对业务基本无感。Online ddl主要包括3个阶段:prepare阶段,ddl执行阶段,commit阶段,rebuild方式比no-rebuild方式实质多了一个ddl执行阶段,prepare阶段和commit阶段类似。
- Prepare阶段:
- 创建新的临时frm文件
- 持有EXCLUSIVE-MDL锁,禁止读写
- 根据alter类型,确定执行方式(copy,online-rebuild,online-norebuild)
- 更新数据字典的内存对象
- 分配row_log对象记录增量(仅rebuild类型需要)
- 生成新的临时ibd文件(仅rebuild类型需要)
- ddl执行阶段:
- 降级EXCLUSIVE-MDL锁,允许读写
- 扫描old_table的聚集索引每一条记录rec
- 遍历新表的聚集索引和二级索引,逐一处理
- 根据rec构造对应的索引项
- 将构造索引项插入sort_buffer块
- 将sort_buffer块插入新的索引
- 处理ddl执行过程中产生的增量(仅rebuild类型需要)
- 重放row_log中的操作到新索引上(no-rebuild数据是在原表上更新的)
- 重放row_log间产生dml操作append到row_log最后一个Block
- commit阶段
- 升级到EXCLUSIVE-MDL锁,禁止读写
- 重做row_log中最后一部分增量
- 更新innodb的数据字典表
- 提交事务(刷事务的redo日志)
- 修改统计信息
- rename临时idb文件,frm文件
比如修改字段类型,无法使用Instant和Inplace算法,只能使用COPY算法实现,这是个非在线DDL实现过程。
2、Varchar字段扩长的问题
在MySQL中VARCHAR 字段的存储需要 长度前缀(Length Prefix) 来记录实际数据的字节数。长度前缀占用的字节数由字段的最大允许长度决定:
- 1字节:当VARCHAR字段的最大可能字节数≤ 255时(例如VARCHAR(63)使用utf8mb4字符集,每个字符最多占4字节,总字节数为63 × 4 = 252)。
- 2字节:当最大可能字节数>255 时(例如 VARCHAR(64) 使用 utf8mb4,总字节数为 64 × 4 = 256)。
而由于数据库默认的字符集是utf8mb4,每个字符最多占4个字节:ASCII字符(英文字母、数字等)占1字节、希腊字母等占2个字节、常用汉字等占3个字节、一些表情符和生僻汉字等占4个字节。Varchar字段的临界长度=255/字符集最大字节数,对于字符集为utf8mb4,临界值为255 / 4 = 63.75,即63字符是临界点。所以当Varchar字段扩长时,有两种情况:
- 若修改字段长度后,长度前缀的字节数未变化(例如从50改到63),仅需更新元数据,无需重建表数据。
- 若修改字段长度后,长度前缀的字节数增加(例如从 63 改到 64,导致长度前缀从1字节变为2字节),则需使用上文中的COPY算法重建表数据,导致DDL操作耗时变长。
因此,基于MySQL数据库内核二次开发的信创数据库如GoldenDB、TDSQL(for MySQL)、GreatSQL等,如果这一块在内核层面没有优化,还是存在这部分问题的。其它类型的数据库如Oracle、PostgreSQL和GaussDB等,在varchar扩长时不会出现存储格式的变化,只需要更新元数据,秒级完成。
3、数据库大表DDL变更优化
目前大多数据库在字段类型修改等场景还不支持在线DDL,即使是在线DDL操作,如果涉及到大表重建,整个DDL变更窗口也是相当长的。那么在DDL变更过程中,有哪些优化点或注意地方?
1)修改字段类型变成新增字段
直接在表中新增字段,有两个方向:一是后续的业务直接在这个新增的字段中操作,从开发的角度来说无疑增加了程序处理的难度,旧有的字段数据访问需要程序逻辑加以控制;另一种是新建临时字段后在后台将原有字段数据迁移到新字段,并且在过渡阶段应用同时更新新旧字段,等旧字段数据迁移完成后,在停机窗口进行新旧字段的切换。无论哪一种,都增加了应用处理上的复杂度。
2)大表重建优化为数据迁移的过程
这是大表在非在线DDL或者DDL耗时过长时候的一个优化方向,基本思路是在T-3日的窗口新建临时表并进行存量数据迁移,等到正式投产时候增量迁移当天的数据,并完成新旧表切换。这种方案可以避免大表DDL变更带来的变更窗口问题,风险点在于实现一整套数据迁移的流程,尤其是要完成数据的校验和核对,不能出现变更前后数据不一致的情况。
3)评估大表的DDL变更时长
确认了DDL变更的类型后,根据不同数据库能够明确是在线还是非在线,是否有表重建或build索引的操作,然后根据表大小来评估DDL变更的时长。通常在使用COPY算法重建表数据时,效率为1~1.5G/1min,表重建完成后还要基于表来build索引,效率约为0.5G/1min,而索引越多,build索引阶段耗时越长。
变更类型 | 在线 | 重建表 | 数据页 | 索引页 | 评估耗时 |
---|---|---|---|---|---|
新增字段 | 是 | 否 | 100G | 20G | <1s |
字段类型修改 | 否 | 是 | 100G | 20G | 140min |
所以在评估大表DDL变更时长,最好在演练环境先进行性能测试以做到时间预期,这个评估时长也决定了应用DDL变更的业务影响窗口,如果窗口有明确限制,比如不能超过2小时,则需要考虑其他方案,比如新建表在线迁移的方案。从这里也看到分布式数据库的其中一个优点,就是实现分库分表的操作,单个库中表尽量小,这样在DDL操作的时候利用分布式的并发特性,也可以缩短整体的变更时长。
4)统计信息的更新
在MySQL数据库中当DDL使用COPY算法时候,可能会遇到分区表统计信息丢失的bug(Bug #113750),如果在做完DDL时候没有注意到这一点,也可能会出现统计信息不准确,进而影响到SQL的执行计划导致业务处理效率变慢。MySQL系的数据库中如果没有专门针对这个问题优化,都会有潜在的影响的。
当大表在做完DDL后,尤其是涉及到表重建的操作,遇到统计信息可能发生了变化,最安全的操作是手动执行一个统计信息收集的操作。比如MySQL系数据库中的Analyze table,这个也是秒级完成的。
5)DDL操作过程的影响
DDL操作像非在线DDL会锁表影响写操作、涉及到表重建的DDL会有大IO操作,因此尽量在业务低峰期执行DDL、将非在线DDL转换为在线DDL(在线DDL工具pt-osc/gh-ost等)、大表的DDL使用分阶段数据迁移方案等。同时需要注意的是DDL操作是一个事务的原子性操作,当DDL事务未执行完成,备机是无法同步这个事务,因此在DDL执行过程中会出现备机同步阻塞导致主备之间出现数据延迟。对于一些有需要使用到备机抽数的场景,错开DDL执行的时间窗口,否则会出现数据不同步的情形。
参考资料:
- https://blog.youkuaiyun.com/OceanBaseGFBK/article/details/138126035
- https://opensource.actionsky.com/20190517-mysql-ddl/
- https://bugs.mysql.com/bug.php?id=113750
- InnoDB在线DDL实现机制
- https://zhuanlan.zhihu.com/p/567083306