MYSQLDDL
在MySQL 5.6推出在线DDL之前,执行DDL的方式主要有两种:copy和inplace。到5.6版本以后,推出了online DDL。
1、Copy Table方式(5.5版本默认算法)
通过拷贝临时表的方式实现的,这期间原表只读不可写,实现流程如下:
MySQL 会建立一个新的临时表,把源表的所有数据写入到临时表(将原表数据拷贝到临时表(无排序,一行一行操作),在此期间无法对源表进行数据写入,MySQL 在完成临时表的写入之后,用临时表替换掉源表,新建临时表存储空间会增加一倍
2、Inplace方式(5.6版本默认)
Inplace是指在原表上之间进行操作,不会拷贝到临时表,相对于copy table方式,效率会更高,但是同样会锁表,原表只读不可写。inplace 算法还包含两种类型:rebuild-table 和 not-rebuild-table。MySQL 使用 inplace 算法时,会自动判断,能使用 not-rebuild-table 的情况下会尽量使用,不能的时候才会使用 rebuild-table。当 DDL 涉及到主键和全文索引相关的操作时,无法使用 not-rebuild-table,必须使用 rebuild-table。其他情况下都会使用 not-rebuild-table。
inplace 算法的操作阶段主要分为三个:
Prepare阶段:- 创建新的临时 frm 文件,-持有 EXCLUSIVE-MDL 锁,禁止读写。- 根据 alter 类型,确定执行方式(copy,online-rebuild,online-not-rebuild)。更新数据字典的内存对象。- 分配 row_log 对象记录数据变更的增量(仅 rebuild 类型需要)。- 生成新的临时ibd文件new_table (仅rebuild类型需要)。
Execute 阶段:降级EXCLUSIVE-MDL锁,允许读写。扫描old_table聚集索引(主键)中的每一条记录 。遍历new_table的聚集索引和二级索引,逐一处理。构造对应的索引项。将构造索引项插入 sort_buffer 块排序。将 sort_buffer 块更新到 new_table 的索引上。记录 online-ddl 执行过程中产生的增量(仅 rebuild 类型需要)。重放 row_log 中的操作到 new_table 的索引上(not-rebuild 数据是在原表上更新)。重放 row_log 中的DML操作到 new_table 的数据行上。
Commit阶段:当前 Block 为 row_log 最后一个时,禁止读写,升级到 EXCLUSIVE-MDL 锁。重做 row_log 中最后一部分增量。更新 innodb 的数据字典表。提交事务(刷事务的 redo 日志)。修改统计信息。rename 临时 ibd 文件,frm文件。变更完成,释放 EXCLUSIVE-MDL 锁。
3、 MySQL 8.0.12 开始,引入了 instant 算法并且默认使用,利用 8.0 新的表结构设计,可以直接修改表的 metadata 数据,省掉了 rebuild 的过程,极大的缩短了 DDL 语句的执行时间。。目前 instant 算法只支持增加列等少量 DDL 类型的操作,其他类型仍然会默认使用 inplace。
4、第三方工具最常见的是 percona 的 pt-online-schema-change 工具(简称为 pt-osc),和 github 的 gh-ost 工具,均支持 MySQL 5.5 以上的版本。
pt-online-schema-change
借鉴了 copy 算法的思路,由外部工具来完成临时表的建立,数据同步,用临时表替换源表这三个步骤。其中数据同步是利用 MySQL 的触发器来实现的,会少量影响到线上业务的 QPS 及 SQL 响应时间。