写点什么

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

  • 2025-07-29
    福建
  • 本文字数:3239 字

    阅读完需:约 11 分钟

前言


线上千万级的大表在新增字段的时候,一定要小心,我见过太多团队在千万级大表上执行 DDL 时翻车的案例。

很容易影响到正常用户的使用。

本文将深入剖析大表加字段的核心难点,并给出可落地的解决方案。

希望对你会有所帮助。


1.为什么大表加字段如此危险?


核心问题:MySQL 的 DDL 操作会锁表

当执行ALTER TABLE ADD COLUMN时:

  1. MySQL 5.6 之前:全程锁表(阻塞所有读写)

  2. MySQL 5.6+:仅支持部分操作的 Online DDL

通过实验验证锁表现象:

-- 会话1:执行DDL操作ALTER TABLE user ADD COLUMN age INT;
-- 会话2:尝试查询(被阻塞)SELECT * FROM user WHERE id=1; -- 等待DDL完成
复制代码


锁表时间计算公式:

锁表时间 ≈ 表数据量 / 磁盘IO速度
复制代码


对于 1000 万行、单行 1KB 的表,机械磁盘(100MB/s)需要 100 秒的不可用时间!

如果在一个高并发的系统中,这个问题简直无法忍受。

那么,我们要如何解决问题呢?



2.原生 Online DDL 方案


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

例如:

ALTER TABLE user ADD COLUMN age INT,ALGORITHM=INPLACE, LOCK=NONE;
复制代码


实现原理



致命缺陷

  1. 仍可能触发表锁(如添加全文索引)

  2. 磁盘空间需双倍(实测 500GB 表需要 1TB 空闲空间)

  3. 主从延迟风险(从库单线程回放)


3.停机维护方案



适用场景

  • 允许停服时间(如凌晨 3 点)

  • 数据量小于 100GB(减少导入时间)

  • 有完整回滚预案


4.使用 PT-OSC 工具方案


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

工作原理:



操作步骤:

# 安装工具sudo yum install percona-toolkit
# 执行迁移(添加age字段)pt-online-schema-change \--alter "ADD COLUMN age INT" \D=test,t=user \--execute
复制代码


5.逻辑迁移 + 双写方案


还有一个金融级安全的方案是:逻辑迁移 + 双写方案。

适用场景

  • 字段变更伴随业务逻辑修改(如字段类型变更)

  • 要求零数据丢失的金融场景

  • 超 10 亿行数据的表

实施步骤

1. 创建新表结构

-- 创建包含新字段的副本表CREATE TABLE user_new (    id BIGINT PRIMARY KEY,    name VARCHAR(50),    -- 新增字段    age INT DEFAULT 0,    -- 增加原表索引    KEY idx_name(name)) ENGINE=InnoDB;
复制代码


2. 双写逻辑实现(Java 示例)

// 数据写入服务public class UserService {    @Transactional    public void addUser(User user) {        // 写入原表        userOldDAO.insert(user);        // 写入新表(包含age字段)        userNewDAO.insert(convertToNew(user));    }        private UserNew convertToNew(User old) {        UserNew userNew = new UserNew();        userNew.setId(old.getId());        userNew.setName(old.getName());        // 新字段处理(从其他系统获取或默认值)        userNew.setAge(getAgeFromCache(old.getId()));        return userNew;    }}
复制代码


3. 数据迁移(分批处理)

-- 分批迁移脚本SET @start_id = 0;WHILE EXISTS(SELECT 1 FROM user WHERE id > @start_id) DO    INSERT INTO user_new (id, name, age)    SELECT id, name,         COALESCE(age_cache, 0) -- 从缓存获取默认值    FROM user    WHERE id > @start_id    ORDER BY id    LIMIT 10000;        SET @start_id = (SELECT MAX(id) FROM user_new);    COMMIT;    -- 暂停100ms避免IO过载    SELECT SLEEP(0.1); 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 INTOUPDATE → 全行覆盖更新 DELETE → DELETE

  • 原子切换(Cut-over):短暂锁源表(毫秒级)执行原子 RENAME:RENAME TABLE source TO _source_del, _source_gho TO source清理旧表(_source_del


典型命令示例:

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


2. 监控与优化建议


  • 进度跟踪

echo status | nc -U /tmp/gh-ost.sock  # 查看实时进度
复制代码
  • 延迟控制:设置--max-lag-millis=1500,超阈值自动暂停从库延迟过高时切换为直连主库模式

  • 切换安全

  • 使用--postpone-cut-over-flag-file人工控制切换时机


7.分区表滑动窗口方案


适用场景:

  • 按时间分区的日志型大表

  • 需要频繁变更结构的监控表

核心原理:通过分区表特性,仅修改最新分区结构。

操作步骤

修改分区定义:

-- 原分区表定义CREATE TABLE logs (    id BIGINT,    log_time DATETIME,    content TEXT) PARTITION BY RANGE (TO_DAYS(log_time)) (    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')));
-- 添加新字段(仅影响新分区)ALTER TABLE logs ADD COLUMN log_level VARCHAR(10) DEFAULT 'INFO';
复制代码


创建新分区(自动应用新结构):

-- 创建包含新字段的分区ALTER TABLE logs REORGANIZE PARTITION p202302 INTO (    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),    PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01')));
复制代码


历史数据处理:

-- 仅对最近分区做数据初始化UPDATE logs PARTITION (p202302) SET log_level = parse_log_level(content);
复制代码


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

  1. 主键必须存在(无主键将全表扫描)

  2. 磁盘空间监控(至少预留 1.5 倍表空间)

  3. 复制延迟控制

SHOW SLAVE STATUS; -- 确保Seconds_Behind_Master < 10
复制代码


4.灰度验证步骤

  • 先在从库执行

  • 检查数据一致性

  • 低峰期切主库

5.字段属性选择

  • 避免 NOT NULL(导致全表更新)

  • 优先使用 ENUM 代替 VARCHAR

  • 默认值用 NULL 而非空字符串


9.各方案对比


以下是针对千万级 MySQL 表新增字段的 6 种方案的对比。



总结


1、常规场景(<1 亿行)

首选 Online DDLALGORITHM=INSTANT,MySQL 8.0 秒级加字段)

备选 PT-OSC(兼容低版本 MySQL)


2、高并发大表(>1 亿行)

必选 gh-ost(无触发器设计,对写入影响<5%)


3、金融核心表

双写方案 是唯一选择(需 2-4 周开发周期)


4、日志型表

分区滑动窗口 最优(仅影响新分区)


5、紧急故障处理

超百亿级表异常时,考虑 停机维护 + 回滚预案


给大家一些建议

  • 加字段前优先使用 JSON 字段预扩展ALTER TABLE user ADD COLUMN metadata JSON

  • 万亿级表建议 分库分表 而非直接 DDL

  • 所有方案执行前必须 全量备份mysqldump + binlog

  • 流量监测(Prometheus+Granfa 实时监控 QPS)


在千万级系统的战场上,一次草率的 ALTER 操作可能就是压垮骆驼的最后一根稻草。


文章转载自:苏三说技术

原文链接:https://www.cnblogs.com/12lisu/p/19008591

体验地址:http://www.jnpfsoft.com/?from=001YH

用户头像

还未添加个人签名 2025-04-01 加入

还未添加个人简介

评论

发布
暂无评论
千万级的大表如何新增字段?_大数据_电子尖叫食人鱼_InfoQ写作社区