MySQL大表添加字段的快速方案

背景

当一张表的数据量达到千万级甚至亿级时，直接执行 ALTER TABLE ADD COLUMN 可能会带来严重问题：

锁表：传统 DDL 操作期间表被锁定，无法读写
耗时长：大表 DDL 可能需要数小时甚至更久
主从延迟：DDL 在主库执行完后才开始在从库执行，导致从库严重落后
业务中断：上述问题叠加可能导致线上故障

本文梳理 MySQL 各版本的改进以及主流的快速添加字段方案。

一、MySQL 原生方案演进

1.1 MySQL 5.5 及以前：COPY 算法

1
ALTER TABLE big_table ADD COLUMN new_col VARCHAR(100);

默认使用 COPY 算法：创建临时表 → 复制数据 → 替换原表。全程锁表，大表基本不可用。

1.2 MySQL 5.6+：Online DDL（INPLACE）

MySQL 5.6 引入了 Online DDL，支持 ALGORITHM=INPLACE，允许 DDL 期间并发 DML 操作：

1
ALTER TABLE big_table ADD COLUMN new_col VARCHAR(100), ALGORITHM=INPLACE, LOCK=NONE;

关键参数：

参数	说明
`ALGORITHM=INPLACE`	在原表上直接修改，避免全表复制
`LOCK=NONE`	允许并发读写
`LOCK=SHARED`	允许读，阻塞写
`LOCK=EXCLUSIVE`	阻塞读写

注意事项：

ADD COLUMN 在 INPLACE 模式下仍需要重建表（全表拷贝），只是不锁表
执行期间会消耗额外的磁盘空间（临时表）
对于超大表，INPLACE 模式依然很慢

1.3 MySQL 8.0.12+：Instant ADD COLUMN

MySQL 8.0.12 引入了 ALGORITHM=INSTANT，只需修改表的元数据，不涉及数据拷贝，执行时间是毫秒级：

1
ALTER TABLE big_table ADD COLUMN new_col VARCHAR(100), ALGORITHM=INSTANT;

这是最理想的方案。 但 Instant ADD COLUMN 有严格的限制条件：

支持的场景：

✅ 添加列（ADD COLUMN），且添加到表的末尾
✅ MySQL 8.0.12+ 添加列到末尾
✅ MySQL 8.0.29+ 支持添加列到任意位置

不支持的场景：

❌ 添加列的同时添加索引
❌ 列涉及外键
❌ 表使用了 ROW_FORMAT=COMPRESSED
❌ 表含有全文索引
❌ 临时表（TEMPORARY TABLE）
❌ 数据字典中的表

检查当前表是否支持 INSTANT：

1
2
3
4
-- MySQL 8.0.29+ 可查看 instant 列数
SELECT TABLE_NAME, INSTANT_COLS 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'big_table';

二、第三方工具方案

当 MySQL 版本不支持 INSTANT，或场景受限时，第三方工具是首选。

2.1 gh-ost（GitHub 开源）

gh-ost 是 GitHub 开发的无触发器在线表变更工具（⭐ 13,300+ Stars）。

核心原理：

创建一张与原表结构相同的幽灵表（ghost table）
在幽灵表上执行 DDL 变更
通过解析 binlog 获取增量变更，应用到幽灵表
数据同步完成后，通过 RENAME TABLE 原子交换两张表

优势：

无触发器：不使用触发器，对主库性能影响极小
可暂停/恢复：随时可以暂停和恢复迁移
可回滚：支持 --test-on-replica 先在从库测试
动态调参：运行时可以调整 chunk 大小和 throttle 策略
可审计：所有操作通过 binlog，可追踪

基本用法：

1
2
3
4
5
6
7
8
9
gh-ost \
  --user="root" \
  --password="your_password" \
  --host="127.0.0.1" \
  --database="your_db" \
  --table="big_table" \
  --alter="ADD COLUMN new_col VARCHAR(100) DEFAULT ''" \
  --allow-on-master \
  --execute

关键参数：

参数	说明
`--allow-on-master`	允许在主库上执行（默认连从库）
`--initially-drop-ghost-table`	如果之前有残留的幽灵表，先删除
`--chunk-size=1000`	每次迁移的行数
`--max-load=Threads_running=25`	负载阈值，超过则暂停
`--throttle-control-replicas`	指定监控延迟的从库
`--postpone-cut-over-flag-file`	创建此文件则暂停最终的 rename 操作

暂停和恢复：

1
2
3
4
5
# 暂停
touch /tmp/gh-ost.postpone.flag

# 恢复
rm /tmp/gh-ost.postpone.flag

2.2 pt-online-schema-change（Percona Toolkit）

Percona 出品的经典在线表变更工具，使用触发器实现增量同步。

核心原理：

