MySQL DDL操作執行的三種方式 1,INPLACE,在進行DDL操作時,不影響表的讀&寫,可以正常執行表上的DML操作,避免與COPY方法相關的磁碟I/O和CPU周期,從而最小化資料庫的總體負載。 最小化負載有助於在DDL操作期間保持良好的性能和高吞吐量。 2,COPY,不允許併發執行過多個D ...
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;
參考: https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html https://dbaplus.cn/news-11-2552-1.html