今天與大家一起分享一下 mysql DDL執(zhí)行方式。

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

• DDL：Data Definition Language，即數(shù)據(jù)定義語言，那相關(guān)的定義操作就是DDL，包括：新建、修改、刪除等；相關(guān)的命令有：CREATE，ALTER，DROP，TRUNCATE截?cái)啾韮?nèi)容（開發(fā)期，還是挺常用的），COMMENT 為數(shù)據(jù)字典添加備注。
• DML：Data Manipulation Language，即數(shù)據(jù)操作語言，即處理數(shù)據(jù)庫中數(shù)據(jù)的操作就是DML，包括：選取，插入，更新，刪除等；相關(guān)的命令有：SELECT,INSERT,UPDATE,DELETE，還有 LOCK TABLE，以及不常用的CALL – 調(diào)用一個(gè)PL/SQL或Java子程序，EXPLAIN PLAN – 解析分析數(shù)據(jù)訪問路徑。

我們可以認(rèn)為：

• CREATE，ALTER ，DROP，TRUNCATE，定義相關(guān)的命令就是DDL;
• SELECT，INSERT，UPDATE，DELETE，操作處理數(shù)據(jù)的命令就是DML；

DDL、DML區(qū)別：

• DML操作是可以手動(dòng)控制事務(wù)的開啟、提交和回滾的。
• DDL操作是隱性提交的，不能rollback，一定要謹(jǐn)慎哦！

日常開發(fā)我們對(duì)一條DML語句較為熟悉，很多開發(fā)人員都了解sql的執(zhí)行過程，比較熟悉，但是DDL是如何執(zhí)行的呢，大部分開發(fā)人員可能不太關(guān)心，也認(rèn)為沒必要了解，都交給DBA吧。其實(shí)不然，了解一些能盡量避開一些ddl的坑，那么下面帶大家一起了解一下DDL執(zhí)行的方式，也算拋磚引玉吧。如有錯(cuò)誤，還請(qǐng)各位大佬們指正。

概述

在MySQL使用過程中，根據(jù)業(yè)務(wù)的需求對(duì)表結(jié)構(gòu)進(jìn)行變更是個(gè)普遍的運(yùn)維操作，這些稱為DDL操作。常見的DDL操作有在表上增加新列或給某個(gè)列添加索引。

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

下圖是執(zhí)行方式的性能對(duì)比及說明：

本文將對(duì)DDL的執(zhí)行工具之Online DDL進(jìn)行簡要介紹及分析，pt-osc會(huì)專門再進(jìn)行介紹。

介紹

MySQL Online DDL 功能從 5.6 版本開始正式引入，發(fā)展到現(xiàn)在的 8.0 版本，經(jīng)歷了多次的調(diào)整和完善。其實(shí)早在 MySQL 5.5 版本中就加入了 INPLACE DDL 方式，但是因?yàn)閷?shí)現(xiàn)的問題，依然會(huì)阻塞 INSERT、UPDATE、DELETE 操作，這也是 MySQL 早期版本長期被吐槽的原因之一。

在MySQL 5.6版本以前，最昂貴的數(shù)據(jù)庫操作之一就是執(zhí)行DDL語句，特別是ALTER語句，因?yàn)樵谛薷谋頃r(shí)，MySQL會(huì)阻塞整個(gè)表的讀寫操作。例如，對(duì)表 A 進(jìn)行 DDL 的具體過程如下：

1. 按照表 A 的定義新建一個(gè)表 B
2. 對(duì)表 A 加寫鎖
3. 在表 B 上執(zhí)行 DDL 指定的操作
4. 將 A 中的數(shù)據(jù)拷貝到 B
5. 釋放 A 的寫鎖
6. 刪除表 A
7. 將表 B 重命名為 A

在以上 2-4 的過程中，如果表 A 數(shù)據(jù)量比較大，拷貝到表 B 的過程會(huì)消耗大量時(shí)間，并占用額外的存儲(chǔ)空間。此外，由于 DDL 操作占用了表 A 的寫鎖，所以表 A 上的 DDL 和 DML 都將阻塞無法提供服務(wù)。

