使用 pt-online-schema-change 实现在线DDL

时间:2024-01-06 18:47:02

问题背景

平时进行修改表的结构,更改字段,新增字段,更改字段名称一般都是通过ALTER TABLE  语法进行修改的。对于小表或者并发访问不是很大的情况是OK。但是如果是在线大表,那就很麻烦。由于表数据量大,复制表需要比较长的时间,在这个时间段里面,表是被加了锁的(写锁),加写锁时其他用户只能select表不能update、insert表。表数据量越大,耗时越长。

mysql在线ddl(加字段、加索引等修改表结构之类的操作)过程如下:

  1. 对表加锁(表此时只读)
  2. 复制原表物理结构
  3. 修改表的物理结构
  4. 把原表数据导入中间表中,数据同步完后,锁定中间表,并删除原表
  5. rename中间表为原表
  6. 刷新数据字典,并释放锁

可见,在这个过程中会锁表。造成当前操作的表无法写入数据,影响用户使用。由于需要复制原表的数据到中间表,所以表的数据量越大,等待的时候越长,卡死在那里(用户被拒绝执行update和insert操作,表现就是延迟了一直在等待)。

对于DDL操作一个基本的想法:它的变化是就地执行还是执行表拷贝, 在命令结束之后看看显示“rows affected “的值。例如,这里您可能会看到在做不同类型的DDL操作: 修改列默认值(超级快,不影响表的所有数据): Query OK, 0 rows affected (0.07 sec) 添加索引 (需要时间, 但0 rows affected 表明表没有被复制): Query OK, 0 rows affected (21.42 sec) 改变列的数据类型(需要大量的时间和需要重建表中的所有行): Query OK, 1671168 rows affected (1 min 35.54 sec) 例如, 在一个大表运行一个DDL操作之前,你可能会检查操作是将快还是慢,如下所示: 克隆表结构。 用少量数据填充克隆的表。 在克隆的表运行DDL操作。 检查 “行受影响”的值是否为零或不是。一个非零值意味着操作需要重建整个表,这可能需要特殊的规划。例如,你可能在计划停机期间做DDL操作,或在复制每个从服务器。

解决方案

percona 的 pt-online-schema-change 工具原理:

1、如果存在外键,根据alter-foreign-keys-method参数的值,检测外键相关的表,做相应设置的处理。
2、创建一个新的表,表结构为修改后的数据表,用于从源数据表向新表中导入数据。
3、创建触发器,用于记录从拷贝数据开始之后,对源数据表继续进行数据修改的操作记录下来,用于数据拷贝结束后,执行这些操作,保证数据不会丢失。
4、拷贝数据,从源数据表中拷贝数据到新表中。
5、修改外键相关的子表,根据修改后的数据,修改外键关联的子表。
6、rename源数据表为old表,把新表rename为源表名,并将old表删除。
7、删除触发器。

可见,复制表的时候无需加锁,不影响原表继续接受写请求;