MySQL Online DDL詳解

wongchaofan發表於2024-07-19
MySql

一、Online DDL的發展歷史
MySQL Online DDL 功能從 5.6 版本開始正式引入，發展到現在的 8.0 版本，經歷了多次的調整和完善。本文主要就 Online DDL 的發展過程，以及各版本的區別進行總結。其實早在 MySQL 5.5 版本中就加入了 INPLACE DDL 方式，但是因為實現的問題，依然會阻塞 INSERT、UPDATE、DELETE 操作，這也是 MySQL 早期版本長期被吐槽的原因之一。

在 MySQL 5.6 中，官方開始支援更多的 ALTER TABLE 型別操作來避免資料複製，同時支援了線上上 DDL 的過程中不阻塞 DML 操作，真正意義上的實現了 Online DDL。然而並不是所有的 DDL 操作都支援線上操作，後面會附上 MySQL 官方文件對於 DDL 操作的總結。

到了 MySQL 5.7，在 5.6 的基礎上又增加了一些新的特性，比如：增加了重新命名索引支援，支援了數值型別長度的增大和減小，支援了 VARCHAR 型別的線上增大等。但是基本的實現邏輯和限制條件相比 5.6 並沒有大的變化。

MySQL 8.0 對 DDL 的實現重新進行了設計，其中一個最大的改進是 DDL 操作支援了原子特性。另外，Online DDL 的 ALGORITHM 引數增加了一個新的選項：INSTANT，只需修改資料字典中的後設資料，無需複製資料也無需重建表，同樣也無需加排他 MDL 鎖，原表資料也不受影響。整個 DDL 過程幾乎是瞬間完成的，也不會阻塞 DML。

二、Online DDL的演算法
瞭解 Online DDL 先了解一下之前 DDL 的 2 種演算法 copy 和 inplace。

Copy演算法
按照原表定義建立一個新的臨時表
對原表加寫鎖（禁止 DML，允許 select）
步驟 1）建立的臨時表執行 DDL
將原表中的資料 copy 到臨時表
釋放原表的寫鎖
將原表刪除，並將臨時表重新命名為原表
可見，採用 copy 方式期間需要鎖表，禁止 DML，因此是非 Online 的。比如：刪除主鍵、修改列型別、修改字符集，這些操作會導致行記錄格式發生變化（無法透過全量 + 增量實現 Online）。
Inplace演算法
在原表上進行更改，不需要生成臨時表，不需要進行資料 copy 的過程。根據是否變更行記錄格式，分為兩類：

rebuild：需要重建表（重新組織聚簇索引）。比如 optimize table、新增索引、新增/刪除列、修改列 NULL/NOT NULL 屬性等；
no-rebuild：不需要重建表，只需要修改表的後設資料，比如刪除索引、修改列名、修改列預設值、修改列自增值等。
對於 rebuild 方式實現 Online 是透過快取 DDL 期間的 DML，待 DDL 完成之後，將 DML 應用到表上來實現的。

說明：

在 copy 資料到新表期間，在原表上是加的 MDL 讀鎖（允許 DML，禁止 DDL）
在應用增量期間對原表加 MDL 寫鎖（禁止 DML 和 DDL）
根據表A重建出來的資料是放在 tmp_file 裡的，這個臨時檔案是 InnoDB 在內部建立出來的，整個 DDL 過程都在 InnoDB 內部完成。對於 server 層來說，沒有把資料挪動到臨時表，是一個原地操作，這就是“inplace”名稱的來源。
MySQL中，表級別的鎖有2種
一種是我們通常說的表鎖，由InnoDB引擎實現，如lock tables …
read/write，表鎖影響較大，不常用。另一種表級別的鎖是MDL( metadata lock
)，由Server層實現，MDL我們不顯式使用，是在訪問一個表時由資料庫自動加的，對錶記錄增刪改查時，加MDL讀鎖；對錶結構進行變更時，加MDL寫鎖。MDL鎖，讀讀不互斥，讀寫、寫寫互斥。
哪些常用操作“鎖表”
建立二級索引（二級索引是指除主鍵索引之外的索引）、刪除索引、重新命名索引、改變索引型別——不“鎖表”。
新增欄位、刪除欄位、重新命名欄位、調整欄位順序、設定欄位預設值、刪除欄位預設值、修改auto-increment值、調整欄位允許NULL、調整欄位不允許NULL
—— 不“鎖表”。
擴充套件Varchar欄位大小——不“鎖表”。
更改欄位資料型別，如varchar改成text——“鎖表”

