技術文章 » MySQL DDL執行方式-Online DDL介紹

MySQL DDL執行方式-Online DDL介紹

2022-09-22 12:01:08

1 引言

大家好，今天與大家一起分享一下 mysql DDL執行方式。

一般來說MySQL分為DDL（定義）和DML（操作）。

  • DDL：Data Definition Language，即資料定義語言，那相關的定義操作就是DDL，包括：新建、修改、刪除等；相關的命令有：CREATE，ALTER，DROP，TRUNCATE截斷表內容（開發期，還是挺常用的），COMMENT 為資料字典新增備註。
  • DML：Data Manipulation Language，即資料操作語言，即處理資料庫中資料的操作就是DML，包括：選取，插入，更新，刪除等；相關的命令有：SELECT,INSERT,UPDATE,DELETE，還有 LOCK TABLE，以及不常用的CALL – 呼叫一個PL/SQL或Java子程式，EXPLAIN PLAN – 解析分析資料存取路徑。

我們可以認為：

  • CREATE，ALTER ，DROP，TRUNCATE，定義相關的命令就是DDL;
  • SELECT，INSERT，UPDATE，DELETE，操作處理資料的命令就是DML；

DDL、DML區別：

  • DML操作是可以手動控制事務的開啟、提交和回滾的。
  • DDL操作是隱性提交的，不能rollback，一定要謹慎哦！

日常開發我們對一條DML語句較為熟悉，很多開發人員都瞭解sql的執行過程，比較熟悉，但是DDL是如何執行的呢，大部分開發人員可能不太關心，也認為沒必要了解，都交給DBA吧。 其實不然，瞭解一些能儘量避開一些ddl的坑，那麼下面帶大家一起了解一下DDL執行的方式，也算拋磚引玉吧。如有錯誤，還請各位大佬們指正。

2 概述

在MySQL使用過程中，根據業務的需求對錶結構進行變更是個普遍的運維操作，這些稱為DDL操作。常見的DDL操作有在表上增加新列或給某個列新增索引。

我們常用的易維平臺提供了兩種方式可執行DDL，包括MySQL原生線上DDL（online DDL）以及一種第三方工具pt-osc。

下圖是執行方式的效能對比及說明：

 

本文將對DDL的執行工具之Online DDL進行簡要介紹及分析，pt-osc會專門再進行介紹。

3 介紹

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

在MySQL 5.6版本以前，最昂貴的資料庫操作之一就是執行DDL語句，特別是ALTER語句，因為在修改表時，MySQL會阻塞整個表的讀寫操作。例如，對錶 A 進行 DDL 的具體過程如下：

  1. 按照表 A 的定義新建一個表 B
  2. 對錶 A 加寫鎖
  3. 在表 B 上執行 DDL 指定的操作
  4. 將 A 中的資料拷貝到 B
  5. 釋放 A 的寫鎖
  6. 刪除表 A
  7. 將表 B 重新命名為 A

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

如果遇到巨大的表，可能需要幾個小時才能執行完成，勢必會影響應用程式，因此需要對這些操作進行良好的規劃，以避免在高峰時段執行這些更改。對於那些要提供全天候服務（24*7）或維護時間有限的人來說，在大表上執行DDL無疑是一場真正的噩夢。

因此，MySQL官方不斷對DDL語句進行增強，自MySQL 5.6 起，開始支援更多的 ALTER TABLE 型別操作來避免資料拷貝，同時支援了線上上 DDL 的過程中不阻塞 DML 操作，真正意義上的實現了 Online DDL，即在執行 DDL 期間允許在不中斷資料庫服務的情況下執行DML(insert、update、delete)。然而並不是所有的DDL操作都支援線上操作。到了 MySQL 5.7，在 5.6 的基礎上又增加了一些新的特性，比如：增加了重新命名索引支援，支援了數值型別長度的增大和減小，支援了 VARCHAR 型別的線上增大等。但是基本的實現邏輯和限制條件相比 5.6 並沒有大的變化。

4 用法

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

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的取值）。

5 兩種演演算法

第一種 Copy：

  1. 按照原表定義建立一個新的臨時表；
  2. 對原表加寫鎖（禁止DML，允許select）；
  3. 在步驟1 建立的臨時表執行 DDL；
  4. 將原表中的資料 copy 到臨時表；
  5. 釋放原表的寫鎖；
  6. 將原表刪除，並將臨時表重新命名為原表。
  7. 從上可見，採用 copy 方式期間需要鎖表，禁止DML，因此是非Online的。比如：刪除主鍵、修改列型別、修改字元集，這些操作會導致行記錄格式發生變化（無法通過全量 + 增量實現 Online）。

第二種 Inplace：

在原表上進行更改，不需要生成臨時表，不需要進行資料copy的過程。根據是否行記錄格式，又可分為兩類：

  • rebuild：需要重建表（重新組織聚簇索引）。比如 optimize table、新增索引、新增/刪除列、修改列 NULL/NOT NULL 屬性等；
  • no-rebuild：不需要重建表，只需要修改表的後設資料，比如刪除索引、修改列名、修改列預設值、修改列自增值等。

