MySQL & MariaDB Online DDL的詳解示例

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

MySQL教程欄目介紹指導(dǎo)MySQL & MariaDB Online DDL。

概述

在早期的 MySQL 版本中，DDL 操作（如創(chuàng)建索引等）通常都需要對數(shù)據(jù)表加鎖，操作過程中 DML 操作都會(huì)被阻塞，影響正常業(yè)務(wù)。MySQL 5.6 和 MariaDB 10.0 開始支持  Online  DDL，可以在執(zhí)行 DDL 操作的同時(shí)，不影響 DML 的正常執(zhí)行，線上直接執(zhí)行 DDL 操作對用戶基本無感知（部分操作對性能有影響）。

不同版本的數(shù)據(jù)庫對各種 DDL 語句的支持存在一定的差異，本文將會(huì)針對 MySQL 和 MariaDB 對 Online DDL 的支持情況做一個(gè)匯總，在需要執(zhí)行 DDL 操作時(shí)，可以參考本文的 Online DDL 支持情況 部分。

本文將會(huì)持續(xù)修正和更新，最新內(nèi)容請參考我的 GITHUB 上的 程序猿成長計(jì)劃 項(xiàng)目，歡迎 Star，更多精彩內(nèi)容請 follow me。

在 ALTER TABLE 語句中，支持通過 ALGORITHM 和 LOCK 語句來實(shí)現(xiàn) Online  DDL：

• ALGORITHM -  控制 DDL 操作如何執(zhí)行，使用哪個(gè)算法
• LOCK - 控制在執(zhí)行 DDL 時(shí)允許對表加鎖的級別

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

ALGORITHM 支持的算法

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

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

算法使用規(guī)則：

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

MySQL 服務(wù)主要為 Server 層 和 存儲引擎層 兩部分組成，Server 層包含了 MySQL 大部分核心功能，所有的內(nèi)置函數(shù)，跨存儲引擎的功能如存儲過程、觸發(fā)器、視圖等。存儲引擎層負(fù)責(zé)數(shù)據(jù)的存儲和讀取，采用了插件式的架構(gòu)模式。

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

INPLACE 算法 作用于存儲引擎層，是 InnoDB 存儲引擎特有的 DDL 算法，執(zhí)行過程如下圖所示

LOCK 策略

默認(rèn)情況下，MySQL/MariaDB 在執(zhí)行 DDL 期間會(huì)使用盡可能少的鎖，如果必要，可以通過 LOCK 子句控制在執(zhí)行 DDL 時(shí)允許對表加鎖的級別。如果指定的操作所要求的限制級別不滿足（EXCLUSIVE > SHARED > NONE），則語句執(zhí)行失敗并報(bào)錯(cuò)。

為了避免執(zhí)行 DDL 時(shí)，由于鎖表導(dǎo)致生產(chǎn)服務(wù)不可用，在執(zhí)行表結(jié)構(gòu)變更語句時(shí)，可以添加 LOCK=NONE 子句，如果語句需要獲取共享鎖或者排它鎖，則會(huì)直接報(bào)錯(cuò)，這樣就可以避免意外鎖表，造成線上服務(wù)不可用了。

Online DDL 執(zhí)行過程

Online  DDL 操作主要分為三個(gè)階段：

• 階段 1：初始化

  在初始化階段，服務(wù)器會(huì)根據(jù)存儲引擎的能力，操作的語句和用戶指定的 ALGORITHM 和 LOCK 選項(xiàng)來決定允許多大程度的并發(fā)。在這個(gè)階段會(huì)創(chuàng)建一個(gè) 可升級的元數(shù)據(jù)共享鎖（SU）來保護(hù)表定義。

• 階段 2：執(zhí)行

  這個(gè)階段會(huì) 準(zhǔn)備 并 執(zhí)行 DDL 語句，根據(jù) 階段 1 評估的結(jié)果來決定是否將元數(shù)據(jù)鎖升級為 排它鎖 （X），如果需要升級為排它鎖，則只在 DDL 的 準(zhǔn)備階段 短暫的添加排它鎖。

