MySQL online DDL阻塞业务事务？pt-online-schema-change与gh-ost改表方案对比

各位朋友，大家好！今天我们来聊聊MySQL在线DDL操作，以及两种常用的在线改表工具：pt-online-schema-change 和 gh-ost。在线DDL操作，顾名思义，就是在不停止数据库服务的情况下修改表结构。这在生产环境中至关重要，因为长时间的停机维护会对业务造成严重影响。但是，传统的DDL操作往往会锁表，阻塞业务事务，所以在线DDL技术应运而生。

为什么传统的DDL操作会阻塞业务？

传统的ALTER TABLE操作在MySQL中通常是阻塞的，原因在于：

锁表： 在修改表结构时，MySQL会获取表级别的锁，防止其他事务修改表结构或数据。这会导致并发访问该表的事务被阻塞。
重构表： 许多DDL操作，例如添加或删除列，会涉及创建一个新的表结构，并将原表数据复制到新表中。这个过程非常耗时，尤其是在数据量大的情况下。在此期间，原表会被锁定，业务无法正常进行。

Online DDL的原理

Online DDL的核心思想是化整为零，分而治之。它避免直接锁定原表进行修改，而是通过一系列步骤，逐步完成表结构的变更，从而最大程度地减少对业务的影响。

Online DDL通常包含以下几个步骤：

创建影子表： 创建一个与原表结构相同的新表（影子表）。
执行DDL操作： 在影子表上执行实际的DDL操作，例如添加索引、修改列类型等。
数据复制： 将原表的数据复制到影子表中。这是一个持续的过程，实时同步原表的数据变更到影子表。
切换表名： 当数据复制完成后，将原表重命名，并将影子表重命名为原表的名称。
清理旧表： 删除或重命名原表（旧表）。

在这个过程中，业务可以继续访问原表，只有在切换表名的一瞬间会短暂阻塞。

pt-online-schema-change 详解

pt-online-schema-change (简称pt-osc) 是Percona Toolkit中的一个工具，用于在线修改MySQL表结构。它通过创建触发器来捕获原表的数据变更，并将其同步到影子表中，从而实现在线DDL。

工作流程：

创建影子表： pt-osc 创建一个与原表结构相同的新表（影子表）。
执行DDL操作： 在影子表上执行指定的DDL操作。
创建触发器： pt-osc 在原表上创建三个触发器：INSERT、UPDATE 和 DELETE。这些触发器会将原表的数据变更同步到影子表中。
数据复制： pt-osc 分批次地将原表的数据复制到影子表中。
切换表名： 当数据复制完成后，pt-osc 会锁定原表和影子表，交换它们的表名。
删除触发器： pt-osc 删除原表上的触发器。
清理旧表： pt-osc 可以选择删除或重命名原表。

示例：

假设我们要给 users 表添加一个索引 idx_email 到 email 列。

pt-online-schema-change 
    --alter "ADD INDEX idx_email (email)" 
    --host=127.0.0.1 
    --user=root 
    --password=your_password 
    --database=your_database 
    --table=users 
    --execute

--alter: 指定要执行的DDL操作。
--host, --user, --password, --database, --table: 指定数据库连接信息和表名。
--execute: 执行DDL操作。如果不加 --execute，则只进行 dry-run，不会真正执行。

触发器示例：

pt-osc 在原表上创建的触发器类似如下：

-- INSERT 触发器
CREATE TRIGGER `pt_osc_your_database_users_ins`
AFTER INSERT ON `your_database`.`users`
FOR EACH ROW
BEGIN
    INSERT IGNORE INTO `your_database`.`_users_new` VALUES (NEW.id, NEW.name, NEW.email, NEW.created_at, NEW.updated_at);
END;

-- UPDATE 触发器
CREATE TRIGGER `pt_osc_your_database_users_upd`
AFTER UPDATE ON `your_database`.`users`
FOR EACH ROW
BEGIN
    UPDATE `your_database`.`_users_new` SET id = NEW.id, name = NEW.name, email = NEW.email, created_at = NEW.created_at, updated_at = NEW.updated_at WHERE id = OLD.id;
END;

-- DELETE 触发器
CREATE TRIGGER `pt_osc_your_database_users_del`
AFTER DELETE ON `your_database`.`users`
FOR EACH ROW
BEGIN
    DELETE FROM `your_database`.`_users_new` WHERE id = OLD.id;
END;

优点：

成熟稳定： pt-osc 已经经过了长时间的验证，在生产环境中广泛使用。
功能强大：支持多种DDL操作，例如添加索引、修改列类型、修改字符集等。
灵活配置：提供了丰富的配置选项，可以根据实际情况进行调整。
免费开源： pt-osc 是Percona Toolkit的一部分，可以免费使用。

缺点：

触发器开销：触发器会增加原表的写入开销，可能影响业务性能。尤其是高并发写入的场景。
切换表名短暂阻塞：在切换表名时，需要锁定原表和影子表，会造成短暂的阻塞。虽然时间很短，但仍然需要考虑。
依赖Percona Toolkit：需要安装Percona Toolkit。
主从延迟问题：如果主从延迟较大，可能会导致数据不一致。

