MySQL DDL執行方式-Online DDL介紹

来源:https://www.cnblogs.com/Jcloud/archive/2022/09/22/16718123.html
-Advertisement-
Play Games

日常開發我們對一條DML語句較為熟悉,很多開發人員都瞭解sql的執行過程,比較熟悉,但是DDL是如何執行的呢,大部分開發人員可能不太關心,也認為沒必要瞭解,都交給DBA吧。 其實不然,瞭解一些能儘量避開一些ddl的坑,那麼下麵帶大家一起瞭解一下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 在從庫以單線程執行,這將導致主從延遲問題。

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


作者:劉鄧忠


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 版本控制gitlab 什麼是版本控制gitlab GitLab 是一個用於倉庫管理系統的開源項目,使用Git作為代碼管理工具,併在此基礎上搭建起來的Web服務。安裝方法是參考GitLab在GitHub上的Wiki頁面。Gitlab是目前被廣泛使用的基於git的開源代碼管理平臺, 基於Ruby on ...
  • 提到直流無刷電機,那不得不提的就是有刷電機了。有刷電機有一個比較令人討厭的缺點:那就是“吵”。 因為電刷和換向環需要時刻不停地摩擦,才能給電樞供電。 所以,如果你想要一個“靜音風扇”的話,肯定不能選使用了有刷電機的產品。 並且電刷使用時間久了,比較容易損壞。電流較大的時候,你甚至可以看到電刷在換向的 ...
  • 腳本安裝lamp [[email protected] ~]# mkdir lamp [[email protected] ~]# cd lamp/ [[email protected] lamp]# mkdir files [[email protected] lamp]# ls files [[email protected] ...
  • 2022-09-18-21:28:59 老師作業說明: TOP500中國超算占比,LINUX系統占比 說明:當時使用的是bing搜索,中國超算占比其實澎湃新聞什麼的都有介紹,但是我對它的數據來源持懷疑態度,索性自己去官網上看資料,後面解決Linux系統占比問題時,也是直接想著在官網解決,其實後面和同 ...
  • 近日, 在一個小型項目中, 遇到了一個觸及我知識盲區的bug. 項目用的是MySQL 5.7.25, 其中有一張表 config_data, 包含四個欄位, id, name, value, expireAt. 其中id為主鍵, name建有唯一索引, 表的用途大概就是存放一些有時效性的配置. 以上 ...
  • Hadoop的第一個產品是HDFS,可以說分散式文件存儲是分散式計算的基礎,也可見分散式文件存儲的重要性。如果我們將大數據計算比作烹飪,那麼數據就是食材,而Hadoop分散式文件系統HDFS就是燒菜的那口大鍋。這些年來,各種計算框架、各種演算法、各種應用場景不斷推陳出新,讓人眼花繚亂,但是大數據存儲的 ...
  • 前記 (可跳過這段來自本up的羅里吧嗦。。。) 在做上一個項目的時候,需要用到本地資料庫,以前做公司項目用的是輕量級資料庫Realm,做自己小項目用的是greenDAO,大學學的是SQL server,但是在flutter中,相關插件用的資料庫是SQLite(sqflite插件),但本人還沒接觸過S ...
  • 7月28日,在袋鼠雲2022產品發佈會上,袋鼠雲技術負責人思樞正式宣佈旗下產品「大數據基礎平臺EasyMR」發佈。 EasyMR是袋鼠雲自研的大數據基礎平臺,提供Hadoop、Hive、Spark、Trino、HBase、Kafka等組件,完全相容Apache開源生態;支持企業級安全管控,一鍵開啟L ...
一周排行
    -Advertisement-
    Play Games
  • Github / Gitee QQ群(1群) : 813100564 / QQ群(2群) : 579033769 視頻教學 介紹 MiniWord .NET Word模板引擎,藉由Word模板和數據簡單、快速生成文件。 Getting Started 安裝 nuget link : https:// ...
  • Array.Sort Array類中相當實用的我認為是Sort方法,相比起冗長的冒泡排序,它的出現讓排序更加的簡化 結果如下: 還可以聲明一個靜態方法用來專門調用指定數組排序,從名為 array 的一維數組中 a 索引處開始,到 b 元素 從小到大排序。 註意: a + b 不能大於 array 的 ...
  • 前言 在上一篇文章CLR類型系統概述里提到,當運行時掛起時, 垃圾回收會執行堆棧遍歷器(stack walker)去拿到堆棧上值類型的大小和堆棧根。這裡我們來翻譯BotR里一篇專門介紹Stackwalking的文章,希望能加深理解。 順便說一句,StackWalker在中文里似乎還沒有統一的翻譯,J ...
  • 使用過 nginx 的小伙伴應該都知道,這個中間件是可以設置跨域的,作為今天的主角,同樣的 反向代理中間件的 YARP 毫無意外也支持了跨域請求設置。 有些小伙伴可能會問了,怎樣才算是跨域呢? 在 HTML 中,一些標簽,例如 img、a 等,還有我們非常熟悉的 Ajax,都是可以指向非本站的資源的 ...
  • 什麼是Git Git 是一個開源的分散式版本控制系統,用於敏捷高效地處理任何或小或大的項目。 Git 是 Linus Torvalds 為了幫助管理 Linux 內核開發而開發的一個開放源碼的版本控制軟體。 Git 與常用的版本控制工具 CVS, Subversion 等不同,它採用了分散式版本庫的 ...
  • 首先CR3是什麼,CR3是一個寄存器,該寄存器內保存有頁目錄表物理地址(PDBR地址),其實CR3內部存放的就是頁目錄表的記憶體基地址,運用CR3切換可實現對特定進程記憶體地址的強制讀寫操作,此類讀寫屬於有痕讀寫,多數驅動保護都會將這個地址改為無效,此時CR3讀寫就失效了,當然如果能找到CR3的正確地址... ...
  • 說明 onlyoffice為一款開源的office線上編輯組件,提供word/excel/ppt編輯保存操作 以下操作均基於centos8系統,officeonly鏡像版本7.1.2.23 鏡像下載地址:https://yunpan.360.cn/surl_y87CKKcPdY4 (提取碼:1f92 ...
  • 二叉樹查找指定的節點 前序查找的思路 1.先判斷當前節點的no是否等於要查找的 2.如果是相等,則返回當前節點 3.如果不等,則判斷當前節點的左子節點是否為空,如果不為空,則遞歸前序查找 4.如果左遞歸前序查找,找到節點,則返回,否繼續判斷,當前的節點的右子節點是否為空,如果不為空,則繼續向右遞歸前 ...
  • ##Invalid bound statement (not found)出現原因和解決方法 ###前言: 想必各位小伙伴在碼路上經常會碰到奇奇怪怪的事情,比如出現Invalid bound statement (not found),那今天我就來分析以下出現此問題的原因。 其實出現這個問題實質就是 ...
  • ###一、背景知識 爬蟲的本質就是一個socket客戶端與服務端的通信過程,如果我們有多個url待爬取,只用一個線程且採用串列的方式執行,那隻能等待爬取一個結束後才能繼續下一個,效率會非常低。 需要強調的是:對於單線程下串列N個任務,並不完全等同於低效,如果這N個任務都是純計算的任務,那麼該線程對c ...