首先要说明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，在业务低峰期做，将影响降到最低
假设你的表名是“manager”，现自增主键列名是“id”，另有一列名为“user_id"，现在要把现有主键“id”取消主键约束，更改为新主键“user_id”，那么用pt工具更改命令如下：/usr/bin/pt-online-schema-change --user=root --host=127.0.0.1 --port=3306 --charset=utf8 --nodrop-old-table --alter="change id id int(10), drop primary key, modify user_id int auto_increment primary key" D=数据库名,t=manager --exec
说明：以上第一步取消原自增主键‘id’的自增属性（否则无法修改），第二步drop主键约束，第三步将user_id设置为新主键。
注：如表上有触发器则不可使用此pt工具。
使用完毕会产生一个“manager_old”表，这是原表备份，以便更改失误后回退，务必确认更改无误后删除。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）