一、前言

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

?

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

?

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

?

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

2.1 Online DDL

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

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

?缺點(diǎn)：

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

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

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

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

?

2.2 pt-osc

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

?優(yōu)點(diǎn)：

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

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

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

?缺點(diǎn)：

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

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

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

?

2.3 加鎖

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

?

三、各種DDL操作

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

?

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

?

3.1 索引操作

圖一：Online DDL 索引操作

??

?

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

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

?

pt-osc：復(fù)制整個(gè)表的數(shù)據(jù)用于建新表，優(yōu)勢(shì)是有從庫(kù)時(shí)，幾乎不存在復(fù)制延遲的問(wèn)題；劣勢(shì)也很明顯，因?yàn)榭截愓麄€(gè)表的數(shù)據(jù)，所以時(shí)間長(zhǎng)，同時(shí)磁盤(pán)IO也會(huì)變高。

?

3.1.2 刪除索引、索引重命名

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

?

pt-osc：直接無(wú)視

?

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

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

?

3.2 唯一索引及主鍵操作

圖二：Online DDL主鍵操作

??

?

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

?

3.3 列操作

圖三：Online DDL列操作

??

?

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

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

?

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

1）增加一個(gè)自增列。

2）單純修改列類型。

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

?

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

?

3.3.2 VARCHAR列增加列大小

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

?

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

?

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

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

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

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

?

回歸正題，VARCHAR列增加大小：

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

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

?

例子：

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

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


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

# 列存儲(chǔ)最大需要字節(jié)數(shù)計(jì)算：長(zhǎng)度 63，utf8mb4字符集存儲(chǔ)單字符最大需要字節(jié)數(shù) 4
# 列存儲(chǔ)最大需要字節(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;

# 列存儲(chǔ)最大需要字節(jié)數(shù)計(jì)算：長(zhǎng)度 64，utf8mb4字符集存儲(chǔ)單字符最大需要字節(jié)數(shù) 4
# 列存儲(chǔ)最大需要字節(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：從圖三可以看出，這類操作會(huì)只修改表元數(shù)據(jù)信息，速度極快，直接選Online DDL方式即可

?

pt-osc：直接無(wú)視

?

3.3.4 修改自增列的自增值

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

?

pt-osc：直接無(wú)視

?

3.4 Generated列操作

圖四：Online DDL 虛擬列操作

??

?

3.4.1 新增/刪除Generated虛擬列

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

?

pt-osc：直接無(wú)視

?

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

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

?

pt-osc：首選

?

3.5 外鍵操作

圖五：Online DDL外鍵操作

??

?

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

?

3.6 表操作

圖六：Online DDL表操作

??

?

3.6.1 修改表名

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

?

pt-osc：不支持

?

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

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

?

pt-osc：首選

?

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

其他操作平常基本很少用到，暫時(shí)不討論。

?

3.7 表空間操作

圖七：Online DDL表空間操作

??

?

在實(shí)際使用中，幾乎見(jiàn)不到，咱就不討論了。

?

3.8 表分區(qū)操作

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

??

?

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

?

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

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

?

pt-osc：必選

?

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

Online DDL：

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

?

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

?

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

?

pt-osc：直接無(wú)視

?

四、結(jié)束語(yǔ)

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

審核編輯 黃宇