Summary of whether MySQL Online DDL and DML are blocked at the same time

Three ways to perform MySQL DDL operations
1. INPLACE, during DDL operation, does not affect the read & write of the table, can normally perform DML operation on the table, avoid disk I/O and CPU cycle related to COPY method, so as to minimize the overall load of the database.
Minimizing the load helps maintain good performance and high throughput during DDL operations.
2. COPY. It is not allowed to execute too many DDL S concurrently. During execution, the table is not allowed to be written but readable.
The process is to create a temporary table with a new structure, copy the data to the temporary table, delete the original table after completion, rename the new table, and copy the original table,
3. Install, introduced from MySQL 8.0.12 and used by default. At present, the install algorithm only supports a small number of DDL type operations such as adding columns, and other types still use replace by default.

The following is a summary of the execution methods of various DDL operations in MySQL version 5.7:
1. If the execution mode of DDL is InPlace = YES, the execution of changing DDL will support concurrent DML and will not affect the addition, deletion, query and modification of tables,
1.1. If the execution mode of DDL is inplace = Yes & & Rebuilds Table = No, only modifications metadata must be Yes, that is, only the metadata is modified, similar to install
1.2. If the execution mode of DDL is InPlace = YES & & Rebuilds Table = Yes, only modifications metadata must be No. the consumption of Rebuilds Table on IO, CPU and other resources needs to be considered
2. If the execution mode of DDL is InPlace = NO, the table read-only and block write (add, delete and change) during the execution of DDL should be changed. Meanwhile, the consumption of IO and CPU and other resources should be considered
3. If it is installed, it is similar to 1.1
 
As follows, for operations that do not support concurrent DML during execution, it is marked. If the operations do not affect concurrent DML, it is not necessary to consider the third-party tools, only the consumption of IO and CPU and other resources.
Because the third-party tools also need to consume IO, CPU and other resources.
In normal operation, when modifying field data type, adding derived column and modifying derived column field order, as well as most partition related operations, concurrent DML is not supported, while other DDL S support concurrent DML.
 
Index operation
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;
 
Primary key operation
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)

Column operation

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 operation
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
 
Foreign key operation
ALTER TABLE tbl1 ADD CONSTRAINT fk_name FOREIGN KEY index (col1)REFERENCES tbl2(col2) referential_actions;
ALTER TABLE tbl DROP FOREIGN KEY fk_name;
 
Table operation
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;
 
Tablespace operations
ALTER TABLE tbl_name ENCRYPTION='Y', ALGORITHM=COPY;

 

Partition operation

 

 
 
Reference resources:
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html
https://dbaplus.cn/news-11-2552-1.html

Tags: MySQL Database

Posted on Tue, 05 Nov 2019 05:44:33 -0800 by gowni