🔍 登录 注册

MySQL 大表在线变更:INSTANT/INPLACE、gh-ost 与 pt-osc 的选择边界

南 南风知我意 2026-11-17 👁 2 ⏱ 4min

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 的三个隐藏成本

  1. 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;
  1. INPLACE rebuild 的磁盘与 IO:需要约等于表大小的临时空间(innodb_online_alter_log_max_size 期间的 DML 缓存也占盘);先确认 df 余量 > 1.5×表大小。
  2. 主从延迟: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 进这张表。

四、流程模板:一次大表变更的标准动作

  1. 预演:在预发库用生产规模数据跑一遍,记录耗时与 IO 峰值;
  2. 备份/回退点:gh-ost 保留影子表可 unhook;pt-osc 有 _old 表 rename 回退窗口——明确"如果失败,回到什么状态";
  3. 执行窗口:低峰 + 监控三屏(从库延迟、Threads_running、磁盘余量)+ 一键暂停文件;
  4. 分批哲学:多张表的变更拆成多个独立发布,别一次 ALTER 五张大表;
  5. 变更后: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 就从年度高危操作降级为一次普通的发布动作。

读完了?给作者一点支持

本文采用 CC BY 4.0 协议转载 · © 南风知我意

评论(0)

登录 后参与评论。

  • 还没有评论,来抢沙发。

🔗 相关文章

审计人的职业规划地图:事务所、企业内审、监管与自由执业的四条路线

"做了三年审计接下来去哪"是行业论坛上的永恒问题。2026 年的就业市场给出了新的答案坐标:AI 压缩了基础核算与核对类岗位,同时抬高了风险判断、内控体系、数据审计类岗位的身价。本文把审计人的出路整理成四条主路线——事务所纵深、企业内审/风控、监管与准则机构、自由执业/专家证人——每条给进入条件、典型轨迹、5 年收入区间感与适配画像,帮你做一次结构化的自我定位。

1 天前👍 1

AI 正在怎样改变审计工作:一份可落地的工具地图与边界清单

"AI 取代审计师"是标题党,"AI 不改审计流程"是天真的。2026 年的真实图景是:审计的每个环节都出现了成熟度不一的 AI 应用——有的已进四大方法论并受监管审视(分析程序、抽凭),有的仍处试点(风险评价辅助),有的干脆不该用(对管理层意图的判断)。本文按审计流程逐环节给出一份工具地图:能做什么、成熟度如何、边界在哪,以及从业者该怎么上手。

1 天前👍 1

内部审计的转型:从合规检查到持续审计,2026 年的方法论与落地路径

企业内部审计正经历十年来最大的一次定位调整:监管趋严(上市公司内控规范迭代、"十五五"会计规划对行业数字化基建的明确指向)加上 AI 工具成熟,让传统"年度计划 + 现场抽查"模式的价值密度持续下降。领先实践已经给出替代方案:风险导向的动态审计计划 + 持续审计(continuous auditing)+ 咨询角色。本文给企业内审负责人与准备进入内审的事务所人士一套转型方法论:架构怎么搭、数据管道怎么建、异常规则库怎么运营、以及如何向审计委员会证明价值。

1 天前👍 1