mysql online ddl add index_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