一、背景介紹

在 MySQL 數(shù)據(jù)庫(kù)中，DDL（數(shù)據(jù)定義語(yǔ)言）操作包括對(duì)表結(jié)構(gòu)、索引、觸發(fā)器等進(jìn)行修改、創(chuàng)建和刪除等操作。由于 MySQL 自帶的 DDL 操作可能會(huì)阻塞 DML（數(shù)據(jù)操作語(yǔ)言）寫(xiě)語(yǔ)句的執(zhí)行，大表變更容易產(chǎn)生主備延時(shí)，DDL 變更的速度也不能控制，因此在進(jìn)行表結(jié)構(gòu)變更時(shí)需要非常謹(jǐn)慎。

為了解決這個(gè)問(wèn)題，可以使用 GitHub 開(kāi)源的工具 GH-OST。GH-OST 是一個(gè)可靠的在線表結(jié)構(gòu)變更工具，可以實(shí)現(xiàn)零宕機(jī)、低延遲、自動(dòng)化、可撤銷的表結(jié)構(gòu)變更。相比于 MySQL 自帶的 DDL 操作，GH-OST 可以在不影響正常業(yè)務(wù)運(yùn)行的情況下進(jìn)行表結(jié)構(gòu)變更，避免了 DDL 操作可能帶來(lái)的風(fēng)險(xiǎn)和影響。

通過(guò)使用 GH-OST工具，可以對(duì) MySQL 數(shù)據(jù)庫(kù)中的表進(jìn)行在線結(jié)構(gòu)變更，而不會(huì)對(duì)業(yè)務(wù)造成太大的影響。同時(shí)，GH-OST 工具還提供了多種高級(jí)特性，如安全性檢測(cè)、自動(dòng)化流程等，可以幫助用戶更加高效地進(jìn)行表結(jié)構(gòu)變更。

二、MySQL5.7幾種DDL介紹

2.1 copy

• server層觸發(fā)創(chuàng)建臨時(shí)表
• server層對(duì)源表加MDL鎖,阻塞DML寫(xiě)、不阻塞DML讀
• server層從源表中逐行讀取數(shù)據(jù)，寫(xiě)入到臨時(shí)表
• 數(shù)據(jù)拷貝完成后,升級(jí)字典鎖，禁止讀寫(xiě)
• 刪除源表，把臨時(shí)表重命名為源表

MySQL copy方式的DDL變更，數(shù)據(jù)表的重建(主鍵、二級(jí)索引重建)，server層作為中轉(zhuǎn)把從innodb讀取數(shù)據(jù)表，在把數(shù)據(jù)寫(xiě)到innodb層臨時(shí)表。簡(jiǎn)單示意圖如下：

2.2 inplace

（1）rebuild table

需要根據(jù)DDL語(yǔ)句創(chuàng)建新的表結(jié)構(gòu)，根據(jù)源表的數(shù)據(jù)和變更期間增量日志，重建新表的主鍵索引和所有的二級(jí)索引。

Prepare階段：

• 創(chuàng)建新的臨時(shí)frm文件
• 持有EXCLUSIVE-MDL鎖，禁止讀寫(xiě)
• 根據(jù)alter類型，確定執(zhí)行方式(copy,online-rebuild,online-norebuild)
  假如是Add Index，則選擇online-norebuild
• 更新數(shù)據(jù)字典的內(nèi)存對(duì)象
• 分配row_log對(duì)象記錄增量
• 生成新的臨時(shí)ibd文件

ddl執(zhí)行階段 :

• 降級(jí)EXCLUSIVE-MDL鎖，允許讀寫(xiě)
• 掃描old_table的聚集索引每一條記錄rec
• 遍歷新表的聚集索引和二級(jí)索引，逐一處理各個(gè)索引
• 根據(jù)rec構(gòu)造對(duì)應(yīng)的索引項(xiàng)
• 將構(gòu)造索引項(xiàng)插入sort_buffer塊排序
• 將sort_buffer塊更新到新表的索引上
• 記錄ddl執(zhí)行過(guò)程中產(chǎn)生的增量(記錄主鍵和索引字段)
• 重放row_log中的操作到新表索引商
• 重放row_log間產(chǎn)生dml操作append到row_log最后一個(gè)Block

