MySQL & MariaDB Online DDL 參考指南

概述

在早期的 MySQL 版本中，DDL 操作（如建立索引等）通常都需要對資料表加鎖，操作過程中 DML 操作都會被阻塞，影響正常業務。MySQL 5.6 和 MariaDB 10.0 開始支援 Online DDL，可以在執行 DDL 操作的同時，不影響 DML 的正常執行，線上直接執行 DDL 操作對使用者基本無感知（部分操作對效能有影響）。

不同版本的資料庫對各種 DDL 語句的支援存在一定的差異，本文將會針對 MySQL 和 MariaDB 對 Online DDL 的支援情況做一個彙總，在需要執行 DDL 操作時，可以參考本文的 Online DDL 支援情況 部分。

本文將會持續修正和更新，最新內容請參考我的 GITHUB 上的 程式猿成長計劃 專案，歡迎 Star，更多精彩內容請 follow me。

在 ALTER TABLE 語句中，支援通過 ALGORITHM 和 LOCK 語句來實現 Online DDL：

• ALGORITHM - 控制 DDL 操作如何執行，使用哪個演算法
• LOCK - 控制在執行 DDL 時允許對錶加鎖的級別

sql ALTER TABLE tab ADD COLUMN c varchar(50), ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM 支援的演算法

| ALGORITHM | 說明 | | --------- | ------------------------------------------------------------ | | DEFAULT | 預設演算法，自動使用可用的最高效的演算法 | | COPY | 最原始的方式，所有的儲存引擎都支援，不使用 Online DDL，操作時會建立臨時表，執行全表拷貝和重建，過程中會寫入 Redo Log 和大量的 Undo Log，需要新增讀鎖，非常低效 | | INPLACE | 儘可能避免表拷貝和重建，更確切的名字應該是 ENGINE 演算法，由儲存引擎決定如何實現，有些操作是可以立即生效的（比如重新命名列，改變列的預設值等），但有些操作依然需要全表或者部分表的拷貝和重建（比如新增刪除列、新增主鍵、改變列為 NULL 等） | | NOCOPY | 該演算法是 INPLACE 演算法的子集，用於避免聚簇索引（主鍵索引）的重建造成全表重建，也就說用該演算法會禁止任何引起聚簇索引重建的操作 | | INSTANT | 用於避免 INPLACE 演算法在需要修改資料檔案時異常低效的問題，所有涉及到表拷貝和重建的操作都會被禁止 |

NOCOPY 演算法支援：MariaDB 10.3.2+，MySQL 不支援該演算法。

INSTANT 演算法支援：MariaDB 10.3.2+，MySQL 8.0.12+。

演算法使用規則：

• 如果使用者指定的演算法為 COPY，則 InnoDB 使用 COPY 演算法。
• 如果使用者指定的是 COPY 之外的其它演算法，則 InnoDB 會按照演算法效率，選擇最高效的演算法，最差的情況下采用使用者指定的演算法。比如使用者指定了 ALOGRITHM = NOCOPY，則 InnoDB 會從 (NOCOPY, INSTANT) 中選擇支援的最高效的演算法。

MySQL 服務主要為 Server 層 和 儲存引擎層 兩部分組成，Server 層包含了 MySQL 大部分核心功能，所有的內建函式，跨儲存引擎的功能如儲存過程、觸發器、檢視等。儲存引擎層負責資料的儲存和讀取，採用了外掛式的架構模式。

COPY 演算法 作用在 Server 層，其執行過程都是在 Server 層，因此所有儲存引擎都支援使用該演算法，執行過程如下圖

INPLACE 演算法 作用於儲存引擎層，是 InnoDB 儲存引擎特有的 DDL 演算法，執行過程如下圖所示

LOCK 策略

預設情況下，MySQL/MariaDB 在執行 DDL 期間會使用盡可能少的鎖，如果必要，可以通過 LOCK 子句控制在執行 DDL 時允許對錶加鎖的級別。如果指定的操作所要求的限制級別不滿足（EXCLUSIVE > SHARED > NONE），則語句執行失敗並報錯。

