MySQLOnlineDDL与DML并发阻塞关系总结

Z时代
2024-01-10
分类：综合

database

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。

索引操作

CREATEINDEX name ONtable (col_list);（ALTERTABLE tbl_name ADDINDEX name (col_list);）
DROPINDEX name ONtable;（ALTERTABLE tbl_name DROPINDEX name;）
ALTERTABLE tbl_name RENAME INDEX old_index_name TO new_index_name, ALGORITHM=INPLACE, LOCK=NONE;
CREATE FULLTEXT INDEX name ONtable(column);
CREATETABLE geom (g GEOMETRY NOTNULL);ALTERTABLE geom ADD SPATIAL INDEX(g), ALGORITHM=INPLACE, LOCK=SHARED;
ALTERTABLE tbl_name DROPINDEX i1, ADDINDEX i1(key_part,...) USING BTREE, ALGORITHM=INPLACE;

主键操作

ALTERTABLE tbl_name ADDPRIMARYKEY (column)
ALTERTABLE tbl_name DROPPRIMARYKEY
ALTERTABLE tbl_name DROPPRIMARYKEY, ADDPRIMARYKEY (column)

列操作

ALTERTABLE tbl_name ADDCOLUMN column_name column_definition,
ALTERTABLE tbl_name DROPCOLUMN column_name
ALTERTABLE tbl CHANGE old_col_name new_col_name data_type
ALTERTABLE tbl_name MODIFY COLUMNcol_name column_definition FIRST
ALTERTABLE tbl_name CHANGE c1 c1 BIGINT
ALTERTABLE tbl_name CHANGE COLUMN c1 c1 VARCHAR(255)
ALTERTABLE tbl_name ALTERCOLUMN col SETDEFAULT literal
ALTERTABLE tbl ALTERCOLUMN col DROPDEFAULT
ALTERTABLEtable AUTO_INCREMENT=next_value
ALTERTABLE tbl_name MODIFY COLUMN column_name data_type NULL
ALTERTABLE tbl_name MODIFY COLUMN column_name data_type NOTNULL
ALTERTABLE t1 MODIFY COLUMN c1 ENUM("a", "b", "c", "d")

衍生列（generated column）操作

ALTERTABLE t1 ADDCOLUMN (c2 INT GENERATED ALWAYS AS (c1 +1) STORED)
ALTERTABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 +1) STORED FIRST
ALTERTABLE t1 DROPCOLUMN c2, ALGORITHM=INPLACE, LOCK=NONE;
ALTERTABLE t1 ADDCOLUMN (c2 INT GENERATED ALWAYS AS (c1 +1) VIRTUAL)
ALTERTABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 +1) VIRTUAL FIRST
ALTERTABLE t1 DROPCOLUMN c2, ALGORITHM=INPLACE

外键操作

ALTERTABLE tbl1 ADDCONSTRAINT fk_name FOREIGNKEYindex (col1)REFERENCES tbl2(col2) referential_actions;
ALTERTABLE tbl DROPFOREIGNKEY fk_name;

表操作

ALTERTABLE tbl_name ROW_FORMAT = row_format
ALTERTABLE tbl_name KEY_BLOCK_SIZE = value
ALTERTABLE tbl_name STATS_PERSISTENT=0, STATS_SAMPLE_PAGES=20, STATS_AUTO_RECALC=1, ALGORITHM=INPLACE, LOCK=NONE;
ALTERTABLE tbl_name CHARACTERSET= charset_name, ALGORITHM=INPLACE, LOCK=NONE;
ALTERTABLE tbl_name CONVERTTOCHARACTERSET charset_name, ALGORITHM=COPY;
OPTIMIZE TABLE tbl_name;
ALTERTABLE tbl_name FORCE, ALGORITHM=INPLACE, LOCK=NONE;
ALTERTABLE tbl_name ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;
ALTERTABLE old_tbl_name RENAME TO new_tbl_name, ALGORITHM=INPLACE, LOCK=NONE;

表空间操作

ALTERTABLE tbl_name ENCRYPTION="Y", ALGORITHM=COPY;

分区操作

参考：

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html

https://dbaplus.cn/news-11-2552-1.html

以上是 MySQLOnlineDDL与DML并发阻塞关系总结的全部内容，来源链接： utcz.com/z/531228.html

MySQLOnlineDDL与DML并发阻塞关系总结

列操作

其他人也看了：