• 階段 3：提交表定義

  在表定義的提交階段，元數(shù)據(jù)鎖會(huì)升級為排它鎖來更新表的定義。獨(dú)占排它鎖的持續(xù)時(shí)間非常短。

元數(shù)據(jù)鎖（MDL，Metadata Lock）主要用于 DDL 和 DML 操作之間的并發(fā)訪問控制，保護(hù)表結(jié)構(gòu)（表定義）的一致，保證讀寫的正確性。MDL 不需要顯式的使用，在訪問表時(shí)會(huì)自動(dòng)加上。

由于上面三個(gè)階段中對元數(shù)據(jù)鎖的獨(dú)占，  Online  DDL 過程必須等待已經(jīng)持有元數(shù)據(jù)鎖的并發(fā)事務(wù)提交或者回滾才能繼續(xù)執(zhí)行。

注意：當(dāng)  Online  DDL 操作正在等待元數(shù)據(jù)鎖時(shí)，該元數(shù)據(jù)鎖會(huì)處于掛起狀態(tài)，后續(xù)的所有事務(wù)都會(huì)被阻塞。在 MariaDB 10.3 之后，可以通過添加 NO WAIT 或者 WAIT n 來控制等待所得超時(shí)時(shí)間，超時(shí)立即失敗。

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]復(fù)制代碼

評估 Online DDL 操作的性能

Online DDL 操作的性能取決于是否發(fā)生了表的重建。在對大表執(zhí)行 DDL 操作之前，為了避免影響正常業(yè)務(wù)操作，最好是先評估一下 DDL 語句的性能再選擇如何操作。

1. 復(fù)制表結(jié)構(gòu)，創(chuàng)建一個(gè)新的表
2. 在新創(chuàng)建的表中插入少量數(shù)據(jù)
3. 在新表上面執(zhí)行 DDL 操作
4. 檢查執(zhí)行操作后返回的 rows affected 是否是 0。如果該值非 0，則意味著需要拷貝表數(shù)據(jù)，此時(shí)對 DDL 的上線需要慎重考慮，周密計(jì)劃

比如

• 修改某一列的默認(rèn)值（快速，不會(huì)影響到表數(shù)據(jù)）

  Query OK, 0 rows affected (0.07 sec)復(fù)制代碼

• 添加索引（需要花費(fèi)一些時(shí)間，但是 0 rows affected 說明沒有發(fā)生表拷貝）

  Query OK, 0 rows affected (21.42 sec)復(fù)制代碼

• 修改列的數(shù)據(jù)類型（需要花費(fèi)很長時(shí)間，并且重建表）

  Query OK, 1671168 rows affected (1 min 35.54 sec)復(fù)制代碼

由于在執(zhí)行  Online  DDL 過程中需要記錄并發(fā)執(zhí)行的 DML 操作發(fā)生的變更，然后在執(zhí)行完 DDL 操作之后再應(yīng)用這些變更，因此使用  Online  DDL 操作花費(fèi)的時(shí)間比不使用 Online 模式執(zhí)行要更長一些。

Online  DDL 支持情況

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

重點(diǎn)關(guān)注是否 重建表 和 支持并發(fā) DML：不需要重建表，支持并發(fā) DML 最佳。

二級索引

說明：

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

主鍵

說明：

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

普通列

說明：

• ③ 并發(fā) DML：當(dāng)插入一個(gè)自增列時(shí)，不支持并發(fā)的 DML 操作，添加自增列時(shí)，大量的數(shù)據(jù)會(huì)被重新組織，代價(jià)高昂

• ③ 重建表：添加列時(shí)，MySQL 5.7及之前版本需要重建表，MySQL 8.0 當(dāng) ALGORITHM=INPLACE 時(shí)，需要重建表，ALGORITHM=INSTANT 時(shí)不需要重建

