MySQL 5.7 Online DDL 技術深度解析

14.13.1 在線DDL操作

索引操作
主鍵操作
列操作
生成列操作
外鍵操作
表操作
表空間操作
分區操作

索引操作

下表概述了對索引操作的在線DDL支持情況。星號表示有附加信息、例外情況或依賴條件。有關詳細信息，請參閱語法和使用說明。

操作	原地執行	重建表	允許并發DML	僅修改元數據
創建或添加二級索引	是	否	是	否
刪除索引	是	否	是	是
重命名索引	是	否	是	是
添加全文索引	是*	否*	否	否
添加空間索引	是	否	否	否
更改索引類型	是	否	是	是

語法和使用說明

創建或添加二級索引

CREATE INDEX name ON table (col_list);
ALTER TABLE tbl_name ADD INDEX name (col_list);

在創建索引期間，表仍然可用于讀寫操作。CREATE INDEX語句只有在所有正在訪問該表的事務都完成后才會結束，這樣索引的初始狀態就能反映表的最新內容。

對添加二級索引的在線DDL支持意味著，通常可以通過先創建不含二級索引的表，然后在數據加載后再添加二級索引的方式，加快創建和加載表以及相關索引的整體進程。

新創建的二級索引只包含在CREATE INDEX或ALTER TABLE語句執行完成時表中已提交的數據。它不包含任何未提交的值、值的舊版本，或已標記為刪除但尚未從舊索引中移除的值。

如果在創建二級索引時服務器退出，恢復后MySQL會刪除任何部分創建的索引。你必須重新運行ALTER TABLE或CREATE INDEX語句。

一些因素會影響此操作的性能、空間使用和語義。有關詳細信息，請參閱14.13.6節 “在線DDL的限制”。
2. 刪除索引

DROP INDEX name ON table;
ALTER TABLE tbl_name DROP INDEX name;

在刪除索引期間，表仍然可用于讀寫操作。DROP INDEX語句只有在所有正在訪問該表的事務都完成后才會結束，這樣索引的初始狀態就能反映表的最新內容。
3. 重命名索引

ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name, ALGORITHM=INPLACE, LOCK=NONE;

添加全文索引

CREATE FULLTEXT INDEX name ON table(column);

如果沒有用戶定義的FTS_DOC_ID列，添加第一個全文索引會重建表。后續添加全文索引則可能無需重建表。
5. 添加空間索引

CREATE TABLE geom (g GEOMETRY NOT NULL);
ALTER TABLE geom ADD SPATIAL INDEX(g), ALGORITHM=INPLACE, LOCK=SHARED;

更改索引類型（使用 {BTREE | HASH}）

ALTER TABLE tbl_name DROP INDEX i1, ADD INDEX i1(key_part,...) USING BTREE, ALGORITHM=INPLACE;

主鍵操作

下表概述了對主鍵操作的在線DDL支持情況。星號表示有附加信息、例外情況或依賴條件。請參閱語法和使用說明。

操作	原地執行	重建表	允許并發DML	僅修改元數據
添加主鍵	是*	是*	是	否
刪除主鍵	否	是	否	否
刪除主鍵并添加另一個主鍵	是	是	是	否

語法和使用說明

添加主鍵

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

原地重建表。數據會進行大量重組，這是一項開銷較大的操作。如果必須將列轉換為NOT NULL，在某些條件下不允許使用ALGORITHM=INPLACE。

重組聚簇索引總是需要復制表數據。因此，最好在創建表時就定義主鍵，而不是之后再使用ALTER TABLE... ADD PRIMARY KEY語句。

當你創建UNIQUE或PRIMARY KEY索引時，MySQL必須執行一些額外的工作。對于UNIQUE索引，MySQL會檢查表中該鍵是否存在重復值。對于PRIMARY KEY索引，MySQL還會檢查PRIMARY KEY列中是否有NULL值。

當你使用ALGORITHM=COPY子句添加主鍵時，MySQL會將相關列中的NULL值轉換為默認值：數字類型轉換為0，基于字符的列和BLOB類型轉換為空字符串，DATETIME類型轉換為0000-00-00 00:00:00。這是一種非標準行為，Oracle建議你不要依賴它。只有當SQL_MODE設置中包含strict_trans_tables或strict_all_tables標志時，才允許使用ALGORITHM=INPLACE添加主鍵；當SQL_MODE設置為嚴格模式時，允許使用ALGORITHM=INPLACE，但如果請求的主鍵列中包含NULL值，語句仍然可能失敗。ALGORITHM=INPLACE的行為更符合標準。

如果你創建的表沒有主鍵，InnoDB會為你選擇一個，可能是在NOT NULL列上定義的第一個UNIQUE鍵，或者是系統生成的鍵。為了避免不確定性以及額外隱藏列可能帶來的空間需求，應在CREATE TABLE語句中指定PRIMARY KEY子句。

MySQL通過將原始表中的現有數據復制到具有所需索引結構的臨時表中來創建新的聚簇索引。一旦數據完全復制到臨時表中，原始表會被重命名為一個不同的臨時表名。包含新聚簇索引的臨時表會被重命名為原始表的名稱，而原始表則會從數據庫中刪除。

應用于二級索引操作的在線性能增強不適用于主鍵索引。InnoDB表的行存儲在基于主鍵組織的聚簇索引中，形成了一些數據庫系統所稱的 “索引組織表”。由于表結構與主鍵緊密相關，重新定義主鍵仍然需要復制數據。

當對主鍵的操作使用ALGORITHM=INPLACE時，即使仍然需要復制數據，它也比使用ALGORITHM=COPY更高效，原因如下：

ALGORITHM=INPLACE不需要撤銷日志記錄或相關的重做日志記錄。這些操作會增加使用ALGORITHM=COPY的DDL語句的開銷。
二級索引條目是預排序的，因此可以按順序加載。
不使用更改緩沖區，因為不會對二級索引進行隨機訪問插入。