如果遇到巨大的表，可能需要幾個(gè)小時(shí)才能執(zhí)行完成，勢(shì)必會(huì)影響應(yīng)用程序，因此需要對(duì)這些操作進(jìn)行良好的規(guī)劃，以避免在高峰時(shí)段執(zhí)行這些更改。對(duì)于那些要提供全天候服務(wù)（24*7）或維護(hù)時(shí)間有限的人來說，在大表上執(zhí)行DDL無疑是一場真正的噩夢(mèng)。

因此，MySQL官方不斷對(duì)DDL語句進(jìn)行增強(qiáng)，自MySQL 5.6 起，開始支持更多的 ALTER TABLE 類型操作來避免數(shù)據(jù)拷貝，同時(shí)支持了在線上 DDL 的過程中不阻塞 DML 操作，真正意義上的實(shí)現(xiàn)了 Online DDL，即在執(zhí)行 DDL 期間允許在不中斷數(shù)據(jù)庫服務(wù)的情況下執(zhí)行DML(insert、update、delete)。然而并不是所有的DDL操作都支持在線操作。到了 MySQL 5.7，在 5.6 的基礎(chǔ)上又增加了一些新的特性，關(guān)注公眾號(hào)：碼猿技術(shù)專欄，回復(fù)關(guān)鍵詞：1111 獲取阿里內(nèi)部Java性能調(diào)優(yōu)手冊(cè)！比如：增加了重命名索引支持，支持了數(shù)值類型長度的增大和減小，支持了 VARCHAR 類型的在線增大等。但是基本的實(shí)現(xiàn)邏輯和限制條件相比 5.6 并沒有大的變化。

用法

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

ALTER 語句中可以指定參數(shù) ALGORITHM 和 LOCK 分別指定 DDL 執(zhí)行的算法模式和 DDL 期間 DML 的鎖控制模式。

• ALGORITHM=INPLACE 表示執(zhí)行DDL的過程中不發(fā)生表拷貝，過程中允許并發(fā)執(zhí)行DML（INPLACE不需要像COPY一樣占用大量的磁盤I/O和CPU，減少了數(shù)據(jù)庫負(fù)載。同時(shí)減少了buffer pool的使用，避免 buffer pool 中原有的查詢緩存被大量刪除而導(dǎo)致的性能問題）。
• 如果設(shè)置 ALGORITHM=COPY，DDL 就會(huì)按 MySQL 5.6 之前的方式，采用表拷貝的方式進(jìn)行，過程中會(huì)阻塞所有的DML。另外也可以設(shè)置 ALGORITHEM=DAFAULT，讓 MySQL 以盡量保證 DML 并發(fā)操作的原則選擇執(zhí)行方式。
• LOCK=NONE 表示對(duì) DML 操作不加鎖，DDL 過程中允許所有的 DML 操作。此外還有 EXCLUSIVE（持有排它鎖，阻塞所有的請(qǐng)求，適用于需要盡快完成DDL或者服務(wù)庫空閑的場景）、SHARED（允許SELECT，但是阻塞INSERT UPDATE DELETE，適用于數(shù)據(jù)倉庫等可以允許數(shù)據(jù)寫入延遲的場景）和 DEFAULT（根據(jù)DDL的類型，在保證最大并發(fā)的原則下來選擇LOCK的取值）。

兩種算法

第一種 Copy：

1. 按照原表定義創(chuàng)建一個(gè)新的臨時(shí)表；
2. 對(duì)原表加寫鎖（禁止DML，允許select）；
3. 在步驟1 建立的臨時(shí)表執(zhí)行 DDL；
4. 將原表中的數(shù)據(jù) copy 到臨時(shí)表；
5. 釋放原表的寫鎖；
6. 將原表刪除，并將臨時(shí)表重命名為原表。
7. 從上可見，采用 copy 方式期間需要鎖表，禁止DML，因此是非Online的。比如：刪除主鍵、修改列類型、修改字符集，這些操作會(huì)導(dǎo)致行記錄格式發(fā)生變化（無法通過全量 + 增量實(shí)現(xiàn) Online）。

