📌今日关键词：大表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，毫秒级完成，不用拷数据。
原理很简单：只修改元数据，不触碰实际数据文件。比如在表末尾加一个字段，直接更新数据字典就行。
ALTER TABLE orders ADD COLUMN remark VARCHAR(255), ALGORITHM=INSTANT;
但Instant DDL有条件限制。只有在表末尾加字段、改列默认值这类操作才走INSTANT。在中间插字段、改字段类型、删主键，还是得走INPLACE甚至COPY。
我那张表要加的字段需要放在中间位置，所以没走成Instant。如果你的场景只是在末尾加字段，先试这个，省得折腾。
三种方案对比
Instant DDL条件不满足的时候，就得考虑其他方案了。踩坑之后我花了一周研究了几种工具。
MySQL原生Online DDL（5.6+）是最简单的：
ALTER TABLE orders ADD COLUMN remark VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;
官方支持，不用装额外工具。但问题也不少。不是所有DDL都走INPLACE。大表执行时间长会阻塞从库回放。执行过程中没法暂停，出了问题只能KILL。
pt-online-schema-change是Percona Toolkit里的工具，原理是创建影子表，通过触发器同步数据：
pt-online-schema-change --alter "ADD COLUMN remark VARCHAR(255)" \
D=mydb,t=orders --execute
工作流程：先创建影子表，在影子表上执行ALTER（空表秒级完成）。然后在原表上创建三个触发器，分批拷数据。最后原子性RENAME TABLE替换。
gh-ost是GitHub开源的，用binlog解析替代触发器：
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、磁盘空间：
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参数才能保留。
我是数据库小学妹，咱们下篇见 👋
