数据库中大表DDL变更注意点及优化_大库 ddl 怎么做比较好-优快云博客

本文链接：https://blog.youkuaiyun.com/solihawk/article/details/145968742

应用系统中数据库的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执行的时间窗口，否则会出现数据不同步的情形。