第二種 Inplace：

在原表上進(jìn)行更改，不需要生成臨時(shí)表，不需要進(jìn)行數(shù)據(jù)copy的過程。根據(jù)是否行記錄格式，又可分為兩類：

• rebuild：需要重建表（重新組織聚簇索引）。比如 optimize table、添加索引、添加/刪除列、修改列 NULL/NOT NULL 屬性等；
• no-rebuild：不需要重建表，只需要修改表的元數(shù)據(jù)，比如刪除索引、修改列名、修改列默認(rèn)值、修改列自增值等。
• 對(duì)于 rebuild 方式實(shí)現(xiàn) Online 是通過緩存 DDL 期間的 DML，待 DDL 完成之后，將 DML 應(yīng)用到表上來實(shí)現(xiàn)的。例如，執(zhí)行一個(gè) alter table A engine=InnoDB; 重建表的 DDL 其大致流程如下：
• 建立一個(gè)臨時(shí)文件，掃描表 A 主鍵的所有數(shù)據(jù)頁；
• 用數(shù)據(jù)頁中表 A 的記錄生成 B+ 樹，存儲(chǔ)到臨時(shí)文件中；
• 生成臨時(shí)文件的過程中，將所有對(duì) A 的操作記錄在一個(gè)日志文件（row log）中；
• 臨時(shí)文件生成后，將日志文件中的操作應(yīng)用到臨時(shí)文件，得到一個(gè)邏輯數(shù)據(jù)上與表 A 相同的數(shù)據(jù)文件；
• 用臨時(shí)文件替換表 A 的數(shù)據(jù)文件。

說明：

1. 在 copy 數(shù)據(jù)到新表期間，在原表上是加的 MDL 讀鎖（允許 DML，禁止 DDL）；
2. 在應(yīng)用增量期間對(duì)原表加 MDL 寫鎖（禁止 DML 和 DDL）；
3. 根據(jù)表 A 重建出來的數(shù)據(jù)是放在 tmp_file 里的，這個(gè)臨時(shí)文件是 InnoDB 在內(nèi)部創(chuàng)建出來的，整個(gè) DDL 過程都在 InnoDB 內(nèi)部完成。對(duì)于 server 層來說，沒有把數(shù)據(jù)挪動(dòng)到臨時(shí)表，是一個(gè)原地操作，這就是”inplace”名稱的來源。

使用Inplace方式執(zhí)行的DDL，發(fā)生錯(cuò)誤或被kill時(shí)，需要一定時(shí)間的回滾期，執(zhí)行時(shí)間越長，回滾時(shí)間越長。

使用Copy方式執(zhí)行的DDL，需要記錄過程中的undo和redo日志，同時(shí)會(huì)消耗buffer pool的資源，效率較低，優(yōu)點(diǎn)是可以快速停止。

不過并不是所有的 DDL 操作都能用 INPLACE 的方式執(zhí)行，具體的支持情況可以在（在線 DDL 操作) 中查看。

以下是常見DDL操作：

官網(wǎng)支持列表：

執(zhí)行過程

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

1）Prepare階段：初始化階段會(huì)根據(jù)存儲(chǔ)引擎、用戶指定的操作、用戶指定的 ALGORITHM 和 LOCK 計(jì)算 DDL 過程中允許的并發(fā)量，這個(gè)過程中會(huì)獲取一個(gè) shared metadata lock，用來保護(hù)表的結(jié)構(gòu)定義。

• 創(chuàng)建新的臨時(shí)frm文件(與InnoDB無關(guān))。
• 持有EXCLUSIVE-MDL鎖，禁止讀寫。
• 根據(jù)alter類型，確定執(zhí)行方式(copy,online-rebuild,online-norebuild)。假如是Add Index，則選擇online-norebuild即INPLACE方式。
• 更新數(shù)據(jù)字典的內(nèi)存對(duì)象。
• 分配row_log對(duì)象來記錄增量(僅rebuild類型需要)。
• 生成新的臨時(shí)ibd文件(僅rebuild類型需要) 。
• 數(shù)據(jù)字典上提交事務(wù)、釋放鎖。

