数据库大表ALTER最佳实践 pt-osc与gh-ost调优原理

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

先说一个经典翻车案例：生产环境有张订单表，800多万行数据，业务那边只是要加一个字段。很多人会条件反射地敲一条ALTER TABLE SQL——听起来确实很直接。结果呢？这个操作跑了将近40分钟，期间订单系统几乎瘫痪，用户下单全部超时，客服电话直接被打爆。虽然最后还是救回来了，但那天晚上根本睡不着，一直在想问题到底出在哪。

今天就把这次踩坑经历和后续研究总结出来，希望各位少走弯路，让大表ALTER操作更安全高效。

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

在MySQL 5.6之前，ALTER TABLE基本等于重建整张表。5.6之后虽然引入了Online DDL，但某些操作依然会触发MDL锁，导致锁表。

那张800万行的表执行ALTER时，底层发生了什么？MySQL会在tmpdir下创建一个与原表结构相同的临时文件，然后逐行拷贝数据。800万行，全量复制需要好几分钟。在COPY算法下，整个过程会持有MDL写锁，所有SELECT、INSERT、UPDATE、DELETE全部被堵住。

那次ALTER正好走了COPY算法——虽然Online DDL支持INPLACE算法，但改字段类型、删主键这类操作依然会走COPY。另一个容易被忽略的坑是：800万行的表，ibd文件大概15GB，ALTER时临时文件也会占用差不多大小的空间。如果磁盘空间不够，ALTER会直接失败，而且临时文件可能不会自动清理。

MySQL 8.0有个救星：Instant DDL

如果用的MySQL版本是8.0.12以上，有个好消息。部分DDL操作支持ALGORITHM=INSTANT，毫秒级完成，完全不需要拷数据，堪称大表ALTER的福音。

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

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

但Instant DDL有严格的条件限制。只有在表末尾加字段、改列默认值这类操作才能走INSTANT。如果要在中间插字段、改字段类型、删主键，依然得走INPLACE甚至COPY。

当时要加的字段需要放在中间位置，所以没走成Instant。如果只是为了在末尾加字段，建议先试这个，省得折腾，能极大提升数据库运维效率。

三种方案对比

当Instant DDL条件不满足时，就得考虑其他方案了。踩坑之后，花了一周时间研究了几种主流工具，包括MySQL原生Online DDL、pt-osc和gh-ost。

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的核心是“影子表”——本质上是把大表变更拆成了“小表变更 + 数据迁移”两个步骤，非常适合数据库大表ALTER场景。

原表叫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相比，各有侧重：

生产环境怎么搞

这次踩坑后总结了几条关键原则，用于生产调优：

执行前先确认表的大小、当前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参数才能保留。