跟同事聊天说到pt-online-schema-change添加唯一索引会不会出现问题。
基于pt-online-schema-change它自身的机制:
* 新建目标结构表
* 全量同步数据(INSERT LOW_PRIORITY IGNORE INTO select xxx from tab force index (pk range) LOCK IN SHARE MODE)
* 同时使用三个触发器(insert,update,delete)做新数据的同步处理
* rename 新表完成“ddl”操作
假设添加唯一索引的时候没有任何操作(排除掉触发器的影响 ), 如果原表存在非唯一的键值,根据 INSERT LOW_PRIORITY IGNORE INTO,pt-osc是会直接忽略掉后面重复行的,也就是会丢失重复行数据。
审计如下测试(percona-toolkit-2.2.4):
建表:
CREATE TABLE `ybp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB
数据示例:
root:3306:zgp>select * from ybp;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 8 | 1 |
+----+------+
7 rows in set (0.00 sec)
使用pt-osc添加唯一索引:
[root@db ~]# pt-online-schema-change --user=test --password=test --host=127.0.0.1 --port=3306 --alter="add unique key(name)" D=zgp,t=ybp --execute --nocheck-replication-filters
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `zgp`.`ybp`...
Creating new table...
Created new table zgp.__ybp_new OK.
Altering new table...
Altered `zgp`.`__ybp_new` OK.
2015-09-02T01:47:24 Creating triggers...
2015-09-02T01:47:24 Created triggers OK.
2015-09-02T01:47:24 Copying approximately 6 rows...
2015-09-02T01:47:24 Copied rows OK.
2015-09-02T01:47:24 Swapping tables...
2015-09-02T01:47:24 Swapped original and new tables OK.
2015-09-02T01:47:24 Dropping old table...
2015-09-02T01:47:24 Dropped old table `zgp`.`_ybp_old` OK.
2015-09-02T01:47:24 Dropping triggers...
2015-09-02T01:47:24 Dropped triggers OK.
Successfully altered `zgp`.`ybp`.
[root@db ~]#
完成后检查数据:
root:3306:zgp>select * from ybp;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
+----+------+
6 rows in set (0.00 sec)
测试结果表明确实重复行数据被忽略,可以进一步发现,即使加索引操作之前表的列是唯一的,如果这个过程中有人添加了重复的key值的行,也会被忽略掉。所以在使用它做
唯一索引添加的时候需要特别小心丢数据问题。
基于pt-online-schema-change它自身的机制:
* 新建目标结构表
* 全量同步数据(INSERT LOW_PRIORITY IGNORE INTO select xxx from tab force index (pk range) LOCK IN SHARE MODE)
* 同时使用三个触发器(insert,update,delete)做新数据的同步处理
* rename 新表完成“ddl”操作
假设添加唯一索引的时候没有任何操作(排除掉触发器的影响 ), 如果原表存在非唯一的键值,根据 INSERT LOW_PRIORITY IGNORE INTO,pt-osc是会直接忽略掉后面重复行的,也就是会丢失重复行数据。
审计如下测试(percona-toolkit-2.2.4):
建表:
CREATE TABLE `ybp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB
数据示例:
root:3306:zgp>select * from ybp;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 8 | 1 |
+----+------+
7 rows in set (0.00 sec)
使用pt-osc添加唯一索引:
[root@db ~]# pt-online-schema-change --user=test --password=test --host=127.0.0.1 --port=3306 --alter="add unique key(name)" D=zgp,t=ybp --execute --nocheck-replication-filters
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `zgp`.`ybp`...
Creating new table...
Created new table zgp.__ybp_new OK.
Altering new table...
Altered `zgp`.`__ybp_new` OK.
2015-09-02T01:47:24 Creating triggers...
2015-09-02T01:47:24 Created triggers OK.
2015-09-02T01:47:24 Copying approximately 6 rows...
2015-09-02T01:47:24 Copied rows OK.
2015-09-02T01:47:24 Swapping tables...
2015-09-02T01:47:24 Swapped original and new tables OK.
2015-09-02T01:47:24 Dropping old table...
2015-09-02T01:47:24 Dropped old table `zgp`.`_ybp_old` OK.
2015-09-02T01:47:24 Dropping triggers...
2015-09-02T01:47:24 Dropped triggers OK.
Successfully altered `zgp`.`ybp`.
[root@db ~]#
完成后检查数据:
root:3306:zgp>select * from ybp;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
+----+------+
6 rows in set (0.00 sec)
测试结果表明确实重复行数据被忽略,可以进一步发现,即使加索引操作之前表的列是唯一的,如果这个过程中有人添加了重复的key值的行,也会被忽略掉。所以在使用它做
唯一索引添加的时候需要特别小心丢数据问题。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20625855/viewspace-1789014/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/20625855/viewspace-1789014/