如果在創建新的聚簇索引時服務器退出，不會丟失數據，但你必須使用該過程中存在的臨時表完成恢復過程。由于在大型表上重新創建聚簇索引或重新定義主鍵的情況很少見，并且在該操作期間遇到系統崩潰的情況也很少，因此本手冊不提供有關從這種情況中恢復的信息。
2. 刪除主鍵

ALTER TABLE tbl_name DROP PRIMARY KEY, ALGORITHM=COPY;

只有ALGORITHM=COPY支持在同一個ALTER TABLE語句中刪除主鍵而不添加新的主鍵。
3. 刪除主鍵并添加另一個主鍵

ALTER TABLE tbl_name DROP PRIMARY KEY, ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

數據會進行大量重組，這是一項開銷較大的操作。

列操作

下表概述了對列操作的在線DDL支持情況。星號表示有附加信息、例外情況或依賴條件。有關詳細信息，請參閱語法和使用說明。

操作	原地執行	重建表	允許并發DML	僅修改元數據
添加列	是	是	是*	否
刪除列	是	是	是	否
重命名列	是	否	是*	是
重新排序列	是	是	是	否
設置列默認值	是	否	是	是
更改列數據類型	否	是	否	否
擴展VARCHAR列大小	是	否	是	是
刪除列默認值	是	否	是	是
更改自動遞增的值	是	否	是	否*
將列設為NULL	是	是*	是	否
將列設為NOT NULL	是*	是*	是	否
修改ENUM或SET列的定義	是	否	是	是

語法和使用說明

添加列

ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INPLACE, LOCK=NONE;

添加自動遞增列時不允許并發DML。數據會進行大量重組，這是一項開銷較大的操作。至少需要ALGORITHM=INPLACE, LOCK=SHARED。
2. 刪除列

ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INPLACE, LOCK=NONE;

數據會進行大量重組，這是一項開銷較大的操作。
3. 重命名列

ALTER TABLE tbl CHANGE old_col_name new_col_name data_type, ALGORITHM=INPLACE, LOCK=NONE;

為了允許并發DML，應保持相同的數據類型，只更改列名。

當你保持相同的數據類型和[NOT] NULL屬性，僅更改列名時，該操作始終可以在線執行。

你還可以重命名作為外鍵約束一部分的列。外鍵定義會自動更新以使用新的列名。重命名參與外鍵的列僅在ALGORITHM=INPLACE時有效。如果你使用ALGORITHM=COPY子句，或者某些其他條件導致操作使用ALGORITHM=COPY，ALTER TABLE語句將失敗。

不支持使用ALGORITHM=INPLACE重命名生成列。
4. 重新排序列
要重新排序列，可在CHANGE或MODIFY操作中使用FIRST或AFTER。

ALTER TABLE tbl_name MODIFY COLUMN col_name column_definition FIRST, ALGORITHM=INPLACE, LOCK=NONE;

數據會進行大量重組，這是一項開銷較大的操作。
5. 更改列數據類型

ALTER TABLE tbl_name CHANGE c1 c1 BIGINT, ALGORITHM=COPY;

僅支持使用ALGORITHM=COPY更改列數據類型。
6. 擴展VARCHAR列大小

ALTER TABLE tbl_name CHANGE COLUMN c1 c1 VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;

VARCHAR列所需的長度字節數必須保持不變。對于大小為0到255字節的VARCHAR列，需要一個長度字節來編碼該值。對于大小為256字節或更大的VARCHAR列，需要兩個長度字節。因此，原地ALTER TABLE僅支持將VARCHAR列大小從0增加到255字節，或者從256字節增加到更大的大小。原地ALTER TABLE不支持將VARCHAR列的大小從小于256字節增加到等于或大于256字節。在這種情況下，所需的長度字節數從1變為2，這僅支持通過表復制（ALGORITHM=COPY）實現。例如，嘗試使用原地ALTER TABLE將單字節字符集的VARCHAR列大小從VARCHAR(255)更改為VARCHAR(256)會返回以下錯誤：

ALTER TABLE tbl_name ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change
column type INPLACE. Try ALGORITHM=COPY.

注意：VARCHAR列的字節長度取決于字符集的字節長度。

不支持使用原地ALTER TABLE減小VARCHAR列的大小。減小VARCHAR列的大小需要進行表復制（ALGORITHM=COPY）。
7. 設置列默認值

ALTER TABLE tbl_name ALTER COLUMN col SET DEFAULT literal, ALGORITHM=INPLACE, LOCK=NONE;

僅修改表元數據。默認列值存儲在表的.frm文件中，而不是InnoDB數據字典中。
8. 刪除列默認值

ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT, ALGORITHM=INPLACE, LOCK=NONE;

更改自動遞增的值

ALTER TABLE table AUTO_INCREMENT=next_value, ALGORITHM=INPLACE, LOCK=NONE;

修改存儲在內存中的值，而不是數據文件中的值。

在使用復制或分片的分布式系統中，有時你需要將表的自動遞增計數器重置為特定值。插入到表中的下一行將使用指定的值作為其自動遞增列的值。你也可能在數據倉庫環境中使用此技術，在該環境中，你會定期清空所有表并重新加載它們，并從1重新開始自動遞增序列。
10. 將列設為NULL

ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NULL, ALGORITHM=INPLACE, LOCK=NONE;

原地重建表。數據會進行大量重組，這是一項開銷較大的操作。
11. 將列設為NOT NULL

ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;

原地重建表。操作成功需要STRICT_ALL_TABLES或STRICT_TRANS_TABLES SQL模式。如果列中包含NULL值，操作將失敗。服務器禁止對可能導致引用完整性丟失的外鍵列進行更改。請參閱13.1.8節 “ALTER TABLE語句”。數據會進行大量重組，這是一項開銷較大的操作。
12. 修改ENUM或SET列的定義

CREATE TABLE t1 (c1 ENUM('a', 'b', 'c'));
ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a', 'b', 'c', 'd'), ALGORITHM=INPLACE, LOCK=NONE;

只要數據類型的存儲大小不變，通過在有效成員值列表末尾添加新的枚舉或集合成員來修改ENUM或SET列的定義可以原地執行。例如，向具有8個成員的SET列添加一個成員會將每個值所需的存儲從1字節更改為2字節；這需要進行表復制。在列表中間添加成員會導致對現有成員進行重新編號，這也需要進行表復制。

生成列操作

下表概述了對生成列操作的在線DDL支持情況。有關詳細信息，請參閱語法和使用說明。

操作	原地執行	重建表	允許并發DML	僅修改元數據
添加存儲列	否	是	否	否
修改存儲列順序	否	是	否	否
刪除存儲列	是	是	是	否
添加虛擬列	是	否	是	是
修改虛擬列順序	否	是	否	否
刪除虛擬列	是	否	是	是

語法和使用說明

添加存儲列

ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) STORED), ALGORITHM=COPY;

對于存儲列，ADD COLUMN不是原地操作（不使用臨時表完成），因為表達式必須由服務器進行計算。
2. 修改存儲列順序

ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED FIRST, ALGORITHM=COPY;

原地重建表。
3. 刪除存儲列

ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INPLACE, LOCK=NONE;

原地重建表。
4. 添加虛擬列

ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL), ALGORITHM=INPLACE, LOCK=NONE;

對于非分區表，添加虛擬列是一個原地操作。然而，添加虛擬列不能與其他ALTER TABLE操作結合進行。

對于分區表，添加虛擬列不是一個原地操作。
5. 修改虛擬列順序

ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL FIRST, ALGORITHM=COPY;

刪除虛擬列

ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INPLACE, LOCK=NONE;

對于非分區表，刪除虛擬列是一個原地操作。然而，刪除虛擬列不能與其他ALTER TABLE操作結合進行。

對于分區表，刪除虛擬列不是一個原地操作。

外鍵操作

下表概述了對外鍵操作的在線DDL支持情況。星號表示有附加信息、例外情況或依賴條件。有關詳細信息，請參閱語法和使用說明。

操作	原地執行	重建表	允許并發DML	僅修改元數據
添加外鍵約束	是*	否	是	是
刪除外鍵約束	是	否	是	是

語法和使用說明

添加外鍵約束
當foreign_key_checks禁用時，支持INPLACE算法。否則，僅支持COPY算法。

ALTER TABLE tbl1 ADD CONSTRAINT fk_name FOREIGN KEY index (col1)REFERENCES tbl2(col2) referential_actions;

刪除外鍵約束

ALTER TABLE tbl DROP FOREIGN KEY fk_name;

無論foreign_key_checks選項是啟用還是禁用，都可以在線刪除外鍵。

如果你不知道特定表上的外鍵約束名稱，可以執行以下語句，并在每個外鍵的CONSTRAINT子句中查找約束名稱：

SHOW CREATE TABLE table\G

或者，查詢Information Schema的TABLE_CONSTRAINTS表，并使用CONSTRAINT_NAME和CONSTRAINT_TYPE列來識別外鍵名稱。

你也可以在單個語句中刪除外鍵及其關聯的索引：

ALTER TABLE table DROP FOREIGN KEY constraint, DROP INDEX index;

注意：如果正在更改的表中已經存在外鍵（即它是一個包含FOREIGN KEY...REFERENCE子句的子表），即使是那些不直接涉及外鍵列的在線DDL操作，也會有額外的限制：

如果父表的更改通過使用CASCADE或SET NULL參數的ON UPDATE或ON DELETE子句導致子表發生相關更改，對子表的ALTER TABLE操作可能會等待另一個事務提交。
同樣，如果一個表是外鍵關系中的父表，即使它不包含任何FOREIGN KEY子句，如果INSERT、UPDATE或DELETE語句導致子表中發生ON UPDATE或ON DELETE操作，它也可能會等待ALTER TABLE操作完成。

表操作

下表概述了對表操作的在線DDL支持情況。星號表示有附加信息、例外情況或依賴條件。有關詳細信息，請參閱語法和使用說明。

操作	原地執行	重建表	允許并發DML	僅修改元數據
更改行格式	是	是	是	否
更改鍵塊大小	是	是	是	否
設置持久表統計信息	是	否	是	是
指定字符集	是	是*	是	否
轉換字符集	否	是*	否	否
優化表	是*	是	是	否
使用FORCE選項重建表	是*	是	是	否
執行空重建	是*	是	是	否
重命名表	是	否	是	是

語法和使用說明

更改行格式

ALTER TABLE tbl_name ROW_FORMAT = row_format, ALGORITHM=INPLACE, LOCK=NONE;

數據會進行大量重組，這是一項開銷較大的操作。

有關ROW_FORMAT選項的更多信息，請參閱表選項。
2. 更改鍵塊大小

ALTER TABLE tbl_name KEY_BLOCK_SIZE = value, ALGORITHM=INPLACE, LOCK=NONE;

數據會進行大量重組，這是一項開銷較大的操作。

有關KEY_BLOCK_SIZE選項的更多信息，請參閱表選項。
3. 設置持久表統計信息選項

ALTER TABLE tbl_name STATS_PERSISTENT=0, STATS_SAMPLE_PAGES=20, STATS_AUTO_RECALC=1, ALGORITHM=INPLACE, LOCK=NONE;

僅修改表元數據。

持久統計信息包括STATS_PERSISTENT、STATS_AUTO_RECALC和STATS_SAMPLE_PAGES。有關更多信息，請參閱14.8.11.1節 “配置持久優化器統計信息參數”。
4. 指定字符集

ALTER TABLE tbl_name CHARACTER SET = charset_name, ALGORITHM=INPLACE, LOCK=NONE;

