MySQL亿级大表安全添加字段实战指南

一、准备工作

1. 评估表现状

• 统计表行数、数据量大小
• 检查当前索引情况
• 分析表碎片率
• 确认当前负载情况

1. 选择合适的时间窗口

• 低峰期执行（通常凌晨2-5点）
• 避开业务高峰期和重要活动期

二、添加字段的几种方法及对比

方法	优点	缺点	适用场景
直接ALTER TABLE	简单直接	锁表时间长，影响业务	小表或可接受长时间停机的场景
在线DDL工具(pt-online-schema-change)	几乎不影响业务	执行时间长，需要额外空间	大表且要求业务连续性
影子表+数据迁移	完全可控	实现复杂，需要应用配合	超大表且变更复杂的情况

三、推荐方案：pt-online-schema-change实战

1. 安装Percona工具包

sudo yum install percona-toolkit

2. 基本用法

pt-online-schema-change \
  --alter "ADD COLUMN new_column INT COMMENT '新字段'" \
  D=database,t=table \
  --execute

3. 关键参数优化

pt-online-schema-change \
  --alter "ADD COLUMN new_column INT COMMENT '新字段'" \
  D=database,t=table \
  --chunk-size=1000 \
  --critical-load="Threads_running=50" \
  --max-load="Threads_running=20" \
  --max-lag=5 \
  --check-interval=1 \
  --no-drop-old-table \
  --execute

4. 监控执行进度

# 查看数据拷贝进度
SELECT * FROM `_table_new` WHERE 1=0;

# 查看复制延迟
SHOW SLAVE STATUS\G

四、超大表的特殊处理

1. 分批次执行

• 先添加字段设置为NULL
• 后续再分批更新字段值

1. 垂直拆分考虑

• 评估是否更适合拆分成新表
• 使用外键关联而非直接添加字段

1. 使用触发器同步

   CREATE TRIGGER sync_new_column AFTER INSERT ON original_table
   FOR EACH ROW INSERT INTO new_table (id, new_column) VALUES (NEW.id, NULL);

五、风险评估与回滚方案

1. 风险评估清单

• 磁盘空间是否充足（需要1倍额外空间）
• 主从复制是否正常
• 是否有长事务运行

1. 回滚方案

   # 停止pt-osc进程
   # 删除临时表
   DROP TABLE IF EXISTS _table_new;
   # 恢复触发器（如果使用了）
   DROP TRIGGER IF EXISTS sync_new_column;

六、PostgreSQL的替代方案

对于使用PostgreSQL的用户，可以考虑：

-- PostgreSQL 11+ 快速添加列（只更新元数据）
ALTER TABLE large_table ADD COLUMN new_column int DEFAULT NULL;

七、最佳实践总结

1. 提前在测试环境验证
2. 做好完整备份
3. 监控系统资源使用情况
4. 准备应急预案
5. 变更后立即验证数据一致性

八、性能优化建议

1. 添加字段后，评估是否需要新增索引
2. 考虑字段的默认值对性能的影响
3. 对于枚举类型，使用TINYINT而非VARCHAR
4. 避免在大表上频繁添加字段，考虑设计优化

通过以上方法，可以在最小化业务影响的前提下，安全地为亿级MySQL大表添加字段。