SQL 大表加字段的在线变更与默认值填充性能优化路径

2026年2月16日 星耀云

mysql 5.6前add column带default会全表拷贝锁表；5.7+仅null默认值支持instant，非空默认值仍copy；8.0.12+对短varchar等有限支持instant；pt-osc有触发器开销和外键风险，rds慎用。

SQL 大表加字段的在线变更与默认值填充性能优化路径

ALTER TABLE ADD COLUMN 时默认值导致锁表和慢查询

MySQL 5.6 之前，ALTER TABLE ADD COLUMN 带 DEFAULT 值会触发全表拷贝，加写锁、阻塞 DML，尤其在千万级以上大表上可能卡住数小时。5.7+ 引入了“instant DDL”机制，但仅对不带默认值或默认为 NULL 的列生效；一旦指定非空默认值（如 DEFAULT '0' 或 DEFAULT 1），仍会退化为 copy-alter。

实操建议：

  • 优先用 DEFAULT NULL 加字段，后续分批更新数据（避免单次 UPDATE 全表）
  • 若业务强依赖非空默认值，改用两阶段：先加 NULL 列 → 应用层/后台任务补值 → 再 ALTER TABLE ... SET DEFAULT ... + MODIFY COLUMN ... NOT NULL
  • 确认 MySQL 版本和存储引擎：InnoDB 在 8.0.12+ 支持部分非空默认值的 instant 操作（仅限 VARCHAR 等变长类型且长度 ≤ 255），但 INT、DATETIME 等仍走 copy

pt-online-schema-change 不是万能解药

pt-online-schema-change（pt-osc）确实能规避锁表，但它靠触发器同步增量变更，对高 QPS 写入场景有明显副作用：触发器开销叠加、主从延迟加剧、临时表膨胀快。当原表有大量 UPDATE 或 DELETE 时，容易出现 Cannot add or update a child row: a foreign key constraint fails 或 Lost connection to MySQL server during query。

实操建议：

  • 上线前用 --dry-run 和 --execute 分开跑，观察 SHOW PROCESSLIST 中触发器相关线程是否堆积
  • 避开业务高峰执行，同时监控 Threads_running 和从库 Seconds_Behind_Master
  • 若表有外键，必须加 --alter-foreign-keys-method=auto，否则 pt-osc 会拒绝执行（MySQL 5.7+ 默认禁用外键检查）
  • 不要在 RDS（如阿里云 PolarDB、AWS Aurora）上盲目套用——它们自带在线 DDL 优化，pt-osc 反而可能绕过原生加速路径

MySQL 8.0+ 的 ALGORITHM=INSTANT 实际限制

文档说 ALGORITHM=INSTANT 支持加字段，但真实约束比想象中多：ADD COLUMN 必须满足“不修改行格式、不触发二级索引重建、不改变聚集索引结构”。一旦字段带 NOT NULL DEFAULT、或类型是 TINYTEXT/MEDIUMTEXT、或表启用了 ROW_FORMAT=COMPRESSED，MySQL 会静默降级为 COPY 或 INPLACE，并不报错。

实操建议：

  • 执行后立刻查 performance_schema.table_lock_waits_summary_by_table 或慢日志，确认是否真走 instant（耗时应
  • 用 SELECT * FROM information_schema.INNODB_TABLES WHERE NAME LIKE '%your_table%' 对比执行前后 ROW_FORMAT 和 ZIP_PAGE_SIZE 是否变化
  • 生产环境加字段前，在同规格从库上先做一次 EXPLAIN FORMAT=JSON ALTER TABLE ...（MySQL 8.0.19+ 支持），看输出里 "alter_algorithm": "INSTANT" 是否存在

填充默认值的分批 UPDATE 比想象中更难控制节奏

很多人以为“先加 NULL 字段，再分页 UPDATE”就万事大吉，结果发现：单次 UPDATE ... LIMIT 10000 在无合适索引时仍是全表扫描；或者事务太大导致 binlog 膨胀、主从延迟飙升；更隐蔽的是，应用层缓存未失效，导致刚写入的默认值查不出来。

实操建议：

  • UPDATE 必须基于主键范围分片，例如 WHERE id BETWEEN 1000000 AND 1010000，而不是 LIMIT —— 后者在高并发写入下会跳过或重复
  • 每次 UPDATE 后加 SLEEP(0.1)（应用层控制），避免 IO 打满；同时设 innodb_lock_wait_timeout=3 防止长事务阻塞
  • UPDATE 完成后，主动清 Redis 缓存（按主键或业务维度），别依赖缓存自然过期
  • 如果字段要建索引，等所有数据填充完毕再建，否则索引维护会拖慢每一批 UPDATE

真正麻烦的从来不是加字段这一步，而是默认值怎么填得既快又稳——它牵扯到主从同步节奏、应用缓存策略、binlog 大小、甚至下游 CDC 组件的解析能力。漏掉其中一环，线上就容易出“值写了但查不到”这种低级又难定位的问题。