三、Online DDL過程中的鎖
預設情況下，MySQL就是支援online的DDL操作的，在online的DDL語句執行的過程中，MySQL會盡量少使用鎖的限制，我們不需要特殊的操作來啟用它。

MySQL在選擇的時候，儘量少使用鎖，但是不排除它會選擇使用鎖。而如果我擔心它選擇了鎖而導致我們的表不能讀也不能寫，顯然這不是我們想要的結果，我們希望：如果選擇了鎖就不要執行，直接退出執行；如果沒有選擇鎖就執行。想要達到我們希望的這個效果，該怎麼做呢？

可以在執行我們的online DDL語句的時候，使用ALGORITHM和LOCK關鍵字，這兩個關鍵字在我們的DDL語句的最後面，用逗號隔開即可。示例如下：

ALTER TABLE tbl_name ADD COLUMN col_name col_type, ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM的選項
INPLACE：替換：直接在原表上面執行DDL的操作。
COPY：複製：使用一種臨時表的方式，克隆出一個臨時表，在臨時表上執行DDL，然後再把資料匯入到臨時表中，在重新命名等。這期間需要多出一倍的磁碟空間來支撐這樣的 操作。執行期間，表不允許DML的操作。
DEFAULT：預設方式，有MySQL自己選擇，優先使用INPLACE的方式。
LOCK的選項
SHARE：共享鎖，執行DDL的表可以讀，但是不可以寫。
NONE：沒有任何限制，執行DDL的表可讀可寫。
EXCLUSIVE：排它鎖，執行DDL的表不可以讀，也不可以寫。
DEFAULT：預設值，也就是在DDL語句中不指定LOCK子句的時候使用的預設值。如果指定LOCK的值為DEFAULT，那就是交給MySQL子句去覺得鎖還是不鎖表。不建議使用，如果你確定你的DDL語句不會鎖表，你可以不指定lock或者指定它的值為default，否則建議指定它的鎖型別。
執行DDL操作時，ALGORITHM選項可以不指定，這時候MySQL按照INSTANT、INPLACE、COPY的順序自動選擇合適的模式。也可以指定ALGORITHM=DEFAULT，也是同樣的效果。如果指定了ALGORITHM選項，但不支援的話，會直接報錯。

注意:
在執行OnlineDDL之前，要在非業務高峰期去執行，並要確認待執行的表上面沒有未提交的事務、鎖等資訊。可以透過如下的SQL語句檢視是否有事務和鎖等資訊。

select * from information_schema.innodb_locks;
select * from information_schema.innodb_trx;
select * from information_schema.innodb_lock_waits;
select * from information_schema.processlist;

四、理解DDL操作的需求和挑戰
DDL操作涉及對資料庫表結構的修改，例如新增/刪除列、修改列定義、新增/刪除索引等。在以往的版本中，執行這些DDL操作時需要鎖定整個表，對資料庫的可用性產生了負面影響。因此，實現線上DDL成為了提高系統靈活性和效能的重要需求。

五、MySQL 5.7的線上DDL功能特點
MySQL 5.7透過InnoDB儲存引擎實現了線上DDL功能的改進。以下是該功能的主要特點：

支援新增輔助索引：可以在執行中的表上新增輔助索引，而不會對整個表進行鎖定。
支援修改列定義：可以線上修改列的資料型別、長度等定義。
支援修改字符集和排序規則：可以線上修改表的字符集和排序規則設定。
支援重新命名列：可以在不影響正在進行的讀寫操作的情況下，對錶中的列進行重新命名。
六、實現原理和最佳化
線上DDL功能的實現涉及以下關鍵步驟和最佳化：