如果新的字符編碼不同，則會重建表。
5. 轉換字符集

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name, ALGORITHM=COPY;

如果新的字符編碼不同，則會重建表。
6. 優化表

OPTIMIZE TABLE tbl_name;

對于包含全文索引的表，不支持原地操作。該操作使用INPLACE算法，但不允許使用ALGORITHM和LOCK語法。
7. 使用FORCE選項重建表

ALTER TABLE tbl_name FORCE, ALGORITHM=INPLACE, LOCK=NONE;

從MySQL 5.6.17開始使用ALGORITHM=INPLACE。對于包含全文索引的表，不支持ALGORITHM=INPLACE。
8. 執行空重建

ALTER TABLE tbl_name ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

從MySQL 5.6.17開始使用ALGORITHM=INPLACE。對于包含全文索引的表，不支持ALGORITHM=INPLACE。
9. 重命名表

ALTER TABLE old_tbl_name RENAME TO new_tbl_name, ALGORITHM=INPLACE, LOCK=NONE;

MySQL會重命名與表tbl_name對應的文件，而不進行復制。（你也可以使用RENAME TABLE語句來重命名表。請參閱13.1.33節 “RENAME TABLE語句”。）專門為重命名的表授予的權限不會遷移到新名稱。必須手動更改這些權限。

表空間操作

下表概述了對表空間操作的在線DDL支持情況。有關詳細信息，請參閱語法和使用說明。

操作	原地執行	重建表	允許并發DML	僅修改元數據
啟用或禁用文件表空間加密	否	是	否	否

語法和使用說明

啟用或禁用文件表空間加密

ALTER TABLE tbl_name ENCRYPTION='Y', ALGORITHM=COPY;

僅支持對文件表空間進行加密。有關相關信息，請參閱14.14節 “InnoDB靜態數據加密”。

分區操作

除了大多數ALTER TABLE分區子句外，分區InnoDB表的在線DDL操作遵循與常規InnoDB表相同的規則。

大多數ALTER TABLE分區子句不會像常規非分區InnoDB表那樣通過相同的內部在線DDL API。因此，對ALTER TABLE分區子句的在線支持情況各不相同。

下表顯示了每個ALTER TABLE分區語句的在線狀態。無論使用哪種在線DDL API，MySQL都會盡可能減少數據復制和鎖定。

使用ALGORITHM=COPY或僅允許“ALGORITHM=DEFAULT, LOCK=DEFAULT”的ALTER TABLE分區選項，會使用COPY算法對表進行重新分區。換句話說，會使用新的分區方案創建一個新的分區表。新創建的表包括ALTER TABLE語句應用的任何更改，并且表數據會被復制到新的表結構中。

