mysql online ddl的演化

背景
最早采用copy-delete方式创建二级索引，5.1引入in-place/fast index creation节约临时文件开销(仍旧阻塞写)，5.6引入online(借助日志表)；
Online不仅限于创建/删除二级索引，还包含重命名/添加/删除列，更改row_format/key_block_size/default；
创建聚簇索引：仍然采用copy+delete模式，临时文件存于tmpdir；
每个online DDL的开头和结束都会短暂占用X lock，可通过lock=none/shared/exclusive指定DDL的并发程度,algorithm=inplace/copy控制创建方法；

参数 
innodb_online_alter_log_max_size：online DDL会将期间的DML存于临时文件，以innodb_sort_buffer_size为单位扩展；
innodb_sort_buffer_size：创建索引时指定sort buffer，只用于merge sort，分配3个buffer；
https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_sort_buffer_size
 
fast index creation
采用inplace替代 table-copying，索引记录插入前进行merge-sort，有助于提高fill factor；
可设置old_alter_table=0选用旧的table-copying
 
online index creation
5.6重写了alter table接口，可通过algorithm=copy选择 table-copying算法；
在prepare和commit阶段会申请X lock，若commit等待锁超时则会回滚alter table操作，大致步骤如下：
1 Set up a stub for the index, for logging changes.
2 Scan the table for index records.
3 Sort the index records.
4 Bulk load the index records.
5 Apply the logged changes.
6 Replace the stub with the actual index.
alter table会跳过所有标志为deleted的行并将其删除；
https://blogs.oracle.com/mysqlinnodb/entry/online_alter_table_in_mysql
 

修改表列
官方解释：Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
而add/drop/reorder column仍需table copy, However, the table copy for these operations is faster than it was in MySQL 5.5 and prior.
add column比modify column会快，不修改现有数据(default value存于frm而非数据字典)；
案例http://www.itpub.net/thread-1876844-1-1.html 
Inplace即便采用table copy，也比old copy快很多
add/drop column的情况下，如果是inplace算法， 数据文件在执行过程中是这样的
root@debian:/mysqldata/test# ls -l
total 639220
-rw-rw---- 1 mysql mysql     15118 Jul  9 13:31 #sql-d19_1.frm
-rw-rw---- 1 mysql mysql    212992 Jul  9 13:31 #sql-ib53.ibd
-rw-rw---- 1 mysql mysql     15154 Jul  9 13:15 task.frm
-rw-rw---- 1 mysql mysql 654311424 Jul  9 13:24 task.ibd

如果是copy算法，数据文件是这样的
root@debian:/mysqldata/test# ls -l
total 1105960
-rw-rw---- 1 mysql mysql     15154 Jul  9 13:15 #sql-d19_1.frm
-rw-rw---- 1 mysql mysql 603979776 Jul  9 13:23 #sql-d19_1.ibd
-rw-rw---- 1 mysql mysql     15118 Jul  8 17:50 task.frm
-rw-rw---- 1 mysql mysql 528482304 Jul  8 17:51 task.ibd
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15480802/viewspace-1307441/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15480802/viewspace-1307441/