千万级表数据添加字段DDL变更注意

这里仅讨论 MySQL8 的情况下在线变更表结构DDL

当需要给千万级数据量的数据表添加列字段时，普通默认值是不需要担心的。MySQL8已经对新增字段有优化，支持快速加列，可以实现大表秒级加字段，会自动判断加字段时的算法 ALGORITHM=INSTANT （MySQL 8.0.12）。

当然这里会有一些限制

• 如果 ALTER 语句包含了 ADD COLUMN 和其他的操作，其中有操作不支持 INSTANT 算法的，那么 ALTER 语句会报错，所有的操作都不会执行
• 只能顺序加列, 仅支持在最后添加列，而不支持在现有列的中间添加列
• 不支持压缩表，即该表行格式不能是 COMPRESSED
• 不支持包含全文索引的表
• 不支持临时表
• 不支持那些在数据字典表空间中创建的表

那如果有些需要其他语句的该如何操作呢，这里举一个特殊场景的例子：

要帮一张大表添加一个 更新时间 的字段，且默认值为 当前时间，要求MySQL自动维护更新时间。语句如下

-- SELECT VERSION()

ALTER TABLE `jeeinn_test` ADD COLUMN `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- ALGORITHM = INSTANT,
-- ALGORITHM = INPLACE,
LOCK = NONE;

即使语句中指定了某些算法，但这个语句在千万级数据量中的执行时间还是分钟级别以上且会全局锁表。这里的锁表对业务是灾难性的，如果是业务频繁读写，那请在业务低低谷期执行（受限于服务器性能及数据库所在磁盘空间）。

针对于大表，我们可以利用秒级加字段的特性将上述语句进行拆分。

-- 秒加字段 0.3s
ALTER TABLE `jeeinn_test` ADD COLUMN `update_time` DATETIME;

-- UPDATE `jeeinn_test` SET `update_time` = CURRENT_TIMESTAMP WHERE `update_time` IS NULL;
-- 二次修改字段 大数据量下数百秒起步
ALTER TABLE `jeeinn_test` MODIFY COLUMN `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

拆分的优势在于利用二次修改字段的默认值，在 InnoDB 引擎下不会进行表锁定，数据仍可进行读写，业务几乎无感知。