| 策略 | 說明 | | --------- | --------------------------------------------- | | DEFAULT | 使用當前操作支援的粒度最小的鎖策略 | | NONE | 不獲取任何表鎖，允許所有的 DML 操作 | | SHARED | 對錶新增共享鎖（讀鎖），只允許只讀的 DML 操作 | | EXCLUSIVE | 對錶新增排它鎖（寫鎖），不允許任何 DML 操作 |

為了避免執行 DDL 時，由於鎖表導致生產服務不可用，在執行表結構變更語句時，可以新增 LOCK=NONE 子句，如果語句需要獲取共享鎖或者排它鎖，則會直接報錯，這樣就可以避免意外鎖表，造成線上服務不可用了。

Online DDL 執行過程

Online DDL 操作主要分為三個階段：

• 階段 1：初始化

  在初始化階段，伺服器會根據儲存引擎的能力，操作的語句和使用者指定的 ALGORITHM 和 LOCK 選項來決定允許多大程度的併發。在這個階段會建立一個 可升級的後設資料共享鎖（SU）來保護表定義。

• 階段 2：執行

  這個階段會 準備 並 執行 DDL 語句，根據 階段 1 評估的結果來決定是否將後設資料鎖升級為 排它鎖 （X），如果需要升級為排它鎖，則只在 DDL 的 準備階段 短暫的新增排它鎖。

• 階段 3：提交表定義

  在表定義的提交階段，後設資料鎖會升級為排它鎖來更新表的定義。獨佔排它鎖的持續時間非常短。

後設資料鎖（MDL，Metadata Lock）主要用於 DDL 和 DML 操作之間的併發訪問控制，保護表結構（表定義）的一致，保證讀寫的正確性。MDL 不需要顯式的使用，在訪問表時會自動加上。

由於上面三個階段中對後設資料鎖的獨佔， Online DDL 過程必須等待已經持有後設資料鎖的併發事務提交或者回滾才能繼續執行。

注意：當 Online DDL 操作正在等待後設資料鎖時，該後設資料鎖會處於掛起狀態，後續的所有事務都會被阻塞。在 MariaDB 10.3 之後，可以通過新增 NO WAIT 或者 WAIT n 來控制等待所得超時時間，超時立即失敗。

sql ALTER TABLE tbl_name [WAIT n|NOWAIT] ... CREATE ... INDEX ON tbl_name (index_col_name, ...) [WAIT n|NOWAIT] ... DROP INDEX ... [WAIT n|NOWAIT] DROP TABLE tbl_name [WAIT n|NOWAIT] ... LOCK TABLE ... [WAIT n|NOWAIT] OPTIMIZE TABLE tbl_name [WAIT n|NOWAIT] RENAME TABLE tbl_name [WAIT n|NOWAIT] ... SELECT ... FOR UPDATE [WAIT n|NOWAIT] SELECT ... LOCK IN SHARE MODE [WAIT n|NOWAIT] TRUNCATE TABLE tbl_name [WAIT n|NOWAIT]

評估 Online DDL 操作的效能

Online DDL 操作的效能取決於是否發生了表的重建。在對大表執行 DDL 操作之前，為了避免影響正常業務操作，最好是先評估一下 DDL 語句的效能再選擇如何操作。

1. 複製表結構，建立一個新的表
2. 在新建立的表中插入少量資料
3. 在新表上面執行 DDL 操作
4. 檢查執行操作後返回的 rows affected 是否是 0。如果該值非 0，則意味著需要拷貝表資料，此時對 DDL 的上線需要慎重考慮，周密計劃

比如

• 修改某一列的預設值（快速，不會影響到表資料）

  bash Query OK, 0 rows affected (0.07 sec)

