MySQL DDL執行方式-Online DDL介紹

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 在從庫以單線程執行,這將導致主從延遲問題。

官方參考資料://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的執行方式吧,敬請期待!


作者:劉鄧忠