作者：來自 vivo 互聯(lián)網(wǎng)存儲研發(fā)團隊- Xia Qianyong

本文首先介紹MySQL 5.7 DDL以及GH-OST的原理，然后從效率、空間占用、鎖阻塞、binlog日志產(chǎn)生量、主備延時等方面，對比GH-OST和MySQL5.7 DDL的差異。

一、背景介紹

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

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

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

二、MySQL5.7幾種DDL介紹

2.1 copy

• server層觸發(fā)創(chuàng)建臨時表

• server層對源表加MDL鎖,阻塞DML寫、不阻塞DML讀

• server層從源表中逐行讀取數(shù)據(jù)，寫入到臨時表

• 數(shù)據(jù)拷貝完成后,升級字典鎖，禁止讀寫

• 刪除源表，把臨時表重命名為源表

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

2.2 inplace

（1）rebuild table

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

Prepare階段：

• 創(chuàng)建新的臨時frm文件

• 持有EXCLUSIVE-MDL鎖，禁止讀寫

• 根據(jù)alter類型，確定執(zhí)行方式(copy,online-rebuild,online-norebuild)假如是Add Index，則選擇online-norebuild

• 更新數(shù)據(jù)字典的內(nèi)存對象

• 分配row_log對象記錄增量

• 生成新的臨時ibd文件

ddl執(zhí)行階段 :

• 降級EXCLUSIVE-MDL鎖，允許讀寫

• 掃描old_table的聚集索引每一條記錄rec

• 遍歷新表的聚集索引和二級索引，逐一處理各個索引

• 根據(jù)rec構(gòu)造對應(yīng)的索引項

• 將構(gòu)造索引項插入sort_buffer塊排序

• 將sort_buffer塊更新到新表的索引上

• 記錄ddl執(zhí)行過程中產(chǎn)生的增量(記錄主鍵和索引字段)

• 重放row_log中的操作到新表索引商

• 重放row_log間產(chǎn)生dml操作append到row_log最后一個Block

commit階段 :

• 當前Block為row_log最后一個時，禁止讀寫，升級到EXCLUSIVE-MDL鎖

• 重做row_log中最后一部分增量

• 更新innodb的數(shù)據(jù)字典表

• rename臨時idb文件，frm文件

• 增量完成

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

（2）build-index

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

Prepare階段?:

• 持有EXCLUSIVE-MDL鎖，禁止讀寫

• 根據(jù)alter類型，確定執(zhí)行方式(copy,online-rebuild,online-norebuild)

• 假如是Add Index，則選擇online-norebuild

• 更新數(shù)據(jù)字典的內(nèi)存對象

• 分配row_log對象記錄增量

ddl執(zhí)行階段 :

• 降級EXCLUSIVE-MDL鎖，允許讀寫

• 掃描old_table的聚集索引每一條記錄rec

• 遍歷新表的聚集索引,根據(jù)rec構(gòu)造新的索引數(shù)據(jù)

• 將構(gòu)造索引項插入sort_buffer塊排序

• 將sort_buffer塊更新到新表的索引上

• 記錄ddl執(zhí)行過程中產(chǎn)生的增量(僅記錄主鍵和新索引字段)

• 重放row_log中的操作到新表索引上

• 重放row_log間產(chǎn)生dml操作append到row_log最后一個Block

commit階段 :

• 當前Block為row_log最后一個時，禁止讀寫，升級到EXCLUSIVE-MDL鎖

• 重做row_log中最后一部分增量

• 更新innodb的數(shù)據(jù)字典表

• 增量完成

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

（3）only modify metadata

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

三、GH-OST

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

主要流程如下：

• 根據(jù)DDL語句和源表創(chuàng)建新的表結(jié)構(gòu)

• 根據(jù)唯一索引(主鍵索引或者其它唯一索引)

- 優(yōu)先應(yīng)用新增量的binlog到新的表中，需要經(jīng)過GH-OST把binlog日志轉(zhuǎn)換為sql，然后回放到影子表

- 其次拷貝源表中的數(shù)據(jù)到新的表中，表數(shù)據(jù)拷貝通過sql語句 insert ignore into (select .. from)直接在MySQL實例上執(zhí)行，無需經(jīng)過GH-OST中轉(zhuǎn)

• 數(shù)據(jù)拷貝完成并應(yīng)用完binlog后，通過lock table write 鎖住源表

