MySQL ONLINE DDL 工具之pt-online-schema-change

pt-online-schema-change（下面简称pt-osc）是 Percona公司提供的一种在线改表的工具，也是目前业界使用最普遍和熟知一种工具。下面我们就从工作流程、使用限制和风险等方面做一个整体了解。

1.1 主要工作流程

pt-osc的主要工作流程如下：

创建影子表：创建t1表的副本_t1_new。此时 _t1_new 里没有任何数据，表结构和t1完全相同；
在影子表上执行变更：在 _t1_new 表上添加列c4 ，执行语句为 ALTER 语句，因为此时 _t1_new里没有数据，所以变更很快完成；
创建触发器：在表t1上创建触发器，分别对应INSERT，DELETE和UPDATE操作。创建触发的目的就是为了在变更期间发生在t1上的DML操作同步到_t1_new上，保证数据的一致性。
1. INSERT触发器
```
CREATE TRIGGER `pt_osc_test_t1_ins` 
AFTER INSERT ON `test`.`t1` FOR 
EACH ROW REPLACE INTO `test`.`_t1_new` (`id`, `col1`, `col2`, `col3`) 
VALUES (NEW.`id`, NEW.`col1`, NEW.`col2`, NEW.`col3`);
```
  在原表中的插入操作，对于每一条新增的数据，在影子表中都会执行一条REPLACE INTO 操作。对于原表来说，若是能插入成功，那么在影子表中也能插入成功；在原表中插入不成功也就不会触发触发器的执行，也就是影子表不会有任何变化，那么为什么原表的INSERT操作会触发影子表的INPLACE操作呢？这是因为在MYSQL中，REPLACE操作也会触发INSERT触发器，所以这里触发器的动作若是改成INSERT操作，那么在原表上的REPLACE操作在触发INSERT操作上时就很可能会报错，导致影子表的数据没有变更，从而导致数据丢失。
2. DELETE触发器
```
CREATE TRIGGER `pt_osc_test_t1_del` 
AFTER DELETE ON `test`.`t1` FOR
EACH ROW DELETE IGNORE FROM `test`.`_t1_new` 
WHERE `test`.`_t1_new`.`id` <=> OLD.`id`
```
  DELETE触发器相对比较简单，在原表的DELETE操作，每删除一行都会触发在影子表上的删除动作，只是注意触发器上的是 DELETE IGNORE ，因为在原表上删除的数据，在影子表上可能存在也可能不存在。
3. UPDATE触发器
```
CREATE TRIGGER `pt_osc_test_t1_upd` 
AFTER UPDATE ON `test`.`oldmapping` FOR EACH ROW 
BEGIN 
DELETE IGNORE FROM `test`.`_t1_new` 
WHERE !(OLD.`id` <=> NEW.`id`) AND `test`.`_t1_new`.`id` <=> OLD.`id`;
REPLACE INTO `test`.`_t1_new` (`id`, `col1`, `col2`, `col3`) 
VALUES (NEW.`id`, NEW.`col1`, NEW.`col2`, NEW.`col3`);
END
```
  先看触发器中的 REPLACE INTO 语句，在原表更新数据的情况下，对于每一条发生更新的数据，都会触发在影子表上的REPLACE INTO操作。对于影子表，若存在被更新的数据就会更新相应数据，但是对于不存在的数据就会添加到影子表中，由于同步原表数据是使用的INSERT IGNORE INTO 这种语句，所以即便这里提前将数据添加到影子表也不影响。但是为什么这里还是REPLACE INTO 操作呢？因为在MySQL中，INSERT INTO ... ON DUPLICATE UPDATE 也会更新数据，触发的也是UPDATE触发器。
  
  再看触发器中的DELETE IGNORE 语句，这个语句主要为了在原表上主键（或者唯一键）的值发生变更时，先删除影子表中的对应的数据，然后使用REPLACE INTO 在影子表中插入变更后的数据。若是没有DELETE 操作，那么执行REPLACE INTO 后影子表上就会多出一条更新前的数据，导致数据不一致。
同步数据：循环将数据库从T1拷贝到 _t1_new，主要执行的就是INSERT ... SELECT ... LOCK IN SHARE MODE。注意此时正在同步的数据是无法进行DML操作的；另外根据选项设置，每次循环时都会监控主从延迟情况或着数据库负载情况。