對於 rebuild 方式實現 Online 是通過快取 DDL 期間的 DML，待 DDL 完成之後，將 DML 應用到表上來實現的。例如，執行一個 alter table A engine=InnoDB; 重建表的 DDL 其大致流程如下：

  1. 建立一個臨時檔案，掃描表 A 主鍵的所有資料頁；
  2. 用資料頁中表 A 的記錄生成 B+ 樹，儲存到臨時檔案中；
  3. 生成臨時檔案的過程中，將所有對 A 的操作記錄在一個紀錄檔檔案（row log）中；
  4. 臨時檔案生成後，將紀錄檔檔案中的操作應用到臨時檔案，得到一個邏輯資料上與表 A 相同的資料檔案；
  5. 用臨時檔案替換表 A 的資料檔案。

說明：

  1. 在 copy 資料到新表期間，在原表上是加的 MDL 讀鎖（允許 DML，禁止 DDL）；
  2. 在應用增量期間對原表加 MDL 寫鎖（禁止 DML 和 DDL）；
  3. 根據表 A 重建出來的資料是放在 tmp_file 裡的，這個臨時檔案是 InnoDB 在內部建立出來的，整個 DDL 過程都在 InnoDB 內部完成。對於 server 層來說，沒有把資料挪動到臨時表，是一個原地操作，這就是」inplace」名稱的來源。

使用Inplace方式執行的DDL，發生錯誤或被kill時，需要一定時間的回滾期，執行時間越長，回滾時間越長。

使用Copy方式執行的DDL，需要記錄過程中的undo和redo紀錄檔，同時會消耗buffer pool的資源，效率較低，優點是可以快速停止。

不過並不是所有的 DDL 操作都能用 INPLACE 的方式執行，具體的支援情況可以在（線上 DDL 操作) 中檢視。

以下是常見DDL操作：

 

官網支援列表：

 

6 執行過程

Online DDL主要包括3個階段，prepare階段，ddl執行階段，commit階段。下面將主要介紹ddl執行過程中三個階段的流程。

1）Prepare階段：初始化階段會根據儲存引擎、使用者指定的操作、使用者指定的 ALGORITHM 和 LOCK 計算 DDL 過程中允許的並行量，這個過程中會獲取一個 shared metadata lock，用來保護表的結構定義。

  • 建立新的臨時frm檔案(與InnoDB無關)。
  • 持有EXCLUSIVE-MDL鎖，禁止讀寫。
  • 根據alter型別，確定執行方式(copy,online-rebuild,online-norebuild)。假如是Add Index，則選擇online-norebuild即INPLACE方式。
  • 更新資料字典的記憶體物件。
  • 分配row_log物件來記錄增量(僅rebuild型別需要)。
  • 生成新的臨時ibd檔案(僅rebuild型別需要) 。
  • 資料字典上提交事務、釋放鎖。

注：Row log是一種獨佔結構，它不是redo log。它以Block的方式管理DML記錄的存放，一個Block的大小為由引數innodb_sort_buffer_size控制，預設大小為1M，初始化階段會申請兩個Block。

2）DDL執行階段：執行期間的 shared metadata lock 保證了不會同時執行其他的 DDL，但 DML 能可以正常執行。

  • 降級EXCLUSIVE-MDL鎖，允許讀寫（copy不可寫）。
  • 掃描old_table的聚集索引每一條記錄rec。
  • 遍歷新表的聚集索引和二級索引，逐一處理。
  • 根據rec構造對應的索引項
  • 將構造索引項插入sort_buffer塊排序。
  • 將sort_buffer塊更新到新的索引上。
  • 記錄ddl執行過程中產生的增量(僅rebuild型別需要)
  • 重放row_log中的操作到新索引上(no-rebuild資料是在原表上更新的)。
  • 重放row_log間產生dml操作append到row_log最後一個Block。

3）Commit階段：將 shared metadata lock 升級為 exclusive metadata lock，禁止DML，然後刪除舊的表定義，提交新的表定義。

  • 當前Block為row_log最後一個時，禁止讀寫，升級到EXCLUSIVE-MDL鎖。
  • 重做row_log中最後一部分增量。
  • 更新innodb的資料字典表。
  • 提交事務(刷事務的redo紀錄檔)。
  • 修改統計資訊。
  • rename臨時idb檔案，frm檔案。
  • 變更完成。

 

Online DDL 過程中佔用 exclusive MDL 的步驟執行很快，所以幾乎不會阻塞 DML 語句。
不過，在 DDL 執行前或執行時，其他事務可以獲取 MDL。由於需要用到 exclusive MDL，所以必須要等到其他佔有 metadata lock 的事務提交或回滾後才能執行上面兩個涉及到 MDL 的地方。

7 踩坑

前面提到 Online DDL 執行過程中需要獲取 MDL，MDL (metadata lock) 是 MySQL 5.5 引入的表級鎖，在存取一個表的時候會被自動加上，以保證讀寫的正確性。當對一個表做 DML 操作的時候，加 MDL 讀鎖；當做 DDL 操作時候，加 MDL 寫鎖。

