非阻塞改表工具：pt-online-schema-change

源代码00

已于 2023-05-08 11:30:08 修改

阅读量190

点赞数 1

文章标签：数据库 mysql sql

于 2023-05-08 11:25:57 首次发布

本文链接：https://blog.csdn.net/qq_42694809/article/details/130555404

版权

pt-osc使用场景

事务A：执行一个非常大的查询
事务B：执行了DDL操作

结果

事务A执行查询时会持有MDL锁，而事务B同样也需要MDL锁，但事务A在进行大查询，所以导致事务B后的所有操作都会被堵塞

解决方案

该情况常见于大表热表的改表，可使用pt-online-schema-change工具解决

pt-osc原理

执行流程

1、相关环境参数检查
2、检查该表格是否存在
3、show create table tbosc
4、create table _tbosc_new
5、alter table _tbosc_new
6、创建删除触发器 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.）
7、创建更新触发器 pt_osc_dbddl_tbosc_upd
8、创建插入触发器 pt_osc_dbddl_tbosc_ins
9、按块拷贝数据到新表，拷贝过程对数据行持有S锁
10、analyze 新表
11、rename表名：

RENAME TABLE dbddl.tbosc TO dbddl._tbosc_old；
dbddl._tbosc_new TO dbddl.tbosc

12、删除旧表
13、删除新表上的删除、更新、插入触发器

DDL期间DML

DDL过程采用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 data，等待主从延迟恢复，默认为1min，可以通过--max-lag设置
检测到服务器负载异常，也会停止操作，可以通过 --max-load，--critical-load设置

限制情况

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

pt-osc唯一约束的校验逻辑代码：

SELECT IF(COUNT(DISTINCT `fields`) = COUNT(*)

当索引涉及的字段存在null的情况，执行DISTINCT时字段会被清理，count时不会包括null行，导致前置的索引参数检查发生异常，终止执行，返回报错：
No, the desired unique index contains duplicated values. There will be data loss