首頁 > 文章 > 資料庫 > 參考指南：MySQL & MariaDB Online DDL

參考指南：MySQL & MariaDB Online DDL

coldplay.xixi轉載: 2020-10-27 17:41:172259瀏覽

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 支援的演算法

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：提交表定义

在表定义的提交阶段，元数据锁会升级为排它锁来更新表的定义。独占排它锁的持续时间非常短。

元数据锁（參考指南：MySQL & MariaDB Online DDL，Metadata Lock）主要用于 DDL 和 DML 操作之间的并发访问控制，保护表结构（表定义）的一致，保证读写的正确性。參考指南：MySQL & MariaDB Online DDL 不需要显式的使用，在访问表时会自动加上。

由于上面三个阶段中对元数据锁的独占， 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 语句的性能再选择如何操作。

复制表结构，创建一个新的表
在新创建的表中插入少量数据
在新表上面执行 DDL 操作
检查执行操作后返回的 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 最佳。

二級索引

##重建表並發DML只修改元資料#建立或新增二級索引❌✅ ❌✅❌刪除索引❌✅❌✅✅ 重新命名索引（⚠️MySQL 5.7 ，MariaDB 10.5.2 ）❌✅❌✅✅❌✅ ①❌ ①❌❌SPATIAL❌

操作



#FULLTEXT`索引`

	新增	索引（⚠️MySQL 5.7 ，MariaDB 10.2.2 ）

✅

❌

❌#✅#✅❌❌❌修改索引類型✅✅❌✅✅說明：#① 第一次新增全文索引欄位時需要重建表，之後就不需要了#INSTANTINPLACE#重建表並發DML只修改元資料# 新增主鍵❌

❌	❌

		主鍵

✅ ②

✅
❌
❌❌✅❌

刪除一個主鍵同時新增一個新的❌✅✅✅❌說明：NOT NULL 的UNIQUEINPLACECOPYundo logredo log普通列並發DML✅ ③#✅✅ ⑤✅✅





				#重建叢集索引總是需要拷貝表資料（InnoDB 是「索引組織表」），所以最好是在建立表的時候就定義好主鍵	如果建立表是沒有指定主鍵，InnoDB 會選擇第一個
索引作為主鍵，或使用系統產生的KEY	② 對叢集索引來說，使用	模式比	模式要高效一些：不會產生	和	，二級索引是有序的，所以可以按順序加載，不需要使用變更緩衝區
			#INSTANT	INPLACE	重建表格
只修改元資料		列新增	✅ ③	✅	❌ ③
❌		#欄刪除	❌ ④	✅	✅
❌			##❌	✅	❌
✅		改變列的順序	❌ ⑫	✅	✅
❌		#設定預設值	✅	✅	❌
✅修改資料類型	❌	❌	✅	❌	❌

############################################################################################################################ #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

產生列

❌STORED❌STORED❌ ## 列✅✅❌✅✅

操作	INSTANT	#INPLACE	重建表	並發DML	#只修改元資料
新增`STORED` 欄位	❌	❌
				✅	❌
#修改	列的排序	❌	❌	✅	❌
刪除	欄位	❌	✅	✅	✅
添加

#修改列的排序✅❌✅❌#❌刪除欄位✅✅❌✅✅

				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 作業完成後才會執行。 ###############寫在最後######本文將會持續修正與更新，更多精彩內容請 follow me。 ############更多相關免費學習推薦：#########mysql教學########(影片)######### #

以上是參考指南：MySQL & MariaDB Online DDL的詳細內容。更多資訊請關注PHP中文網其他相關文章！

陳述：

本文轉載於：juejin.im。如有侵權，請聯絡admin@php.cn刪除

上一篇：我所理解的MySQL之四：事務、隔離等級及MVCC下一篇：我所理解的MySQL之四：事務、隔離等級及MVCC

看更多