首先要说明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)
- 定义三个触发器
delete 触发器:
CREATE TRIGGER pt_osc_test_testpt_osc_del
AFTER DELETE ON test
.testpt_osc
FOR EACH ROW
DELETE IGNORE FROM test
._testpt_osc_new
WHERE test
._testpt_osc_new
.id
<=> OLD.id
update 触发器:
CREATE TRIGGER pt_osc_test_testpt_osc_upd
AFTER UPDATE ON test
.testpt_osc
FOR EACH ROW
BEGIN
DELETE IGNORE FROM test
._testpt_osc_new
WHERE !(OLD.id
<=> NEW.id
)
AND test
._testpt_osc_new
.id
<=> OLD.id
;
REPLACE INTO test
._testpt_osc_new
(id
, name
) VALUES
(NEW.id
, NEW.name
);
ENDinsert 触发器:
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操作前或者后进行其它的操作,下面是我定义的两个测试的触发器如下:
显然如果对t1表进行数据插入,那么会在之前向t2表插入一条数据,然后在之后向t3插入一条数据,这一点可以通过函数调用trace进行验证如下:
这里就能够看到顺序了,其次我们还需要知道这些所有的操作会包裹在一个事务里面,这一点也可以通过函数调用trace进行验证,还可以使用binlog进行验证,下面是一次调用的binlog信息:
这里我们使用binlog不仅验证了执行顺序并且还验证了所有操作都包含在一个事务里面。既然所有的语句都包裹在一个事务里面,那么加锁的范围就更大了,这不仅关系到本身的DML操作表,并且还关系到触发语句的相关表,需要额外注意。
其次所有语句不仅包裹在一个事务里面,并且共享一个错误返回接口,那么如下的错误:
我们惊讶的发现t1表一条数据都没有,但是居然返回重复的行。原因就在于虽然t1表没有数据,但是t2或者t3表有违反唯一性检查的可能,因此返回了错误,错误由统一的接口返回给客户端。
最后触发器会导致处理逻辑混乱,尽量避免使用触发器。
三、Insert ignore/replace语法
关于ignore语法我们以insert ignore语法为例,一般来讲如果遇到重复行insert ignore语法会通过忽略重复值错误的方式进行跳过,这实际上和replace的处理方式一致,但是replace不同的是如果遇到重复行不是进行忽略,而是执行的delete然后执行insert操作。换句话说他们的触发形式一致,但是触发后执行的行为是不同的,下面我们就来看看。
首先对于insert语句来讲我们需要定位到需要插入的位置,这部分略过。
1、进行重复行判断操作
这一步对于主键/唯一索引 而言需要判断是否已经有重复的行。其判断标准基本都是通过插入的值进行索引定位,然后判断定位游标的值是否和需要插入值相同,下面是栈帧:
主键: