[[356257]]

本文轉載自微信公眾號「三太子敖丙」，作者三太子敖丙 。轉載本文請聯系三太子敖丙公眾號。

前言

隨著業務的發展，用戶對系統需求變得越來越多，這就要求系統能夠快速更新迭代以滿足業務需求，通常系統版本發布時，都要先執行數據庫的DDL變更，包括創建表、添加字段、添加索引、修改字段屬性等。

在數據量大不大的情況下，執行DDL都很快，對業務基本沒啥影響，但是數據量大的情況，而且我們業務做了讀寫分離，接入了實時數倉，這時DDL變更就是一個的難題，需要綜合各方業務全盤考慮。

下面就聊聊這些年我公司在里面，MySQL中的DDL執行方式的變化、大表DDL該如何選擇以及DDL執行過程監控。

MySQL中的DDL

DDL概述

MySQL中的DDL語句形式比較多，概括一下有以下幾類：CREATE，ALTER，DROP，RENAME，TRUNCATE。

這些操作都是隱式提交且原子性，要么成功，要么失敗，在MySQL 8.0之前DDL操作是不記錄日志的。

今天就聊一下跟系統版本發布相關的數據庫結構變更，主要就是ALTER TABLE變更了，DDL變更流程普通的DML變更是類似的，如下所示

 

注：這里涉及MySQL基礎知識，還不知道的朋友翻看下我MySQL基礎章節即可。

在早期的MySQL版本，DDL變更都會導致全表被鎖，阻塞表上的DML操作，影響業務正常運行，好的一點就是，隨著MySQL版本的迭代，DDL的執行方式也在變化。

MetaData元數據

MySQL的元數據(MetaData)跟其他的RDBMS數據庫一樣的，描述的對象的結構信息，存儲在information_schema架構下，例如常見的TABLES、COLUMNS等，下面例子是創建一個表crm_users，MySQL會自動往Information_schema.tables和columns等相關數據字典表中插入數據，這些數據稱為元數據，一般都是靜態化，只有表上發生了DDL操作才會實時更新。

 

MetaData Lock

MySQL利用MetaData Lock來管理對象的訪問，保證數據的一致性，對于一些核心業務表，表上DML操作比較頻繁，這個時候添加字段可能會觸發MetaData Lock。

 

可以看到Waiting for table metadata lock等待事件，thread 155正在執行alter table等待thread 154執行的select釋放鎖，因為DML在執行期間會持有SHARED_READ鎖，要執行DDL時獲取SHARED_UPGRADABLE(共享可升級鎖，縮寫為SU，允許并發更新和讀同一個表)鎖成功，但是獲取EXCLUSIVE MetaData Lock鎖失敗，處于暫掛PENDING狀態。

DDL執行方式

從MySQL官方文檔可以看到，ALTER TABLE的選項很多，跟性能相關的選項主要有ALGORITHM和LOCK。

 

ALGORITHM OPTION	DESCRIPTION
COPY	MySQL早期的變更方式，需要創建修改后的臨時表，然后按數據行拷貝原表數據到臨時表，做rename重命名來完成創建，在此期間不允許并發DML操作，原表是可讀的，不可寫，同時需要額外一倍的磁盤空間。
INPLACE	直接在原表上進行修改，不需創建臨時表拷貝數據及重命名，原表會持有Exclusive Metadata  Lock，通常是允許并發DML操作。
INSTANT	MySQL 5.8開始支持，只修改數據字典中的元數據，表數據不受影響，執行期間沒有Exclusive Metadata  Lock，允許并發的DML操作。

從這張表可以看到，MySQL對于DDL執行方式一直在做優化，目的就是為了提高DDL執行效率，減少鎖等待，不影響表數據，同時不影響正常的DML操作。

LOCK選項

LOCK OPTiON	DESCRIPTION
DEFAULT	默認模式：MySQL根據運行情況，在盡量不鎖表的情況下自動選擇LOCK模式。
NONE	無鎖：允許Online DDL期間進行并發讀寫操作，如果Online DDL操作不支持對表并發DML操作，則DDL操作失敗，對表修改無效。
SHARED	共享鎖：Online DDL操作期間不影響讀取，阻塞寫入。
EXCLUSIVE	排它鎖：Online DDL操作期間不允許對鎖表進行任何操作。