1 建立臨時表：透過建立臨時表來儲存將要進行的DDL操作所需的新結構。這樣，舊錶仍然可用於讀寫操作。
2 資料複製和同步：將舊錶中的資料逐步複製到臨時表中，並保持舊錶資料與臨時表資料的同步。這一過程確保了資料在DDL操作期間的完整性和一致性。
3 變更捕獲和重放：透過使用日誌和重做日誌等機制，捕獲在執行DDL操作期間發生的資料變更，並將其重放到臨時表中。這確保了DDL操作完成後資料的一致性。
4 最終切換：當DDL操作完成時，資料庫引擎將在適當的時機切換到臨時表，使其成為新的表結構，並且對新表進行後續的讀寫操作。
七、使用限制和注意事項
儘管MySQL 5.7的線上DDL功能提供了一種近似線上的體驗，但仍然有一些限制和注意事項：

並非所有DDL操作都支援線上執行，某些操作仍然需要鎖定整個表。
在進行DDL操作期間，可能會佔用較多的系統資源，因此在高負載時應謹慎使用。
進行線上DDL操作時，需要對操作進行充分的評估和測試，以確保資料的完整性和一致性。

操作 版本 INSTANT INPLACE 重建表 併發 DML 僅修改後設資料
二級索引
建立二級索引 MySQL 8.0 No Yes No Yes No
MySQL 5.7 Yes No Yes No
MySQL 5.6 Yes No Yes No
刪除索引 MySQL 8.0 No Yes No Yes Yes
MySQL 5.7 Yes No Yes Yes
MySQL 5.6 Yes No Yes Yes
重新命名索引 MySQL 8.0 No Yes No Yes Yes
MySQL 5.7 Yes No Yes Yes
MySQL 5.6
增加全文索引 MySQL 8.0 No Yes* No* No No
MySQL 5.7 Yes* No* No No
MySQL 5.6 Yes* No* No No
增加空間索引 MySQL 8.0 No Yes No No No
MySQL 5.7 Yes No No No
MySQL 5.6
修改索引型別 MySQL 8.0 Yes Yes No Yes Yes
MySQL 5.7 Yes No Yes Yes
MySQL 5.6 Yes No Yes Yes
主鍵
增加主鍵 MySQL 8.0 No Yes* Yes* Yes No
MySQL 5.7 Yes* Yes* Yes No
MySQL 5.6 Yes* Yes* Yes No
刪除主鍵 MySQL 8.0 No No Yes No No
MySQL 5.7 No Yes No No
MySQL 5.6 No Yes No No
重建主鍵 MySQL 8.0 No Yes Yes Yes No
MySQL 5.7 Yes Yes Yes No
MySQL 5.6 Yes Yes Yes No