commit階段 :

• 當(dāng)前Block為row_log最后一個(gè)時(shí)，禁止讀寫(xiě)，升級(jí)到EXCLUSIVE-MDL鎖
• 重做row_log中最后一部分增量
• 更新innodb的數(shù)據(jù)字典表
• rename臨時(shí)idb文件，frm文件
• 增量完成

MySQL rebuild table方式的DDL，數(shù)據(jù)不需要通過(guò)sever層中轉(zhuǎn)，innodb層自己完成數(shù)據(jù)表的重建。簡(jiǎn)單示意圖如下：

（2）build-index

需要根據(jù)DDL語(yǔ)句創(chuàng)建新的表結(jié)構(gòu)，根據(jù)源表的數(shù)據(jù)和變更期間增量日志，創(chuàng)建新的索引。

Prepare階段 :

• 持有EXCLUSIVE-MDL鎖，禁止讀寫(xiě)
• 根據(jù)alter類型，確定執(zhí)行方式(copy,online-rebuild,online-norebuild)
• 假如是Add Index，則選擇online-norebuild
• 更新數(shù)據(jù)字典的內(nèi)存對(duì)象
• 分配row_log對(duì)象記錄增量

ddl執(zhí)行階段 :

• 降級(jí)EXCLUSIVE-MDL鎖，允許讀寫(xiě)
• 掃描old_table的聚集索引每一條記錄rec
• 遍歷新表的聚集索引,根據(jù)rec構(gòu)造新的索引數(shù)據(jù)
• 將構(gòu)造索引項(xiàng)插入sort_buffer塊排序
• 將sort_buffer塊更新到新表的索引上
• 記錄ddl執(zhí)行過(guò)程中產(chǎn)生的增量(僅記錄主鍵和新索引字段)
• 重放row_log中的操作到新表索引上
• 重放row_log間產(chǎn)生dml操作append到row_log最后一個(gè)Block

commit階段 :

• 當(dāng)前Block為row_log最后一個(gè)時(shí)，禁止讀寫(xiě)，升級(jí)到EXCLUSIVE-MDL鎖
• 重做row_log中最后一部分增量
• 更新innodb的數(shù)據(jù)字典表
• 增量完成

MySQL rebuild index方式的DDL，數(shù)據(jù)不需要通過(guò)sever層中轉(zhuǎn)，innodb層只需要完成變更二級(jí)索引的創(chuàng)建。簡(jiǎn)單示意圖如下：

（3）only modify metadata

只修改元數(shù)據(jù)(.frm文件和數(shù)據(jù)字典)，不需要拷貝表的數(shù)據(jù)。

三、GH-OST

在GH-OST端，根據(jù)DDL語(yǔ)句創(chuàng)建新的表結(jié)構(gòu)，根據(jù)源表的數(shù)據(jù)和增量期間增量日志，重建新表的主鍵索引和所有的二級(jí)索引，最終完成DDL增量。

主要流程如下：

• 根據(jù)DDL語(yǔ)句和源表創(chuàng)建新的表結(jié)構(gòu)
• 根據(jù)唯一索引(主鍵索引或者其它唯一索引)
  - 優(yōu)先應(yīng)用新增量的binlog到新的表中，需要經(jīng)過(guò)GH-OST把binlog日志轉(zhuǎn)換為sql，然后回放到影子表
  - 其次拷貝源表中的數(shù)據(jù)到新的表中，表數(shù)據(jù)拷貝通過(guò)sql語(yǔ)句 insert ignore into (select .. from)直接在MySQL實(shí)例上執(zhí)行，無(wú)需經(jīng)過(guò)GH-OST中轉(zhuǎn)
• 數(shù)據(jù)拷貝完成并應(yīng)用完binlog后，通過(guò)lock table write 鎖住源表
• 應(yīng)用數(shù)據(jù)完成-獲取到鎖期間產(chǎn)生的增量binlog
• delete源表，rename影子表為源表，完成數(shù)據(jù)增量

