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