• ③ INSTANT算法：添加列時(shí)，使用 INSTANT 算法有下面這些限制

  • 添加列操作不能和其它不支持 INSTANT 算法的操作合并為一條 ALTER TABLE 語句
  • 新增的列只能添加到表的最后，不能放到其它列的前面，在 MariaDB 10.4 之后，支持在任意位置添加
  • 不能將列添加到 ROW_FORMAT=COMPRESSED 的表中
  • 不能將列添加到包含 FULLTEXT 的表中
  • 不能將列添加到臨時(shí)表中，臨時(shí)表只支持 ALGORITHM=COPY
  • 不能將列添加到駐留在數(shù)據(jù)字典表空間中的表中
  • 在添加列的時(shí)候不會(huì)計(jì)算行的大小限制，該限制在執(zhí)行 DML 操作插入或者更新表時(shí)才會(huì)被檢查

• ④ 刪除列時(shí)，大量的數(shù)據(jù)需要被重新組織，代價(jià)高昂，在 MariaDB 10.4 之后，刪除列支持 INSTANT 算法

• ⑤ 重命名列時(shí)，確保只改變列名，不改變數(shù)據(jù)類型，這樣才能支持并發(fā)的 DML 操作

• ⑥ 擴(kuò)展 VARCHAR 長度時(shí)，INPLACE 是有條件的，必須保證用于標(biāo)識字符串長度的長度字節(jié)不變（這里說的都是字節(jié)，不是 VARCHAR 的字符長度，字節(jié)占用與采用的字符集有關(guān)，utf8 字符集下，一個(gè)字符占 3 個(gè)字節(jié)， utf8mb4 則 4 個(gè)字節(jié)）

  • 當(dāng) VARCHAR 列長度在 0-255 個(gè)字節(jié)時(shí)，長度標(biāo)識占用一個(gè)字節(jié)
  • 當(dāng) VARCHAR 列長度大于 255 個(gè)字節(jié)時(shí)，長度標(biāo)識占用兩個(gè)字節(jié)

  因此，INPLACE 只支持 0-255 個(gè)字節(jié)之間或者 256 個(gè)字節(jié)到更大的長度之間的變更。VARCHAR 列長度減小是不支持 INPLACE 的。

• ⑦ 自增列值變更是修改的內(nèi)存中的值，不是數(shù)據(jù)文件

• ⑧ ⑨ 設(shè)置列為 [NOT] NULL 時(shí)，大量的數(shù)據(jù)被重新組織，代價(jià)高昂

• ⑩ 修改 ENUM 和 SET 類型的列定義時(shí)，是否需要表拷貝取決于已有元素的個(gè)數(shù)和插入成員的位置

• ? 在 MariaDB 10.4 之后，列排序支持 INSTANT 算法

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

生成列

外鍵

說明：

• ? 添加外鍵時(shí)，只有當(dāng) foreign_key_checks 選項(xiàng)被禁用的時(shí)候才支持 INPLACE 算法

表

說明：

• ?? 當(dāng)字符集不同時(shí)，需要重建表
• ??? 如果表中包含 FULLTEXT 的字段，則不支持 INPLACE

表空間

限制

• 在臨時(shí)表 TEMPORARY TABLE 上創(chuàng)建索引時(shí)會(huì)發(fā)生表拷貝
• 如果表上有 ON...CASCADE 或者 ON...SET NULL 約束，則 ALERT TABLE 不支持字句 LOCK=NONE
• 在 Onlne DDL 操作完成之前，它必須等待相關(guān)表已經(jīng)持有元數(shù)據(jù)鎖的事務(wù)提交或者回滾，在這個(gè)過程中，相關(guān)表的新事務(wù)會(huì)被阻塞，無法執(zhí)行
• 當(dāng)在大表上執(zhí)行涉及到表重建的 DDL 時(shí)，會(huì)存在以下限制
  • 沒有任何機(jī)制可以暫停 Online DDL操作或限制 Online DDL 操作的 I/O 或CPU使用率
  • 如果操作失敗，則回滾 Online DDL操作的代價(jià)非常高昂
  • 長時(shí)間運(yùn)行的 Online  DDL 可能會(huì)導(dǎo)致復(fù)制延遲。 Online  DDL 操作必須在 Master 上執(zhí)行完成后才能在 Slave 上執(zhí)行，在這個(gè)過程中， 并發(fā)處理的 DML 在 Slave 上面必須等待 DDL 操作完成后才會(huì)執(zhí)行。

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