一、背景介紹

在 MySQL 數據庫中，DDL（數據定義語言）操作包括對表結構、索引、觸發器等進行修改、創建和刪除等操作。由于 MySQL 自帶的 DDL 操作可能會阻塞 DML（數據操作語言）寫語句的執行，大表變更容易產生主備延時，DDL 變更的速度也不能控制，因此在進行表結構變更時需要非常謹慎。

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

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

二、MySQL5.7幾種DDL介紹

2.1 copy

• server層觸發創建臨時表
• server層對源表加MDL鎖,阻塞DML寫、不阻塞DML讀
• server層從源表中逐行讀取數據，寫入到臨時表
• 數據拷貝完成后,升級字典鎖，禁止讀寫
• 刪除源表，把臨時表重命名為源表

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

2.2 inplace

（1）rebuild table

需要根據DDL語句創建新的表結構，根據源表的數據和變更期間增量日志，重建新表的主鍵索引和所有的二級索引。

Prepare階段：

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

ddl執行階段 :

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

commit階段 :

• 當前Block為row_log最后一個時，禁止讀寫，升級到EXCLUSIVE-MDL鎖
• 重做row_log中最后一部分增量
• 更新innodb的數據字典表
• rename臨時idb文件，frm文件
• 增量完成

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

（2）build-index

需要根據DDL語句創建新的表結構，根據源表的數據和變更期間增量日志，創建新的索引。

Prepare階段 :

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

ddl執行階段 :

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

commit階段 :

• 當前Block為row_log最后一個時，禁止讀寫，升級到EXCLUSIVE-MDL鎖
• 重做row_log中最后一部分增量
• 更新innodb的數據字典表
• 增量完成

MySQL rebuild index方式的DDL，數據不需要通過sever層中轉，innodb層只需要完成變更二級索引的創建。簡單示意圖如下：

（3）only modify metadata

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

三、GH-OST

在GH-OST端，根據DDL語句創建新的表結構，根據源表的數據和增量期間增量日志，重建新表的主鍵索引和所有的二級索引，最終完成DDL增量。

主要流程如下：

• 根據DDL語句和源表創建新的表結構
• 根據唯一索引(主鍵索引或者其它唯一索引)
  - 優先應用新增量的binlog到新的表中，需要經過GH-OST把binlog日志轉換為sql，然后回放到影子表
  - 其次拷貝源表中的數據到新的表中，表數據拷貝通過sql語句 insert ignore into (select .. from)直接在MySQL實例上執行，無需經過GH-OST中轉
• 數據拷貝完成并應用完binlog后，通過lock table write 鎖住源表
• 應用數據完成-獲取到鎖期間產生的增量binlog
• delete源表，rename影子表為源表，完成數據增量

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

四、對比分析

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

4.1 執行效率

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

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

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

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

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

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

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

• 相對于copy，增量日志數據需要從GH-OST中轉，所以比copy耗時多
• 有各種限流,(主備延時，threads超限延時…)，增加耗時
• 增量期間應用binlog速度如果跟不上業務產生binlog日志的速度，將無法完成增量
• critical 參數還會導致主動退出，例如thread_running

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

4.2 占用表空間

• 【only modify metadata】：忽略
• 【build-index】：額外需要，新增索引字段占用的空間
• 【rebuild-table】：額外需要約兩倍的表空間
• 【copy】：額外需要約兩倍的表空間
• 【GH-OST】 ：臨時表占用約兩倍的表空間，另外生成影子表會產生大量的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排他鎖，阻塞讀寫
• 執行階段(主要耗時階段)，MDL SHARED_UPGRADABLE鎖，不阻塞讀寫
• 執行階段的最后會回放增量日志row_log，兩個block間隙和最后block，持有源表索引的數據結構鎖，會阻塞寫
• 提交階段，MDL鎖升級為排他鎖
• 回放剩余的row_log(執行完成致MDL鎖升級期間新增的row_log，持有源表索引的數據結構鎖，阻塞讀寫)

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

（4）copy

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

（5）GH-OST

• 等待鎖的時間也會阻塞業務
• 進入rename到拿表寫鎖的間隙有少量的新增binlog，后續需要持鎖回放這部分日志
• rename表本身的耗時通常1s以內左右

鎖阻塞時間：

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

鎖阻塞分析：

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

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

4.4 產生binlog日志量

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

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

產生binlog日志量：MySQL5.7 DDL < GH-OST

4.5 主備延時分析

（1）MySQL5.7 DDL：MySQL集群主備環境

• Master上DDL執行完成，binlog提交后，slave才開始進行DDL。
• slave串行復制、group復制模式,需要等前面的DDL回放完成后才會進行后續binlog回放，主備延時至少是DDL回放的時間。

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

• GH-OST在master上創建一個影子表,在執行數據拷貝和binlog應用階段，GHO表的binlog會實時同步到備。
• 影子表(_GHO表)應用完成后，通過rename實現新表切換，這個rename動作也會通過binlog傳到salve執行完成DDL。

延時時間：GH-OST < MySQL DDL

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

4.6 總結

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

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

參考資料：

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