MySQL专列，Online DDL邀你上车

朱雀 2022-04-08 10:15 182阅读 0赞

在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回退，重复值插入成功。