溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內(nèi)容主要講解“MySQL大表是怎么DDL變更的”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“MySQL大表是怎么DDL變更的”吧!
MySQL中的DDL
DDL概述
MySQL中的DDL語句形式比較多，概括一下有以下幾類：CREATE，ALTER，DROP，RENAME，TRUNCATE。
這些操作都是隱式提交且原子性，要么成功，要么失敗，在MySQL 8.0之前DDL操作是不記錄日志的。
今天就聊一下跟系統(tǒng)版本發(fā)布相關(guān)的數(shù)據(jù)庫結(jié)構(gòu)變更，主要就是ALTER TABLE變更了，DDL變更流程普通的DML變更是類似的，如下所示
注：這里涉及MySQL基礎(chǔ)知識，還不知道的朋友翻看下我MySQL基礎(chǔ)章節(jié)即可。
在早期的MySQL版本，DDL變更都會導致全表被鎖，阻塞表上的DML操作，影響業(yè)務正常運行，好的一點就是，隨著MySQL版本的迭代，DDL的執(zhí)行方式也在變化。
MetaData元數(shù)據(jù)
MySQL的元數(shù)據(jù)(MetaData)跟其他的RDBMS數(shù)據(jù)庫一樣的，描述的對象的結(jié)構(gòu)信息，存儲在information_schema架構(gòu)下，例如常見的TABLES、COLUMNS等，下面例子是創(chuàng)建一個表crm_users，MySQL會自動往Information_schema.tables和columns等相關(guān)數(shù)據(jù)字典表中插入數(shù)據(jù)，這些數(shù)據(jù)稱為元數(shù)據(jù)，一般都是靜態(tài)化，只有表上發(fā)生了DDL操作才會實時更新。
MetaData Lock
MySQL利用MetaData Lock來管理對象的訪問，保證數(shù)據(jù)的一致性，對于一些核心業(yè)務表，表上DML操作比較頻繁，這個時候添加字段可能會觸發(fā)MetaData Lock。
可以看到Waiting for table metadata lock等待事件，thread 155正在執(zhí)行alter table等待thread 154執(zhí)行的select釋放鎖，因為DML在執(zhí)行期間會持有SHARED_READ鎖，要執(zhí)行DDL時獲取SHARED_UPGRADABLE(共享可升級鎖，縮寫為SU，允許并發(fā)更新和讀同一個表)鎖成功，但是獲取EXCLUSIVE MetaData Lock鎖失敗，處于暫掛PENDING狀態(tài)。
DDL執(zhí)行方式
從MySQL官方文檔可以看到，ALTER TABLE的選項很多，跟性能相關(guān)的選項主要有ALGORITHM和LOCK。
| ALGORITHM OPTION | DESCRIPTION |
|---|---|
| COPY | MySQL早期的變更方式，需要創(chuàng)建修改后的臨時表，然后按數(shù)據(jù)行拷貝原表數(shù)據(jù)到臨時表，做rename重命名來完成創(chuàng)建，在此期間不允許并發(fā)DML操作，原表是可讀的，不可寫，同時需要額外一倍的磁盤空間。 |
| INPLACE | 直接在原表上進行修改，不需創(chuàng)建臨時表拷貝數(shù)據(jù)及重命名，原表會持有Exclusive Metadata Lock，通常是允許并發(fā)DML操作。 |
| INSTANT | MySQL 5.8開始支持，只修改數(shù)據(jù)字典中的元數(shù)據(jù)，表數(shù)據(jù)不受影響，執(zhí)行期間沒有Exclusive Metadata Lock，允許并發(fā)的DML操作。 |
從這張表可以看到，MySQL對于DDL執(zhí)行方式一直在做優(yōu)化，目的就是為了提高DDL執(zhí)行效率，減少鎖等待，不影響表數(shù)據(jù)，同時不影響正常的DML操作。
LOCK選項
| LOCK OPTiON | DESCRIPTION |
|---|---|
| DEFAULT | 默認模式：MySQL根據(jù)運行情況，在盡量不鎖表的情況下自動選擇LOCK模式。 |
| NONE | 無鎖：允許Online DDL期間進行并發(fā)讀寫操作，如果Online DDL操作不支持對表并發(fā)DML操作，則DDL操作失敗，對表修改無效。 |
| SHARED | 共享鎖：Online DDL操作期間不影響讀取，阻塞寫入。 |
| EXCLUSIVE | 排它鎖：Online DDL操作期間不允許對鎖表進行任何操作。 |
下面舉例說明下這幾種方式的執(zhí)行過程，先創(chuàng)建測試表，制造一些數(shù)據(jù)。
COPY
COPY方式的變更流程如下：
根據(jù)業(yè)務需要，需要在crm_users添加一個字段user_type，采用COPY方式執(zhí)行變更。
從執(zhí)行過程及profile可以看出，通過COPY方式會創(chuàng)建臨是表#sql-564_85，獲取System Lock，拷貝數(shù)據(jù)到臨時表，最后做rename表名切換，釋放Lock資源，在執(zhí)行期間不支持并發(fā)DML操作。
INPLACE
INPLACE方式是在原表上直接修改，對于添加索引、添加/刪除列、修改字段NULL/NOT NULL屬性等操作，需要修改MySQL內(nèi)部的數(shù)據(jù)記錄，需要重建表(Rebuild Table)。
從執(zhí)行過程可以看到，需要獲取Exclusive Metadata Lock，修改表數(shù)據(jù)，釋放Lock，在執(zhí)行期間支持并發(fā)DML操作。
INSTANT
MySQL 5.8開始推出的方式，DDL只修改數(shù)據(jù)字典中的元數(shù)據(jù)，表數(shù)據(jù)不受影響，沒有Exclusive Metadata Lock，允許并發(fā)的DML操作，支持的DDL變更是有限制的，目前主要包括添加字段，添加/刪除生成列，修改ENUM或SET列，改變索引類型以及重命名表。
比對下這三種方式的執(zhí)行效率
| 執(zhí)行方式/項目 | 數(shù)據(jù)量(w) | 執(zhí)行時間(s) | 重建表 | 修改MetaData | 修改Data | 允許并發(fā)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會自動根據(jù)執(zhí)行的DDL選擇使用哪種方式，一般會優(yōu)先選擇INSTANT方式，如果不支持，就選擇INPLANCE方式，再不支持就只能選擇COPY方式了。
MySQL官方文檔也給出了Online DDL的支持矩陣，列下常用的DDL操作，對比項主要包括是否重建表，允許并發(fā)的DML操作以及只修改元數(shù)據(jù)，表數(shù)據(jù)不受影響。
| 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方案在實際業(yè)務系統(tǒng)中，業(yè)務發(fā)展比較快，表的數(shù)據(jù)量比較大，業(yè)務層面又做了讀寫分離，同時會將MySQL數(shù)據(jù)實時同步到數(shù)據(jù)倉庫(包括實時數(shù)倉和離線數(shù)倉)，實際的數(shù)據(jù)庫架構(gòu)如下。
假設這是一個交易系統(tǒng)數(shù)據(jù)庫，訂單表booking有8000w數(shù)據(jù)，且接入到了實時和離線倉庫，根據(jù)業(yè)務需要，在訂單表booking添加一個字段，在MySQL 5.7之前添加字段屬于高危操作，需要充分考慮對業(yè)務的影響，主要存在于兩個方面：
鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)
在讀寫分離場景，主從同步延遲導致業(yè)務數(shù)據(jù)不一致
實時數(shù)倉ADB不允許源端MySQL表重命名，如果通過COPY方式或者pt-osc、gh-ost等工具都會rename表名，那么就需要從數(shù)倉刪除該表，重新配置同步(全量 + 增量)，會影響數(shù)倉業(yè)務
ONLINE DDL方式
對于MySQL 5.6到5.7的版本，可以使用OnLine DDL的方式變更，對于大表來說，執(zhí)行時間會很長，好處是在Master上DML操作不受影響，但是會導致主從延時。
假如Master上添加字段執(zhí)行了20分鐘，相應的Slave也要執(zhí)行20分鐘，在這期間Slave一直處于延遲狀態(tài)，會造成業(yè)務數(shù)據(jù)不一致，比如用戶在Master下單成功，由于Slave延遲查詢不到訂單信息，用戶誤以為網(wǎng)絡原因沒有下單成功，又下了一單，導致重復下單的情況。
這種方式會導致主從延遲，但是不會影響實時數(shù)倉的業(yè)務，根據(jù)業(yè)務情況，只能選擇在業(yè)務低峰期執(zhí)行了。
pt-osc工具
為了解決DDL變更導致主從延時對業(yè)務的影響，會想到用大表變更利器pt-osc(pt-online-schema-change)或者gh-ost工具來做，這兩個工具執(zhí)行過程及原理大同小異，變更流程如下(不考慮外鍵，按照MySQL規(guī)范不允許使用外鍵)：
創(chuàng)建一個新的表，表結(jié)構(gòu)為修改后的數(shù)據(jù)表，用于從源數(shù)據(jù)表向新表中導入數(shù)據(jù)。
在源表上創(chuàng)建觸發(fā)器，用于記錄從拷貝數(shù)據(jù)開始之后，對源數(shù)據(jù)表繼續(xù)進行數(shù)據(jù)修改的操作記錄下來，用于數(shù)據(jù)拷貝結(jié)束后，執(zhí)行這些操作，保證數(shù)據(jù)不會丟失。
拷貝數(shù)據(jù)，從源數(shù)據(jù)表中拷貝數(shù)據(jù)到新表中。
修改外鍵相關(guān)的子表，根據(jù)修改后的數(shù)據(jù)，修改外鍵關(guān)聯(lián)的子表。
rename源數(shù)據(jù)表為old表，把新表rename為源表名，并將old表刪除。
刪除觸發(fā)器。
執(zhí)行pt-osc的時候也需要獲取一個Exclusive Metadata Lock，如果在此期間表上有DML操作正在進行，pt-osc操作會一直處于暫掛PENDING狀態(tài)，這個時候表上正常DML操作都會被阻塞，MySQL活動連接數(shù)瞬間暴漲，CPU使用率100%，依賴的該表的接口都會報錯，所以要選擇在業(yè)務低峰期執(zhí)行，同時做好MetaData Lock鎖的監(jiān)控以便業(yè)務不受影響，來看一個例子：
D=trade, t=booking：數(shù)據(jù)庫trade，表名booking。
--chunk-size=1000：每次拷貝的數(shù)據(jù)行數(shù)。
--max-log = 1：確保從庫延遲不超過1s，超過就停止拷貝數(shù)據(jù)。
--check-interval=2：表示等待2s之后繼續(xù)拷貝數(shù)據(jù)。
--recursion-method="hosts"：如果不是使用默認端口3306，那么使用hosts方式來查找從庫更可靠。
一般MySQL binlog格式都是ROW，pt-osc在拷貝數(shù)據(jù)的過程也會產(chǎn)生大量的binlog，也可能導致主從延時，需要控制好每次拷貝數(shù)據(jù)的大小和頻率，在執(zhí)行期間，也會降低DML的并發(fā)度。
MySQL 8.0變更方式
用過Oracle的都知道，DDL變更都是修改元數(shù)據(jù)，上億的表在Oracle中DDL變更都是瞬間完成。
令人激動的是，MySQL 8.0也推出了INSTANT方式，真正的只修改MetaData，不影響表數(shù)據(jù)，所以它的執(zhí)行效率跟表大小幾乎沒有關(guān)系。建議新系統(tǒng)上線用MySQL的話盡量使用MySQL 8.0，老的數(shù)據(jù)庫也可以升級到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數(shù)倉不同步的問題，目前只有INSTANT方式滿足需求了。
監(jiān)控DDL執(zhí)行
進度在大表執(zhí)行DDL變更的時候，非常關(guān)心它的執(zhí)行進度，MySQL 5.7之前是沒有好的工具去監(jiān)控，基本只能坐等了。在MySQL 8.0可以通過開啟performance_schema，打開events_stages_current事件進行監(jiān)控。
總結(jié)DDL在業(yè)務系統(tǒng)版本迭代的過程是必不可少的，如何在不影響業(yè)務以及外圍系統(tǒng)的情況下，實現(xiàn)DDL的平滑變更，是需要綜合個系統(tǒng)特性考慮的，評估出重要性和優(yōu)先級，同時也要掌握不同MySQL版本DDL執(zhí)行方式，以便我們做更好的選擇。
例如上面提到了，目前我在大數(shù)據(jù)團隊，我們的業(yè)務都做了讀寫分離，同時接入實時數(shù)倉，數(shù)倉不支持rename操作，這時就可以選擇在業(yè)務低峰期使用ONLINE DDL的方式執(zhí)行，對業(yè)務系統(tǒng)影響最小，同時不影響數(shù)倉。
到此，相信大家對“MySQL大表是怎么DDL變更的”有了更深的了解，不妨來實際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進入相關(guān)頻道進行查詢，關(guān)注我們，繼續(xù)學習！
免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼
