pt-online-schema-change 使用解析

最新推荐文章于 2025-05-03 10:05:14 发布

原创

最新推荐文章于 2025-05-03 10:05:14 发布

· 7.5k 阅读

11 ·

版权

文章标签：

#pt-online-schema-change

pt-online-schema-change是一种用于在线修改MySQL表结构的工具。它在ALTER操作期间支持DML，通过创建新表、拷贝数据、触发器同步及rename实现，期间会考虑从库延迟和服务器负载。在执行期间，它对数据库锁的影响减至最低，但会占用大量IO和CPU资源。使用前需确保表有主键或唯一索引，且注意相关环境准备、语法和选项设置。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本文简单介绍 pt-online-schema-change 工具。

1 原理介绍

表格必须带有主键或者唯一索引！！

假设现有tbosc需要做ALTER操作，使用pt-online-schema-change的时候，根据tbddl表结构及索引情况，创建一个新的空表_tbosc_new，然后从原始表格 tbosc 中拷贝数据到新的表格 _tbosc_new，copy data结束后，使用_tbosc_new替换tbddl，同时，删除旧表。

简单流程如上描述，那么详细流程是怎么样的呢？

带着这几个问题来了解：

ALTER操作期间，表格是否支持DML？
如果支持DML，是如何把DML同步到新的临时表上？
整个操作流程锁情况是怎么样的？
执行期间有什么性能影响？
该工具有什么限制情况？

1.1 详细执行流程

如何查看其详细的执行流程呢？数据库开启general log，然后执行pt-online-schema-change，它对数据库的所有操作，就都呈现在眼前。

详细执行流程如下：

相关环境参数检查
检查该表格是否存在
show create table tbosc
create table _tbosc_new
alter table _tbosc_new
创建删除触发器 pt_osc_dbddl_tbosc_del (如果数据修改的时候，还没有拷贝过来，修改后再拷贝则是覆盖，正确；如果是已经拷贝过来，再修改，也是正确，这里同时会检查是否具有主键或者唯一索引，如果都没有，这一步会报错，提示The new table `dbosc`.`_tbosc_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.）
创建更新触发器 pt_osc_dbddl_tbosc_upd
创建插入触发器 pt_osc_dbddl_tbosc_ins
按块拷贝数据到新表，拷贝过程对数据行持有S锁
analyze 新表
rename 表名，RENAME TABLE `dbddl`.`tbosc` TO `dbddl`.`_tbosc_old`, `dbddl`.`_tbosc_new` TO `dbddl`.`tbosc`
删除旧表
删除新表上的删除、更新、插入触发器

1.2 问题解答

根据其执行流程，可以对一开始的提问一一来解答。

ALTER操作期间，表格是否支持DML？
- ALTER过程采用Copy Table To New Table的方式，新建一个表格，然后在原表上创建3个触发器：DELETE\UPDATE\INSERT触发器，一旦新表，拷贝数据到新表的过程中，如果原表数据发生变化，则会通过触发器更新到新表上。
- 如果支持DML，是如何把DML同步到新的临时表上？
- ALTER过程采用Copy Table To New Table的方式，新建一个表格，然后在原表上创建3个触发器：DELETE\UPDATE\INSERT触发器，一旦新表，拷贝数据到新表的过程中，如果原表数据发生变化，则会通过触发器更新到新表上。
- INSERT原表的时候，触发器根据其主键ID把新纪录INSERT到新表上；
- UPDATE原表的时候，触发器根据其主键ID判断新旧ID是否一致，如果一致则删除，然后在REPLACE INTO新纪录到新表
- DELETE原表的时候，触发器根据其主键ID直接删除行记录
- 如果数据修改的时候，还没有拷贝到新表，修改后再拷贝，虽然重复覆盖，但是数据也没有出错；如果是数据已经拷贝，原表发生修改，这时触发器同步修改数据，两种情况下都保证了数据的一致性；
整个操作流程锁情况是怎么样的？
- 创建新表后，按照每一个chunk的大小拷贝数据到新表，每次SELECT都是share mode，带S锁，但是每个chunk都比较小，所以锁时间不大
- 最后数据拷贝结束，会有一个rename操作，这个操作过程中，是不支持DML操作的，但其速度很快，不会造成长时间锁表情况
- 该工具会设置该DDL操作的锁等待超时为1s，当出现异常的时候，会是ALTER操作异常，而不是其他业务操作异常，这样可以最大程度的不影响其他事务的进行

执行期间有什么性能影响？

- 总体而言，对数据库的锁影响降低到了最小，执行期间允许DML操作
- 但是注意，任何DDL SQL在这里，都是转换成copy table to new table的形式，这个过程中，会极大占用磁盘的IO跟CPU资源，同时跟住从延时带来一定的影响，还是那句老话，重复了解DDL的影响程度后，再选择合适时机执行。
- copy data过程中，如果主从延迟异常超过 max-lag则停止copy da