MySQL :: MySQL 9.0 參考手冊 :: 17.12.2 線上 DDL 效能與並行

版本 9.0

8.4 目前版本
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / 線上 DDL 效能與並行

17.12.2 線上 DDL 效能與並行

線上 DDL 改善了 MySQL 操作的幾個方面

存取資料表的應用程式反應更快速，因為在 DDL 操作進行時，可以繼續在資料表上執行查詢和 DML 操作。減少鎖定和等待 MySQL 伺服器資源可提高延展性，即使對於未參與 DDL 操作的操作也是如此。
立即操作只會修改資料字典中的中繼資料。在操作的執行階段，可能會短暫取得資料表的獨佔中繼資料鎖定。資料表資料不受影響，使操作成為瞬間完成。允許並行的 DML。
線上操作避免了與資料表複製方法相關的磁碟 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: 5
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 44
  State: Waiting for table metadata lock
   Info: ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE
...
*************************** 4. row ***************************
     Id: 7
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 5
  State: Waiting for table metadata lock
   Info: SELECT * FROM t1
4 rows in set (0.00 sec)

中繼資料鎖定資訊也會透過 Performance Schema metadata_locks 資料表公開，此資料表提供工作階段之間中繼資料鎖定相依性的相關資訊、工作階段正在等待的中繼資料鎖定，以及目前持有中繼資料鎖定的工作階段。如需更多資訊，請參閱第 29.12.13.3 節：「metadata_locks 資料表」。

線上 DDL 效能

DDL 操作的效能主要取決於操作是否立即執行、是否就地執行以及是否重建資料表。

若要評估 DDL 操作的相對效能，您可以使用 ALGORITHM=INSTANT、ALGORITHM=INPLACE 和 ALGORITHM=COPY 來比較結果。您也可以在啟用 old_alter_table 的情況下執行語句，以強制使用 ALGORITHM=COPY。

對於修改資料表資料的 DDL 操作，您可以查看命令完成後顯示的「影響的列數」值，以判斷 DDL 操作是否就地執行變更或執行資料表複製。例如

變更資料行的預設值（快速，不影響資料表資料）
```
Query OK, 0 rows affected (0.07 sec)
```
新增索引（需要時間，但 0 rows affected 表示沒有複製資料表）
```
Query OK, 0 rows affected (21.42 sec)
```
變更資料行的資料類型（需要大量時間並需要重建資料表的所有列）
```
Query OK, 1671168 rows affected (1 min 35.54 sec)
```

在大型資料表上執行 DDL 操作之前，請檢查操作是快速還是慢速，方法如下

複製資料表結構。
使用少量資料填入複製的資料表。
在複製的資料表上執行 DDL 操作。
檢查「影響的列數」值是否為零。非零值表示操作會複製資料表資料，這可能需要特別規劃。例如，您可能會在排定的停機期間或一次在每個複本伺服器上執行 DDL 操作。

注意

為了更深入了解與 DDL 操作相關聯的 MySQL 處理，請在 DDL 操作前後檢查 Performance Schema 和與 InnoDB 相關的 INFORMATION_SCHEMA 資料表，以查看實體讀取、寫入、記憶體配置等的數量。

Performance Schema 階段事件可用於監控 ALTER TABLE 的進度。請參閱第 17.16.1 節：「使用 Performance Schema 監控 InnoDB 資料表的 ALTER TABLE 進度」。

由於記錄並行 DML 操作所做的變更，然後在最後套用這些變更需要一些處理工作，因此線上 DDL 操作的整體耗時可能會比封鎖其他工作階段資料表存取的資料表複製機制更長。原始效能的降低與使用資料表的應用程式更好的回應能力之間取得了平衡。在評估變更資料表結構的技術時，請考慮終端使用者對效能的感知，例如網頁的載入時間。