MySQL如何優(yōu)雅的執(zhí)行DDL

一、前言

關(guān)于MySQL DDL表結(jié)構(gòu)變更，各個工單平臺基本上都支持了pt-osc及Online DDL的方式，但是，我相信仍然有一大部分人，不太了解這兩種方式各自的優(yōu)缺點是啥，以至于實際當(dāng)中，會稀里糊涂的隨機(jī)選一種去執(zhí)行，選對了固然好，選錯了，自然免不了領(lǐng)導(dǎo)的一頓K，這......當(dāng)然是開玩笑的哈。

?

在各搜索平臺上，介紹關(guān)于pt-osc及Online DDL工作原理的文章，不計其數(shù)，但是，對于非專業(yè)選手而已，又有幾個人是完全吃透的呢？所以，在這，不打算對其原理再重復(fù)一遍，僅從他們的執(zhí)行機(jī)制角度出發(fā)，介紹各種DDL在選擇不同方式時所產(chǎn)生的影響，并基于此來分析該如何選擇。

?

另，現(xiàn)在普遍使用的版本為5.7，所以，咱就以 MySQL 5.7.24 版本為例。

?

二、pt-osc及Online DDL執(zhí)行機(jī)制

2.1 Online DDL

?機(jī)制：MySQL通過Innodb引擎在內(nèi)部執(zhí)行一系列的操作進(jìn)行表變更，當(dāng)然，同一個表，不同的DDL，會有不同效果，甚至?xí)霈F(xiàn)一天上一個地下的差異，所以不同DDL，后面再對其進(jìn)行具體分析；如果有從庫，則在主庫執(zhí)行完成后，從庫再操作一遍，動作和主庫一模一樣（執(zhí)行時間也很接近）。

?優(yōu)點：這個咱后面在講具體SQL時再進(jìn)行具體分析。

?缺點：

?某些場景下，會鎖表引發(fā)堵塞增刪改操作，這個是需要重點注意的，具體場景后面會標(biāo)紅說明。

?如果有從庫，這有一個很致命的弱點：復(fù)制延遲。因為主庫執(zhí)行的動作，會在從庫再來一遍，如果這個動作是非常耗時的，那在從庫執(zhí)行（重放主庫的動作）的時間點開始，其后續(xù)所有動作都被堵塞住，直到從庫也執(zhí)行完這個DDL后，才會繼續(xù)按順序執(zhí)行其他SQL。這就就意味著，從從庫執(zhí)行開始，從庫復(fù)制就出現(xiàn)了延遲，延遲的時間會慢慢變大，直到DDL執(zhí)行完后，延遲才會慢慢變小。

?存在數(shù)據(jù)copy的情況時，需要額外的磁盤空間，但有可能同樣的SQL，空間需求會比使用pt-osc低。

?特殊情況：云側(cè)MySQL RDS，本身有一個隱藏從庫用于高可用，因為這個隱藏從庫不對外提供服務(wù)，所以基本上業(yè)務(wù)側(cè)也不需要去關(guān)注他。但極端情況，如果大表DDL操作使用Online DDL模式時，在隱藏從庫正在執(zhí)行DDL期間，主庫掛了，那常理就需要切換到隱藏從庫，才能繼續(xù)提供服務(wù)，但為了保證數(shù)據(jù)的一致性，隱藏從庫必須要等DDL執(zhí)行完，再回放DDL之后的binlog，然后，才能將其提升為主庫，對外提供服務(wù)，所以這個恢復(fù)時間有可能很長。總得來講，這個情況對業(yè)務(wù)而言也是致命的，只不過概率極低。

?

2.2 pt-osc

?機(jī)制：創(chuàng)建一個新臨時表，并在老表上創(chuàng)建3個觸發(fā)器，再進(jìn)行新老表的數(shù)據(jù)同步，直至新老表數(shù)據(jù)一致后，再進(jìn)行表名互換，達(dá)到表變更的目的。不同的DDL，只要pt-osc支持，他的操作方式都是一樣的，這點與Online DDL完全不同。

