farerboy

15. 千万级大表如何新增字段？别再直接 ALTER 了

# 15. 千万级大表如何新增字段？别再直接 ALTER 了

# 前言

在日常开发中，我们经常需要为数据库表加字段。对于一张只有几千行的小表来说，一句 ALTER TABLE 瞬间完成，几乎没有任何感知。

但当这张表的数据量达到千万级甚至上亿级时，事情就变得复杂起来了。

你可能会问：“我只是加个字段，又不是删数据，至于大动干戈吗？”

至于。非常至于。

因为一旦你在大表上执行ALTER TABLE，数据库可能会长时间锁表，导致：

  • 读写阻塞：所有查询和写入请求被挂起
  • 业务中断：订单无法提交、支付失败、页面卡顿
  • 主从延迟加剧：主库执行 DDL 时，从库复制延迟飙升，影响报表、备份甚至高可用切换
  • 连接堆积：应用连接池耗尽，服务雪崩

假设有一个用户中心的核心表，数据量超过 5000 万。如果直接执行 ALTER TABLE 加字段，可能会导致服务中断近几分钟。这期间可能会导致各种订单流失、客服报警，于是老板震怒……

大表结构变更，必须慎之又慎。

# 原因分析

为什么大表ALTER TABLE会这么慢？

要解决问题，先理解原理。

MySQL 的 ALTER TABLE 操作本质上是重建表（Rebuild Table）的过程：

  • 创建一个临时的新结构表
  • 将原表数据逐行拷贝到新表
  • 删除原表，重命名新表
  • 重建索引

这个过程在早期版本（如 MySQL 5.5 及以前）中是全程锁表的（LOCK=EXCLUSIVE），意味着整个操作期间，任何 DML（INSERT/UPDATE/DELETE）都会被阻塞。

虽然 MySQL5.6 引入了Online DDL，允许部分DDL操作在不锁表的情况下进行，但仍然存在性能影响和兼容性限制。

# 解决方案

针对大表加字段的问题，业界有几种成熟方案。下面我们逐一分析其原理、优缺点及适用场景。

# 方案一：低峰期直接ALTER TABLE

这种方案适合小表，最简单粗暴和直接：

ALTER TABLE user ADD COLUMN new_flag TINYINT DEFAULT 0;
1

# 适用场景：

  • 表数据量较小（< 100 万）
  • 业务容忍短暂不可用
  • 无主从延迟要求

# 优点：

  • 操作简单，无需额外工具
  • 成本最低

# 缺点：

  • 锁表时间不可控，大表风险极高
  • 无法做到“无感变更”

建议：仅用于测试环境或极小表。

# 方案二：使用MySQL Online DDL

从 MySQL5.6 开始，支持Online DDL，允许在执行DDL时不阻塞DML操作，该方案推荐用于中等大小的表。

# 关键语法：

ALTER TABLE user 
ADD COLUMN new_flag TINYINT DEFAULT 0, 
ALGORITHM=INPLACE, 
LOCK=NONE;
1
2
3
4

ALGORITHM=INPLACE：使用原地修改算法，避免全表重建

LOCK=NONE：表示不加锁，允许并发读写

# 支持情况（以加字段为例）：

注意：即使LOCK=NONE，也并非完全无影响。拷贝数据期间仍会占用I/O和CPU，可能影响性能。

# 优点：

  • 原生支持，无需外部工具
  • 真正实现不停机变更

# 缺点：

  • 不支持所有DDL类型（如修改列类型仍需重建）
  • 大表执行时间长，仍可能引发主从延迟
  • 需要足够磁盘空间（临时文件）

建议：适用于100万~100万行的中等表，且使用MySQL 5.7+。

# 方案三：使用PT-OSC（Percona Toolkit）——生产环境首选

PT-OSC（pt-online-schema-change） 是Percona 提供的开源工具，专为大表在线 DDL 设计。

# 工作原理：

  • 创建一个新表 tbl_new，结构包含新字段
  • 在原表上创建三个触发器（INSERT/UPDATE/DELETE），同步变更到新表
  • 分批将原表数据拷贝到新表（每次只拷几百条，减少压力）
  • 数据同步完成后，原子性重命名：RENAME TABLE tbl TO tbl_old, tbl_new TO tbl
  • 删除旧表

