MySQL & MariaDB Online DDL的詳解示例

這篇文章主要介紹MySQL & MariaDB Online DDL的詳解示例，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！

MySQL教程欄目介紹指導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 時允許對表加鎖的級別

ALTER TABLE tab ADD COLUMN c varchar(50), ALGORITHM=INPLACE, LOCK=NONE;復制代碼

ALGORITHM 支持的算法

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），則語句執行失敗并報錯。

為了避免執行 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 來控制等待所得超時時間，超時立即失敗。

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 的上線需要慎重考慮，周密計劃

比如

• 修改某一列的默認值（快速，不會影響到表數據）

  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)復制代碼

由于在執行  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 最佳。

二級索引

說明：

• ① 第一次添加全文索引字段時需要重建表，之后就不需要了

主鍵

說明：

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

普通列

說明：

• ③ 并發 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

生成列

外鍵

說明：

• ? 添加外鍵時，只有當 foreign_key_checks 選項被禁用的時候才支持 INPLACE 算法

表

說明：

• ?? 當字符集不同時，需要重建表
• ??? 如果表中包含 FULLTEXT 的字段，則不支持 INPLACE

表空間

限制

• 在臨時表 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 操作完成后才會執行。

以上是MySQL & MariaDB Online DDL的詳解示例的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注億速云行業資訊頻道！