GH-OST 進(jìn)行DDL變更，GH-OST服務(wù)通知server層，server層作為中轉(zhuǎn)把從innodb讀取數(shù)據(jù)表，在把數(shù)據(jù)寫(xiě)到innodb層影子表。并且GH-OST作為中轉(zhuǎn)讀取DDL變更期間增量binlog解析成SQL寫(xiě)語(yǔ)句回放到影子表。簡(jiǎn)單示意圖如下：

四、對(duì)比分析

DDL變更執(zhí)行時(shí)長(zhǎng)、對(duì)磁盤(pán)的額外占用(臨時(shí)數(shù)據(jù)表+binlog)、鎖阻塞時(shí)長(zhǎng)、主備延時(shí)都是執(zhí)行DDL變更人員比較關(guān)心的問(wèn)題，本章將從從執(zhí)行效率、占用表空間、鎖阻塞、產(chǎn)生binlog日志量、主備延時(shí)等方面對(duì)MySQL原生的DDL和GH-OST進(jìn)行對(duì)比分析。

4.1 執(zhí)行效率

（1）only modify metadata(正常小于1S)

（2）build-index: 數(shù)據(jù)條目越多、新索引字段越大耗時(shí)越多

•  增量日志超過(guò)innodb_online_alter_log_max_
  size造成DDL失敗

（3）rebuild table: 數(shù)據(jù)條目越多、所有索引字段之和越大耗時(shí)越多

• 增量日志超過(guò)innodb_online_alter_log_max_
  size造成DDL失敗

（4）copy：數(shù)據(jù)條目越多，所有索引字段之和越大耗時(shí)越多，相對(duì)于rebuild table，數(shù)據(jù)需要從server層中轉(zhuǎn)，所以比rebuild table耗時(shí)多

（5）GH-OST ：數(shù)據(jù)條目越多，所有索引字段之和越大耗時(shí)越多,

• 相對(duì)于copy，增量日志數(shù)據(jù)需要從GH-OST中轉(zhuǎn)，所以比copy耗時(shí)多
• 有各種限流,(主備延時(shí)，threads超限延時(shí)…)，增加耗時(shí)
• 增量期間應(yīng)用binlog速度如果跟不上業(yè)務(wù)產(chǎn)生binlog日志的速度，將無(wú)法完成增量
• critical 參數(shù)還會(huì)導(dǎo)致主動(dòng)退出，例如thread_running

耗時(shí)：only modify metadata < build-index < build < copy < GH-OST

4.2 占用表空間

• 【only modify metadata】：忽略
• 【build-index】：額外需要，新增索引字段占用的空間
• 【rebuild-table】：額外需要約兩倍的表空間
• 【copy】：額外需要約兩倍的表空間
• 【GH-OST】 ：臨時(shí)表占用約兩倍的表空間，另外生成影子表會(huì)產(chǎn)生大量的binlog日志會(huì)占用表空間

占用表空間: only modify metadata < build-index < build = copy < GH-OST

4.3 鎖阻塞

（1）only modify metadata

• DDL prepare階段短暫的MDL排他鎖，阻塞讀寫(xiě)

（2）build-index table

• DDL prepare階段短暫的MDL排他鎖，阻塞讀寫(xiě)
• 執(zhí)行階段(主要耗時(shí)階段)，MDL SHARED_UPGRADABLE鎖，不阻塞讀寫(xiě)
• 執(zhí)行階段的最后會(huì)回放增量日志row_log，兩個(gè)block間隙和最后block，持有源表索引的數(shù)據(jù)結(jié)構(gòu)鎖，會(huì)阻塞寫(xiě)
• 提交階段，MDL鎖升級(jí)為排他鎖
• 回放剩余的row_log(執(zhí)行完成致MDL鎖升級(jí)期間新增的row_log，持有源表索引的數(shù)據(jù)結(jié)構(gòu)鎖，阻塞讀寫(xiě))

（3）rebuild-table: 和build-index table一致

（4）copy

• DDL prepare階段短暫的MDL排他鎖，阻塞讀寫(xiě)
• 執(zhí)行階段(主要耗時(shí)階段)，阻塞寫(xiě)，不阻塞讀

（5）GH-OST