下面舉例說明下這幾種方式的執行過程，先創建測試表，制造一些數據。

 

COPY

COPY方式的變更流程如下：

 

根據業務需要，需要在crm_users添加一個字段user_type，采用COPY方式執行變更。

 

從執行過程及profile可以看出，通過COPY方式會創建臨是表#sql-564_85，獲取System Lock，拷貝數據到臨時表，最后做rename表名切換，釋放Lock資源，在執行期間不支持并發DML操作。

INPLACE

INPLACE方式是在原表上直接修改，對于添加索引、添加/刪除列、修改字段NULL/NOT NULL屬性等操作，需要修改MySQL內部的數據記錄，需要重建表(Rebuild Table)。

 

從執行過程可以看到，需要獲取Exclusive Metadata Lock，修改表數據，釋放Lock，在執行期間支持并發DML操作。

INSTANT

MySQL 5.8開始推出的方式，DDL只修改數據字典中的元數據，表數據不受影響，沒有Exclusive Metadata Lock，允許并發的DML操作，支持的DDL變更是有限制的，目前主要包括添加字段，添加/刪除生成列，修改ENUM或SET列，改變索引類型以及重命名表。

 

比對下這三種方式的執行效率

執行方式/項目	數據量(w)	執行時間(s)	重建表	修改MetaData	修改Data	允許并發DML
COPY	650	29.89	YES	No	Yes	No
INPLACE	650	10.56	YES	No	Yes	Yes
INSTANT	650	0.19	No	Yes	No	Yes

ONLINE DDL

截止MySQL 8.0，OnLine DDL有三種方式COPY，INPLACE，INSTANT，MySQL會自動根據執行的DDL選擇使用哪種方式，一般會優先選擇INSTANT方式，如果不支持，就選擇INPLANCE方式，再不支持就只能選擇COPY方式了。

MySQL官方文檔也給出了Online DDL的支持矩陣，列下常用的DDL操作，對比項主要包括是否重建表，允許并發的DML操作以及只修改元數據，表數據不受影響。

Operation	Instant	In Place	Copy	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a column	Yes	Yes*	Yes	No*	Yes*	Yes
Dropping a column	No	Yes	Yes	Yes	Yes	No
Renaming a column	No	Yes	Yes	No	Yes	Yes
Setting a column default value	Yes	Yes	Yes	No	Yes	Yes
Dropping the column default value	Yes	Yes	Yes	No	Yes	Yes
Changing the auto-increment value	No	Yes	Yes	No	Yes	No
Making a column NULL	No	Yes	Yes	Yes*	Yes	No
Making a column NOT NULL	No	Yes	Yes	Yes*	Yes	No
Adding a primary key	No	Yes*	Yes	Yes*	Yes	No
Dropping a primary key	No	No	Yes	Yes	No	No
Creating or adding a secondary index	No	Yes	Yes	No	Yes	No
Dropping an index	No	Yes	Yes	No	Yes	Yes
Renaming an index	No	Yes	Yes	No	No	No
Adding a FULLTEXT index	No	Yes*	Yes	No*	No	No

大表DDL方案在實際業務系統中，業務發展比較快，表的數據量比較大，業務層面又做了讀寫分離，同時會將MySQL數據實時同步到數據倉庫(包括實時數倉和離線數倉)，實際的數據庫架構如下。

 

 

假設這是一個交易系統數據庫，訂單表booking有8000w數據，且接入到了實時和離線倉庫，根據業務需要，在訂單表booking添加一個字段，在MySQL 5.7之前添加字段屬于高危操作，需要充分考慮對業務的影響，主要存在于兩個方面：

1. 在讀寫分離場景，主從同步延遲導致業務數據不一致
2. 實時數倉ADB不允許源端MySQL表重命名，如果通過COPY方式或者pt-osc、gh-ost等工具都會rename表名，那么就需要從數倉刪除該表，重新配置同步(全量 + 增量)，會影響數倉業務

ONLINE DDL方式

對于MySQL 5.6到5.7的版本，可以使用OnLine DDL的方式變更，對于大表來說，執行時間會很長，好處是在Master上DML操作不受影響，但是會導致主從延時。