• 新增索引（需要花費一些時間，但是 0 rows affected 說明沒有發生表拷貝）

  bash Query OK, 0 rows affected (21.42 sec)

• 修改列的資料型別（需要花費很長時間，並且重建表）

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

由於在執行 Online DDL 過程中需要記錄併發執行的 DML 操作發生的變更，然後在執行完 DDL 操作之後再應用這些變更，因此使用 Online DDL 操作花費的時間比不使用 Online 模式執行要更長一些。

Online DDL 支援情況

INSTANT 演算法支援：MariaDB 10.3.2+，MySQL 8.0.12+。NOCOPY 只支援 MariaDB 10.3.2 以上版本，不支援 MySQL，這裡就暫且忽略了。

重點關注是否 重建表 和 支援併發 DML：不需要重建表，支援併發 DML 最佳。

二級索引

| 操作 | INSTANT | INPLACE | 重建表 | 併發 DML | 只修改後設資料 | | --------------------------------------------------- | ------- | ------- | ------ | -------- | ------------ | | 建立或者新增二級索引 | ❌ | ✅ | ❌ | ✅ | ❌ | | 刪除索引 | ❌ | ✅ | ❌ | ✅ | ✅ | | 重新命名索引 （⚠️MySQL 5.7+，MariaDB 10.5.2+） | ❌ | ✅ | ❌ | ✅ | ✅ | | 新增 FULLTEXT 索引 | ❌ | ✅ ① | ❌ ① | ❌ | ❌ | | 新增 SPATIAL 索引（⚠️MySQL 5.7+，MariaDB 10.2.2+） | ❌ | ✅ | ❌ | ❌ | ❌ | | 修改索引型別 | ✅ | ✅ | ❌ | ✅ | ✅ |

說明：

• ① 第一次新增全文索引欄位時需要重建表，之後就不需要了

主鍵

| 操作 | INSTANT | INPLACE | 重建表 | 併發 DML | 只修改後設資料 | | ---------------------------- | ------- | ------- | ------ | -------- | ------------ | | 新增主鍵 | ❌ | ✅ ② | ✅ ② | ✅ | ❌ | | 刪除主鍵 | ❌ | ❌ | ✅ | ❌ | ❌ | | 刪除一個主鍵同時新增一個新的 | ❌ | ✅ | ✅ | ✅ | ❌ |

說明：

• 重建聚簇索引總是需要拷貝表資料（InnoDB 是“索引組織表”），所以最好是在建立表的時候就定義好主鍵
• 如果建立表是沒有指定主鍵，InnoDB 會選擇第一個 NOT NULL 的 UNIQUE 索引作為主鍵，或者使用系統生成的 KEY
• ② 對聚簇索引來說，使用 INPLACE 模式比 COPY 模式要高效一些：不會產生 undo log 和 redo log，二級索引是有序的，所以可以按順序載入，不需要使用變更緩衝區

普通列

| 操作 | INSTANT | INPLACE | 重建表 | 併發 DML | 只修改後設資料 | | ----------------------------- | ------- | ------- | ------ | -------- | ------------ | | 列新增 | ✅ ③ | ✅ | ❌ ③ | ✅ ③ | ❌ | | 列刪除 | ❌ ④ | ✅ | ✅ | ✅ | ❌ | | 列重新命名 | ❌ | ✅ | ❌ | ✅ ⑤ | ✅ | | 改變列的順序 | ❌ ⑫ | ✅ | ✅ | ✅ | ❌ | | 設定預設值 | ✅ | ✅ | ❌ | ✅ | ✅ | | 修改資料型別 | ❌ | ❌ | ✅ | ❌ | ❌ | | 擴充套件 VARCHAR 長度（⚠️MySQL 5.7+, MariaDB 10.2.2+） | ❌ ⑬ | ✅ | ❌ ⑥ | ✅ | ✅ | | 刪除列的預設值 | ✅ | ✅ | ❌ | ✅ | ✅ | | 改變自增值 | ❌ | ✅ | ❌ | ✅ | ❌ ⑦ | | 設定列為 NULL | ❌ | ✅ | ✅ ⑧ | ✅ | ❌ | | 設定列為 NOT NULL | ❌ | ✅ ⑨ | ✅ ⑨ | ✅ | ❌ | | 修改 ENUM 和 SET 列的定義 | ✅ | ✅ | ❌ ⑩ | ✅ | ✅ |

