高并发场景下的MySQL无锁变更：选Online DDL还是PT-OSC？

在MySQL数据库运维中，表结构变更（DDL）是常见的操作需求，但对于千万级甚至亿级的大表，传统的ALTER TABLE操作可能引发长时间锁表，导致业务中断或性能雪崩。

如何在不影响业务的前提下安全执行DDL？今天从实战角度出发，给大家深入剖析原生Online DDL与PT-OSC（Percona Toolkit Online Schema Change）两大无锁变更方案，结合具体示例与避坑指南，助你轻松应对大表结构变更难题。

一、原生Online DDL：轻量级变更的首选

1. 核心原理

MySQL 5.6+的Online DDL通过ALGORITHM=INPLACE和LOCK=NONE参数实现“原地修改”，避免全表拷贝。

• ALGORITHM=INPLACE 表示执行DDL的过程中不发生表拷贝；另一种是COPY，就是之前的拷贝方式。一般都是默认，让 MySQL 以尽量保证 DML 并发操作的原则选择执行方式。

Copy方式简单过程：首先按照原表定义创建一个新的临时表，然后对原表加写锁（禁止DML，允许select），接着在步骤1创建的临时表执行 DDL，然后将原表中的数据copy到临时表，最后释放原表的写锁将原表删除，并将临时表重命名为原表

• LOCK=NONE 表示对 DML 操作不加锁，DDL 过程中允许所有的 DML 操作。此外还有 EXCLUSIVE（持有排它锁，阻塞所有的请求）、SHARED（允许SELECT，但是阻塞INSERT UPDATE DELETE）和 DEFAULT（根据DDL的类型，在保证最大并发的原则下来选择LOCK的取值）。

其核心流程分为三个阶段：

• Prepare阶段：短暂持有排他元数据锁（MDL），禁止其他DDL操作。
• DDL执行阶段：降级为共享锁，允许DML操作，通过日志（Row Log）记录变更期间的增量数据。
• Commit阶段：再次升级为排他锁，合并增量数据并提交变更。

2. 适用场景与示例

场景1：添加二级索引

ALTER TABLE user_order ADD INDEX idx_user_id (user_id), ALGORITHM=INPLACE, LOCK=NONE;  

优势：无需重建表，仅修改元数据，允许并发DML操作。

场景2：扩展VARCHAR列长度

ALTER TABLE product MODIFY description VARCHAR(500), ALGORITHM=INPLACE, LOCK=NONE;  

限制：VARCHAR列所需的长度字节数必须保持不变。0~255需要一个字节来编码值,大于等256需要两个字节来编码。使用In Place ALTER TABLE只支持将VARCHAR列大小从0到255字节增加，或从256字节增加到更大的大小。反之需要使用ALGORITHM=COPY才可以。

二、PT-OSC：复杂变更的“万能钥匙”

pt-osc 主要用于修改表时不锁表，简单地说，首先创建一个与原始表一样的新的空表，并根据需要更改表结构，因为是空表修改表结构速度会非常快，然后将原始表中的数据以小块形式复制到新表中，接着删除原始表，最后将新表重命名为原始名称。在复制过程中，对原始表的所有新的更改（insert，delete，update）都将应用于新表，因为在原始表上创建了一个触发器，以确保所有新的更改都将应用于新表。

1. 核心原理

PT-OSC通过影子表+触发器实现无锁变更：

1. 创建与原表结构一致的影子表（_table_new）；
2. 在影子表执行DDL；
3. 通过触发器同步原表的增量变更；
4. 分批拷贝数据，最后原子化切换表名。

2. 适用场景与示例

场景1：修改列类型（如CHAR转VARCHAR）

pt-online-schema-change --alter "MODIFY c VARCHAR(200) NOT NULL DEFAULT ''" \  
--user=admin --password=*** --host=127.0.0.1 --port=3306 \  
--execute D=testdb,t=user_info --no-check-alter  

说明：需确保原表有主键，且新列允许NULL或指定默认值。

场景2：删除外键约束

pt-online-schema-change --alter "DROP FOREIGN KEY _fk_order_user" \  
--alter-foreign-keys-method=rebuild_constraints \  
--user=admin --password=*** --execute D=testdb,t=order  

关键参数：--alter-foreign-keys-method处理外键依赖，优先选择rebuild_constraints重建约束。

三、混合方案：灵活应对极端场景

1. Online DDL + PT-OSC组合

场景：需同时添加索引并修改列类型。

• 步骤1：使用Online DDL快速添加索引（避免PT-OSC的拷贝开销）。
• 步骤2：通过PT-OSC修改列类型（避免Online DDL的列类型限制）。

2. 主从切换策略

1. 在从库执行PT-OSC变更；
2. 主从切换后，在新主库执行变更；
3. 适用于高可用架构，减少主库压力。

四、实战注意事项与避坑指南

1. 负载监控：
   1. PT-OSC通过--max-load和--max-lag控制负载，避免拖垮数据库。
   2. Online DDL需关注Innodb_rows_read和锁等待时间。
2. 磁盘空间：
   1. PT-OSC需要至少原表1.5倍的磁盘空间。
   2. Online DDL的tmpdir需足够存放临时文件。
3. 触发器冲突：
   1. PT-OSC无法在已有触发器的表上运行，需提前备份并删除触发器。
4. 外键处理：
   1. PT-OSC需显式指定--alter-foreign-keys-method，否则任务失败。

五、总结与互动

总结：

• Online DDL适合简单、支持INPLACE的操作（如索引变更），速度快且资源占用低。
• PT-OSC适合复杂变更（如修改主键、列类型），但需额外空间与主键约束。