MySQL 大表在线变更:INSTANT/INPLACE、gh-ost 与 pt-osc 的选择边界
给千万级大表加一列、改一个类型,是 DBA 生涯里最能把业务搞挂的操作之一。MySQL 8.0/9.x 的在线 DDL 能力已经大幅进化(INSTANT 加列、并行索引构建),但**"支持 online"与"无感变更"之间仍有三个鸿沟:元数据锁、磁盘空间翻倍、主从延迟**。本文给一套按操作类型选择工具与流程的决策框架。
一、先判断 DDL 的执行模式
ALTER TABLE articles ADD COLUMN word_count INT, ALGORITHM=INSTANT;
-- 报错 "ALGORITHM=INSTANT is not supported" 才降级,显式声明是防意外拷贝
| 模式 | 行为 | 典型操作 |
|---|---|---|
| INSTANT | 只改元数据,秒级 | 加列到末尾(8.0.29+ 任意位置)、设默认值、改名 |
| INPLACE | 原地改,可能 rebuild 但不拷贝全表语义 | 加索引(8.0.27+ 并行)、删索引、部分类型变更 |
| COPY | 全表拷贝 + rename | 改主键、改字符集、多数类型转换 |
9.x 延续这些能力且 INSTANT 覆盖面还在扩大(列重组后 INSTANT 加列次数有 ALTER TABLE ... FORCE 整理回收的机制)。先查文档再动手:同一操作在不同小版本模式可能不同。
二、INSTANT/INPLACE 的三个隐藏成本
- MDL(元数据锁):DDL 开始与结束各要一次排他 MDL。长查询/未提交事务占着表 → DDL 排队 → 它后面所有查询全部堵死,这是"无害 DDL 打挂全站"的第一事故源。执行前检查并设置超时:
SET lock_wait_timeout = 6; -- DDL 拿不到 MDL 6 秒即放弃,而不是无限排
SELECT * FROM sys.schema_table_lock_waits; -- 找持锁者
ALTER TABLE articles ADD COLUMN ..., ALGORITHM=INSTANT, LOCK=NONE;
- INPLACE rebuild 的磁盘与 IO:需要约等于表大小的临时空间(
innodb_online_alter_log_max_size期间的 DML 缓存也占盘);先确认df余量 > 1.5×表大小。 - 主从延迟:DDL 在从库串行重放,大表 rebuild 分钟级 = 从库落后同样时长。缓解:先在从库灰度(8.0.27+ 支持
ALTER TABLE ... ALGORITHM=COPY, WAIT_FOR_TABLE_VERSION? 更通用做法是 pt-osc/gh-ost 的 throttle),或低峰执行 + 读写分离临时摘流量。
三、外部工具:gh-ost vs pt-online-schema-change
COPY 类操作与超大表的索引重建,外置触发器/日志式工具仍是最稳路径:
| 维度 | gh-ost | pt-osc |
|---|---|---|
| 原理 | 伪装 binlog 订阅(无触发器) | 触发器 + 影子表拷贝 |
| 对主库侵入 | 低(binlog dump) | 中(每行 DML 多三次写放大) |
| 限流/暂停 | --max-lag-millis、交互式 pause/throttle |
同类能力,cut-over 机制多 |
| 前提 | ROW binlog + GTID 友好;需要 binlog 权限 | 需要触发器权限;表有主键/唯一键 |
gh-ost --host=prod-master --database=okgoes --table=articles \
--alter="CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci" \
--chunk-size=1500 --max-lag-millis=1500 --throttle-flag-file=/tmp/ghost.pause \
--critical-load="Threads_running=64" \
--cut-over=default --initially-only-ghost --execute
实操要点:--max-lag-millis 与 --critical-load 是刹车,必须设;cut-over 瞬间仍需一次短暂排他(秒级),业务高峰前演练过才敢用;变更期间禁止其他 DDL 进这张表。
四、流程模板:一次大表变更的标准动作
- 预演:在预发库用生产规模数据跑一遍,记录耗时与 IO 峰值;
- 备份/回退点:gh-ost 保留影子表可
unhook;pt-osc 有_old表 rename 回退窗口——明确"如果失败,回到什么状态"; - 执行窗口:低峰 + 监控三屏(从库延迟、Threads_running、磁盘余量)+ 一键暂停文件;
- 分批哲学:多张表的变更拆成多个独立发布,别一次 ALTER 五张大表;
- 变更后:
ANALYZE TABLE更新统计;新列若有索引需求,索引单独一次 DDL(避免一个语句里既加列又建索引的复合风险)。
五、高频场景速查
- 加列带默认值回填:INSTANT 加列 + 分批 UPDATE 回填(每批 1000~5000 行 sleep 交错),永远不要
ALTER TABLE ... ADD COLUMN c INT DEFAULT x后再指望全表即时改写——8.0.29+ 默认值走 INSTANT 元数据,旧行读取时合成,但回填新逻辑仍要按业务时间窗分批; - 加索引:INPLACE + 并行(
innodb_ddl_threads/innodb_ddl_buffer_size,8.0.27+)大表提速明显;从库延迟是主要风险,低峰做; - 改类型/字符集:COPY 类 → gh-ost;注意 varchar 扩长若跨 255 字节边界(长度前缀变化)不能 INSTANT;
- 删列/改名:先代码停写(发布顺序:代码先行一个版本),再 DDL——物理删除放最后一步。
小结
大表在线变更的决策树:能 INSTANT 就显式 ALGORITHM=INSTANT(失败即降级,绝不隐式拷贝);INPLACE rebuild 先算磁盘与从库延迟两笔账;COPY 类交给 gh-ost/pt-osc 并配好 throttle 刹车。三个隐藏成本——MDL 排队、临时空间、复制延迟——每一个都比 DDL 本身更容易出事。把"lock_wait_timeout=6 + 预演 + 分批回填 + 变更后 ANALYZE"固化成变更模板后,大表 DDL 就从年度高危操作降级为一次普通的发布动作。
评论(0)
登录 后参与评论。