說明：

• ③ 併發 DML：當插入一個自增列時，不支援併發的 DML 操作，新增自增列時，大量的資料會被重新組織，代價高昂

• ③ 重建表：新增列時，MySQL 5.7及之前版本需要重建表，MySQL 8.0 當 ALGORITHM=INPLACE 時，需要重建表，ALGORITHM=INSTANT 時不需要重建

• ③ INSTANT演算法：新增列時，使用 INSTANT 演算法有下面這些限制

  • 新增列操作不能和其它不支援 INSTANT 演算法的操作合併為一條 ALTER TABLE 語句
  • 新增的列只能新增到表的最後，不能放到其它列的前面，在 MariaDB 10.4 之後，支援在任意位置新增
  • 不能將列新增到 ROW_FORMAT=COMPRESSED 的表中
  • 不能將列新增到包含 FULLTEXT 的表中
  • 不能將列新增到臨時表中，臨時表只支援 ALGORITHM=COPY
  • 不能將列新增到駐留在資料字典表空間中的表中
  • 在新增列的時候不會計算行的大小限制，該限制在執行 DML 操作插入或者更新表時才會被檢查

• ④ 刪除列時，大量的資料需要被重新組織，代價高昂，在 MariaDB 10.4 之後，刪除列支援 INSTANT 演算法

• ⑤ 重新命名列時，確保只改變列名，不改變資料型別，這樣才能支援併發的 DML 操作

• ⑥ 擴充套件 VARCHAR 長度時，INPLACE 是有條件的，必須保證用於標識字串長度的長度位元組不變（這裡說的都是位元組，不是 VARCHAR 的字元長度，位元組佔用與採用的字符集有關，utf8 字符集下，一個字元佔 3 個位元組， utf8mb4 則 4 個位元組）

  • 當 VARCHAR 列長度在 0-255 個位元組時，長度標識佔用一個位元組
  • 當 VARCHAR 列長度大於 255 個位元組時，長度標識佔用兩個位元組

  因此，INPLACE 只支援 0-255 個位元組之間或者 256 個位元組到更大的長度之間的變更。VARCHAR 列長度減小是不支援 INPLACE 的。

• ⑦ 自增列值變更是修改的記憶體中的值，不是資料檔案

• ⑧ ⑨ 設定列為 [NOT] NULL 時，大量的資料被重新組織，代價高昂

• ⑩ 修改 ENUM 和 SET 型別的列定義時，是否需要表拷貝取決於已有元素的個數和插入成員的位置

• ⑫ 在 MariaDB 10.4 之後，列排序支援 INSTANT 演算法

• ⑬ 在 MariaDB 10.4.3 之後，InnoDB 支援使用 INSTANT 演算法增加列的長度，但是也有一些限制，具體參考 Changing the Data Type of a Column

生成列

| 操作 | INSTANT | INPLACE | 重建表 | 併發 DML | 只修改後設資料 | | ----------------------- | ------- | ------- | ------ | -------- | ------------ | | 新增 STORED 列 | ❌ | ❌ | ✅ | ❌ | ❌ | | 修改 STORED 列的排序 | ❌ | ❌ | ✅ | ❌ | ❌ | | 刪除 STORED 列 | ❌ | ✅ | ✅ | ✅ | ❌ | | 新增 VIRTUAL 列 | ✅ | ✅ | ❌ | ✅ | ✅ | | 修改 VIRTUAL 列的排序 | ✅ | ❌ | ✅ | ❌ | ❌ | | 刪除 VIRTUAL 列 | ✅ | ✅ | ❌ | ✅ | ✅ |

