MySQL Online DDL与DML并发阻塞关系总结

mysql ddl操作执行的三种方式

1，inplace，在进行ddl操作时，不影响表的读&写，可以正常执行表上的dml操作，避免与copy方法相关的磁盘i/o和cpu周期，从而最小化数据库的总体负载。

最小化负载有助于在ddl操作期间保持良好的性能和高吞吐量。

2，copy，不允许并发执行过多个ddl，执行过程中表不允许写但可读。

过程是通过创建一个新结构的临时表，将数据copy到临时表，完成后删除原表，重命名新表的方式，需要拷贝原始表，

3，instant，从 mysql 8.0.12 开始被引入并默认使用。目前 instant 算法只支持增加列等少量 ddl 类型的操作，其他类型仍然会默认使用 inplace。

以下是mysql 5.7版本中各种ddl操作的执行方式，总结一下：

1，如果ddl的执行方式是inplace = yes ，那么改ddl的执行会支持并发dml，不会影响表的增删查改，

  1.1，如果ddl的执行方式是inplace = yes &  rebuilds table = no，那么only modifies metadata一定为yes，也即仅仅修改元数据，类似于instant 

  1.2，如果ddl的执行方式是inplace = yes  & rebuilds table = yes，那么only modifies metadata一定为no，需要考虑rebuilds table对io和cpu等资源的消耗

2，如果ddl的执行方式是inplace = no，那么改ddl的执行期间表只读，阻塞写（增删改），同时需要考虑对io和cpu等资源的消耗

3，如果是instant方式，类似于1.1

 

如下，对于执行期间不支持并发dml的操作，标记了出来，如果不是影响并发dml的操作，就不需要考虑第三方工具了，只需要考虑io和cpu等资源的消耗。
因为用第三方工具同样需要消耗io以及cpu等资源。

正常来说操作，修改字段数据类型，以及增加衍生列，修改衍生列字段顺序这三种，以及多数分区相关的操作的同时，不支持并发dml，其他ddl执行时都支持并发dml。

 

索引操作

create index name on table (col_list);（alter table tbl_name add index name (col_list);）
drop index name on table;（alter table tbl_name drop index name;）
alter table tbl_name rename index old_index_name to new_index_name, algorithm=inplace, lock=none;
create fulltext index name on table(column);
create table geom (g geometry not null);alter table geom add spatial index(g), algorithm=inplace, lock=shared;
alter table tbl_name drop index i1, add index i1(key_part,...) using btree, algorithm=inplace;

 

主键操作

alter table tbl_name add primary key (column)
alter table tbl_name drop primary key
alter table tbl_name drop primary key, add primary key (column)

列操作

alter table tbl_name add column column_name column_definition,
alter table tbl_name drop column column_name
alter table tbl change old_col_name new_col_name data_type
alter table tbl_name modify column col_name column_definition first
alter table tbl_name change c1 c1 bigint
alter table tbl_name change column c1 c1 varchar(255)
alter table tbl_name alter column col set default literal
alter table tbl alter column col drop default
alter table table auto_increment=next_value
alter table tbl_name modify column column_name data_type null
alter table tbl_name modify column column_name data_type not null
alter table t1 modify column c1 enum('a', 'b', 'c', 'd')

 

衍生列（generated column）操作

alter table t1 add column (c2 int generated always as (c1 + 1) stored)
alter table t1 modify column c2 int generated always as (c1 + 1) stored first
alter table t1 drop column c2, algorithm=inplace, lock=none;
alter table t1 add column (c2 int generated always as (c1 + 1) virtual)
alter table t1 modify column c2 int generated always as (c1 + 1) virtual first
alter table t1 drop column c2, algorithm=inplace

 

外键操作

alter table tbl1 add constraint fk_name foreign key index (col1)references tbl2(col2) referential_actions;
alter table tbl drop foreign key fk_name;

 

表操作

alter table tbl_name row_format = row_format
alter table tbl_name key_block_size = value
alter table tbl_name stats_persistent=0, stats_sample_pages=20, stats_auto_recalc=1, algorithm=inplace, lock=none;
alter table tbl_name character set = charset_name, algorithm=inplace, lock=none;
alter table tbl_name convert to character set charset_name, algorithm=copy;
optimize table tbl_name;
alter table tbl_name force, algorithm=inplace, lock=none;
alter table tbl_name engine=innodb, algorithm=inplace, lock=none;
alter table old_tbl_name rename to new_tbl_name, algorithm=inplace, lock=none;

 

表空间操作

alter table tbl_name encryption='y', algorithm=copy;

 

分区操作

 

 

 

参考：