?優(yōu)點：

?可以設(shè)置相應(yīng)的參數(shù)，根據(jù)主、從庫負(fù)載（比如復(fù)制延遲）的情況，動態(tài)調(diào)整數(shù)據(jù)拷貝速度，整個表結(jié)構(gòu)變更過程相對比較溫和。

?不會引發(fā)從庫復(fù)制延遲超級大的情況；

?執(zhí)行完后，新表會將老表占用的碎片空間完全釋放掉。

?缺點：

?需要將老表的所有數(shù)據(jù)都拷貝到新表上，這就意味著拷貝期間，磁盤IO可能較高。

?要拷貝全量數(shù)據(jù)，所以執(zhí)行時間也會很長。

?新臨時表存放數(shù)據(jù)也需要空間（最大空間需求可能和原表一樣），拷貝數(shù)據(jù)時還會產(chǎn)生大量binlog，所以對于本來空間就緊張的實例而言，這方式真的是雪上加霜。

?

2.3 加鎖

加鎖情況，想必是大家使用時關(guān)心較多的一個問題，但是，我想說，MySQL 5.7加鎖情況會比你想象中的要好。Online DDL及pt-osc，大部分情況下，只會在執(zhí)行前后加表元數(shù)據(jù)鎖，其在獲取到表元數(shù)據(jù)鎖并上鎖后，在極短時間內(nèi)做完后續(xù)相關(guān)動作，緊接著就會將鎖釋放掉。Online DDL除特殊操作外（后面會說明），大部分情況下，不會對現(xiàn)有業(yè)務(wù)造成堵塞影響。在業(yè)務(wù)足夠繁忙時，反而有可能會出現(xiàn)表結(jié)構(gòu)變更操作獲取不到表元數(shù)據(jù)鎖（鎖等待超時），從而導(dǎo)致執(zhí)行失敗的情況。

?

三、各種DDL操作

在具體分析后面各種DDL之前，咱統(tǒng)一假設(shè)要操作的表足夠大，要不然表太小的話，不管什么方式都是瞬間完成，就沒有對比的意義了。另，編寫的DDL語句，如果想用Online DDL方式，咱也不需要刻意去指定 ALGORITHM 及 LOCK 選項，就讓MySQL自動判斷后默認(rèn)選擇就好。

?

下面，咱就從MySQL官方文檔Online DDL對各種操作的支持角度去分析兩種方式的差異情況。當(dāng)然，如果你的DDL語句同時含有好幾種不同的操作，那就以最壞的那種情形做參考即可。

?

3.1 索引操作

圖一：Online DDL 索引操作

??

?

3.1.1 創(chuàng)建普通二級索引

Online DDL：從圖一，我們可以看出，這會選擇In Place的方式執(zhí)行，整個過程，只會涉及到拷貝二級索引列相關(guān)的數(shù)據(jù)用于創(chuàng)建索引，所以需要拷貝的數(shù)據(jù)，相對于pt-osc而已，肯定會少很多，反過來說，執(zhí)行需要的時間也相對會少。如果沒從庫，不存在復(fù)制延遲的問題，那選擇Online DDL顯然會比pt-osc更優(yōu)；但如果有從庫，那復(fù)制延遲的問題，自然是需要考慮的，而且大表復(fù)制延遲的時間，當(dāng)然也會較長，如果接受不了延遲，那直接選pt-osc就好。

?

pt-osc：復(fù)制整個表的數(shù)據(jù)用于建新表，優(yōu)勢是有從庫時，幾乎不存在復(fù)制延遲的問題；劣勢也很明顯，因為拷貝整個表的數(shù)據(jù)，所以時間長，同時磁盤IO也會變高。

?

3.1.2 刪除索引、索引重命名