列操作
新增列 MySQL 8.0 Yes* Yes No* Yes* No
MySQL 5.7 Yes Yes Yes* No
MySQL 5.6 Yes Yes Yes* No
刪除列 MySQL 8.0 No Yes Yes Yes No
MySQL 5.7 Yes Yes Yes No
MySQL 5.6 Yes Yes Yes No
重新命名列 MySQL 8.0 No Yes No Yes* Yes
MySQL 5.7 Yes No Yes* Yes
MySQL 5.6 Yes No Yes* Yes
調整列順序 MySQL 8.0 No Yes Yes Yes No
MySQL 5.7 Yes Yes Yes No
MySQL 5.6 Yes Yes Yes No
修改列預設值 MySQL 8.0 Yes Yes No Yes Yes
MySQL 5.7 Yes No Yes Yes
MySQL 5.6 Yes No Yes Yes
修改列資料型別 MySQL 8.0 No No Yes No No
MySQL 5.7 No Yes No No
MySQL 5.6 No Yes No No
擴充套件 VARCHAR 長度 MySQL 8.0 No Yes No Yes Yes
MySQL 5.7 Yes No Yes Yes
MySQL 5.6
刪除列預設值 MySQL 8.0 Yes Yes No Yes Yes
MySQL 5.7 Yes No Yes Yes
MySQL 5.6 Yes No Yes Yes
修改自增值 MySQL 8.0 No Yes No Yes No*
MySQL 5.7 Yes No Yes No*
MySQL 5.6 Yes No Yes No*
修改列為空 MySQL 8.0 No Yes Yes* Yes No
MySQL 5.7 Yes Yes* Yes No
MySQL 5.6 Yes Yes* Yes No
修改列為非空 MySQL 8.0 No Yes* Yes* Yes No
MySQL 5.7 Yes* Yes* Yes No
MySQL 5.6 Yes* Yes* Yes No
修改列 ENUM 值 MySQL 8.0 Yes Yes No Yes Yes
MySQL 5.7 Yes No Yes Yes
MySQL 5.6 Yes No Yes Yes
表操作
修改 ROW_FORMAT MySQL 8.0 No Yes Yes Yes No
MySQL 5.7 Yes Yes Yes No
MySQL 5.6 Yes Yes Yes No
修改 KEY_BLOCK_SIZE MySQL 8.0 No Yes Yes Yes No
MySQL 5.7 Yes Yes Yes No
MySQL 5.6 Yes Yes Yes No
指定字符集 MySQL 8.0 No Yes Yes* No No
MySQL 5.7 Yes Yes* No No
MySQL 5.6 Yes Yes* No No
修改字符集 MySQL 8.0 No No Yes* No No
MySQL 5.7 No Yes* No No
MySQL 5.6 No Yes No No
OPTIMIZE 表 MySQL 8.0 No Yes* Yes Yes No
MySQL 5.7 Yes* Yes Yes No
MySQL 5.6 Yes* Yes Yes No
重新命名錶 MySQL 8.0 Yes Yes No Yes Yes
MySQL 5.7 Yes No Yes Yes
MySQL 5.6 Yes No Yes Yes

結合上面的表格，對 MySQL 當前 DDL 的執行模式總結如下：

INSTANT DDL 是 MySQL 8.0 引入的新功能，當前支援的範圍較小，包括：

  • 修改二級索引型別
  • 新增列
  • 修改列預設值
  • 修改列 ENUM 值
  • 重新命名錶

在執行 DDL 操作時，MySQL 內部對於 ALGORITHM 的選擇策略是：如果使用者顯式指定了 ALGORITHM，那麼使用使用者指定的選項；如果使用者未指定，那麼如果該操作支援 INPLACE 則優先選擇 INPLACE，否則選擇 COPY；當前不支援 INPLACE 的操作主要有：

  • 刪除主鍵
  • 修改列資料型別
  • 修改表字符集

我們常說的 Online DDL，其實是從 DML 操作的角度描述的，如果 DDL 操作不阻塞 DML 操作，那麼這個 DDL 就是 Online 的。當前非 Online 的 DDL 其實已經比較少了，主要有：

  • 新增全文索引
  • 新增空間索引
  • 刪除主鍵
  • 修改列資料型別
  • 指定表字符集
  • 修改表字符集

更多詳細的示例請參考上面的官方文件的地址。

幾個問題

最後討論幾個非常容易混淆的問題：

  1. Online DDL 不會鎖表，可以隨意的執行。
  2. 支援 INPLACE 演算法的 DDL 一定是 Online 的。
  3. 對於支援 INPLACE 演算法的 DDL，DDL 操作是原地修改資料，不需要額外的資料空間。

Q1: Online DDL 會不會鎖表

Online DDL 會不會鎖表？要回答這個問題，首先要明確“鎖表”的含義。很多 MySQL 使用者經常在表無法正常的進行 DML 時就覺得是鎖表了，這種說法其實過於寬泛，實際上能夠影響 DML 操作的鎖至少包括以下幾種（預設為 InnoDB 表）：

  • MDL 鎖
  • 表鎖
  • 行鎖
  • GAP 鎖