• 等待鎖的時(shí)間也會(huì)阻塞業(yè)務(wù)
• 進(jìn)入rename到拿表寫(xiě)鎖的間隙有少量的新增binlog，后續(xù)需要持鎖回放這部分日志
• rename表本身的耗時(shí)通常1s以內(nèi)左右

鎖阻塞時(shí)間：

only modify metadata=GH-OST < build-index table = rebuild-table  < copy(整個(gè)DDL期間都會(huì)阻塞業(yè)務(wù)的寫(xiě))

鎖阻塞分析：

MySQL DDL在獲取MDL排它鎖和GH-OST獲取表的的寫(xiě)鎖，在獲取鎖的等待期間都會(huì)阻塞業(yè)務(wù)的讀寫(xiě)

• MySQL等待鎖的超時(shí)時(shí)間為MySQL參數(shù)innodb_lock_wait_timeout。等待超時(shí)則失敗
• GH-OST等待鎖的時(shí)間，等待超時(shí)時(shí)間可配(默認(rèn)6秒)，等待超時(shí)次數(shù)可配

4.4 產(chǎn)生binlog日志量

【MySQL5.7 DDL】: 在DDL執(zhí)行結(jié)束時(shí)僅向binlog中寫(xiě)入一條DDL語(yǔ)句，日志量較小。

【GH-OST】: 影子表在全量數(shù)據(jù)拷貝和增量數(shù)據(jù)應(yīng)用過(guò)程中產(chǎn)生大量的binlog日志(row模式)，對(duì)于大表日志量非常大。

產(chǎn)生binlog日志量：MySQL5.7 DDL < GH-OST

4.5 主備延時(shí)分析

（1）MySQL5.7 DDL：MySQL集群主備環(huán)境

• Master上DDL執(zhí)行完成，binlog提交后，slave才開(kāi)始進(jìn)行DDL。
• slave串行復(fù)制、group復(fù)制模式,需要等前面的DDL回放完成后才會(huì)進(jìn)行后續(xù)binlog回放，主備延時(shí)至少是DDL回放的時(shí)間。

（2）GH-OST：主備復(fù)制延時(shí)基本可以忽略

• GH-OST在master上創(chuàng)建一個(gè)影子表,在執(zhí)行數(shù)據(jù)拷貝和binlog應(yīng)用階段，GHO表的binlog會(huì)實(shí)時(shí)同步到備。
• 影子表(_GHO表)應(yīng)用完成后，通過(guò)rename實(shí)現(xiàn)新表切換，這個(gè)rename動(dòng)作也會(huì)通過(guò)binlog傳到salve執(zhí)行完成DDL。

延時(shí)時(shí)間：GH-OST < MySQL DDL

備庫(kù)執(zhí)行DDL期間主庫(kù)異常，主備切換。備庫(kù)升級(jí)為主過(guò)程中，要回放完relaylog中的DDL和dml，才能對(duì)外服務(wù)，否則會(huì)出現(xiàn)數(shù)據(jù)丟失，這將造成業(yè)務(wù)較長(zhǎng)時(shí)間的阻塞。

4.6 總結(jié)

GH-OST 工具和 MySQL 原生 DDL 工具的適用場(chǎng)景不同，具體使用哪種工具需要根據(jù)實(shí)際需求進(jìn)行選擇。

• 變更人員無(wú)法判斷本次DDL是否會(huì)造成DML阻塞、鎖阻塞等，建議使用GH-OST工具。
• 如果需要進(jìn)行在線表結(jié)構(gòu)變更，并且需要減少鎖阻塞時(shí)間、減少主備延時(shí)等問(wèn)題，建議使用 GH-OST 工具。
• 變更只涉及到元數(shù)據(jù)的修改，建議使用mysql原生DDL。
• 如果表結(jié)構(gòu)變更較小，對(duì)鎖阻塞時(shí)間和主備延時(shí)要求不高，建議使用 MySQL 原生 DDL 工具。

參考資料：

• online DDL Operations
• MySQL · 源碼閱讀 · 白話Online DDL
• 【騰訊云CDB】源碼分析·MySQL online ddl日志回放解析 
• GH-OST一些使用限制
• mysql mdl鎖類型