详解数据库大表DDL变更的各种方案、优化策略及注意事项，助你提升数据库性能。
原文标题：数据库中大表DDL变更问题及优化
原文作者：牧羊人的方向
冷月清谈：
文章还特别提到了VARCHAR字段扩长的问题。由于VARCHAR字段存储需要长度前缀，当扩长导致长度前缀字节数增加时，MySQL会使用COPY算法重建表，耗时较长。而其他数据库如Oracle、PostgreSQL和GaussDB则不存在这个问题。
最后，文章总结了大表DDL变更的优化方案，包括：将修改字段类型操作转换为新增字段，通过后台迁移数据减少业务影响；将大表重建优化为数据迁移，分阶段进行以缩短变更窗口；评估大表的DDL变更时长，选择合适的变更方案；手动更新统计信息，避免SQL执行计划错误；注意DDL操作对主备同步的影响，避免数据延迟。
怜星夜思：
2、文章提到了VARCHAR字段扩长的问题，那么在实际应用中，如何避免这个问题呢？在设计数据库表结构时，应该如何选择VARCHAR的长度？
3、文章主要以MySQL为例，那么其他类型的数据库，例如Oracle、PostgreSQL等，在大表DDL变更方面有哪些最佳实践？
原文内容
应用系统中数据库的DDL变更操作是一个非常常见的场景，但是对于一些DDL变更的原理以及变更影响评估在理解上似乎有些不足。本文梳理DDL实现算法、Varchar字段扩长影响以及大表DDL变更的优化方案等，以加深理解。
1、数据库中的DDL变更
随着业务的发展，应用上线以后需要对数据库表进行添加字段、修改字段类型或者默认值等DDL变更操作。不同数据库在不同的DDL执行的时候有在线和非在线等类型，在线DDL指的是DDL执行过程中不影响业务的读写操作，非在线DDL则会堵塞业务的写操作。因此在数据库的DDL变更中，尽可能减少非在线DDL操作，或者将非在线转换为在线DDL、缩短业务影响窗口。本文主要以MySQL为例介绍数据库中DDL实现的算法。
MySQL数据库中DDL实现算法主要有Copy、Inplace和Instant三种，COPY算法是MySQL 5.5以及之前版本的默认算法，随着MySQL版本的迭代，之后的版本引入了Inplace和Instant算法，以减少DDL变更期间的业务影响。不过不是所有的DDL操作都能在线完成或者秒级完成，尤其是一些字段类型的DDL变更，而是默认情况下由数据库自己去选择哪种最优的算法实现。
COPY算法的基本原理是通过Server层创建临时表，将原表数据逐行复制到新表结构，最终替换原表。该过程会阻塞读以外的DML操作，且需要大量I/O和磁盘空间。因此使用COPY算法会锁表，属于非在线DDL。COPY算法执行流程如下：
-
准备阶段：
-
对表加DML元数据共享锁，读取元数据
-
升级DML元数据为排他锁，禁止其它写操作
-
按照原表定义创建一个新的临时表，生成对应ibd，frm文件
-
执行阶段
-
对临时表进行DDL，修改临时表元数据
-
将原表中的数据copy到临时表（最耗时）
-
将临时表重命名为原表
-
删除原表和文件
-
提交阶段：
-
释放原表的写锁
与COPY算法不同，INPLACE算法直接在原始表上进行修改，无需创建临时表和拷贝数据。另外copy是在Server层处理的，INPLACE是在innodb引擎层处理的。分为两种模式：
-
No-Rebuild：仅修改元数据（如添加二级索引），无需重建表，速度极快。
-
Rebuild：需重建表（如修改主键、增减列），期间生成临时排序文件，允许DML并发，但需记录变更日志（row log）并最终应用
Rebuild是在线完成的，只是对元数据锁的操作会短暂锁表，对业务基本无感。主要包括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算法仅修改数据字典中的元数据（如添加列到表末尾），无需拷贝数据或重建表，执行时间极短（毫秒级）。Instant算法是在MySQL 8.0.12及以上版本支持，适用于添加列、修改列默认值、重命名列等有限操作。
其它数据库中DDL的操作和MySQL类似，也分为在线DDL和非在线DDL，DDL的实现算法上也大致一致。比如OceanBase数据库的非在线DDL使用的是两表双写的方案。两表双写方案的思路是新建一张临时的隐藏表格（对用户不可见）用于双写，同时在后台将原表的数据补全到新建的临时表格中，然后将原表重命名为一个另外的临时表格，将补全数据的临时表格重命名为原表原来的名字，最后将原表的删除。在实现原理上实现其实跟MySQL的COPY方式实现原理非常的像。
MySQL 8.0以后版本在执行DDL时候使用DEFAULT，也就是由数据库自动选择资源占用最少得方式，优先选择Instant和Inplace，无法满足时使用COPY算法。MySQL默认对不同的DDL使用的算法如下：
比如修改字段类型，无法使用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-n日的窗口新建临时表并进行存量数据迁移，等到正式投产时候增量迁移当天的数据，并完成新旧表切换。这种方案可以避免大表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.csdn.net/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