Online DDL：從圖一可以看到Only Modifies Metadata對應(yīng)的是YES，也就意味著僅修改元數(shù)據(jù)，速度非?？?，幾乎瞬間完成，必選Online DDL。

?

pt-osc：直接無視

?

3.1.3 變更索引類型、全文索引、空間索引

咱現(xiàn)在使用的索引類型基本上都是BTREE，幾乎很少用到HASH，同時也很少見到有用全文索引及空間索引的，所以，這幾種咱就不討論了。

?

3.2 唯一索引及主鍵操作

圖二：Online DDL主鍵操作

??

?

因為pt-osc拷貝數(shù)據(jù)的過程，會依賴于唯一鍵（主鍵或者唯一索引）來校驗數(shù)據(jù)的一致性，對唯一鍵進(jìn)行相關(guān)的操作可能會引發(fā)各種各樣的問題，所以不管pt-osc實際支持或不支持這類操作，咱都直接默認(rèn)為不支持就好。也就是涉及主鍵及唯一索引相關(guān)的操作，都直接選Online DDL。但是，需要注意的是，單獨刪除主鍵的操作，會引發(fā)鎖表，導(dǎo)致不允許對表進(jìn)行其他增刪改的操作，也就是增刪改會被堵塞住，這操作需要慎重考慮。而同一個DDL里面，刪除老主鍵的同時又加上新主鍵，是不會引起堵塞的。

?

3.3 列操作

圖三：Online DDL列操作

??

?

3.3.1 添加列、刪除列、重排列順序、變更列類型、修改列為空或非空

Online DDL：從圖三可以看出，這6種DDL操作，在選擇Online方式時，都會重建表，效果上與pt-osc并無太大差別，還得擔(dān)憂從庫復(fù)制延遲的問題，那既然如此，直接選擇pt-osc的方式更省事。

?

Online DDL以下這幾種情況會鎖表，堵塞其他增刪改操作，需要注意：

1）增加一個自增列。

2）單純修改列類型。

3）修改列名，同時修改了列類型（該情形應(yīng)該算修改列類型的一個特殊例子）（只支持 Online DDL）。

?

pt-osc：首選。需要注意的是，因為pt-osc不支持修改列名，所以上述的第三點，只能選擇Online DDL的方式執(zhí)行，但是選擇Online DDL，又會出現(xiàn)鎖表導(dǎo)致堵塞其他增刪改的操作，所以慎重。

?

3.3.2 VARCHAR列增加列大小

Online DDL：MySQL底層在存儲變長列VARCHAR列的內(nèi)容時，還會額外記錄內(nèi)容占用字節(jié)數(shù)的大小，記錄這個大小，也是需要空間的。另外，還有個東西咱需要了解下，VARCHAR列存儲一個字符，使用utf8字符集時，最大需要3字節(jié)（比如存儲一個中文字符），而utf8mb4最大需要4字節(jié)（比如存儲一個表情符）。知道了使用什么字符集，咱就可以計算出存儲一個VARCHAR變長列最大需要多少字節(jié)了。

?

列存儲最大需要字節(jié)數(shù) = VARCHAR列定義的長度 * 不同字符集存儲單個字符需要最大字節(jié)數(shù)

?

而記錄列占用字節(jié)數(shù)大小，所需的空間，會根據(jù) 列存儲最大需要字節(jié)數(shù) 細(xì)分出兩種情況：

1）列存儲最大需要字節(jié)數(shù)為0-255時，記錄列占用字節(jié)數(shù)大小需要1字節(jié)。

2）列存儲最大需要字節(jié)數(shù)為256-65535時，記錄列占用字節(jié)數(shù)大小需要2字節(jié)。

3）因表數(shù)據(jù)行，非大對象的列，總的存儲內(nèi)容長度限制就是65535，所以，單列VARCHAR存儲需求自然也不能超過這個限制，也就是不存在超過65535的情況。

?

回歸正題，VARCHAR列增加大?。?/p>

