作者：杨挺

一 、背景

在我们的数据库操作中，更改表结构是一个常见的操作，而当我们的表数据量非常大时，我们更改表结构的时间是非常的长，并且在跟改期间，会生成一个互斥锁，阻塞对整个表的所有操作，这样，对于我们线上数据来说是无法容忍的，而我们怎么能在线修改表结构而不影响线上业务呢？这就本文所要介绍的Online Schema Change（简称：OSC）

在我们的以前做法中，为了不影响线上业务，我们一般采用：先在线下从库更改表结构，然后替换线上从库，这样一台台的修改，最后做一下主库切换，这个过程会耗费很长时间，并且在做主库切换时，风险也非常的大，我们怎样才能让时间更短，且能不阻塞读写情况下在线修改呢？早在2008年Shlomi Noach 就利用触发器的原理，开发了python版本oak-online-alter-table在线更改表结构脚本，最近，Percona 公司在自己的percona-toolkit脚本集合中也发布了在线更改表结构的perl版本脚本pt-online-schema-change，Facebook公司也开发自己的在线更改表结构php版本脚本 OnlineSchemaChange.php，而它们最底层的实现原理都为触发器。因为oak-online-alter-table不确定是否在被开发与支持，而OnlineSchemaChange.php的使用比较复杂，且有很多功能不支持如：表结构中如果有外键的情况，故在此我就Percona公司的脚本做详细的剖析;

二、详细实现原理及过程

pt-online-schema-change在线更改表结构的实现核心有如下几个过程：

（注：在跟改过程中涉及到三个表：原表、tmp_table即作为原表导数据的临时表，old_table在最后rename 原表的结果表）

1、        CREATE TABLE `$db`.`$tmp_tbl` LIKE `$db`.`$tbl`"  新建tmp_table，表结构同原表

2、        在tmp_table上更改表结构为需要的表结构

3、        在原表上建立三个触发器，如下：

（1）CREATE TRIGGER mk_osc_del AFTER DELETE ON $table " "FOR EACH ROW "

"DELETE IGNORE FROM $new_table ""WHERE $new_table.$chunk_column = OLD.$chunk_column";

（2）CREATE TRIGGER mk_osc_ins AFTER INSERT ON $table " "FOR EACH ROW "

 "REPLACE INTO $new_table ($columns) "  "VALUES($new_values)";

（3）CREATE TRIGGER mk_osc_upd AFTER UPDATE ON $table " "FOR EACH ROW "

"REPLACE INTO $new_table ($columns) " "VALUES ($new_values)";

      我们可以看到这三个触发器分别对应于INSERT、UPDATE、DELETE三种操作，

（1）      mk_osc_del，DELETE操作，我们注意到DELETE IGNORE，当新有数据时，我们才进行操作，也就是说，当在后续导入过程中，如果删除的这个数据还未导入到新表，那么我们可以不在新表执行操作，因为在以后的导入过程中，原表中改行数据已经被删除，已经没有数据，那么他也就不会导入到新表中；

（2）      mk_osc_ins，INSERT操作，所有的INSERT INTO全部转换为REPLACE INTO，为了确保数据的一致性，当有新数据插入到原表时，如果触发器还未把原表数据未同步到新表，这条数据已经被导入到新表了，那么我们就可以利用replace into进行覆盖，这样数据也是一致的

（3）      mk_osc_upd  UPDATE操作，所有的UPDATE也转换为REPLACE INTO，因为当跟新的数据的行还未同步到新表时，新表是不存在这条记录的，那么我们就只能插入该条数据，如果已经同步到新表了，那么也可以进行覆盖插入，所有数据与原表也是一致的；

我们也能看出上述的精髓也就这这几条replace into操作，正是因为这几条replace into才能保证数据的一致性

4、        拷贝原表数据到临时表中，在脚本中使用如下语句

INSERT IGNORE INTO $to_table ($columns) "  "SELECT $columns FROM $from_table " "WHERE ($chunks->[$chunkno])"，我们能看到他是通过一些查询（基本为主键、唯一键值）分批把数据导入到新的表中，在导入前，我们能通过参数--chunk-size对每次导入行数进行控制，已减少对原表的锁定时间，并且在导入时，我们能通过—sleep参数控制，在每个chunk导入后与下一次chunk导入开始前sleep一会，sleep时间越长，对于磁盘IO的冲击就越小

5、        Rename 原表到old表中，在把临时表Rename为原表，

"RENAME TABLE `$db`.`$tmp_tbl` TO `$db`.`$tbl`"; 在rename过程，其实我们还是会导致写入读取堵塞的，所以从严格意思上说，我们的OSC也不是对线上环境没有一点影响，但由于rename操作只是一个修改名字的过程，也只会修改一些表的信息，基本是瞬间结束，故对线上影响不太大

6、        清理以上过程中的不再使用的数据，如OLD表

   以上即为整个Percona OSC的过程，我们看到精华部分就触发器那一块，不过还有很多细节我未介绍，如：外键、记录binlog（默认情况是不记录binlog的）等等，由于环境的复杂性，此工具还是有很多风险，如以下几个方面问题或者需要规避的一些问题：

1、     此工具不是原子操作，如果某一点失败，不仅仅会留下很多中间过程的垃圾文件，而这些文件很难完全清理，并且如果有这些文件存在，那么就不能在次执行OSC操作；

2、     在执行时，尽量避免有这个表的批量更新、锁表、优化表的操作，我们能想象的到，如果有锁表、优化表那么OSC是否还能正常执行？

3、     如果存在主从结构，那么尽量在从库先执行，因为如果在主库执行完毕后在到从库执行，我们能想象，主库字段多同步到从库，会不会有问题呢？

4、     必须是单一列的主键或者单一唯一键，这样我们在insert select *from分片时，是不是能更好的处理量呢？

5、     不要有外键，尽管脚本经过严格测试，但是是否还有bug，也未知，表的外键是不是会带来更多的问题呢？

6、     在执行之前，我们是不是要对磁盘容量进行评估呢？因为OSC会使用表的一倍以上空间。

以上列到的，只是部分问题，我想如果需要在线进行实施，还需要经过严格的测试，但是它的实现为我们提供了一个很好的在线更改表结构方法，我相信只要我们能很好的规避他的弊端，它会给我们带来很大的帮助；

使用方法：

pt-online-schema-change h=*,u=* p=**,P=* ,D=enk,t=my1 --alter "add is_sign_1 int(11) unsigned NOT NULL DEFAULT '0'" --drop-old-table [--sleep 10]   

                .

参考文档：

1、 http://www.percona.com/doc/percona-toolkit/pt-online-schema-change.html

2、 http://openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/oak-online-alter-table.html

3、 http://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932