MySQL: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）：

从整个过程来讲需要注意的几个地方：

我们可以看到整个过程中有如下的重点知识点：

其次对于第4和第5点来讲，有出现死锁的可能。下面我们分别讨论。

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

显然如果对t1表进行数据插入，那么会在之前向t2表插入一条数据，然后在之后向t3插入一条数据，这一点可以通过函数调用trace进行验证如下：

这里就能够看到顺序了，其次我们还需要知道这些所有的 *** 作会包裹在一个事务里面，这一点也可以通过函数调用trace进行验证，还可以使用binlog进行验证，下面是一次调用的binlog信息：

这里我们使用binlog不仅验证了执行顺序并且还验证了所有 *** 作都包含在一个事务里面。 既然所有的语句都包裹在一个事务里面，那么加锁的范围就更大了，这不仅关系到本身的DML *** 作表，并且还关系到触发语句的相关表，需要额外注意。

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

我们惊讶的发现t1表一条数据都没有，但是居然返回重复的行。 原因就在于虽然t1表没有数据，但是t2或者t3表有违反唯一性检查的可能，因此返回了错误，错误由统一的接口返回给客户端。

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

关于ignore语法我们以insert ignore语法为例，一般来讲如果遇到重复行insert ignore语法会通过忽略重复值错误的方式进行跳过，这实际上和replace的处理方式一致，但是replace不同的是如果遇到重复行不是进行忽略，而是执行的delete然后执行insert *** 作。换句话说他们的触发形式一致，但是触发后执行的行为是不同的，下面我们就来看看。

首先对于insert语句来讲我们需要定位到需要插入的位置，这部分略过。

这一步对于主键/唯一索引 而言需要判断是否已经有重复的行。其判断标准基本都是通过插入的值进行索引定位，然后判断定位游标的值是否和需要插入值相同，下面是栈帧：

如果存在重复的行，这需要进行判断了：

因为我们知道通常insert锁并不会建立显示的锁。对于如果出现了重复的行，持有重复行数据的事务并没有提交或者回滚，需要其事务完成提交或者回滚，然后再进行相应的抛错或者继续插入。需要注意的是对于replace/insert on dup 在进行唯一性检查的时候，通常加的LOCK_S锁，而其他 *** 作通常加的是 LOCK_X。

当然如果没有重复的行，那么接下来就可以继续进行insert插入 *** 作了，Insert ignore/replace实现都是进行insert *** 作。如果有重复行呢？那么接下来进行分析。

这里我们也很明白了，对于了insert ignore/replace是通过主键/唯一键进行判断是否重复行的，具体点来说就是如何处理错误HA_ERR_FOUND_DUPP_KEY。 如果表中一个能够判断唯一性的索引都没有，那么即便2条数据一模一样也不会标记为重复行，视为2条不同的数据 ，当然insert on dup 这里也是同样的逻辑。

在进行唯一性检测的时候，会先检查主键的唯一性，然后依次检查各个唯一索引的唯一性是否满足。

首先对于多行插入和insert select来讲，每次innodb层插入的行数为1行，我们应该牢牢树立以行为单位的处理流程，我们可以在函数Sql_cmd_insert::mysql_insert 中找到 一个大的while 循环，这就是处理的循环。

我们也需要明白，进行判断唯一性的时候是先判断主键的唯一性，如果满足则插入主键数据，然后依次判断二级唯一索引，如果满足则进行插入。这里涉及到一个问题，如果主键数据插入了，但是二级唯一索引由于违法唯一性那么，前面主键插入的数据是需要回滚的。再或者我们执行的insert select *** 作，其中前面的一些行不违反唯一性插入了，但是随后的某行违法了唯一性，那么前面插入的数据也是需要回滚的。函数row_insert_for_mysql_using_ins_graph 中进行这种逻辑处理。

回滚栈帧：

如果有重复的行并且产生了错误HA_ERR_FOUND_DUPP_KEY ，那么就不能进行insert *** 作了，这里就会根据不同的语法进行不同的 *** 作了。我们在函数（write_record ）中可以找到这种分支处理逻辑。

实际上在处理重复行错误的时候，在内部分为了3种方式如下：

但是需要注意的是，当前版本报错后，自增值并不会回退。

再说对于普通的insert *** 作而言，影响的行数通常为1。replace/insert into on dup如果遇到了 重复行更改后 （注意不是直接插入成功的状态），通常返回影响的行数为2如下：

因此不管怎么看起来都是影响行数为2，也不要奇怪。

DML回执接口：

条件：

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

1, 原理

1.1 检查表是否带有主键或者唯一索引，show create table test

1.2 创建新表，create table _test_new

1.3 变更新表，alter table _test_new

1.4 在原表上创建 insert、update、delete 三种类型的触发器；

1.5 将旧表的数据拷贝到新表中，同时通过触发器将旧表中的 *** 作映射到新表；

1.6 将新表替换原表，rename 表名，RENAME TABLE `testdb`.`test` TO `testdb`.`_test_old`, `testdb`.`_test_new` TO `testdb`.`test`

1.7 删除旧表(可以根据选项来控制是否删除旧表)

1.8 删除新表上的删除、更新、插入 触发器

2, 使用场景

2.1 亿级大表在线不锁表变更字段与索引

3，pt-online-schema-change安装

yum install perl-DBI

yum install perl-DBD-MySQL

yum install perl-Time-HiRes

yum install perl-IO-Socket-SSL

4，pt-online-schema-change使用

选项：

示例：

1, 添加列

2, 删除列

3, 添加索引

4, 删除索引

5, 修改字段长度

6,重建表

7, 修改自增ID为bigint

8 添加字段

9 修改字段

10 删除字段

11 添加索引

12 删除索引

13 修改字段为null/not null

# 改为null

# 改为 not null

14 添加主键

15 删除主键

16 重建表

17 保留变更的新旧表

# 保留旧表

# 保留新表，

优势：

1，降低主从延时的风险

2，可以限速、限资源，避免 *** 作时MySQL负载过高

建议:

1，在业务低峰期做，将影响降到最低

如果按网上或书上的解释，可能你很难理解过来information_schema是什么东西，网上和书上的解释都是废话，无非你抄我，我抄你。

说得简单点，information_schema这张数据表保存了mysql服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。再简单点，这台mysql服务器上，到底有哪些数据库、各个数据库有哪些表，每张表的字段类型是什么，各个数据库要什么权限才能访问，等等信息都保存在information_schema表里面。

---