其中除了 MDL 鎖是在 Server 層加的之外，其它三種都是在 InnoDB 層加的。具體的加鎖邏輯不在此進行展開，但是需要明確一點：所有的操作（不管是 DDL 還是 DML 還是查詢語句）都需要先拿 Server 層的 MDL 鎖，然後再去拿 InnoDB 層的某個需要的鎖。一個 DDL 的基本過程是這樣的：

  1. 首選，在開始進行 DDL 時，需要拿到對應表的 MDL X 鎖，然後進行一系列的準備工作；
  2. 然後將 MDL X 鎖降級為 MDL S 鎖，進行真正的 DDL 操作；
  3. 最後再次將 MDL S 鎖升級為 MDL X 鎖，完成 DDL 操作，釋放 MDL 鎖；

所以在真正執行 DDL 操作期間，確實是不會“鎖表”的，但是如果在第一階段拿 MDL X 鎖時無法正常獲取，那就可能真的會“鎖表了”。一個簡單的例子如下：

# session 1
select sleep(300) from mytest.t1;

# session 2
optimize table mytest.t1;

# session 3
select * from mytest.t1;

session 1 模擬了一個慢查詢，然後 session 2 開始進行 DDL 操作，無法拿到 MDL X 鎖，處於等到中。此時 session 3 需要執行一個查詢，發現無法執行。實際上，在 session 1 結束前，表 t1 的所有操作都無法進行了，也可以說表 t1 “鎖表”了。MySQL 5.7/8.0 可以在開啟 performance_schema 的情況下直接查詢 metadata_locks 表。阿里雲 RDS 5.6 版本新增了 I_S.MDL_INFO 表，提供 MDL 的查詢。

MySQL [performance_schema]> select * from metadata_locks where OBJECT_NAME = 't1';
+-------------+---------------+-------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE            | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+---------------+-------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | mytest        | t1          | NULL        |       140730442220576 | SHARED_READ          | TRANSACTION   | GRANTED     | sql_parse.cc:5916 |            1083 |             24 |
| TABLE       | mytest        | t1          | NULL        |       140730576178368 | SHARED_NO_READ_WRITE | TRANSACTION   | PENDING     | sql_parse.cc:5916 |            1091 |              3 |
| TABLE       | mytest        | t1          | NULL        |       140730374843168 | SHARED_READ          | TRANSACTION   | PENDING     | sql_parse.cc:5916 |            1092 |              3 |
+-------------+---------------+-------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
3 rows in set (0.00 sec)

明確了上面的概念之後，再回到我們的問題，Online DDL 是不是不鎖表？如果非要回答，那麼只能說，Online DDL 並不是絕對安全，更不是可以隨意的執行。線上操作還是需要在業務低峰期謹慎操作。

Q2: 支援 INPLACE 演算法的 DDL 一定是 Online 的

從概念上來說，INPLACE 和 Online 是兩個不同維度的事情。COPY 和 INPLACE 指的是 DDL 內部的執行邏輯，可以簡單的理解成：COPY 是在 Server 層的操作，INPLACE 是在 InnoDB 層的操作。而使用者更加關心 Online 與否，通常只與一個問題有關：是否允許併發 DML。兩個基本結論：

  1. COPY 演算法執行的 DDL 肯定不是 Online 的；
  2. INPLACE 演算法執行的 DDL 不一定是 Online 的；

Q3: INPLACE DDL 需不需要額外的資料空間

前面我們提到過，MySQL 內部對於 DDL 的 ALGORITHM 有兩種選擇：INPLACE 和 COPY（8.0 新增了 INSTANT，但是使用範圍較小）。COPY 演算法理解起來相對簡單一點：建立一張臨時表，然後將原表的資料複製到臨時表中，最後再用臨時表替換原表。對於上面的步驟，由於需要將原表的資料複製到臨時表中，所以肯定需要消耗額外的資料空間。

那麼對於支援 INPLACE 演算法的 DDL，是不是不需要額外的資料空間？答案是：需要。其實之所以會問這個問題，還是因為對 INPLACE 本身的理解出現了偏差。簡單來說：INPLACE 描述的是表，而不是資料檔案。只要不建立臨時表，那麼都是 INPLACE 的。