这里有一个有趣的事情，pt-osc是怎么获取现有数据的上下边界的呢？换句话说若是需要变更的表的主键为自增列（ID），那么同步到ID的哪个值原始数据才算是同步完成呢？
1. 在开始第一次数据同步前，会先获取整个原始数据的下边界，也是第一次循环的下边界
```
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM t1  FORCE INDEX(`PRIMARY`) ORDER BY `id` LIMIT 1 /*first lower boundary*/
 // 假设返回数据是 1
```
2. 然后获取本次循环的上边界和下次循环的上边界
```
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM t1  FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) ORDER BY `id` LIMIT 999, 2 /*next chunk boundary*/
// `id` >= '1' 中 1 是 a步骤中获取的,也就是本次循环的下边界值
// LIMIT 999,2 中 999 和 --chunk-size 设置有关，--chunk-size 减 1； 2是固定的。假如返回两条数据1000,10001，那么第一条数据1000就是本次循环的上边界，第二条数据10001是下次循环的下边界
```
3. 同步数据
```
INSERT LOW_PRIORITY IGNORE INTO `_t1_new` (`id`, `c1`, `c2`, `c3`) SELECT `id`, `c1`, `c2`, `c3`
 FROM t1  FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) AND ((`id` <= '1000')) LOCK IN SHARE MODE /*pt-online-schema-change 11260 copy nibble*/
 // 1000 是步骤b中获取的上边界值
```
4. 循环步骤b 获取上下边界
```
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM t1  FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '10001')) ORDER BY `id` LIMIT 999, 2 /*next chunk boundary*/
// 10001 是上次循环执行步骤b获取的下边界值
```
  - 若上面能返回两条数据，那么本次循环还不能将t1中未同步的的数据全部同步；
  - 若上面只返回一条数据，则本次循环刚好能将未同步的的数据全部同步，数据的上边界就是此次获取的id值；此次数据同步完，进入步骤5；
  - 若上面返回数据为空，则本次循环也能将未同步的的数据全部同步，且同步的数据量小于 --chunk-size 设置的数量，需要执行下面语句获取此次循环的上边界。
```
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM t1  FORCE INDEX(`PRIMARY`) ORDER BY `id` DESC LIMIT 1 /*last upper boundary*/
```
  这里还要考虑一点，基于上面的方式，已经通过INSERT触发器插入到_t1_new表的数据，是不是会被重复插入？若是t1上的INSERT速度大于数据同步的速度，那是不是数据同步就会一直持续，表的变更也就一直不能完成呢？
5. 循环步骤c 同步数据
分析表：确认数据拷贝完成后执行ANALYZE TABLE 操作，这一步主要是为了防止执行完第六步以后，相关的SQL无法选择正确的执行计划；
更改表名：RENAME TABLE t1 TO _t1_old, _t1_new TO t1;
删除原始表：删除原始表 _t1_old和触发器。

从上面步骤可以看出，pt-osc是先在空的影子表上执行DDL变更，这样无论MySQL的版本是否支持ONLINE DDL，都不会影响原始表上的操作，并且对于空表的结构和属性变更是非常快的。

1.2 使用限制和风险

1）使用限制

由于pt-osc需要使用触发器来同步表上的变更，所以在使用时也有一些相应的限制：

原始表上必须有主键或者唯一键，因为创建的DELETE 触发器依赖主键或者唯一键进行数据同步；不过，若原始表上没有主键或者唯一键，但是即将执行的变更包含创建主键或唯一键的操作也可以；
原始表上不能存在触发器；
pt-online-schema-change 适用于 Percona XtraDB Cluster (PXC) 5.5.28-23.7 及更高版本，但有两个限制：只能更改 InnoDB 表，并且 wsrep_OSU_method 必须设置为 TOI。如果主机是集群节点并且表是 MyISAM 或正在转换为 MyISAM (ENGINE=MyISAM)，或者wsrep_OSU_method 不是 TOI，则该工具将退出并报错。

2）使用风险

更改列名：
1. 使用CHANGE方式更改非主键或者非唯一键的列名
  
  例如语句如下：
```
pt-online-schema-change -u user -ppasswd  -h127.0.0.1 -P3308 D=test,t=ptosc  --alter "change expect_time expecttime varchar(30) NOT NULL DEFAULT '' " --print --execute
```
  该语句不会执行，而是会抛出警告并推迟，警告如下：
