千万级的大表如何新增字段？

前言

线上千万级的大表在新增字段的时候，一定要小心，我见过太多团队在千万级大表上执行DDL时翻车的案例。
很容易影响到正常用户的使用。
本文将深入剖析大表加字段的核心难点，并给出可落地的解决方案。
希望对你会有所帮助。
1.为什么大表加字段如此危险？

核心问题：MySQL的DDL操作会锁表。
当执行ALTER TABLE ADD COLUMN时：

• MySQL 5.6之前：全程锁表（阻塞所有读写）
  
• MySQL 5.6+：仅支持部分操作的Online DDL
  

通过实验验证锁表现象：

1. -- 会话1：执行DDL操作
2. ALTER TABLE user ADD COLUMN age INT;
4. -- 会话2：尝试查询（被阻塞）
5. SELECT * FROM user WHERE id=1; -- 等待DDL完成

复制代码

锁表时间计算公式：

1. 锁表时间 ≈ 表数据量 / 磁盘IO速度

复制代码

对于1000万行、单行1KB的表，机械磁盘（100MB/s）需要100秒的不可用时间！
如果在一个高并发的系统中，这个问题简直无法忍受。
那么，我们要如何解决问题呢？

2.原生Online DDL方案

在MySQL 5.6+版本中可以使用原生Online DDL的语法。
例如：

1. ALTER TABLE user
2. ADD COLUMN age INT,
3. ALGORITHM=INPLACE,
4. LOCK=NONE;

复制代码

实现原理：

致命缺陷：

• 仍可能触发表锁（如添加全文索引）
  
• 磁盘空间需双倍（实测500GB表需要1TB空闲空间）
  
• 主从延迟风险（从库单线程回放）
  

3.停机维护方案

适用场景：

• 允许停服时间（如凌晨3点）
  
• 数据量小于100GB（减少导入时间）
  
• 有完整回滚预案
  

4.使用PT-OSC工具方案

Percona Toolkit的pt-online-schema-change这个是我比较推荐的工具。
工作原理：

操作步骤：

1. # 安装工具
2. sudo yum install percona-toolkit
4. # 执行迁移（添加age字段）
5. pt-online-schema-change \
6. --alter "ADD COLUMN age INT" \
7. D=test,t=user \
8. --execute

复制代码

5.逻辑迁移 + 双写方案

还有一个金融级安全的方案是：逻辑迁移 + 双写方案。
适用场景：

• 字段变更伴随业务逻辑修改（如字段类型变更）
  
• 要求零数据丢失的金融场景
  
• 超10亿行数据的表
  

实施步骤：
1. 创建新表结构

1. -- 创建包含新字段的副本表
2. CREATE TABLE user_new (
3.     id BIGINT PRIMARY KEY,
4.     name VARCHAR(50),
5.     -- 新增字段
6.     age INT DEFAULT 0,
7.     -- 增加原表索引
8.     KEY idx_name(name)
9. ) ENGINE=InnoDB;

复制代码

2. 双写逻辑实现（Java示例）

1. // 数据写入服务
2. public class UserService {
3.     @Transactional
4.     public void addUser(User user) {
5.         // 写入原表
6.         userOldDAO.insert(user);
7.         // 写入新表（包含age字段）
8.         userNewDAO.insert(convertToNew(user));
9.     }
10.    
11.     private UserNew convertToNew(User old) {
12.         UserNew userNew = new UserNew();
13.         userNew.setId(old.getId());
14.         userNew.setName(old.getName());
15.         // 新字段处理（从其他系统获取或默认值）
16.         userNew.setAge(getAgeFromCache(old.getId()));
17.         return userNew;
18.     }
19. }

复制代码

3. 数据迁移（分批处理）

1. -- 分批迁移脚本
2. SET @start_id = 0;
3. WHILE EXISTS(SELECT 1 FROM user WHERE id > @start_id) DO
4.     INSERT INTO user_new (id, name, age)
5.     SELECT id, name,
6.         COALESCE(age_cache, 0) -- 从缓存获取默认值
7.     FROM user
8.     WHERE id > @start_id
9.     ORDER BY id
10.     LIMIT 10000;
11.    
12.     SET @start_id = (SELECT MAX(id) FROM user_new);
13.     COMMIT;
14.     -- 暂停100ms避免IO过载
15.     SELECT SLEEP(0.1);
16. END WHILE;

复制代码

4. 灰度切换流程

这套方案适合10亿上的表新增字段，不过操作起来比较麻烦，改动有点大。
6.使用gh-ost方案

