📌**今日关键词**：大表ALTER、Online DDL、pt-osc、gh-ost、Instant DDL、MDL锁、数据库运维

大家好，我是数据库小学妹 👋

上周干了件蠢事。生产环境有张订单表，800多万数据，业务说加个字段。我当时想，ALTER TABLE嘛，一条SQL的事。

结果跑了将近40分钟。期间订单系统基本瘫了，用户下单全超时，客服电话被打爆。最后虽然救回来了。但那天晚上我没怎么睡，一直在想到底哪里出了问题。

今天把这次翻车经历和后面研究的东西整理出来。

## 为什么直接ALTER大表会出事？

MySQL 5.6之前，ALTER TABLE基本等于重建整张表。5.6之后支持Online DDL，但某些操作还是会锁表。

我那张800万的表，执行ALTER时发生了什么：

MySQL会在tmpdir下创建临时文件，结构和原表一样。然后逐行把数据拷过去。800万行，全量复制要好几分钟。

COPY算法下，整个过程持有MDL写锁。所有SELECT、INSERT、UPDATE、DELETE全被阻塞。

我那条SQL走的就是COPY算法。虽然Online DDL支持INPLACE算法，但改字段类型、删主键这类操作还是走COPY。

还有个坑：800万行的表，ibd文件大概15GB。ALTER时临时文件也占差不多的空间。磁盘空间不够，ALTER直接失败，失败后临时文件可能不会自动清理。

## MySQL 8.0有个救星：Instant DDL

如果你们用的是MySQL 8.0.12以上，有个好消息。部分DDL操作支持ALGORITHM=INSTANT，毫秒级完成，不用拷数据。

原理很简单：只修改元数据，不触碰实际数据文件。比如在表末尾加一个字段，直接更新数据字典就行。

```sql
ALTER TABLE orders ADD COLUMN remark VARCHAR(255), ALGORITHM=INSTANT;
```

但Instant DDL有条件限制。只有在表末尾加字段、改列默认值这类操作才走INSTANT。在中间插字段、改字段类型、删主键，还是得走INPLACE甚至COPY。

我那张表要加的字段需要放在中间位置，所以没走成Instant。如果你的场景只是在末尾加字段，先试这个，省得折腾。

## 三种方案对比

Instant DDL条件不满足的时候，就得考虑其他方案了。踩坑之后我花了一周研究了几种工具。

MySQL原生Online DDL（5.6+）是最简单的：

```sql
ALTER TABLE orders ADD COLUMN remark VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;
```

官方支持，不用装额外工具。但问题也不少。不是所有DDL都走INPLACE。大表执行时间长会阻塞从库回放。执行过程中没法暂停，出了问题只能KILL。

pt-online-schema-change是Percona Toolkit里的工具，原理是创建影子表，通过触发器同步数据：

```bash
pt-online-schema-change --alter "ADD COLUMN remark VARCHAR(255)" \
  D=mydb,t=orders --execute
```

工作流程：先创建影子表，在影子表上执行ALTER（空表秒级完成）。然后在原表上创建三个触发器，分批拷数据。最后原子性RENAME TABLE替换。

gh-ost是GitHub开源的，用binlog解析替代触发器：

```bash
gh-ost --alter="ADD COLUMN remark VARCHAR(255)" \
  --database=mydb --table=orders --execute
```

原理类似，但同步机制不一样。创建影子表后，通过binlog解析捕获原表变更。边拷贝边同步，最后短暂锁表做切换。

## pt-osc怎么工作的

pt-osc的核心是"影子表"——本质是把大表变更拆成"小表变更+数据迁移"。

原表叫orders，影子表叫_orders_new。在影子表上执行ALTER，因为是空表，秒级完成。然后再把数据搬过去。

原表上会创建三个触发器，任何INSERT/UPDATE/DELETE操作都会同步到影子表。代价是写入性能下降10%-20%，触发器是逐行执行的，批量操作会变慢。

数据分批拷贝，每批默认1000行，每批之间sleep 0.5秒控制压力。

参数调优建议：
- `--chunk-size`：每批行数，大表可以调到5000-10000
- `--chunk-time`：每批目标耗时，默认0.5秒
- `--max-lag`：从库延迟超过这个值就暂停，保护从库