1）如果列長度增加后，記錄列占用字節(jié)數(shù)大小所需字節(jié)數(shù)不變，也就是列存儲最大需要字節(jié)數(shù)依然在同一個范圍內(nèi)：0-255或256-65535，那這類操作，ALGORITHM支持使用 In Place算法，只會修改表的元數(shù)據(jù)信息，瞬間完成，此情形，直接選Online DDL即可。

2）如果列長度增加后，記錄列占用字節(jié)數(shù)大小所需字節(jié)數(shù)變了，從1字節(jié)變成2字節(jié)，ALGORITHM 則只支持COPY算法，這就意味著會出現(xiàn)數(shù)據(jù)拷貝的情況，同時會堵塞其他增刪改的操作，這情形選pt-osc。

?

例子：

# 建表
create table t1(name varchar(10) null) charset=utf8mb4;

# 列存儲最大需要字節(jié)數(shù)計算：長度 10，utf8mb4字符集存儲單字符最大需要字節(jié)數(shù) 4
# 列存儲最大需要字節(jié)數(shù) = 10 * 4 = 40
# 記錄列占用字節(jié)數(shù)大小所需空間為1字節(jié)


# 表結(jié)構(gòu)變更一
alter table t1 modify name varchar(63) null;

# 列存儲最大需要字節(jié)數(shù)計算：長度 63，utf8mb4字符集存儲單字符最大需要字節(jié)數(shù) 4
# 列存儲最大需要字節(jié)數(shù) = 63 * 4 = 252
# 記錄列占用字節(jié)數(shù)大小所需空間依然為1字節(jié)，ALGORITHM默認(rèn)選用In Place，Online DDL執(zhí)行瞬間完成


# 表結(jié)構(gòu)變更二
alter table t1 modify name varchar(64) null;

# 列存儲最大需要字節(jié)數(shù)計算：長度 64，utf8mb4字符集存儲單字符最大需要字節(jié)數(shù) 4
# 列存儲最大需要字節(jié)數(shù) = 64 * 4 = 256
# 記錄列占用字節(jié)數(shù)大小所需空間變?yōu)?字節(jié)，ALGORITHM只能使用COPY，引發(fā)數(shù)據(jù)拷貝，堵塞其他增刪改操作，選擇pt-osc

?

pt-osc：根據(jù)上述信息選合適的。

?

3.3.3 修改列名（只改列名）、設(shè)置/刪除默認(rèn)值、修改ENUM/SET列定義

Online DDL：從圖三可以看出，這類操作會只修改表元數(shù)據(jù)信息，速度極快，直接選Online DDL方式即可

?

pt-osc：直接無視

?

3.3.4 修改自增列的自增值

Online DDL：在MySQL 8.0版本前，自增值不存在持久化的概念，修改這個值，只會在內(nèi)存中修改，更不涉及數(shù)據(jù)的拷貝及變動，所以直接使用Online DDL方式即可。

?

pt-osc：直接無視

?

3.4 Generated列操作

圖四：Online DDL 虛擬列操作

??

?

3.4.1 新增/刪除Generated虛擬列

Online DDL：虛擬列不涉及數(shù)據(jù)存儲的問題，所以新增和刪除都只會涉及到表元數(shù)據(jù)的變更，幾乎瞬間完成，直接選用Online DDL方式執(zhí)行即可。

?

pt-osc：直接無視

?

3.4.2 新增/修改/刪除Generated存儲列、修改Generated虛擬列順序

Online DDL：這幾種操作，在選擇Online DDL時，都會涉及到表重建的問題，大表執(zhí)行時間不會短，另外，新增/修改Generated存儲列 以及 修改Generated虛擬列順序，都會鎖表，引發(fā)堵塞其他增刪改操作，所以，建議選pt-osc。

?

pt-osc：首選

?

3.5 外鍵操作

圖五：Online DDL外鍵操作

??

?

