MySQL专列，Online DDL邀你上车

在MySQL早期的版本，大部分DDL操作是非常昂贵的。因为实现的方式其实就是根据将要执行的DDL语句的定义，在源表的基础上，新建一个空的表，然后将源表的数据一行一行地插入新表，最后新建的表更名为源表，而旧表被删除。这意味着每次执行DDL都需要进行一次全表的复制以及索引的排序等操作，同时排斥外部事务操作，相当expensive。

MySQL忍不了，于是在带有innodb插件的5.1以及MySQL5.5版本中，利用快速创建索引（fast index creation）技术，优化了create index和drop index两个操作，使其执行时不需要再进行复制表数据的操作，同时允许在创建索引时，其他事务并发读。

在MySQL5.6，MySQL继续优化了多种alter table的DDL操作，使其在执行的同时，允许其他事务执行查询和DML操作。这一特性称为online DDL。而且在随后的版本中，也对online DDL进行了拓展。

Online DDL大大地提升了系统的并发性能，MySQL丢弃了自行车，开起了小汽车！来不及解释了，快上车！

默认情况下，MySQL会以允许尽可能多的并发性和避免尽可能多的锁定来执行DDL。但也可以人工指定DDL执行方式，当然，如果所指定的方式不被支持，将会终止DDL执行并报错。

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

其中ALGORITHM指定DDL执行算法，取值为inplace|copy|instant(MySQL8.0)。inplace即是online DDL，在源表的基础上进行操作；copy是复制表的旧算法；而instant是8.0新引入的算法，该方式仅修改数据字典，不影响表数据以及DML操作，该方式执行的DDL是“马上”（instantly）完成的，在8.0.12新引入。
而LOCK指定锁定的粒度，取值为NONE|SHARED|EXCLUSIVE|DEFAULT。NONE代表不做任何锁定，允许并发的DML和查询；SHARED代表只读；EXCLUSIVE代表排它；DEFAULT代表让MySQL去判断锁定粒度，此时MySQL会以允许尽可能多的并发性和避免尽可能多的锁定来执行DDL。

一、online DDL支持的操作
由于篇幅过长，操作的具体细节及注意事项后续博文再介绍。
1.索引操作
Creating or adding a secondary index
Dropping an index
Renaming an index
Adding a FULLTEXT index
Adding a SPATIAL index
Changing the index type

2.主键操作
Adding a primary key
Dropping a primary key
Dropping a primary key and adding another

3.列操作
Adding a column
Dropping a column
Renaming a column
Reordering columns
Setting a column default value
Extending VARCHAR column size
Dropping the column default value
Changing the auto-increment value
Making a column NULL
Making a column NOT NULL
Modifying the definition of an ENUM or SET column

4.繁衍列(generated column)操作
Adding a VIRTUAL column
Dropping a VIRTUAL column

5.外键操作
Adding a foreign key constraint
Dropping a foreign key constraint

6.表操作
Changing the ROW_FORMAT
Changing the KEY_BLOCK_SIZE
Setting persistent table statistics
Specifying a character set
Optimizing a table
Rebuilding with the FORCE option
Performing a null rebuild
Renaming a table

7.分区操作
ADD PARTITION
DROP PARTITION
TRUNCATE PARTITION
COALESCE PARTITION
REORGANIZE PARTITION
EXCHANGE PARTITION
ANALYZE PARTITION
CHECK PARTITION
REBUILD PARTITION
REPAIR PARTITION

二、online DDL带来的性能提升
1.online DDL允许查询和DML的并发操作，提高系统的响应能力。同时，减少锁和资源的等待，能提升系统拓展性，因为锁和资源的竞争，影响的可能不仅仅是DDL涉及的对象；
2.瞬时(instant)操作只修改数据字典的元数据，不需要表上的任何锁；
3.online DDL由于避免了表复制，减少了相应的IO和CPU资源的使用；
4.相对于表复制的方式，online DDL对buffer pool的使用大大减少，也就减低了将热数据从内存从刷出的可能性。

三、online DDL与metadata lock
至于online DDL和metadata lock的关系，在往期博文《metadata lock为何物？好吃吗？》中有讲述。

四、online DDL的空间需求
1.临时日志文件
在online DDL操作期间，会有一个临时日志文件记录并发的DML操作。该文件由innodb_sort_buffer_size指定初始大小，并自动拓展至innodb_online_alter_log_max_size大小。当临时日志文件的容量需求超过了innodb_online_alter_log_max_size，online DDL执行失败，且那些没提交的DML会被回滚。
增大innodb_online_alter_log_max_size值可以使得临时日志文件容纳更多的DML操作，但也会增加在DDL最后执行阶段的重做日志文件中的DML的时间。
2.临时排序文件
online DDL创建索引或重建表数据的时候，会先写到一个临时排序文件，文件路径由tmpdir统一制定，或由innodb_tmpdir将临时排序文件路径独立出来。临时排序文件需要足够大，用以存放所有的索引键以及主键。如果磁盘空间被占满了，则会出现报错。
3.中间表文件
一些online DDL会在源表的目录里创建一个临时表文件，内容和大小与源表一致，文件名以#sql-ib开头。

五、online DDL失败条件
1.当指定的ALGORITHM与对应的DDL不适用；
2.当指定的LOCK粒度过低，不满足对应的DDL需求；
3.在DDL初始化和最后阶段获取排它锁时超时；
4.tmpdir或innodb_tmpdir空间不足；
5.临时日志文件超过了innodb_online_alter_log_max_size；
6.在online DDL期间，执行了与源表兼容，但与目标结构不兼容的DML。例如需要建一个唯一索引，在DDL期间，执行了插入重复值的DML。在DDL的最后重做DML阶段，当遇到插入重复值的DML时，会使得创建唯一索引的DDL回退，重复值插入成功。