為了在大表執行 DDL 的過程中同時保證 DML 能並行執行，前面使用了 ALGORITHM=INPLACE 的 Online DDL，但這裡仍然存在死鎖的風險，問題就出在 Online DDL 過程中需要 exclusive MDL 的地方。

例如，Session 1 在事務中執行 SELECT 操作，此時會獲取 shared MDL。由於是在事務中執行，所以這個 shared MDL 只有在事務結束後才會被釋放。

# Session 1> START TRANSACTION;> SELECT * FROM tbl_name;# 正常執行

這時 Session 2 想要執行 DML 操作也只需要獲取 shared MDL，仍然可以正常執行。

# Session 2> SELECT * FROM tbl_name;# 正常執行

但如果 Session 3 想執行 DDL 操作就會阻塞，因為此時 Session 1 已經佔用了 shared MDL，而 DDL 的執行需要先獲取 exclusive MDL，因此無法正常執行。

# Session 3> ALTER TABLE tbl_name ADD COLUMN n INT;# 阻塞

通過 show processlist 可以看到 ALTER 操作正在等待 MDL。

+----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+
| Id | User            | Host             | db   | Command | Time | State                           | Info            |│----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+
| 11 | root            | 172.17.0.1:53048 | demo | Query   |    3 | Waiting for table metadata lock | alter table ... |+----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+

由於 exclusive MDL 的獲取優先於 shared MDL，後續嘗試獲取 shared MDL 的操作也將會全部阻塞

# Session 4> SELECT * FROM tbl_name;# 阻塞

到這一步，後續無論是 DML 和 DDL 都將阻塞，直到 Session 1 提交或者回滾，Session 1 佔用的 shared MDL 被釋放，後面的操作才能繼續執行。

上面這個問題主要有兩個原因：

  1. Session 1 中的事務沒有及時提交，因此阻塞了 Session 3 的 DDL
  2. Session 3 Online DDL 阻塞了後續的 DML 和 DDL

對於問題 1，有些ORM框架預設將使用者語句封裝成事務執行，如果使用者端程式中斷退出，還沒來得及提交或者回滾事務，就會出現 Session 1 中的情況。那麼此時可以在 infomation_schema.innodb_trx 中找出未完成的事務對應的執行緒，並強制退出。

> SELECT * FROM information_schema.innodb_trx\G*************************** 1. row ***************************trx_id: 421564480355704trx_state: RUNNINGtrx_started: 2022-05-01 014:49:41trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 0trx_mysql_thread_id: 9trx_query: NULLtrx_operation_state: NULLtrx_tables_in_use: 0trx_tables_locked: 0trx_lock_structs: 0trx_lock_memory_bytes: 1136trx_rows_locked: 0trx_rows_modified: 0trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 0trx_autocommit_non_locking: 0trx_schedule_weight: NULL1 row in set (0.0025 sec)

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

8 限制

  • 僅適用於InnoDB（語法上它可以與其他儲存引擎一起使用，如MyISAM，但MyISAM只允許algorithm = copy，與傳統方法相同）；
  • 無論使用何種鎖（NONE，共用或排它），在開始和結束時都需要一個短暫的時間來鎖表（排它鎖）；
  • 在新增/刪除外來鍵時，應該禁用 foreign_key_checks 以避免表複製；
  • 仍然有一些 alter 操作需要 copy 或 lock 表（老方法），有關哪些表更改需要表複製或表鎖定，請檢視官網；
  • 如果在表上有 ON … CASCADE 或 ON … SET NULL 約束，則在 alter table 語句中不允許LOCK = NONE；
  • Online DDL會被複制到從庫（同主庫一樣，如果 LOCK = NONE，從庫也不會加鎖），但複製本身將被阻止，因為 alter 在從庫以單執行緒執行，這將導致主從延遲問題。

官方參考資料：https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-limitations.html

9 總結

本次和大家一起了解SQL的DDL、DML及區別，也介紹了Online DDL的執行方式。

目前可用的DDL操作工具包括pt-osc，github的gh-ost，以及MySQL提供的線上修改表結構命令Online DDL。pt-osc和gh-ost均採用拷表方式實現，即建立個空的新表，通過select+insert將舊錶中的記錄逐次讀取並插入到新表中，不同之處在於處理DDL期間業務對錶的DML操作。

到了MySQL 8.0 官方也對 DDL 的實現重新進行了設計，其中一個最大的改進是 DDL 操作支援了原子特性。另外，Online DDL 的 ALGORITHM 引數增加了一個新的選項：INSTANT，只需修改資料字典中的後設資料，無需拷貝資料也無需重建表，同樣也無需加排他 MDL 鎖，原表資料也不受影響。整個 DDL 過程幾乎是瞬間完成的，也不會阻塞 DML，不過目前8.0的INSTANT使用範圍較小，後續再對8.0的INSTANT做詳細介紹吧。

另外，易維平臺也提供了pt-osc的執行方式，下次再與大家一起分享pt-osc的執行方式吧，敬請期待！

作者：劉鄧忠