## gh-ost怎么工作的

gh-ost最大的特点是不用触发器，改用binlog解析。原表没有额外开销，对业务影响更小。

它通过mysqlbinlog协议接收binlog事件，解析后转换成对影子表的SQL执行。

最后的切换阶段（cut-over）设计得挺精妙。先创建一个连接持有原表的MDL，等所有长事务结束。然后两步RENAME：先把原表RENAME成_old，再把影子表RENAME成原表。如果第一步失败（有长事务没结束），gh-ost会自动重试而不是一直阻塞。整个切换通常毫秒级完成。

和pt-osc相比：

| 特性 | pt-osc | gh-ost |
|------|--------|--------|
| 同步机制 | 触发器 | binlog解析 |
| 主库开销 | 触发器执行开销 | binlog解析开销 |
| 从库影响 | 触发器同步到从库 | 只在主库上操作 |
| 可暂停 | 不支持 | 支持 |
| 最终切换 | RENAME TABLE | 两步RENAME+自动重试 |
| 回滚方式 | 删触发器+删影子表 | 停止进程+删影子表 |
| 最低版本 | MySQL 5.5+ | MySQL 5.6+ |

## 生产环境怎么搞

这次踩坑后我总结了几条：

执行前先看看表的大小、当前QPS、磁盘空间：

```sql
SELECT table_rows, data_length/1024/1024 AS data_mb
FROM information_schema.tables
WHERE table_name = 'orders';
```

先在从库跑一遍，观察执行时间、资源占用，主库执行时心里有底。

pt-osc和gh-ost都有限流机制。`--max-lag`保护从库，`--max-load`控制主库负载。

执行时间选在业务高峰之外。晚上10点到凌晨2点通常比较安全。

变更完成后别急着删旧表。pt-osc默认会删掉旧表，加`--no-drop-old-table`参数可以保留。gh-ost会保留_old表。建议保留观察一段时间，确认没问题再手动清理。

## 避坑清单

这次踩坑总结出来最重要的一条：别直接ALTER大表。哪怕MySQL 5.6+支持Online DDL，也要先看看表多大、走的什么算法。COPY算法等于重建表，百万级以上的表扛不住。

磁盘空间一定要预留够。我后来复盘才发现，ALTER过程会生成临时文件，大小和原表差不多。空间不足的话ALTER直接失败，临时文件还不一定自动清理。留2倍以上才安全。

从库延迟是另一个容易忽略的点。大表变更会阻塞从库回放，配置好`--max-lag`监控。pt-osc和gh-ost都有这个参数，超过阈值自动暂停，保护从库不被拖垮。

动手之前先在从库跑一遍。同样的操作，先评估执行时间和资源占用。主库执行的时候心里有底，不会像我一样手忙脚乱。

执行时间选在业务低峰。晚上10点到凌晨2点通常比较安全。即使是Online DDL，对性能也有影响，高峰期动手风险太大。

变更完成后别急着清理旧表。保留一段时间观察，确认数据和性能都没问题再手动删。pt-osc默认删旧表，加`--no-drop-old-table`参数才能保留。

---

我是数据库小学妹，咱们下篇见 👋


📌今日关键词：大表ALTER、Online DDL、pt-osc、gh-ost、Instant DDL、MDL锁、数据库运维

生产环境大表变更翻车记：Online DDL方案选型与调优实战

讲述一次生产环境大表ALTER失败的踩坑经历，梳理MySQL 8.0 Instant DDL、Online DDL、pt-osc、gh-ost四种方案的原理与差异，附生产环境实操建议和避坑清单

数据库

运维

后端

"MySQL大表ALTER操作避坑指南：详解OnlineDDL、pt-osc和gh-ost三种方案对比，分享800万订单表加字段导致生产事故的实战经验，分析MDL锁、InstantDDL等关键技术，提供大表变更的最佳实践和参数调优建议，帮助DBA安全高效完成数据库结构变更。"

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

生产环境大表变更翻车记：Online DDL方案选型与调优实战-腾讯云开发者社区-腾讯云

生产环境大表变更翻车记：Online DDL方案选型与调优实战

生产环境大表变更翻车记：Online DDL方案选型与调优实战

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