本文共 3939 字,大约阅读时间需要 13 分钟。
MySQL5.6版本开始,以OSC方式进行表结构更新.
OSC(Online Schema Change)大多都是利用了触发器的原理,实现了在线更改表结构的同时,避免了锁表,同时还允许其他的dml操作常见的OSC工具:
可以做到DDL\DML\SELECT同时进行
示例
alter table test add name varchar(10),ALGORITHM=INPLACE ,LOCK=NONE;#Locking Options for Online DDLLOCK=DEFAULTLOCK=NONELOCK=SHAREDLOCK=EXCLUSIVE#Performance of In-Place versus Table-Copying DDL OperationsALGORITHM=DEFAULTALGORITHM=INPLACEALGORITHM=COPY
实现细节
#Prepare阶段1.创建临时frm文件2.持有EXCLUSIVE-MDL锁,禁止读写3.根据ALTER类型,确定执行方式(copy,online-rebuild,online-norebuild)4.更新数据字典的内存对象5.分配row_log对象记录增量6.生成临时ibd文件#ddl执行阶段1.降级EXCLUSIVE-MDL锁,允许读写2.扫描原表的聚簇索引每条记录3.遍历新表的聚簇索引和二级索引,逐一处理4.根据记录构造对应的索引项5.将构造索引项插入sort_buffer块6.将sort_buffer块插入新的索引7.处理ddl执行过程中产生的增量(仅rebuild类型需要)#commit阶段1.升级到EXCLUSIVE-MDL锁,禁止读写2.应用最后row_log中产的日志3.更新innodb的数据字典表4.提交事务(刷事务的redo日志)5.修改统计信息6.rename临时idb文件,frm文件7.变更完成
从上面可以看到 在开始 和 结束阶段 还是锁表了 只是缩短了锁表的时间
以加索引为例,介绍 copy方式跟inplace方式的实现流程
#copy方式1.新建带索引(主键索引)的临时表2.锁原表,禁止DML,允许查询3.将原表数据拷贝到临时表4.禁止读写,进行rename,升级字典锁5.完成创建索引操作
#inplace方式1.创建索引(二级索引)数据字典2.加共享表锁,禁止DML,允许查询3.读取聚簇索引,构造新的索引项,排序并插入新索引4.等待打开当前表的所有只读事务提交5.创建索引结束
示例
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] --print --executeD=lots,t=t_o_tr
实现细节
1. 新建tmp_table,表结构同原表 CREATE TABLE `$db`.`$tmp_tbl` LIKE `$db`.`$tbl`" 2. 在tmp_table上更改表结构为需要的表结构3. 在原表上建立三个触发器,如下: #delete 触发器 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"; #insert 触发器 CREATE TRIGGER mk_osc_ins AFTER INSERT ON $table " "FOR EACH ROW " "REPLACE INTO $new_table ($columns) " "VALUES($new_values)"; #update 触发器 CREATE TRIGGER mk_osc_upd AFTER UPDATE ON $table " "FOR EACH ROW " "REPLACE INTO $new_table ($columns) " "VALUES ($new_values)"; #我们可以看到这三个触发器分别对应于INSERT、UPDATE、DELETE三种操作, mk_osc_del,DELETE操作,我们注意到DELETE IGNORE,当新有数据时,我们才进行操作,也就是说,当在后续导入过程中,如果删除 的这个数据还未导入到新表,那么我们可以不在新表执行操作,因为在以后的导入过程中,原表中改行数据已经被删除,已经没有数据,那 么他也就不会导入到新表中; mk_osc_ins,INSERT操作,所有的INSERT INTO全部转换为REPLACE INTO,为了确保数据的一致性,当有新数据插入到原表时,如果 触发器还未把原表数据未同步到新表,这条数据已经被导入到新表了,那么我们就可以利用replace into进行覆盖,这样数据也是一致的。 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的)等等
由于环境的复杂性,此工具还是有很多风险,如以下几个方面问题或者需要规避的一些问题:
**以上列到的,只是部分问题,我想如果需要在线进行实施,还需要经过严格的测试,但是它的实现为我们提供了一个很好的在线更改表结构 方法,我相信只要我们能很好的规避他的弊端,它会给我们带来很大的帮助;
**PS:
使用 pt-osc 修改主键时注意: 原表上有个复合主键,现在要添加一个自增 id 作为主键,如何进行? 会涉及到以下修改动作:1.删除复合主键定义 2.添加新的自增主键 3.原复合主键字段,修改成唯一索引
需要将删除原主键、增加新主键和增加原主键为唯一键同时操作:
alter "DROP PRIMARY KEY,add column pk int auto_increment primary key,add unique key uk_id_k(id,k)
引用文章地址:
另附一处理修改表结构异常相关文章:
该文章记录内容为alter修改表结构失败,使用各种工具去做修改皆不成功,show processlist发现阻塞的进程有alter的,也有select的,并且在alter之前有一堆的sleep状态连接,因此判断是有未提交的事务.kill事务对应的process ID,即可alter成功.转载地址:http://ghbws.baihongyu.com/