# 示例命令：

pt-online-schema-change \
--host=localhost \
--user=root \
--password=your_password \
--alter="ADD COLUMN membership_level TINYINT DEFAULT 0 COMMENT '会员等级'" \
D=ecdb,t=user \
--chunk-size=5000 \
--max-load="Threads_running=50" \
--critical-load="Threads_running=100" \
--sleep=0.5 \
--execute
1
2
3
4
5
6
7
8
9
10
11

# 参数说明：

  • --chunk-size：每次拷贝的数据量
  • --max-load：负载上限，超过则暂停
  • --critical-load：致命负载，超过则终止
  • --sleep：每批拷贝后休眠时间，降低压力

# 优点：

  • 几乎不影响线上业务
  • 支持精细控制资源占用
  • 成熟稳定，被大量互联网公司采用

# 缺点：

  • 需要安装 Percona Toolkit
  • 需要额外磁盘空间（双表并存）
  • 触发器带来轻微性能开销（通常 < 5%）
  • 不支持有外键引用的表（除非用 --alter-foreign-keys-method）

建议：千万级大表的首选方案。

# 方案四：手动模拟 PT-OSC（无工具时的备选）

如果你无法使用 PT-OSC（如安全限制、权限问题），可以手动实现类似流程。

-- 1. 创建新表
CREATE TABLE user_new LIKE user;
ALTER TABLE user_new ADD COLUMN new_flag TINYINT DEFAULT 0;

-- 2. 分批迁移数据
INSERT INTO user_new SELECT *, 0 FROM user WHERE id BETWEEN 1 AND 100000;
-- 循环执行，逐步迁移

-- 3. 数据追平后，创建触发器同步变更
DELIMITER $$
CREATE TRIGGER user_insert_trg AFTER INSERT ON user
FOR EACH ROW BEGIN
    INSERT INTO user_new VALUES (NEW.*, 0);
END$$
-- 同样创建 UPDATE 和 DELETE 触发器
DELIMITER ;

-- 4. 短暂停机，切换表名（秒级）
RENAME TABLE user TO user_old, user_new TO user;

-- 5. 验证无误后删除旧表
DROP TABLE user_old;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# 优点：

  • 不依赖外部工具
  • 完全可控

# 缺点：

  • 手动操作易出错
  • 切换瞬间仍有短暂锁表（RENAME 是原子操作，但需独占表名）
  • 需精确控制触发器逻辑

建议：仅作为 PT-OSC 不可用时的备选方案。

# MySQL 8.0的新变化

MySQL8.0对DDL进行了重大优化：

  • 原子性 DDL：DDL 操作支持事务回滚（如失败可自动清理）
  • 更快的加字段：新增字段默认为“即时添加”（Instant Add Column），仅修改元数据，几乎瞬间完成 支持更多 INPLACE 操作
ALTER TABLE user ADD COLUMN new_col VARCHAR(50) DEFAULT NULL, ALGORITHM=INSTANT;
1

注意：INSTANT算法仅支持在表末尾添加字段，且不能是主键或NOT NULL无默认值的字段。

建议：如果使用 MySQL 8.0+，优先尝试 ALGORITHM=INSTANT，性能极佳。

# 最佳实践总结

# 补充建议

# 避免 NOT NULL 无默认值的字段

加 NOT NULL 字段需全表初始化，代价极高。建议先加 DEFAULT NULL 或带默认值。

# 尽量在表末尾加字段

有助于触发 INSTANT 算法（MySQL 8.0+）

# 慎用外键

外键会增加 PT-OSC 的复杂度，建议业务层维护一致性

# 考虑影子表（Shadow Table）模式

对于极端敏感的系统，可采用双写影子表 + 流量切换的方式，实现零停机变更

# 替代工具推荐 gh-ost

基于 binlog 同步，无需触发器，更安全 AliSQL Online DDL：阿里云优化版本，支持更多场景

01. ♥Redis教程 - Redis知识体系详解♥