背景:产品需求经常变更的情况下,造成了线上数据库的表结构需要不停地进行修改。
5.6版本之前,在线变更表结构需要在备库执行alter table操作,然后将备库切为主库;或者使用第三方工具:OAK的oak-Online-alter-table或者pt-online-schema-change等实现Online DDL操作。而MySQL 5.6官方提供了一个Online DDL功能。
5.6 Online DDL执行过程:
一、prepare阶段
语法检查,合理性、冲突检查
对原表创建临时frm文件
在原表上加表级排他锁(Exclusive-MDL锁),禁止读写
根据alter table类型确定执行方式:in-replace(Online-rebuild、Online-norebuild),copy
更新数据字典的内存对象,系统表中创建索引
分配row_log对象记录增量日志,增量日志用于记录:DDL操作过程中,记录DML操作对数据的修改。log大小由 innodb_online_alter_log_max_size 决定
执行方式为rebuild,则生成临时ibd文件,提交数据字典操作的事务,释放数据字典的锁
二、DDL执行阶段
降级Exclusive-MDL锁,允许读写
扫描原表的聚簇索引每条记录
遍历新表的聚簇索引和二级索引,逐一处理
根据记录构造对应的索引项
将构造的索引项插入sort_buffer块
利用sort_buffer构造新的索引
如果是rebuild的操作,则还需要处理DDL执行过程中产生的增量,应用row_log,将新数据加入到ibd文件中
三、Commit阶段
升级Exclusive-MDL锁,禁止读写
前一次应用日志到本阶段升级Exclusive-MDL锁这段时间之间的row_log中,可能新产生了日志,再次应用之。
更新innodb的数据字典表
提交事务(刷事务的redo日志)
修改统计信息(数据字典、索引信息等)
rename临时ibd文件、frm文件
变更完成。
old_alter_table:Alter table执行方式(copy、in-place、默认OFF)
tmpdir:DDL执行阶段,构造索引过程中排序时内存空间不足时,需要的临时空间
oak-online-alter-table采用的是copy的方式执行DDL,执行期间新增的DML产生的数据通过一个触发器同步到临时表。
使用oak-online-alter-table的注意点:
必须为单列唯一索引(联合索引和联合主键不可以,否则触发mysql的一个bug)
不能存在外键、不能存在触发器(对于已有触发器,先备份再删除,再执行oak ddl)
Select * from information_schema.key_column_usage where Table_schema=@dbname and table_name=@tablename and Referenced_table_name is not null ;
Select * from information_schema.key_column_usage where Referenced_table_schema=@dbname and Referenced_table_name=@tablename;
执行前检查是否存在大查询,导致Online DDL失败
执行前预估执行时间,选择业务低谷期执行
执行完之后,需要进行数据校验,检查原表和复制的临时表的数据一致性(是因为DDL如果改变表了字段类型,可能导致数据变化)
把临时表切换为正式表
删除触发器
删除原表
(1)官方手册:Online DDL Overview
https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
(2)官方手册:pt-online-schema-change
https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html
(3)官方手册:oak-online-alter-table
http://openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/oak-online-alter-table.html
(4)pt-online-schema-change VS oak-online-alter-table:
http://www.cnblogs.com/gomysql/p/3777607.html
(5)Github提供的gh-ost:
http://www.oschina.net/news/76606/gh-ost-github-s-online-migration-tool-for-mysql
http://www.jianshu.com/p/70bc5c06b289