注：Row log是一種獨(dú)占結(jié)構(gòu)，它不是redo log。它以Block的方式管理DML記錄的存放，一個(gè)Block的大小為由參數(shù)innodb_sort_buffer_size控制，默認(rèn)大小為1M，初始化階段會(huì)申請(qǐng)兩個(gè)Block。

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

• 降級(jí)EXCLUSIVE-MDL鎖，允許讀寫（copy不可寫）。
• 掃描old_table的聚集索引每一條記錄rec。
• 遍歷新表的聚集索引和二級(jí)索引，逐一處理。
• 根據(jù)rec構(gòu)造對(duì)應(yīng)的索引項(xiàng)
• 將構(gòu)造索引項(xiàng)插入sort_buffer塊排序。
• 將sort_buffer塊更新到新的索引上。
• 記錄ddl執(zhí)行過程中產(chǎn)生的增量(僅rebuild類型需要)
• 重放row_log中的操作到新索引上(no-rebuild數(shù)據(jù)是在原表上更新的)。
• 重放row_log間產(chǎn)生dml操作append到row_log最后一個(gè)Block。

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

• 當(dāng)前Block為row_log最后一個(gè)時(shí)，禁止讀寫，升級(jí)到EXCLUSIVE-MDL鎖。
• 重做row_log中最后一部分增量。
• 更新innodb的數(shù)據(jù)字典表。
• 提交事務(wù)(刷事務(wù)的redo日志)。
• 修改統(tǒng)計(jì)信息。
• rename臨時(shí)idb文件，frm文件。
• 變更完成。

Online DDL 過程中占用 exclusive MDL 的步驟執(zhí)行很快，所以幾乎不會(huì)阻塞 DML 語句。

不過，在 DDL 執(zhí)行前或執(zhí)行時(shí)，其他事務(wù)可以獲取 MDL。由于需要用到 exclusive MDL，所以必須要等到其他占有 metadata lock 的事務(wù)提交或回滾后才能執(zhí)行上面兩個(gè)涉及到 MDL 的地方。

踩坑

前面提到 Online DDL 執(zhí)行過程中需要獲取 MDL，MDL (metadata lock) 是 MySQL 5.5 引入的表級(jí)鎖，在訪問一個(gè)表的時(shí)候會(huì)被自動(dòng)加上，以保證讀寫的正確性。當(dāng)對(duì)一個(gè)表做 DML 操作的時(shí)候，加 MDL 讀鎖；當(dāng)做 DDL 操作時(shí)候，加 MDL 寫鎖。

為了在大表執(zhí)行 DDL 的過程中同時(shí)保證 DML 能并發(fā)執(zhí)行，前面使用了 ALGORITHM=INPLACE 的 Online DDL，但這里仍然存在死鎖的風(fēng)險(xiǎn)，問題就出在 Online DDL 過程中需要 exclusive MDL 的地方。

例如，Session 1 在事務(wù)中執(zhí)行 SELECT 操作，此時(shí)會(huì)獲取 shared MDL。由于是在事務(wù)中執(zhí)行，所以這個(gè) shared MDL 只有在事務(wù)結(jié)束后才會(huì)被釋放。

# Session 1> START TRANSACTION;> SELECT * FROM tbl_name;# 正常執(zhí)行

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

# Session 2> SELECT * FROM tbl_name;# 正常執(zhí)行

但如果 Session 3 想執(zhí)行 DDL 操作就會(huì)阻塞，因?yàn)榇藭r(shí) Session 1 已經(jīng)占用了 shared MDL，而 DDL 的執(zhí)行需要先獲取 exclusive MDL，因此無法正常執(zhí)行。

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

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