gh-ost 详解

gh-ost (GitHub Online Schema Transmogrifier) 是GitHub开源的在线DDL工具。它通过binlog流的方式捕获原表的数据变更，并将其同步到影子表中。

工作流程：

创建影子表： gh-ost 创建一个与原表结构相同的新表（影子表）。
执行DDL操作： 在影子表上执行指定的DDL操作。
连接binlog： gh-ost 连接到MySQL的binlog，监听原表的数据变更。
数据复制： gh-ost 将binlog中的数据变更应用到影子表中。同时，也会分批次地将原表的数据复制到影子表中。
切换表名： 当数据复制完成后，gh-ost 会执行原子切换表名操作。
清理旧表： gh-ost 可以选择删除或重命名原表。

示例：

假设我们要给 users 表添加一个索引 idx_email 到 email 列。

gh-ost 
    --host=127.0.0.1 
    --user=root 
    --password=your_password 
    --database=your_database 
    --table=users 
    --alter="ADD INDEX idx_email (email)" 
    --new-table-name=gho_users 
    --execute

--host, --user, --password, --database, --table: 指定数据库连接信息和表名。
--alter: 指定要执行的DDL操作。
--new-table-name: 指定影子表的名字（默认是 _表名_gho）。
--execute: 执行DDL操作。

优点：

无触发器开销： gh-ost 通过binlog流捕获数据变更，无需在原表上创建触发器，减少了对业务性能的影响。
原子切换表名： gh-ost 使用RENAME TABLE语句进行原子切换表名操作，保证数据一致性。
可控性强：提供了丰富的配置选项，可以控制数据复制的速度、暂停和恢复等。
支持多种DDL操作：支持多种DDL操作，例如添加索引、修改列类型等。

缺点：

依赖binlog：需要开启MySQL的binlog功能。
配置相对复杂： gh-ost 的配置选项较多，需要一定的学习成本。
成熟度稍逊：相比 pt-osc，gh-ost 的成熟度稍逊，社区活跃度也稍低。
主从延迟问题：如果主从延迟较大，可能会导致数据不一致。

pt-online-schema-change vs gh-ost：对比总结

特性	pt-online-schema-change	gh-ost
数据同步方式	触发器	binlog
对业务性能影响	触发器开销	无触发器开销
切换表名方式	锁定交换表名	原子切换表名
配置复杂度	简单	相对复杂
成熟度	高	稍逊
依赖	Percona Toolkit	binlog
主从延迟	敏感	敏感
可控性	较好	更好

如何选择？

如果对业务性能要求极高，且可以接受一定的配置复杂度，建议选择 gh-ost。
如果对稳定性要求极高，且对触发器开销可以接受，建议选择 pt-online-schema-change。
如果主从延迟较大，需要特别关注数据一致性问题，可以考虑使用半同步复制或者调整工具的参数。

缓解Online DDL阻塞业务事务的策略

除了选择合适的工具，还可以通过以下策略来缓解Online DDL对业务事务的影响：

控制数据复制速度： pt-osc 和 gh-ost 都提供了控制数据复制速度的参数，例如 --chunk-time 和 --max-lag。通过调整这些参数，可以降低数据复制对原表的压力。
选择合适的执行时间： 尽量选择业务低峰期执行Online DDL操作。
监控数据库性能： 在执行Online DDL操作期间，需要密切监控数据库的性能指标，例如CPU使用率、IO负载、锁等待等。
提前进行测试： 在生产环境执行Online DDL操作之前，务必在测试环境中进行充分的测试，验证方案的可行性和性能。
使用合适的硬件： 使用高性能的硬件可以提高数据复制的速度，缩短Online DDL的执行时间。
在MySQL 8.0+使用INSTANT DDL: MySQL 8.0 引入了 INSTANT DDL，某些类型的 DDL 操作（例如，添加仅包含 NULL 的列）可以瞬间完成，无需复制数据，从而避免了长时间的阻塞。

代码示例：控制pt-online-schema-change的数据复制速度

pt-online-schema-change 
    --alter "ADD INDEX idx_email (email)" 
    --host=127.0.0.1 
    --user=root 
    --password=your_password 
    --database=your_database 
    --table=users 
    --chunk-time=0.5    # 每个chunk执行时间不超过0.5秒
    --execute

代码示例：控制gh-ost的数据复制速度

gh-ost 
    --host=127.0.0.1 
    --user=root 
    --password=your_password 
    --database=your_database 
    --table=users 
    --alter="ADD INDEX idx_email (email)" 
    --new-table-name=gho_users 
    --max-lag-seconds=5   # 允许的最大主从延迟为5秒
    --execute

总结：选择合适的方案，并做好监控和预防

Online DDL 是解决数据库表结构变更的有效方案，pt-online-schema-change 和 gh-ost 都是优秀的工具。选择哪个工具取决于具体的业务场景和需求，需要权衡各种因素。重要的是，在使用 Online DDL 时，需要充分了解其原理和特性，做好监控和预防措施，才能保证业务的稳定运行。