如果表存在外鍵依賴，后期對父表進(jìn)行各種DDL操作時，數(shù)據(jù)庫會有較大的風(fēng)險，嚴(yán)重的甚至?xí)i表，所以不建議用外鍵。

?

3.6 表操作

圖六：Online DDL表操作

??

?

3.6.1 修改表名

Online DDL：從圖六看出，Online DDL修改表名，只會涉及到修改表的元數(shù)據(jù)信息，瞬間完成。

?

pt-osc：不支持

?

3.6.2 表碎片整理、更改行格式、修改字符集、收集統(tǒng)計信息

Online DDL：咱對表的操作，常用到的，可能就是表碎片整理、更改行格式（比如改成壓縮模式），修改字符集（含內(nèi)容轉(zhuǎn)換）以及收集統(tǒng)計信息，這些操作，從圖六也看到了，基本都是需要重建表，建議首選pt-osc。

?

pt-osc：首選

?

3.6.3 其他相關(guān)的表操作

其他操作平?；竞苌儆玫?，暫時不討論。

?

3.7 表空間操作

圖七：Online DDL表空間操作

??

?

在實際使用中，幾乎見不到，咱就不討論了。

?

3.8 表分區(qū)操作

圖八：Online DDL表分區(qū)操作

??

?

表分區(qū)相關(guān)的操作較多，咱就挑比較常用的進(jìn)行分析，其他操作不做贅述。

?

3.8.1 普通表轉(zhuǎn)分區(qū)表

Online DDL：他的本質(zhì)是新建一個臨時表，每個分區(qū)對應(yīng)一個數(shù)據(jù)文件，然后進(jìn)行拷貝，拷貝完畢后，表名互換，刪除老表。眼熟不？從某種程度上講，這個過程與pt-osc是相似的，但是，Online DDL方式會鎖表，堵塞其他增刪改操作，所以，直接選擇pt-osc方式即可。

?

pt-osc：必選

?

3.8.2 新增分區(qū)、刪除分區(qū)、TRUNCATE分區(qū)

Online DDL：

新增分區(qū)，只分析常用的RANGE及LIST分區(qū)。RANGE分區(qū)新增分區(qū)，有個嚴(yán)格的限制，新分區(qū)less than的值必須是遞增的，換句話講就是不存在數(shù)據(jù)拷貝的問題。LIST分區(qū)，這個更直接，相關(guān)內(nèi)容如果在LIST分區(qū)中不存在，直接不允許插入，新增分區(qū)也不存在數(shù)據(jù)拷貝的問題。所以，這兩種選用Online DDL時，操作幾乎都是瞬時完成的，直接使用Online DDL即可。

?

刪除分區(qū)時，會對當(dāng)前分區(qū)上鎖，堵塞該分區(qū)的其他增刪改操作，但既然你都打算刪除分區(qū)了，想必自然也不會再對該分區(qū)有其他操作。 其對應(yīng)系統(tǒng)底層的操作，類似于直接將分區(qū)對應(yīng)的物理文件進(jìn)行刪除，操作時，如果文件足夠大，系統(tǒng)IO會瞬間暴漲，繼而影響業(yè)務(wù)，所以建議在業(yè)務(wù)低峰期間進(jìn)行。

?

TRUNCATE分區(qū)操作，對應(yīng)系統(tǒng)底層的操作，類似于直接將分區(qū)對應(yīng)的物理文件進(jìn)行清空，操作時，如果文件足夠大，系統(tǒng)IO會瞬間暴漲，繼而影響業(yè)務(wù)，所以建議在業(yè)務(wù)低峰期間進(jìn)行。

?

pt-osc：直接無視

?

四、結(jié)束語

從上面的介紹可以看出，DDL相關(guān)的操作較多，想要完全記住各種操作選那種方式最合適，想必也是件費神的事情。如果上面信息對你有用，點贊收藏起來，用到時再慢慢參考即可。

審核編輯 黃宇