外來鍵

| 操作 | INSTANT | INPLACE | 重建表 | 併發 DML | 只修改後設資料 | | ------------ | ------- | ------- | ------ | -------- | ------------ | | 新增外來鍵約束 | ❌ | ✅ ⑭ | ❌ | ✅ | ✅ | | 刪除外來鍵約束 | ❌ | ✅ | ❌ | ✅ | ✅ |

說明：

• ⑭ 新增外來鍵時，只有當 foreign_key_checks 選項被禁用的時候才支援 INPLACE 演算法

表

| 操作 | INSTANT | INPLACE | 重建表 | 併發 DML | 只修改後設資料 | | ----------------------- | ------- | ------- | ------ | -------- | ------------ | | 修改 ROW_FORMAT | ❌ | ✅ | ✅ | ✅ | ❌ | | 修改 KEY_BLOCK_SIZE | ❌ | ✅ | ✅ | ✅ | ❌ | | 設定持久表統計資訊 | ❌ | ✅ | ❌ | ✅ | ✅ | | 指定字符集 | ❌ | ✅ | ✅ ⑮ | ❌ | ❌ | | 轉換字符集 | ❌ | ❌ | ✅ ⑯ | ❌ | ❌ | | 優化表 | ❌ | ✅ ⑰ | ✅ | ✅ | ❌ | | 使用 FORCE 選項重建表 | ❌ | ✅ ⑱ | ✅ | ✅ | ❌ | | 執行空的重建 | ❌ | ✅ ⑲ | ✅ | ✅ | ❌ | | 重新命名錶 | ✅ | ✅ | ❌ | ✅ | ✅ |

說明：

• ⑮⑯ 當字符集不同時，需要重建表
• ⑰⑱⑲ 如果表中包含 FULLTEXT 的欄位，則不支援 INPLACE

表空間

| 操作 | INSTANT | INPLACE | 重建表 | 併發 DML | 只修改後設資料 | | ---------------------------------------- | ------- | ------- | ------ | -------- | ------------ | | 重新命名常規表空間 | ❌ | ✅ | ❌ | ✅ | ✅ | | 啟用或者禁用常規表空間加密 | ❌ | ✅ | ❌ | ✅ | ❌ | | 啟用或者禁用 file-per-table 表空間加密 | ❌ | ❌ | ✅ | ❌ | ❌ |

限制

• 在臨時表 TEMPORARY TABLE 上建立索引時會發生表拷貝
• 如果表上有 ON...CASCADE 或者 ON...SET NULL 約束，則 ALERT TABLE 不支援字句 LOCK=NONE
• 在 Onlne DDL 操作完成之前，它必須等待相關表已經持有後設資料鎖的事務提交或者回滾，在這個過程中，相關表的新事務會被阻塞，無法執行
• 當在大表上執行涉及到表重建的 DDL 時，會存在以下限制
  • 沒有任何機制可以暫停 Online DDL操作或限制 Online DDL 操作的 I/O 或CPU使用率
  • 如果操作失敗，則回滾 Online DDL操作的代價非常高昂
  • 長時間執行的 Online DDL 可能會導致複製延遲。 Online DDL 操作必須在 Master 上執行完成後才能在 Slave 上執行，在這個過程中， 併發處理的 DML 在 Slave 上面必須等待 DDL 操作完成後才會執行。

寫在最後

本文將會持續修正和更新，最新內容請參考我的 GITHUB 上的 程式猿成長計劃 專案，歡迎 Star，更多精彩內容請 follow me。

參考

• MariaDB Knowledge Base: InnoDB Online DDL
• MariaDB Knowledge Base: WAIT and NOWAIT
• MySQL 5.6: InnoDB and Online DDL
• MySQL 5.7: InnoDB and Online DDL
• MySQL 8.0: InnoDB and Online DDL
• 極客時間：MySQL實戰 45 講