千万级表数据添加字段ddl变更注意
Updated
•1 min read
这里仅讨论 MySQL8 的情况下在线变更表结构 Online 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;
这里即使语句中指定了某些算法，但这个语句在千万级数据量中的执行时间还是分钟级别以上且会全局锁表。
这个语句的执行会进行锁表，锁表对业务是灾难性的，如果是业务频繁读写，那请在业务低低谷期执行（执行时间受限于服务器性能及数据库所在磁盘空间）。
针对于大表，我们可以利用秒级加字段的特性将上述语句进行拆分。
-- 拆分语句 1
-- 秒加字段 338ms
ALTER TABLE `jeeinn_test` ADD COLUMN `update_time` DATETIME;
-- UPDATE `jeeinn_test` SET `update_time` = CURRENT_TIMESTAMP WHERE `update_time` IS NULL;
-- 拆分语句 2
-- 二次修改字段 大数据量下执行完毕需要数百秒起步
ALTER TABLE `jeeinn_test` MODIFY COLUMN `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
拆分的优势在于利用二次修改字段的默认值，在 InnoDB 引擎下不会进行表锁定，数据仍可进行读写，业务几乎无感知。
