什么叫做pt-online-schema-change

发布时间：2022-01-17 13:09 所属栏目：115 来源：互联网

导读：什么是pt-online-schema-change，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。一、pt-online-schema-change 原理简析首先要说明pt-online-schema-change工具并不是说修改表结构的

什么是pt-online-schema-change，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。

一、pt-online-schema-change 原理简析
首先要说明pt-online-schema-change工具并不是说修改表结构的时候不上锁，通常我们说的锁一般包含innodb 行锁和MDL lock。而pt-online-schema-change工具就是将某些使用COPY算法的DDL操作使用DML操作来代替，换句话说就是使用Innodb row锁来代替MDL lock，因为MySQL原生的COPY算法的DDL会在MDL lock SNW这个类型保护下完整个表复制操作，整个复制过程中是不允许DML操作，因此造成了我们COPY算法的DDL堵塞线程正常的现象，当然哪些DDL可以online进行可以参考官方文档online ddl一节。整个pt-online-schema-change工具修改过程中，只会在rename阶段才会上MDL LOCK的X锁，但是rename操作一般非常快速。

我们大概看一下pt-online-schema-change的工作方式，这个实际上开启genrnal log就能看出来下面是重点步骤（我的表名叫做testpt_osc）：

首先定义出新表
CREATE TABLE test._testpt_osc_new (
id int(11) NOT NULL,
name varchar(20) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
ALTER TABLE test._testpt_osc_new add index name_index(name)

insert 触发器：
CREATE TRIGGER pt_osc_test_testpt_osc_ins
AFTER INSERT ON test.testpt_osc
FOR EACH ROW
REPLACE INTO test._testpt_osc_new (id, name) VALUES
(NEW.id, NEW.name)

使用分块（chunk）拷贝的方式
首先需要插入数据的确认上界：
SELECT /!40001 SQL_NO_CACHE / id FROM test.testpt_osc FORCE INDEX(PRIMARY)
WHERE ((id >= ‘1’)) ORDERBY id LIMIT 1999, 2 /next chunk boundary/

然后插入：
INSERT LOW_PRIORITY IGNORE INTO test._testpt_osc_new (id, name)
SELECT id, name FROM test.testpt_osc FORCE INDEX(PRIMARY)
WHERE ((id >= ‘1’)) AND ((id <= ‘2000’)) LOCK IN SHARE MODE

最终进行表的重新命名
使用RENAME TABLE test.tp1 TO test._tp1_old, test._tp1_new TO test.tp1
进程重新命名。
从整个过程来讲需要注意的几个地方：

对于delete和update触发器来讲，delete数据均使用了IGNORE进行修饰，因此即便数据还没有拷贝到新表也不会引发错误。
对于update和insert触发器来讲，均使用了replace这种操作来进行，因此如果数据还没有拷贝到新表那么将插入到新表中，如果数据已经拷贝到新表那么将会修改其中的值。因此新表中总是保留的最新的数据。
对于分块拷贝数据而言，使用是insert ignore 新表 select 老表 LOCK S 的方式，因此对于触发器插入的最新值，是不会进行修改的也不会报错。打个比方chunk为200 当前拷贝数据到了1000行，但是我们手动修改了第2000行的数据，那么第2000行将会在update触发器的作用下提前插入到新表中，当拷贝数据来到这一行的时候因为使用了ignore则不会重复行的错误，并且数据是最新的。其次每次insert select操作是一个单独的事务。
insert ignore 新表 select 老表 LOCK S 的方式操作存在对新表中加自增锁的可能，这取决于你的参数设置。
对于触发器而言，原始语句和触发语句被包裹在一个事务里面，也就是说对于任何一个DML语句而言，修改老表和新表的数据需要的行锁将会在一个事务中存在。
pt-online-schema-change 生成的binlog和redo都会比online DDL大得多，效率上讲应该低于online DDL。
由于replace操作的存在，因此pt-online-schema-change将会依赖主键或者唯一键，否则将不能工作。
我们可以看到整个过程中有如下的重点知识点：

触发器和事务
Insert ignore/replace语法
自增死锁的发生
其次对于第4和第5点来讲，有出现死锁的可能。下面我们分别讨论。

二、触发器与事务
在pt-online-schema-change中，触发器占据了重要的地位，我们需要了解一下触发器和事务之间的关系。我们常用的触发器包含了before和after触发器，代表着对原表进行DML操作前或者后进行其它的操作，下面是我定义的两个测试的触发器如下：

CREATE TRIGGER testbef BEFORE INSERT ON t1
FOR EACH ROW
BEGIN
INSERT INTO t2 values(new.id);
END;
CREATE TRIGGER testaft after INSERT ON t1
FOR EACH ROW
BEGIN
INSERT INTO t3 values(new.id);
END;
显然如果对t1表进行数据插入，那么会在之前向t2表插入一条数据，然后在之后向t3插入一条数据，这一点可以通过函数调用trace进行验证如下：

[root@ora12ctest mysql]# cat -n tri2.trace |grep row_ins
   970 T@3: | | | | | | | | | | | >row_ins
   971 T@3: | | | | | | | | | | | | row_ins: table: test/t2 向t2表插入数据
...
  1406 T@3: | | | | | | | | >row_ins
  1407 T@3: | | | | | | | | | row_ins: table: test/t1 向t1表插入数据
...
  1779 T@3: | | | | | | | | | | | | >row_ins
  1780 T@3: | | | | | | | | | | | | | row_ins: table: test/t3 向t3表插入数据
...
这里就能够看到顺序了，其次我们还需要知道这些所有的操作会包裹在一个事务里面，这一点也可以通过函数调用trace进行验证，还可以使用binlog进行验证，下面是一次调用的binlog信息：

# at 194 （这里是GTID EVENT事务开始）
#200212 17:23:16 server id 1903313 end_log_pos 259 CRC32 0x4ff6735e    GTID    last_committed=0        sequence_number=1       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '92008a52-4b7d-11ea-9ec6-000c29c8aca8:202'/*!*/;
# at 259
#200212 17:23:16 server id 1903313 end_log_pos 331 CRC32 0x1ebd3446    Query   thread_id=3     exec_time=0     error_code=0
...
BEGIN
/*!*/;
# at 331
#200212 17:23:16 server id 1903313 end_log_pos 384 CRC32 0xe748dc3a    Rows_query
# INSERT INTO t2 values(new.id)
# at 384
#200212 17:23:16 server id 1903313 end_log_pos 429 CRC32 0x093c5fe3    Table_map: `test`.`t1` mapped to number 108
# at 429
#200212 17:23:16 server id 1903313 end_log_pos 474 CRC32 0x92691238    Table_map: `test`.`t2` mapped to number 110
# at 474
#200212 17:23:16 server id 1903313 end_log_pos 519 CRC32 0x5b9a710f    Table_map: `test`.`t3` mapped to number 111
# at 519
#200212 17:23:16 server id 1903313 end_log_pos 559 CRC32 0xe41b1119    Write_rows: table id 110
# at 559
#200212 17:23:16 server id 1903313 end_log_pos 599 CRC32 0x36c3511c    Write_rows: table id 108
# at 599
#200212 17:23:16 server id 1903313 end_log_pos 639 CRC32 0xa68b9ae6    Write_rows: table id 111 flags: STMT_END_F
### INSERT INTO `test`.`t2`
### SET
###   @1=11000 /* INT meta=0 nullable=0 is_null=0 */
### INSERT INTO `test`.`t1`
### SET
###   @1=11000 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `test`.`t3`
### SET
###   @1=11000 /* INT meta=0 nullable=0 is_null=0 */
# at 639 （这里是XID EVENT事务提交）
#200212 17:23:16 server id 1903313 end_log_pos 670 CRC32 0xbbb6547b    Xid = 19
COMMIT/*!*/;
这里我们使用binlog不仅验证了执行顺序并且还验证了所有操作都包含在一个事务里面。既然所有的语句都包裹在一个事务里面，那么加锁的范围就更大了，这不仅关系到本身的DML操作表，并且还关系到触发语句的相关表，需要额外注意。

其次所有语句不仅包裹在一个事务里面，并且共享一个错误返回接口，那么如下的错误：

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
mysql> insert into t1 values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql>
我们惊讶的发现t1表一条数据都没有，但是居然返回重复的行。原因就在于虽然t1表没有数据，但是t2或者t3表有违反唯一性检查的可能，因此返回了错误，错误由统一的接口返回给客户端。

最后触发器会导致处理逻辑混乱，尽量避免使用触发器。

（编辑：ASP站长网）