實際上，很多 INPLACE DDL 都會重建表（會建立臨時資料檔案），所以都會需要額外的資料空間，例如：

  • 增加主鍵
  • 重建主鍵
  • 新增列（8.0 支援 INSTANT DDL，不需要）
  • 刪除列
  • 調整列順序
  • 刪除列預設值
  • 增加列預設值
  • 修改表的 ROW_FORMAT
  • OPTIMIZE 表

總結

本文主要是對 MySQL Online DDL 進行了一個簡單的整理和總結，更多關於 MySQL 內部實現細節和原始碼的分析，請關注後續文章。

導讀：MySQL 的 DDL(Data Definition Language) 包括增減欄位、增減索引等操作。在 MySQL 5.6 之前，MySQL 的 DDL 操作會按照原來的表複製一份，並做相應的修改。

例如，對錶 A 進行 DDL 的具體過程如下：

  • 按照表 A 的定義新建一個表 B
  • 對錶 A 加寫鎖
  • 在表 B 上執行 DDL 指定的操作
  • 將 A 中的資料複製到 B
  • 釋放 A 的寫鎖
  • 刪除表 A
  • 將表 B 重新命名為 A

在 2-4 的過程中，如果表 A 資料量比較大，複製到表 B 的過程會消耗大量時間，並佔用額外的儲存空間。此外，由於 DDL 操作佔用了表 A 的寫鎖，所以表 A 上的 DDL 和 DML 都將阻塞無法提供服務。

因此，MySQL 5.6 增加了 Online DDL，允許在不中斷資料庫服務的情況下進行 DDL 操作。

ALTER 語句中可以指定引數 ALGORITHM 和 LOCK 分別指定 DDL 執行的方式和 DDL 期間 DML 的兵法控制

  • ALGORITHM=INPLACE 表示執行DDL的過程中不發生表複製，過程中允許併發執行DML（INPLACE不需要像COPY一樣佔用大量的磁碟I/O和CPU，減少了資料庫負載。同時減少了buffer pool的使用，避免 buffer pool 中原有的查詢快取被大量刪除而導致的效能問題）。 如果設定 ALGORITHM=COPY，DDL 就會按 MySQL 5.6 之前的方式，採用表複製的方式進行，過程中會阻塞所有的DML。另外也可以設定 ALGORITHEM=DAFAULT，讓 MySQL 以儘量保證 DML 併發操作的原則選擇執行方式。
  • LOCK=NONE 表示對 DML 操作不加鎖，DDL 過程中允許所有的 DML 操作。此外還有 EXCLUSIVE（持有排它鎖，阻塞所有的請求，適用於需要儘快完成DDL或者服務庫空閒的場景）、SHARED（允許SELECT，但是阻塞INSERT UPDATE DELETE，適用於資料倉儲等可以允許資料寫入延遲的場景）和 DEFAULT（根據DDL的型別，在保證最大併發的原則下來選擇LOCK的取值）

不過並不是所有的 DDL 操作都能用 INPLACE 的方式執行，具體的支援情況可以在 MySQL Reference Manual — Online DDL Operations) 中檢視。

對於問題 1：不少 ORM（例如 pymysql）都預設將使用者語句封裝成事務執行，如果客戶端程式中斷退出，還沒來得及提交或者回滾事務，就會出現 Session 1 中的情況。這時可以在 infomation_schema.innodb_trx中找出未完成的事務對應的執行緒，並強制退出

可以看到 Session 1 正在執行的事務對應的 trx_mysql_thread_id 為 9，然後執行 KILL 9即可中斷 Session 1 中的事務。

對於問題 2：在查詢很多的情況下，會導致阻塞的 session 迅速增多，對於這種情況，可以先中斷 DDL 操作，防止對服務造成過大的影響。也可以嘗試在從庫上修改表結構後進行主從切換或者使用 pt-osc 等第三方工具。



相關文章