MySQL pt-online-schema-change无锁修改表结构的原理

在生产环境中修改大表结构是一项高风险操作。MySQL 默认的 ALTER TABLE 语句在执行期间通常会锁表，导致应用无法写入，甚至阻塞读取，这在高并发场景下是不可接受的。pt-online-schema-change（简称 pt-osc）是 Percona Toolkit 工具包中的一个核心工具，它通过一种“影子拷贝”的机制，实现在不锁表的情况下在线修改表结构。

其核心原理可以概括为：创建一个空表，修改其结构，然后通过触发器将原表的数据增量同步到新表，待数据同步完成后，瞬间切换表名。

核心工作流程

为了直观理解该工具的执行逻辑，我们可以通过以下流程图查看其内部运作步骤。

详细操作步骤与原理

以下是 pt-online-schema-change 在后台执行的具体技术步骤，每一步都旨在保证数据一致性和业务连续性。

1. 创建影子表

工具首先会连接到 MySQL 数据库，创建一个与原表结构完全一致的空表。这个新表通常被称为“影子表”，其表名通常是在原表名后加下划线和后缀（例如 user 变为 _user_new）。

执行 类似以下的 SQL 语句：

CREATE TABLE `db`.`_user_new` LIKE `db`.`user`;

2. 修改影子表结构

在影子表创建完成后，工具会在这个空表上执行用户传入的 ALTER 语句。由于此时影子表是空的，修改操作瞬间完成，且不影响原表。

执行 类似以下的 SQL 语句：

ALTER TABLE `db`.`_user_new` ADD COLUMN `phone` VARCHAR(20);

3. 创建触发器同步增量数据

这是 pt-osc 最关键的一步。为了确保在拷贝历史数据期间，原表新产生的数据也能进入影子表，工具会在原表上创建三个触发器，分别对应 INSERT、UPDATE 和 DELETE 操作。

这三个触发器的逻辑如下：

执行 类似以下的 SQL 语句（以 Insert 为例）：

CREATE TRIGGER `pt_osc_user_ins` AFTER INSERT ON `db`.`user`
FOR EACH ROW
REPLACE INTO `db`.`_user_new` (id, name, ...) VALUES (NEW.id, NEW.name, ...);

此时，原表的所有写操作都会被“双写”一份到影子表。

4. 分块拷贝历史数据

增量同步机制建立后，工具开始拷贝原表中已有的历史数据到影子表。为了避免长时间锁表或占用过多资源，pt-osc 不会一次性拷贝所有数据，而是将数据拆分成一个个小的数据块。

执行 分批查询和插入：

-- 这是一个简化的逻辑示意
INSERT INTO `db`.`_user_new` (id, name, ...) SELECT id, name, ... FROM `db`.`user` WHERE id > 1000 LIMIT 1000;

工具会循环执行这个过程，每次拷贝一定数量的行（默认是 1000 行），并在每次拷贝之间短暂休眠，以降低服务器负载。

5. 数据同步与校验

在分块拷贝的过程中，如果有新数据写入原表，步骤 3 中的触发器会自动捕获这些变更并同步到影子表。这意味着，当拷贝过程结束时，影子表中不仅包含了修改前的所有历史数据，还包含了拷贝期间发生的所有增量数据，理论上两个表的数据此时已完全一致。

6. 原子性重命名表名

当历史数据拷贝完成，且没有显著的复制延迟后，工具进入最后阶段。它会开启一个短暂的读锁，确保此时没有新的写入进入原表，然后瞬间执行表名交换操作。

这个操作利用了 MySQL 的 RENAME TABLE 命令的原子性特性，修改元数据锁，速度极快（通常在毫秒级）。

执行 以下 SQL 语句：

RENAME TABLE `db`.`user` TO `db`.`_user_old`, `db`.`_user_new` TO `db`.`user`;

这一步完成后，业务逻辑访问的 user 表实际上已经是那个结构修改过的新表了，锁随即被释放。

7. 清理旧表

最后，工具删除原来的旧表（即 _user_old）以及之前创建的三个触发器，释放磁盘空间和系统资源。

执行 以下 SQL 语句：

DROP TABLE `db`.`_user_old`;
DROP TRIGGER `pt_osc_user_ins`;
DROP TRIGGER `pt_osc_user_upd`;
DROP TRIGGER `pt_osc_user_del`;

关键限制与注意事项

虽然 pt-osc 能实现无锁变更，但了解其原理后，我们必须注意以下限制，否则可能引发问题。