分區子句	原地執行	允許DML	備注
PARTITION BY	否	否	允許`ALGORITHM=COPY, LOCK={DEFAULT
ADD PARTITION	否	否	僅允許`ALGORITHM=DEFAULT, LOCK=DEFAULT`。對于按`RANGE`或`LIST`分區的表，不復制現有數據。對于按`HASH`或`LIST`分區的表，允許并發查詢。MySQL在持有共享鎖的同時復制數據。
DROP PARTITION	否	否	僅允許`ALGORITHM=DEFAULT, LOCK=DEFAULT`。對于按`RANGE`或`LIST`分區的表，不復制現有數據。
DISCARD PARTITION	否	否	僅允許`ALGORITHM=DEFAULT, LOCK=DEFAULT`
IMPORT PARTITION	否	否	僅允許`ALGORITHM=DEFAULT, LOCK=DEFAULT`
TRUNCATE PARTITION	是	是	不復制現有數據。它只是刪除行；它不會更改表本身或其任何分區的定義。
COALESCE PARTITION	否	否	僅允許`ALGORITHM=DEFAULT, LOCK=DEFAULT`。對于按`HASH`或`LIST`分區的表，允許并發查詢，因為MySQL在持有共享鎖的同時復制數據。
REORGANIZE PARTITION	否	否	僅允許`ALGORITHM=DEFAULT, LOCK=DEFAULT`。對于按`LINEAR HASH`或`LIST`分區的表，允許并發查詢。MySQL在持有共享元數據鎖的同時從受影響的分區復制數據。
EXCHANGE PARTITION	是	是
ANALYZE PARTITION	是	是
CHECK PARTITION	是	是
OPTIMIZE PARTITION	否	否	`ALGORITHM`和`LOCK`子句將被忽略。重建整個表。請參閱22.3.4節 “分區的維護”。
REBUILD PARTITION	否	否	僅允許`ALGORITHM=DEFAULT, LOCK=DEFAULT`。對于按`LINEAR HASH`或`LIST`分區的表，允許并發查詢。MySQL在持有共享元數據鎖的同時從受影響的分區復制數據。
REPAIR PARTITION	是	是
REMOVE PARTITIONING	否	否	允許`ALGORITHM=COPY, LOCK={DEFAULT
分區子句	原地執行	允許DML	備注

對分區表進行的非分區在線ALTER TABLE操作遵循與常規表相同的規則。然而，ALTER TABLE會對每個表分區執行在線操作，由于要對多個分區進行操作，這會導致對系統資源的需求增加。

有關ALTER TABLE分區子句的更多信息，請參閱分區選項和13.1.8.1節 “ALTER TABLE分區操作”。有關分區的一般信息，請參閱第22章 “分區”。

14.13.2 在線DDL的性能和并發性

在線DDL在多個方面改進了MySQL的操作：

訪問表的應用程序響應更加靈敏，因為在DDL操作進行時，對表的查詢和DML操作可以繼續進行。減少了對MySQL服務器資源的鎖定和等待，即使對于那些不涉及DDL操作的業務，也提高了系統的可擴展性。
原地操作避免了與表復制方法相關的磁盤I/O和CPU周期，從而最大限度地減少了數據庫的整體負載。減少負載有助于在DDL操作期間保持良好的性能和高吞吐量。
原地操作比表復制操作讀取到緩沖池中的數據更少，這減少了從內存中清除頻繁訪問數據的情況。頻繁訪問的數據被清除可能會在DDL操作后導致暫時的性能下降。

LOCK子句

默認情況下，MySQL在DDL操作期間盡可能少地使用鎖定。如果需要，可以指定LOCK子句來實施更嚴格的鎖定。如果LOCK子句指定的鎖定級別比特定DDL操作允許的級別更寬松，語句將因錯誤而失敗。LOCK子句按從寬松到嚴格的順序描述如下：

LOCK=NONE：允許并發查詢和DML。例如，對于涉及客戶注冊或購買的表，使用此子句可以避免在長時間的DDL操作期間使表不可用。
LOCK=SHARED：允許并發查詢，但阻止DML。例如，在數據倉庫表上使用此子句，在這種情況下，你可以將數據加載操作延遲到DDL操作完成，但查詢不能長時間延遲。
LOCK=DEFAULT：允許盡可能多的并發（并發查詢、DML或兩者皆可）。省略LOCK子句與指定LOCK=DEFAULT相同。當你知道DDL語句的默認鎖定級別不會對表的可用性造成問題時，使用此子句。
LOCK=EXCLUSIVE：阻止并發查詢和DML。如果首要考慮的是在盡可能短的時間內完成DDL操作，并且不需要并發查詢和DML訪問，則使用此子句。如果服務器應該處于空閑狀態，你也可以使用此子句，以避免意外的表訪問。

在線DDL和元數據鎖

在線DDL操作可以分為三個階段：

階段1：初始化：在初始化階段，服務器會考慮存儲引擎的功能、語句中指定的操作以及用戶指定的ALGORITHM和LOCK選項，來確定操作期間允許的并發程度。在此階段，會獲取一個共享可升級的元數據鎖，以保護當前的表定義。
階段2：執行：在此階段，語句將被準備和執行。元數據鎖是否升級為排他鎖取決于在初始化階段評估的因素。如果需要排他元數據鎖，它只會在語句準備期間短暫獲取。
階段3：提交表定義：在提交表定義階段，元數據鎖將升級為排他鎖，以清除舊的表定義并提交新的表定義。一旦獲得排他鎖，其持續時間很短。

由于上述排他元數據鎖的要求，在線DDL操作可能需要等待持有表元數據鎖的并發事務提交或回滾。在DDL操作之前或期間啟動的事務可能會持有正在更改的表的元數據鎖。在長運行或非活動事務的情況下，在線DDL操作可能會因等待排他元數據鎖而超時。此外，在線DDL操作請求的掛起排他元數據鎖會阻止表上的后續事務。

以下示例演示了在線DDL操作等待排他元數據鎖的情況，以及掛起的元數據鎖如何阻止表上的后續事務：

會話1：

mysql> CREATE TABLE t1 (c1 INT) ENGINE=InnoDB;
mysql> START TRANSACTION;
mysql> SELECT * FROM t1;

會話1的SELECT語句對表t1獲取了一個共享元數據鎖。

會話2：

mysql> ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE;

會話2中的在線DDL操作需要對表t1獲取排他元數據鎖以提交表定義更改，因此它必須等待會話1中的事務提交或回滾。

會話3：

mysql> SELECT * FROM t1;

會話3中發出的SELECT語句會被阻塞，等待會話2中的ALTER TABLE操作請求的排他元數據鎖被授予。

你可以使用SHOW FULL PROCESSLIST來確定事務是否在等待元數據鎖。

mysql> SHOW FULL PROCESSLIST\G
...
*************************** 2. row ***************************Id: 5User: rootHost: localhostdb: test
Command: QueryTime: 44State: Waiting for table metadata lockInfo: ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE
...
*************************** 4. row ***************************Id: 7User: rootHost: localhostdb: test
Command: QueryTime: 5State: Waiting for table metadata lockInfo: SELECT * FROM t1
4 rows in set (0.00 sec)

元數據鎖信息也會通過Performance Schema的metadata_locks表公開，該表提供了有關會話之間元數據鎖依賴關系、會話正在等待的元數據鎖以及當前持有元數據鎖的會話的信息。有關更多信息，請參閱25.12.12.1節 “metadata_locks### 表”。

在線DDL性能

DDL操作的性能在很大程度上取決于該操作是原地執行還是需要重建表。

為了評估DDL操作的相對性能，你可以比較使用ALGORITHM = INPLACE和ALGORITHM = COPY的結果。或者，你也可以比較禁用和啟用old_alter_table時的結果。

對于修改表數據的DDL操作，你可以通過查看命令完成后顯示的 “受影響的行數” 值來確定該操作是原地執行還是進行了表復制。例如：

更改列的默認值（速度快，不影響表數據）：

Query OK, 0 rows affected (0.07 sec)

添加索引（需要時間，但 “受影響的行數” 為 0 表明表未被復制）：

Query OK, 0 rows affected (21.42 sec)

更改列的數據類型（需要大量時間，并且需要重建表的所有行）：

Query OK, 1671168 rows affected (1 min 35.54 sec)

在對大型表執行DDL操作之前，可按以下步驟檢查該操作是快還是慢：

克隆表結構。
用少量數據填充克隆表。
在克隆表上運行DDL操作。
檢查 “受影響的行數” 是否為零。非零值意味著該操作會復制表數據，這可能需要進行特殊規劃。例如，你可以在計劃的停機期間執行DDL操作，或者在每個副本服務器上依次執行。

注意：為了更好地了解與DDL操作相關的MySQL處理過程，你可以在DDL操作前后檢查與InnoDB相關的Performance Schema和INFORMATION_SCHEMA表，以查看物理讀取、寫入、內存分配等的數量。

Performance Schema階段事件可用于監控ALTER TABLE的進度。請參閱14.17.1節 “使用Performance Schema監控InnoDB表的ALTER TABLE進度”。

由于記錄并發DML操作所做的更改，然后在最后應用這些更改會涉及一些處理工作，因此在線DDL操作的總體時間可能比阻止其他會話訪問表的表復制機制更長。不過，這種原始性能的降低可以通過使用該表的應用程序獲得更好的響應能力來平衡。在評估更改表結構的技術時，應根據網頁加載時間等因素考慮最終用戶對性能的感知。

14.13.3 在線DDL的空間要求

在線DDL操作有以下空間要求：

臨時日志文件

當在線DDL操作創建索引或修改表時，臨時日志文件會記錄并發DML。臨時日志文件會根據innodb_sort_buffer_size的值按需擴展，最大不超過innodb_online_alter_log_max_size指定的值。如果操作耗時較長，并且并發DML對表的修改非常大，導致臨時日志文件的大小超過innodb_online_alter_log_max_size的值，在線DDL操作將以DB_ONLINE_LOG_TOO_BIG錯誤失敗，并且未提交的并發DML操作將被回滾。較大的innodb_online_alter_log_max_size設置允許在在線DDL操作期間進行更多的DML，但也會延長DDL操作結束時鎖定表以應用記錄的DML的時間。

innodb_sort_buffer_size變量還定義了臨時日志文件讀取緩沖區和寫入緩沖區的大小。

臨時排序文件

重建表的在線DDL操作在創建索引期間會將臨時排序文件寫入MySQL臨時目錄（Unix系統上為$TMPDIR，Windows系統上為%TEMP%，或者由--tmpdir指定的目錄）。臨時排序文件不會創建在包含原始表的目錄中。每個臨時排序文件的大小足以容納一列數據，并且當數據合并到最終表或索引中時，每個排序文件都會被刪除。涉及臨時排序文件的操作可能需要的臨時空間等于表中的數據量加上索引的大小。如果在線DDL操作使用了數據目錄所在文件系統上的所有可用磁盤空間，將報告錯誤。

如果MySQL臨時目錄不足以容納排序文件，可以將tmpdir設置為不同的目錄。或者，使用innodb_tmpdir為在線DDL操作定義一個單獨的臨時目錄。該選項在MySQL 5.7.11中引入，用于幫助避免因大型臨時排序文件而導致的臨時目錄溢出。

中間表文件

一些重建表的在線DDL操作會在與原始表相同的目錄中創建一個臨時中間表文件。中間表文件可能需要的空間等于原始表的大小。中間表文件名以#sql - ib前綴開頭，并且僅在在線DDL操作期間短暫出現。

innodb_tmpdir選項不適用于中間表文件。

14.13.4 使用在線DDL簡化DDL語句

在引入在線DDL之前，將許多DDL操作組合到一個ALTER TABLE語句中是常見的做法。由于每個ALTER TABLE語句都涉及復制和重建表，因此一次性對同一個表進行多個更改會更高效，因為這些更改可以通過一次表重建操作完成。缺點是涉及DDL操作的SQL代碼更難維護，并且在不同腳本中重用也更困難。如果每次的具體更改都不同，你可能需要為每個略有不同的場景構造一個新的復雜ALTER TABLE語句。

對于可以原地執行的DDL操作，你可以將它們拆分為單獨的ALTER TABLE語句，以便于腳本編寫和維護，而不會犧牲效率。例如，你可以將一個復雜的語句，如：

ALTER TABLE t1 ADD INDEX i1(c1), ADD UNIQUE INDEX i2(c2),CHANGE c4_old_name c4_new_name INTEGER UNSIGNED;

拆分為更簡單的部分，這些部分可以獨立測試和執行，例如：

ALTER TABLE t1 ADD INDEX i1(c1);
ALTER TABLE t1 ADD UNIQUE INDEX i2(c2);
ALTER TABLE t1 CHANGE c4_old_name c4_new_name INTEGER UNSIGNED NOT NULL;

不過，你可能仍然會對以下情況使用多部分的ALTER TABLE語句：

必須按特定順序執行的操作：例如，先創建一個索引，然后創建一個使用該索引的外鍵約束。
使用相同特定LOCK子句的操作：你希望這些操作作為一個組要么全部成功，要么全部失敗。
無法原地執行的操作：即仍然使用表復制方法的操作。
指定ALGORITHM = COPY或old_alter_table = 1的操作：在特殊場景下，為了實現精確的向后兼容性，可能需要強制使用表復制行為。

14.13.5 在線DDL的失敗條件

在線DDL操作失敗通常是由以下條件之一導致的：

ALGORITHM子句指定的算法不兼容：ALGORITHM子句指定的算法與特定類型的DDL操作或存儲引擎不兼容。
LOCK子句指定的鎖定級別不兼容：LOCK子句指定的低鎖定級別（SHARED或NONE）與特定類型的DDL操作不兼容。
等待排他鎖超時：在DDL操作的初始和最終階段可能需要短暫的表排他鎖，如果等待該鎖時發生超時，操作會失敗。
臨時目錄磁盤空間不足：在創建索引時，MySQL在磁盤上寫入臨時排序文件時，tmpdir或innodb_tmpdir文件系統的磁盤空間耗盡。有關更多信息，請參閱14.13.3節 “在線DDL的空間要求”。
臨時在線日志過大：操作耗時較長，并且并發DML對表的修改非常大，導致臨時在線日志的大小超過innodb_online_alter_log_max_size配置選項的值。這種情況會導致DB_ONLINE_LOG_TOO_BIG錯誤。
并發DML與新表定義沖突：并發DML對表所做的更改在原始表定義下是允許的，但在新表定義下不允許。只有在最后，當MySQL嘗試應用并發DML語句的所有更改時，操作才會失敗。例如，在創建唯一索引時，你可能會向列中插入重復值；或者在創建主鍵索引時，向列中插入NULL值。并發DML所做的更改優先，ALTER TABLE操作實際上會被回滾。

14.13.6 在線DDL的限制

以下限制適用于在線DDL操作：

臨時表創建索引需復制表：在臨時表上創建索引時，會復制表。
存在特定約束時不允許LOCK = NONE：如果表上存在ON...CASCADE或ON...SET NULL約束，則不允許使用ALTER TABLE子句LOCK = NONE。
等待元數據鎖事務：在在線DDL操作完成之前，必須等待持有表元數據鎖的事務提交或回滾。在線DDL操作在執行階段可能需要短暫的表排他元數據鎖，并且在操作的最后階段更新表定義時總是需要排他元數據鎖。因此，持有表元數據鎖的事務可能會導致在線DDL操作阻塞。持有表元數據鎖的事務可能在在線DDL操作之前或期間啟動。長時間運行或非活動的持有表元數據鎖的事務可能會導致在線DDL操作超時。
外鍵關系中的操作問題：對外鍵關系中的表執行在線DDL操作時，不會等待外鍵關系中另一個表上執行的事務提交或回滾。該事務會對其正在更新的表持有排他元數據鎖，并對與外鍵相關的表持有共享元數據鎖（用于外鍵檢查）。共享元數據鎖允許在線DDL操作繼續進行，但會在操作的最后階段阻塞該操作，因為更新表定義需要排他元數據鎖。這種情況可能會導致死鎖，因為其他事務會等待在線DDL操作完成。
應用DML日志時可能出現重復鍵錯誤：在運行在線DDL操作時，執行ALTER TABLE語句的線程會應用在同一表上從其他連接線程并發運行的DML操作的在線日志。在應用DML操作時，即使重復條目只是臨時的，并且會被在線日志中的后續條目恢復，也有可能遇到重復鍵條目錯誤（ERROR 1062 (23000): Duplicate entry）。這類似于InnoDB中的外鍵約束檢查，其中約束必須在事務期間保持有效。
OPTIMIZE TABLE操作：OPTIMIZE TABLE對InnoDB表的操作會映射為ALTER TABLE操作，以重建表、更新索引統計信息并釋放聚簇索引中未使用的空間。由于鍵是按照它們在主鍵中出現的順序插入的，因此二級索引的創建效率不高。隨著對重建常規和分區InnoDB表的在線DDL支持的添加，OPTIMIZE TABLE也得到了支持。
舊表不支持ALGORITHM = INPLACE：在MySQL 5.6之前創建的包含時態列（DATE、DATETIME或TIMESTAMP）且未使用ALGORITHM = COPY重建的表不支持ALGORITHM = INPLACE。在這種情況下，ALTER TABLE...ALGORITHM = INPLACE操作會返回以下錯誤：

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported.
Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

以下限制通常適用于涉及重建大型表的在線DDL操作：

無法暫停或限制資源使用：沒有機制可以暫停在線DDL操作，也無法限制其I/O或CPU使用。
回滾成本高：如果在線DDL操作失敗，回滾操作的成本可能很高。
可能導致復制延遲：長時間運行的在線DDL操作可能會導致復制延遲。在線DDL操作必須在主服務器上完成后才能在從服務器上運行。此外，在主服務器上并發處理的DML只有在從服務器上的DDL操作完成后才能在從服務器上處理。

有關對大型表運行在線DDL操作的更多信息，請參閱14.13.2節 “在線DDL的性能和并發性”。

14.13.7 在線 DDL 與復制

在線 DDL 操作在主從復制環境中需要特別考慮，以下是相關的詳細內容：

復制環境中的基本原理

在主從復制架構里，主服務器上執行的在線 DDL 操作會被復制到從服務器。主服務器上的操作會以二進制日志（binlog）的形式記錄下來，從服務器讀取這些二進制日志并在自身上重放這些操作，從而保持與主服務器數據的一致性。

不同操作的復制情況

原地操作：對于可以原地執行的在線 DDL 操作（如添加虛擬列、修改列的默認值等），通常在主從復制中表現良好。這些操作在主服務器上執行時，對表的修改是直接進行的，不需要復制整個表的數據。從服務器可以快速地重放這些操作，因為它們主要是對元數據的修改，不會產生大量的數據傳輸和處理開銷。
重建表操作：當主服務器上執行需要重建表的在線 DDL 操作（如更改列的數據類型、添加或刪除索引等）時，會對復制產生較大影響。這類操作需要創建一個新的表結構，并將原表的數據復制到新表中。在主服務器上，這個過程可能會比較耗時，并且會占用大量的系統資源。從服務器需要等待主服務器完成操作并將相關的二進制日志傳輸過來后，才能開始在自身上重放這些操作。這可能會導致從服務器與主服務器之間出現復制延遲。

復制延遲問題及解決方法

問題描述：長時間運行的在線 DDL 操作可能會導致主從復制延遲。這是因為主服務器在執行 DDL 操作時，可能會阻塞后續的 DML 操作，并且 DDL 操作本身也需要一定的時間完成。從服務器需要等待主服務器完成 DDL 操作并將二進制日志傳輸過來后，才能繼續處理后續的 DML 操作，從而導致從服務器的數據與主服務器的數據不一致。
解決方法
- 選擇合適的時間執行：盡量在業務低谷期執行需要重建表的在線 DDL 操作，以減少對業務的影響。例如，在深夜或周末進行操作，此時系統的負載較低，對用戶的影響最小。
- 優化操作：在執行 DDL 操作之前，對表進行優化，如清理無用的數據、重建索引等，以減少操作所需的時間和資源。
- 增加從服務器資源：如果復制延遲問題經常出現，可以考慮增加從服務器的硬件資源，如 CPU、內存和磁盤 I/O 等，以提高從服務器處理 DDL 操作的能力。

在線 DDL 與多源復制

在多源復制環境中，多個主服務器的二進制日志會被復制到同一個從服務器。當多個主服務器同時執行在線 DDL 操作時，可能會出現沖突和復雜的情況。例如，不同主服務器上對同一個表的不同 DDL 操作可能會導致從服務器上的數據不一致。為了避免這種情況，需要仔細規劃和協調各個主服務器上的 DDL 操作，確保它們不會相互沖突。

14.13.8 在線 DDL 的監控與調試

為了確保在線 DDL 操作的順利進行，需要對其進行監控和調試。以下是一些常用的方法和工具：

使用 SHOW PROCESSLIST 命令

SHOW PROCESSLIST 命令可以顯示當前 MySQL 服務器上正在執行的所有線程的信息，包括 DDL 操作的線程。通過查看該命令的輸出，可以了解 DDL 操作的執行狀態，如是否正在等待鎖、是否已經完成等。例如：

SHOW FULL PROCESSLIST;

輸出結果中會包含每個線程的詳細信息，如線程 ID、用戶、主機、執行的 SQL 語句、執行時間和狀態等。通過檢查 State 列，可以了解 DDL 操作的當前狀態，如 Waiting for table metadata lock 表示該操作正在等待表的元數據鎖。

使用 Performance Schema

Performance Schema 是 MySQL 提供的一個強大的性能監控工具，它可以提供有關在線 DDL 操作的詳細信息。通過查詢 Performance Schema 中的相關表，可以了解 DDL 操作的執行時間、鎖等待時間、I/O 操作等信息。例如：

查看階段事件：可以通過查詢 performance_schema.events_stages_current 和 performance_schema.events_stages_history 表來查看 DDL 操作的各個階段的執行情況。

SELECT * FROM performance_schema.events_stages_current WHERE THREAD_ID = (SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = <DDL操作的線程ID>);

查看鎖信息：通過查詢 performance_schema.metadata_locks 表，可以了解 DDL 操作所涉及的元數據鎖的信息，如鎖的類型、持有鎖的線程、等待鎖的線程等。

SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_NAME = '<表名>';

使用 INFORMATION_SCHEMA

INFORMATION_SCHEMA 是 MySQL 提供的一個系統數據庫，它包含了有關數據庫、表、列、索引等的元數據信息。通過查詢 INFORMATION_SCHEMA 中的相關表，可以了解 DDL 操作對表結構的影響。例如：

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<表名>';

該查詢可以顯示指定表的所有列的信息，包括列名、數據類型、默認值等。通過比較 DDL 操作前后的查詢結果，可以了解 DDL 操作對表結構的具體修改。

日志記錄

MySQL 的錯誤日志和二進制日志也可以用于監控和調試在線 DDL 操作。錯誤日志會記錄 DDL 操作過程中出現的錯誤信息，通過查看錯誤日志，可以了解操作失敗的原因。二進制日志會記錄所有的 DDL 操作，通過分析二進制日志，可以了解操作的執行順序和詳細內容。

14.13.9 在線 DDL 的最佳實踐

為了確保在線 DDL 操作的順利進行，并最大限度地減少對業務的影響，以下是一些最佳實踐：

提前規劃

評估操作影響：在執行在線 DDL 操作之前，仔細評估該操作對系統性能、業務可用性和數據一致性的影響。考慮操作所需的時間、資源和可能出現的問題，并制定相應的應對措施。
選擇合適的時間：盡量在業務低谷期執行需要重建表的在線 DDL 操作，以減少對業務的影響。例如，在深夜或周末進行操作，此時系統的負載較低，對用戶的影響最小。

測試環境驗證

在測試環境中模擬操作：在生產環境中執行在線 DDL 操作之前，先在測試環境中進行模擬操作。測試環境應盡可能與生產環境一致，包括硬件配置、數據庫版本、數據量等。通過在測試環境中模擬操作，可以發現潛在的問題，并進行相應的調整和優化。
驗證操作結果：在測試環境中執行 DDL 操作后，驗證操作結果是否符合預期。檢查表結構是否正確修改、數據是否完整、業務功能是否正常等。

備份數據

執行操作前備份數據：在執行在線 DDL 操作之前，對相關的數據進行備份。備份可以在操作失敗時提供恢復數據的手段，確保數據的安全性和完整性。可以使用 MySQL 提供的備份工具，如 mysqldump 或第三方備份工具進行備份。

監控與日志記錄

實時監控操作過程：在執行在線 DDL 操作時，實時監控操作的執行情況。使用 SHOW PROCESSLIST、Performance Schema 和 INFORMATION_SCHEMA 等工具，了解操作的執行狀態、鎖等待時間、I/O 操作等信息。及時發現并處理操作過程中出現的問題。
記錄操作日志：記錄 DDL 操作的詳細信息，包括操作的時間、執行的 SQL 語句、操作的結果等。操作日志可以在后續的分析和調試中提供重要的參考信息。

逐步執行操作

拆分復雜操作：對于復雜的 DDL 操作，盡量將其拆分為多個簡單的操作。例如，將一個包含多個列修改和索引添加的操作拆分為多個單獨的操作，逐個執行。這樣可以降低操作的風險，并且在出現問題時更容易進行回滾和調試。
分階段執行：如果可能的話，分階段執行 DDL 操作。例如，先在部分數據上進行操作，驗證操作結果后，再在全量數據上執行操作。這樣可以減少操作對系統的影響，并且在出現問題時可以及時停止操作。

與業務團隊溝通

提前通知業務團隊：在執行在線 DDL 操作之前，提前通知業務團隊操作的時間、內容和可能的影響。讓業務團隊做好相應的準備，如調整業務流程、安排備用系統等。
及時反饋操作結果：在操作完成后，及時向業務團隊反饋操作的結果。如果操作過程中出現了問題，及時說明問題的原因和解決情況，確保業務團隊對操作的情況有清晰的了解。

通過遵循以上最佳實踐，可以有效地提高在線 DDL 操作的成功率，減少對業務的影響，確保數據庫系統的穩定運行。