• 應(yīng)用數(shù)據(jù)完成-獲取到鎖期間產(chǎn)生的增量binlog

• delete源表，rename影子表為源表，完成數(shù)據(jù)增量

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

四、對比分析

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

4.1 執(zhí)行效率

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

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

• 增量日志超過innodb_online_alter_log_max_size造成DDL失敗

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

• 增量日志超過innodb_online_alter_log_max_size造成DDL失敗

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

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

• 相對于copy，增量日志數(shù)據(jù)需要從GH-OST中轉(zhuǎn)，所以比copy耗時多

• 有各種限流,(主備延時，threads超限延時…)，增加耗時

• 增量期間應(yīng)用binlog速度如果跟不上業(yè)務(wù)產(chǎn)生binlog日志的速度，將無法完成增量

• critical 參數(shù)還會導(dǎo)致主動退出，例如thread_running

耗時：only modify metadata < build-index < build < copy < GH-OST

4.2 占用表空間

• 【only modify?metadata】：忽略

• 【build-index】：額外需要，新增索引字段占用的空間

• 【rebuild-table】：額外需要約兩倍的表空間

• 【copy】：額外需要約兩倍的表空間

• 【GH-OST】?：臨時表占用約兩倍的表空間，另外生成影子表會產(chǎn)生大量的binlog日志會占用表空間

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

4.3 鎖阻塞

（1）only modify metadata

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

（2）build-index table

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

• 執(zhí)行階段(主要耗時階段)，MDL SHARED_UPGRADABLE鎖，不阻塞讀寫

• 執(zhí)行階段的最后會回放增量日志row_log，兩個block間隙和最后block，持有源表索引的數(shù)據(jù)結(jié)構(gòu)鎖，會阻塞寫

• 提交階段，MDL鎖升級為排他鎖

• 回放剩余的row_log(執(zhí)行完成致MDL鎖升級期間新增的row_log，持有源表索引的數(shù)據(jù)結(jié)構(gòu)鎖，阻塞讀寫)

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

（4）copy

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

• 執(zhí)行階段(主要耗時階段)，阻塞寫，不阻塞讀

（5）GH-OST

• 等待鎖的時間也會阻塞業(yè)務(wù)

• 進入rename到拿表寫鎖的間隙有少量的新增binlog，后續(xù)需要持鎖回放這部分日志

• rename表本身的耗時通常1s以內(nèi)左右

鎖阻塞時間：

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

鎖阻塞分析：

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

• MySQL等待鎖的超時時間為MySQL參數(shù)innodb_lock_wait_timeout。等待超時則失敗

• GH-OST等待鎖的時間，等待超時時間可配(默認6秒)，等待超時次數(shù)可配

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

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

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

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

4.5 主備延時分析

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

• Master上DDL執(zhí)行完成，binlog提交后，slave才開始進行DDL。

• slave串行復(fù)制、group復(fù)制模式,需要等前面的DDL回放完成后才會進行后續(xù)binlog回放，主備延時至少是DDL回放的時間。

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

• GH-OST在master上創(chuàng)建一個影子表,在執(zhí)行數(shù)據(jù)拷貝和binlog應(yīng)用階段，GHO表的binlog會實時同步到備。

• 影子表(_GHO表)應(yīng)用完成后，通過rename實現(xiàn)新表切換，這個rename動作也會通過binlog傳到salve執(zhí)行完成DDL。

延時時間：GH-OST < MySQL DDL

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

4.6 總結(jié)

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

• 變更人員無法判斷本次DDL是否會造成DML阻塞、鎖阻塞等，建議使用GH-OST工具。

• 如果需要進行在線表結(jié)構(gòu)變更，并且需要減少鎖阻塞時間、減少主備延時等問題，建議使用 GH-OST 工具。

• 變更只涉及到元數(shù)據(jù)的修改，建議使用mysql原生DDL。

• 如果表結(jié)構(gòu)變更較小，對鎖阻塞時間和主備延時要求不高，建議使用 MySQL 原生 DDL 工具。

參考資料：

• online DDL Operations

• MySQL · 源碼閱讀 · 白話Online DDL

• 【騰訊云CDB】源碼分析·MySQL online ddl日志回放解析?

• GH-OST一些使用限制

• mysql mdl鎖類型