MySQL Online DDL 原理和踩坑

程序員的成長之路

互聯(lián)網(wǎng)/程序員/技術(shù)/資料共享 

閱讀本文大概需要 4 分鐘。

來自：網(wǎng)絡(luò)，侵刪

導(dǎo)讀：MySQL 的 DDL(Data Definition Language) 包括增減字段、增減索引等操作。在 MySQL 5.6 之前，MySQL 的 DDL 操作會按照原來的表復(fù)制一份，并做相應(yīng)的修改。

例如，對表 A 進(jìn)行 DDL 的具體過程如下：

• 按照表 A 的定義新建一個表 B

• 對表 A 加寫鎖

• 在表 B 上執(zhí)行 DDL 指定的操作

• 將 A 中的數(shù)據(jù)拷貝到 B

• 釋放 A 的寫鎖

• 刪除表 A

• 將表 B 重命名為 A

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

因此，MySQL 5.6 增加了 Online DDL，允許在不中斷數(shù)據(jù)庫服務(wù)的情況下進(jìn)行 DDL 操作。

用法

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

• ALGORITHM=INPLACE 表示執(zhí)行DDL的過程中不發(fā)生表拷貝，過程中允許并發(fā)執(zhí)行DML（INPLACE不需要像COPY一樣占用大量的磁盤I/O和CPU，減少了數(shù)據(jù)庫負(fù)載。同時減少了buffer pool的使用，避免 buffer pool 中原有的查詢緩存被大量刪除而導(dǎo)致的性能問題）。
  如果設(shè)置 ALGORITHM=COPY，DDL 就會按 MySQL 5.6 之前的方式，采用表拷貝的方式進(jìn)行，過程中會阻塞所有的DML。另外也可以設(shè)置 ALGORITHEM=DAFAULT，讓 MySQL 以盡量保證 DML 并發(fā)操作的原則選擇執(zhí)行方式。
  

• LOCK=NONE 表示對 DML 操作不加鎖，DDL 過程中允許所有的 DML 操作。此外還有 EXCLUSIVE（持有排它鎖，阻塞所有的請求，適用于需要盡快完成DDL或者服務(wù)庫空閑的場景）、SHARED（允許SELECT，但是阻塞INSERT UPDATE DELETE，適用于數(shù)據(jù)倉庫等可以允許數(shù)據(jù)寫入延遲的場景）和 DEFAULT（根據(jù)DDL的類型，在保證最大并發(fā)的原則下來選擇LOCK的取值）
  

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

例如 Table 14.10 中顯示修改列的數(shù)據(jù)類型不支持 INPLACE

這時嘗試將原類型為 FLOAT 的 column_name 改為 INT

會報錯

執(zhí)行過程

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

• 執(zhí)行 DDL：根據(jù)第一步的情況決定是否將 shared metadata lock 升級為 exclusive metadata lock（僅在語句準(zhǔn)備階段），然后生成語句并執(zhí)行。執(zhí)行期間的 shared metadata lock 保證了不會同時執(zhí)行其他的 DDL，但 DML 能可以正常執(zhí)行

• 提交：將 shared metadata lock 升級為 exclusive metadata lock，然后刪除舊的表定義，提交新的表定義
  

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

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

踩坑

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

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

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

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

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

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

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

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

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

• Session 1 中的事務(wù)沒有及時提交，因此阻塞了 Session 3 的 DDL

• Session 3 Online DDL 阻塞了后續(xù)的 DML 和 DDL

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

可以看到 Session 1 正在執(zhí)行的事務(wù)對應(yīng)的 trx_mysql_thread_id 為 9，然后執(zhí)行 KILL 9即可中斷 Session 1 中的事務(wù)。

對于問題 2：在查詢很多的情況下，會導(dǎo)致阻塞的 session 迅速增多，對于這種情況，可以先中斷 DDL 操作，防止對服務(wù)造成過大的影響。也可以嘗試在從庫上修改表結(jié)構(gòu)后進(jìn)行主從切換或者使用 pt-osc 等第三方工具。

<END>

推薦閱讀：

美女學(xué)霸考 692 分想當(dāng)“程序媛”，網(wǎng)友：快勸勸孩子！

批處理框架 Spring Batch 這么強(qiáng)，你會用嗎？

最近面試BAT，整理一份面試資料《Java面試BATJ通關(guān)手冊》，覆蓋了Java核心技術(shù)、JVM、Java并發(fā)、SSM、微服務(wù)、數(shù)據(jù)庫、數(shù)據(jù)結(jié)構(gòu)等等。

獲取方式：點個「在看」，點擊上方小卡片，進(jìn)入公眾號后回復(fù)「面試題」領(lǐng)取，更多內(nèi)容陸續(xù)奉上。

朕已閱