假如Master上添加字段執行了20分鐘，相應的Slave也要執行20分鐘，在這期間Slave一直處于延遲狀態，會造成業務數據不一致，比如用戶在Master下單成功，由于Slave延遲查詢不到訂單信息，用戶誤以為網絡原因沒有下單成功，又下了一單，導致重復下單的情況。

這種方式會導致主從延遲，但是不會影響實時數倉的業務，根據業務情況，只能選擇在業務低峰期執行了。

pt-osc工具

為了解決DDL變更導致主從延時對業務的影響，會想到用大表變更利器pt-osc(pt-online-schema-change)或者gh-ost工具來做，這兩個工具執行過程及原理大同小異，變更流程如下(不考慮外鍵，按照MySQL規范不允許使用外鍵)：

 

 

• 創建一個新的表，表結構為修改后的數據表，用于從源數據表向新表中導入數據。
• 在源表上創建觸發器，用于記錄從拷貝數據開始之后，對源數據表繼續進行數據修改的操作記錄下來，用于數據拷貝結束后，執行這些操作，保證數據不會丟失。
• 拷貝數據，從源數據表中拷貝數據到新表中。
• 修改外鍵相關的子表，根據修改后的數據，修改外鍵關聯的子表。
• rename源數據表為old表，把新表rename為源表名，并將old表刪除。
• 刪除觸發器。

執行pt-osc的時候也需要獲取一個Exclusive Metadata Lock，如果在此期間表上有DML操作正在進行，pt-osc操作會一直處于暫掛PENDING狀態，這個時候表上正常DML操作都會被阻塞，MySQL活動連接數瞬間暴漲，CPU使用率100%，依賴的該表的接口都會報錯，所以要選擇在業務低峰期執行，同時做好MetaData Lock鎖的監控以便業務不受影響，來看一個例子：

 

D=trade, t=booking：數據庫trade，表名booking。

--chunk-size=1000：每次拷貝的數據行數。

--max-log = 1：確保從庫延遲不超過1s，超過就停止拷貝數據。

--check-interval=2：表示等待2s之后繼續拷貝數據。

--recursion-method="hosts"：如果不是使用默認端口3306，那么使用hosts方式來查找從庫更可靠。

一般MySQL binlog格式都是ROW，pt-osc在拷貝數據的過程也會產生大量的binlog，也可能導致主從延時，需要控制好每次拷貝數據的大小和頻率，在執行期間，也會降低DML的并發度。

MySQL 8.0變更方式

用過Oracle的都知道，DDL變更都是修改元數據，上億的表在Oracle中DDL變更都是瞬間完成。

令人激動的是，MySQL 8.0也推出了INSTANT方式，真正的只修改MetaData，不影響表數據，所以它的執行效率跟表大小幾乎沒有關系。建議新系統上線用MySQL的話盡量使用MySQL 8.0，老的數據庫也可以升級到MySQL 8.0獲取更好的性能。

官方文檔對INSTANT的解釋：

INSTANT: Operations only modify metadata in the data dictionary. No exclusive metadata locks are taken on the table during preparation and execution, and table data is unaffected, making operations instantaneous. Concurrent DML is permitted. (Introduced in MySQL 8.0.12)

既要解決主從同步，又要解決rename數倉不同步的問題，目前只有INSTANT方式滿足需求了。

監控DDL執行

進度在大表執行DDL變更的時候，非常關心它的執行進度，MySQL 5.7之前是沒有好的工具去監控，基本只能坐等了。在MySQL 8.0可以通過開啟performance_schema，打開events_stages_current事件進行監控。

 

 

總結DDL在業務系統版本迭代的過程是必不可少的，如何在不影響業務以及外圍系統的情況下，實現DDL的平滑變更，是需要綜合個系統特性考慮的，評估出重要性和優先級，同時也要掌握不同MySQL版本DDL執行方式，以便我們做更好的選擇。

 

例如上面提到了，目前我在大數據團隊，我們的業務都做了讀寫分離，同時接入實時數倉，數倉不支持rename操作，這時就可以選擇在業務低峰期使用ONLINE DDL的方式執行，對業務系統影響最小，同時不影響數倉。