gh-ost（GitHub's Online Schema Transmogrifier）是GitHub开源的一种无触发器的MySQL在线表结构变更方案。
专为解决大表DDL（如新增字段、索引变更、表引擎转换）时锁表阻塞、主库负载高等问题而设计。
其核心是通过异步解析binlog，替代触发器同步增量数据，显著降低对线上业务的影响。
与传统方案对比

• 触发器方案（如pt-osc）：
  在源表上创建INSERT/UPDATE/DELETE触发器，在同一事务内将变更同步到影子表。
  痛点：
  
  
  
  • 触发器加重主库CPU和锁竞争，高并发时性能下降30%以上
    
  • 无法暂停，失败需重头开始
    
  • 外键约束支持复杂
    
  
  
• gh-ost方案：
  
  
  
  • 伪装为从库：直连主库或从库，拉取ROW格式的binlog，解析DML事件（INSERT/UPDATE/DELETE）
    
  • 异步应用：将增量数据通过独立连接应用到影子表（如REPLACE INTO处理INSERT事件），与主库事务解耦
    
  • 优先级控制：binlog应用优先级 > 全量数据拷贝，确保数据强一致
    
  
  

关键流程：

• 全量拷贝：按主键分块（chunk-size控制）执行INSERT IGNORE INTO _table_gho SELECT ...，避免重复插入
  
• 增量同步：
  
  
  
  • INSERT → REPLACE INTO
    
  • UPDATE → 全行覆盖更新
    
  • DELETE → DELETE
    
  
  
• 原子切换（Cut-over）：
  
  
  • 短暂锁源表（毫秒级）
    
  • 执行原子RENAME：RENAME TABLE source TO _source_del, _source_gho TO source
    
  • 清理旧表（_source_del）
    
  
  

典型命令示例：

1. gh-ost \
2. --alter="ADD COLUMN age INT NOT NULL DEFAULT 0 COMMENT '用户年龄'" \
3. --host=主库IP --port=3306 --user=gh_user --password=xxx \
4. --database=test --table=user \
5. --chunk-size=2000 \       # 增大批次减少事务数
6. --max-load=Threads_running=80 \
7. --critical-load=Threads_running=200 \
8. --cut-over-lock-timeout-seconds=5 \  # 超时重试
9. --execute \               # 实际执行
10. --allow-on-master         # 直连主库模式

复制代码

2. 监控与优化建议

• 进度跟踪：
  

1. echo status | nc -U /tmp/gh-ost.sock  # 查看实时进度

复制代码

• 延迟控制：
  
  
  
  • 设置--max-lag-millis=1500，超阈值自动暂停
    
  • 从库延迟过高时切换为直连主库模式
    
  
  
• 切换安全：
  使用--postpone-cut-over-flag-file人工控制切换时机
  

7.分区表滑动窗口方案

适用场景：

• 按时间分区的日志型大表
  
• 需要频繁变更结构的监控表
  

核心原理：
通过分区表特性，仅修改最新分区结构。
操作步骤：
修改分区定义：

1. -- 原分区表定义
2. CREATE TABLE logs (
3.     id BIGINT,
4.     log_time DATETIME,
5.     content TEXT
6. ) PARTITION BY RANGE (TO_DAYS(log_time)) (
7.     PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
8.     PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01'))
9. );
11. -- 添加新字段（仅影响新分区）
12. ALTER TABLE logs ADD COLUMN log_level VARCHAR(10) DEFAULT 'INFO';

复制代码

创建新分区（自动应用新结构）：

1. -- 创建包含新字段的分区
2. ALTER TABLE logs REORGANIZE PARTITION p202302 INTO (
3.     PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
4.     PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01'))
5. );

复制代码

历史数据处理：

1. -- 仅对最近分区做数据初始化
2. UPDATE logs PARTITION (p202302)
3. SET log_level = parse_log_level(content);

复制代码

8.千万级表操作注意事项

• 主键必须存在（无主键将全表扫描）
  
• 磁盘空间监控（至少预留1.5倍表空间）
  
• 复制延迟控制
  

1. SHOW SLAVE STATUS;
2. -- 确保Seconds_Behind_Master < 10

复制代码

• 灰度验证步骤：
  
  
  
  • 先在从库执行
    
  • 检查数据一致性
    
  • 低峰期切主库
    
  
  
• 字段属性选择：
  
  
  
  • 避免NOT NULL（导致全表更新）
    
  • 优先使用ENUM代替VARCHAR
    
  • 默认值用NULL而非空字符串
    
  
  

9.各方案对比

以下是针对千万级MySQL表新增字段的6种方案的对比。
[table][tr]方案锁表时间业务影响数据一致性适用场景复杂度[/tr][tr][td]原生Online DDL[/td][td]秒级~分钟级[/td][td]中（并发DML受限）[/td][td]强一致[/td][td]