由于 exclusive MDL 的獲取優(yōu)先于 shared MDL，后續(xù)嘗試獲取 shared MDL 的操作也將會(huì)全部阻塞

+----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+
| 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 ... |+----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+

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

上面這個(gè)問題主要有兩個(gè)原因：

1. Session 1 中的事務(wù)沒有及時(shí)提交，因此阻塞了 Session 3 的 DDL
2. Session 3 Online DDL 阻塞了后續(xù)的 DML 和 DDL

對(duì)于問題 1，有些ORM框架默認(rèn)將用戶語句封裝成事務(wù)執(zhí)行，如果客戶端程序中斷退出，還沒來得及提交或者回滾事務(wù)，就會(huì)出現(xiàn) Session 1 中的情況。那么此時(shí)可以在 infomation_schema.innodb_trx 中找出未完成的事務(wù)對(duì)應(yīng)的線程，并強(qiáng)制退出。

> 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 正在執(zhí)行的事務(wù)對(duì)應(yīng)的 trx_mysql_thread_id 為 9，然后執(zhí)行 KILL 9 即可中斷 Session 1 中的事務(wù)。對(duì)于問題 2，在查詢很多的情況下，會(huì)導(dǎo)致阻塞的 session 迅速增多，對(duì)于這種情況，可以先中斷 DDL 操作，防止對(duì)服務(wù)造成過大的影響。也可以嘗試在從庫上修改表結(jié)構(gòu)后進(jìn)行主從切換或者使用 pt-osc 等第三方工具。

限制

• 僅適用于InnoDB（語法上它可以與其他存儲(chǔ)引擎一起使用，如MyISAM，但MyISAM只允許algorithm = copy，與傳統(tǒng)方法相同）；
• 無論使用何種鎖（NONE，共享或排它），在開始和結(jié)束時(shí)都需要一個(gè)短暫的時(shí)間來鎖表（排它鎖）；
• 在添加/刪除外鍵時(shí)，應(yīng)該禁用 foreign_key_checks 以避免表復(fù)制；
• 仍然有一些 alter 操作需要 copy 或 lock 表（老方法），有關(guān)哪些表更改需要表復(fù)制或表鎖定，請(qǐng)查看官網(wǎng)；
• 如果在表上有 ON … CASCADE 或 ON … SET NULL 約束，則在 alter table 語句中不允許LOCK = NONE；
• Online DDL會(huì)被復(fù)制到從庫（同主庫一樣，如果 LOCK = NONE，從庫也不會(huì)加鎖），但復(fù)制本身將被阻止，因?yàn)?alter 在從庫以單線程執(zhí)行，這將導(dǎo)致主從延遲問題。

官方參考資料：MySQL :: MySQL 5.7 Reference Manual :: 14.13.6 Online DDL Limitations

總結(jié)

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

目前可用的DDL操作工具包括pt-osc，github的gh-ost，以及MySQL提供的在線修改表結(jié)構(gòu)命令Online DDL。pt-osc和gh-ost均采用拷表方式實(shí)現(xiàn)，即創(chuàng)建個(gè)空的新表，通過select+insert將舊表中的記錄逐次讀取并插入到新表中，不同之處在于處理DDL期間業(yè)務(wù)對(duì)表的DML操作。

到了MySQL 8.0 官方也對(duì) DDL 的實(shí)現(xiàn)重新進(jìn)行了設(shè)計(jì)，其中一個(gè)最大的改進(jìn)是 DDL 操作支持了原子特性。另外，Online DDL 的 ALGORITHM 參數(shù)增加了一個(gè)新的選項(xiàng)：INSTANT，只需修改數(shù)據(jù)字典中的元數(shù)據(jù)，無需拷貝數(shù)據(jù)也無需重建表，同樣也無需加排他 MDL 鎖，原表數(shù)據(jù)也不受影響。整個(gè) DDL 過程幾乎是瞬間完成的，也不會(huì)阻塞 DML，不過目前8.0的INSTANT使用范圍較小，后續(xù)再對(duì)8.0的INSTANT做詳細(xì)介紹吧。