MySQL 5.7 DDL 與 GH-OST 對(duì)比分析

作者：存儲(chǔ)研發(fā)團(tuán)隊(duì) 2024-08-08 07:38:42

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

一、背景介紹

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

為了解決這個(gè)問題，可以使用 GitHub 開源的工具 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 操作可能帶來的風(fēng)險(xiǎn)和影響。

通過使用 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寫、不阻塞DML讀
server層從源表中逐行讀取數(shù)據(jù)，寫入到臨時(shí)表
數(shù)據(jù)拷貝完成后,升級(jí)字典鎖，禁止讀寫
刪除源表，把臨時(shí)表重命名為源表

MySQL copy方式的DDL變更，數(shù)據(jù)表的重建(主鍵、二級(jí)索引重建)，server層作為中轉(zhuǎn)把從innodb讀取數(shù)據(jù)表，在把數(shù)據(jù)寫到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鎖，禁止讀寫
根據(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鎖，允許讀寫
掃描old_table的聚集索引每一條記錄rec
遍歷新表的聚集索引和二級(jí)索引，逐一處理各個(gè)索引
根據(jù)rec構(gòu)造對(duì)應(yīng)的索引項(xiàng)
將構(gòu)造索引項(xiàng)插入sort_buffer塊排序
將sort_buffer塊更新到新表的索引上
記錄ddl執(zhí)行過程中產(chǎn)生的增量(記錄主鍵和索引字段)
重放row_log中的操作到新表索引商
重放row_log間產(chǎn)生dml操作append到row_log最后一個(gè)Block

commit階段 :

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

MySQL rebuild table方式的DDL，數(shù)據(jù)不需要通過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鎖，禁止讀寫
根據(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鎖，允許讀寫
掃描old_table的聚集索引每一條記錄rec
遍歷新表的聚集索引,根據(jù)rec構(gòu)造新的索引數(shù)據(jù)
將構(gòu)造索引項(xiàng)插入sort_buffer塊排序
將sort_buffer塊更新到新表的索引上
記錄ddl執(zhí)行過程中產(chǎn)生的增量(僅記錄主鍵和新索引字段)
重放row_log中的操作到新表索引上
重放row_log間產(chǎn)生dml操作append到row_log最后一個(gè)Block

commit階段 :

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

MySQL rebuild index方式的DDL，數(shù)據(jù)不需要通過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)過GH-OST把binlog日志轉(zhuǎn)換為sql，然后回放到影子表
- 其次拷貝源表中的數(shù)據(jù)到新的表中，表數(shù)據(jù)拷貝通過sql語(yǔ)句 insert ignore into (select .. from)直接在MySQL實(shí)例上執(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 進(jìn)行DDL變更，GH-OST服務(wù)通知server層，server層作為中轉(zhuǎn)把從innodb讀取數(shù)據(jù)表，在把數(shù)據(jù)寫到innodb層影子表。并且GH-OST作為中轉(zhuǎn)讀取DDL變更期間增量binlog解析成SQL寫語(yǔ)句回放到影子表。簡(jiǎn)單示意圖如下：

四、對(duì)比分析

DDL變更執(zhí)行時(shí)長(zhǎng)、對(duì)磁盤的額外占用(臨時(shí)數(shù)據(jù)表+binlog)、鎖阻塞時(shí)長(zhǎng)、主備延時(shí)都是執(zhí)行DDL變更人員比較關(guā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í)越多

增量日志超過innodb_online_alter_log_max_
size造成DDL失敗

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

增量日志超過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日志的速度，將無法完成增量
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排他鎖，阻塞讀寫

（2）build-index table

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

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

（4）copy

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

（5）GH-OST

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

鎖阻塞時(shí)間：

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

鎖阻塞分析：

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

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中寫入一條DDL語(yǔ)句，日志量較小。

【GH-OST】: 影子表在全量數(shù)據(jù)拷貝和增量數(shù)據(jù)應(yīng)用過程中產(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才開始進(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)用完成后，通過rename實(shí)現(xiàn)新表切換，這個(gè)rename動(dòng)作也會(huì)通過binlog傳到salve執(zhí)行完成DDL。

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

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