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

版本 8.4

8.0 日文

MySQL 8.4 參考手冊 / ... / 線上 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)

中繼資料鎖定資訊也會透過效能結構描述 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 列受影響 表示未複製表格）
```
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 相關的效能結構描述和 INFORMATION_SCHEMA 表格，以查看實體讀取次數、寫入次數、記憶體配置等等。

效能結構描述階段事件可用於監控 ALTER TABLE 進度。請參閱第 17.16.1 節，「使用效能結構描述監控 InnoDB 表格的 ALTER TABLE 進度」。

由於記錄並行 DML 操作所做的變更，然後在結尾套用這些變更，因此線上 DDL 操作整體而言可能比封鎖其他工作階段表格存取的表格複製機制花費更長的時間。原始效能的降低與使用表格應用程式的較佳回應能力之間取得平衡。在評估變更表格結構的技術時，請根據網頁載入時間等因素，考量終端使用者對效能的感知。