创建一张与原表结构相同的新表
在新表上执行 DDL
在原表上创建 AFTER INSERT/UPDATE/DELETE 触发器，将变更同步到新表
分块（chunk）复制原表数据到新表
数据同步完成后 RENAME TABLE 交换

基本用法：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
pt-online-schema-change \
  --user=root \
  --password=your_password \
  --host=127.0.0.1 \
  D=your_db,t=big_table \
  --alter "ADD COLUMN new_col VARCHAR(100) DEFAULT ''" \
  --chunk-size=1000 \
  --max-load=Threads_running=25 \
  --critical-load=Threads_running=50 \
  --execute

注意：

使用触发器，对高并发写入场景性能影响较大
外键约束需要额外处理（--alter-foreign-keys-method）
如果表已有触发器，可能冲突

2.3 两者对比

维度	gh-ost	pt-osc
增量同步方式	解析 binlog	触发器
主库性能影响	极小	较大（触发器开销）
可暂停/恢复	✅ 原生支持	❌ 不支持
外键支持	需手动处理	`--alter-foreign-keys-method`
并发写入场景	✅ 适合	⚠️ 触发器可能导致锁争用
社区活跃度	⭐ 13,300+	Percona 维护
学习成本	中等	较低

选择建议：

高并发写入环境 → gh-ost（无触发器）
已有 Percona Toolkit 经验 → pt-osc（上手简单）
需要精细控制 → gh-ost（支持暂停、throttle）

三、手工替换表法

适用于没有第三方工具、又不满足 INSTANT 条件的场景。

步骤：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
-- 1. 创建新表（含新字段）
CREATE TABLE big_table_new LIKE big_table;
ALTER TABLE big_table_new ADD COLUMN new_col VARCHAR(100) DEFAULT '';

-- 2. 分批导入数据（避免长事务）
INSERT INTO big_table_new 
SELECT *, '' AS new_col FROM big_table WHERE id BETWEEN 1 AND 100000;
-- 循环执行直到所有数据迁移完成

-- 3. 同步增量数据（需要短暂锁表）
-- 根据业务情况选择低峰期执行 RENAME
RENAME TABLE big_table TO big_table_old, big_table_new TO big_table;

-- 4. 确认无误后删除旧表
DROP TABLE big_table_old;

风险：

RENAME 期间有短暂不可写窗口
需要处理增量数据的一致性
磁盘空间翻倍

四、方案决策树

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
大表需要添加字段
│
├─ MySQL 8.0.12+ 且只需加列到末尾？
│   └─ ✅ 用 INSTANT（秒级完成）
│
├─ MySQL 8.0.29+ 且需加列到任意位置？
│   └─ ✅ 用 INSTANT（秒级完成）
│
├─ MySQL 5.6+ 且允许 INPLACE 重建？
│   └─ ⚠️ Online DDL（不锁表但仍需重建数据）
│
├─ 有第三方工具且高并发写入？
│   └─ ✅ gh-ost
│
├─ 有第三方工具且写入压力不大？
│   └─ ✅ pt-osc
│
└─ 以上都不满足？
    └─ ⚠️ 手工替换表法（低峰期操作）

五、生产环境操作建议

5.1 操作前准备

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
-- 检查表大小和行数
SELECT 
    table_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    table_rows
FROM information_schema.tables 
WHERE table_schema = 'your_db' AND table_name = 'big_table';

-- 检查是否有外键
SELECT * FROM information_schema.key_column_usage 
WHERE referenced_table_name = 'big_table';

-- 检查主从延迟
SHOW SLAVE STATUS\G

5.2 操作中监控

1
2
3
4
5
6
7
8
9
# gh-ost 执行时可以查看进度
# 监控文件在 /tmp/ 下
ls -la /tmp/gh-ost.*

# 监控主从延迟
while true; do
    mysql -e "SHOW SLAVE STATUS\G" | grep Seconds_Behind
    sleep 5
done

5.3 回滚方案

方案	回滚方式
INSTANT	`ALTER TABLE DROP COLUMN`（同样是 instant）
gh-ost	重新执行一次 gh-ost，alter 改为 DROP COLUMN
pt-osc	重新执行一次 pt-osc，alter 改为 DROP COLUMN
手工替换	`RENAME TABLE big_table TO big_table_new, big_table_old TO big_table`

六、总结

方案	MySQL版本	执行时间	锁表	复杂度
INSTANT	8.0.12+	毫秒级	不锁	最低
Online DDL	5.6+	分钟~小时	不锁表但阻塞部分操作	低
gh-ost	5.6+	分钟~小时	不锁	中
pt-osc	5.6+	分钟~小时	不锁	中
手工替换	全版本	小时级	短暂锁	高

核心建议：能升级到 MySQL 8.0 就升级，大多数 ADD COLUMN 操作用 INSTANT 秒级完成。不能升级的，用 gh-ost 是最稳妥的选择。