```
The tool should handle this correctly, but you should test it first because if it fails the renamed columns' data will be lost!  Specify --no-check-alter to disable this check and perform the --alter
```
  大概意思就是该工具正常情况是能正确执行的，但是更改列名若是失败可能会导致数据丢失，所以这种操作我们可以先使用 --dry-run 选项打印一下相关操作的语句，确认是否有问题。若没有问题可以在上面的语句上加上 --no-check-alter 选项，语句就能正常执行了。
2. 更改主键或者唯一键的列名
  
  pt-osc 创建的DELETE触发器是依赖表的主键或者唯一键的，所以若表上只有唯一键或者主键（若两者都有，也一般会使用主键），那么请不要更改对应列名，这会导致在原始表上执行的删除操作报错，并且无法同步到影子表，导致最终数据不一致。
  
  例如以下语句：
```
pt-online-schema-change -u user -ppasswd  -h127.0.0.1 -P3308 D=test,t=ptosc --alter "change id id_new int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key' "  --print --dry-run --check-alter
```
  注意以下输出：
```
Using original table index PRIMARY for the DELETE trigger instead of new table index PRIMARY because the new table index uses column id_new which does not exist in the original table.
CREATE TRIGGER `pt_osc_test_ptosc_del` AFTER DELETE ON `test`.`ptosc` FOR EACH ROW DELETE IGNORE FROM `test`.`_ptosc_new` WHERE `test`.`_ptosc_new`.`id` <=> OLD.`id`
```
  显而易见，由于原始表和影子表上的主键列列名不一致，导致触发器创建的是有问题的。
3. 先删除列然后添加改名后列
  
  这种情况下，pt-osc不会将删除前列的数据同步到改名后的列。
更改有外键引用的表的结构或者属性

更改有外键引用的表会让操作比较负载，目前 pt-osc 提供三种方式处理外键：

rebuild_constraints：该方式会在第六步更改表名后，执行一个原子操作先删除外键再重新添加外键。

drop_swap：该方式会在第六步更改表名前删除原始表，然后将影子表重命名。这会导致表短暂的不存在，若是对表的查询很频繁会导致错误。

none：该方式执行的操作和处理无外键引用的表是相同的，但是外键实际上是引用了已经删除的表。

以上方式的具体解释请参看--alter-foreign-keys-method 的具体解释。
创建唯一索引或者主键

由于pt-osc使用INSERT LOW_PRIORITY IGNORE 方式同步原始表和影子表之间的数据，所以若新建唯一索引的列上有重复数据将会导致数据的丢失。若是需要创建唯一索引或主键需要提前确认数据是否重复，是否允许缺失等，需要指定选项 --no-check-alter。
锁争用问题

另外还有一个需要注意的问题，由于表上创建有触发器，若表的更新此时比较频繁很可能遇见锁争用问题。之前在给线上表增加索引时就遇见过这种问题，应用端频繁的报死锁错误，在停止pt-osc并删除触发器后死锁问题解决。

1.3 丰富的监控功能

该工具除了提供更改表结构的功能外，还提供了其他非常丰富和友好的功能，如：

该工具可以监控变更期间主从延迟情况，默认是监控所有的从库，若发现有其中一个从库延迟时间超过了 --max-lag 参数设置的数值，则该工具会停止新旧表表之间的数据同步，直到复制延迟低于 --max-lag 设置的数值。由于生产环境很多时候是一主多从的架构，我们可能只关心某一（几）台从库的延迟情况，这个时候可以使用 --check-slave-lag参数指定需要关注的从库节点。
该工具可以监控变更期间数据库的负载情况，其对应选项为 --max-load 或者 --critical-load。

指定了--max-load 选项后，pt-osc会在每次同步数据后执行 SHOW GLOBAL STATUS 查看选项定义的需要关注的状态参数，若状态变量高于阈值则会暂停数据同步。--max-load 可以执行单个或多个状态变量，如可以设置为 “Threads_connected:110” or “Threads_connected=110”，也就是Threads_connected 超过110时会暂定数据同步。

--critical-load 和--max-load 类似，只是当指定的状态变量超过阈值时 pt-online-schema-change 会退出，并删除创建的触发器等。这是为了防止由于添加触发器而导致数据库负载异常情况发生。
该工具默认设置 innodb_lock_wait_timeout=1 and (for MySQL 5.5 and newer) lock_wait_timeout=60 ，以防在发生锁争用时阻塞其他正常业务的事务执行。若要更改或者设置其他参数，可以通过 --set-vars 参数设置。