溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容主要講解“MySQL大表是怎么DDL變更的”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“MySQL大表是怎么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之前添加字段屬于高危操作，需要充分考慮對業務的影響，主要存在于兩個方面：
鴻蒙官方戰略合作共建——HarmonyOS技術社區
在讀寫分離場景，主從同步延遲導致業務數據不一致
實時數倉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的方式執行，對業務系統影響最小，同時不影響數倉。
到此，相信大家對“MySQL大表是怎么DDL變更的”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
