一、背景 在我们的数据库操作中,更改表结构是一个常见的操作,而当我们的表数据量非常大时,我们更改表结构的时间是非常的长,并且在跟改期间,会生成一个互斥锁,阻塞对整个表的所有操作,这样,对于我们线上数据来说是无法容忍的,而我们怎么能在线修改表结构而不影响线上业务呢?这就本文所要介绍的Online Schema Change(简称:OSC) 在我们的以前做法中,为了不影响线上业务,我们一般采用:先在线下从库更改表结构,然后替换线上从库,这样一台台的修改,最后做一下主库切换,这个过程会耗费很长时间,并且在做主库切换时,风险也非常的大,我们怎样才能让时间更短,且能不阻塞读写情况下在线修改呢?早在2008年ShlomiNoach就利用触发器的原理,开发了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)CREATETRIGGER mk_osc_del AFTER DELETE ON $table " "FOR EACH ROW " "DELETE IGNORE FROM $new_table ""WHERE$new_table.$chunk_column = OLD.$chunk_column"; (2)CREATETRIGGER mk_osc_ins AFTER INSERT ON $table " "FOR EACH ROW " "REPLACEINTO $new_table ($columns) ""VALUES($new_values)"; (3)CREATETRIGGER 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操作,我们注意到DELETEIGNORE,当新有数据时,我们才进行操作,也就是说,当在后续导入过程中,如果删除的这个数据还未导入到新表,那么我们可以不在新表执行操作,因为在以后的导入过程中,原表中改行数据已经被删除,已经没有数据,那么他也就不会导入到新表中; (2)mk_osc_ins,INSERT操作,所有的INSERT INTO全部转换为REPLACEINTO,为了确保数据的一致性,当有新数据插入到原表时,如果触发器还未把原表数据未同步到新表,这条数据已经被导入到新表了,那么我们就可以利用replaceinto进行覆盖,这样数据也是一致的 (3)mk_osc_updUPDATE操作,所有的UPDATE也转换为REPLACEINTO,因为当跟新的数据的行还未同步到新表时,新表是不存在这条记录的,那么我们就只能插入该条数据,如果已经同步到新表了,那么也可以进行覆盖插入,所有数据与原表也是一致的; 我们也能看出上述的精髓也就这这几条replaceinto操作,正是因为这几条replaceinto才能保证数据的一致性 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 [--sleep10] |