一、前言

關于MySQL DDL表結構變更，各個工單平臺基本上都支持了pt-osc及Online DDL的方式，但是，我相信仍然有一大部分人，不太了解這兩種方式各自的優缺點是啥，以至于實際當中，會稀里糊涂的隨機選一種去執行，選對了固然好，選錯了，自然免不了領導的一頓K，這......當然是開玩笑的哈。

?

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

?

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

?

二、pt-osc及Online DDL執行機制

2.1 Online DDL

?機制：MySQL通過Innodb引擎在內部執行一系列的操作進行表變更，當然，同一個表，不同的DDL，會有不同效果，甚至會出現一天上一個地下的差異，所以不同DDL，后面再對其進行具體分析；如果有從庫，則在主庫執行完成后，從庫再操作一遍，動作和主庫一模一樣（執行時間也很接近）。

?優點：這個咱后面在講具體SQL時再進行具體分析。

?缺點：

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

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

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

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

?

2.2 pt-osc

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

?優點：

?可以設置相應的參數，根據主、從庫負載（比如復制延遲）的情況，動態調整數據拷貝速度，整個表結構變更過程相對比較溫和。

?不會引發從庫復制延遲超級大的情況；

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

?缺點：

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

?要拷貝全量數據，所以執行時間也會很長。

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

?

2.3 加鎖

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

?

三、各種DDL操作

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

?

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

?

3.1 索引操作

圖一：Online DDL 索引操作

??

?

3.1.1 創建普通二級索引

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

?

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

?

3.1.2 刪除索引、索引重命名

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

?

pt-osc：直接無視

?

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

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

?

3.2 唯一索引及主鍵操作

圖二：Online DDL主鍵操作

??

?

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

?

3.3 列操作

圖三：Online DDL列操作

??

?

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

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

?

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

1）增加一個自增列。

2）單純修改列類型。

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

?

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

?

3.3.2 VARCHAR列增加列大小

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

?

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

?

而記錄列占用字節數大小，所需的空間，會根據 列存儲最大需要字節數 細分出兩種情況：

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

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

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

?

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

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

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

?

例子：

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

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


# 表結構變更一
alter table t1 modify name varchar(63) null;

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


# 表結構變更二
alter table t1 modify name varchar(64) null;

# 列存儲最大需要字節數計算：長度 64，utf8mb4字符集存儲單字符最大需要字節數 4
# 列存儲最大需要字節數 = 64 * 4 = 256
# 記錄列占用字節數大小所需空間變為2字節，ALGORITHM只能使用COPY，引發數據拷貝，堵塞其他增刪改操作，選擇pt-osc

?

pt-osc：根據上述信息選合適的。

?

3.3.3 修改列名（只改列名）、設置/刪除默認值、修改ENUM/SET列定義

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

?

pt-osc：直接無視

?

3.3.4 修改自增列的自增值

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

?

pt-osc：直接無視

?

3.4 Generated列操作

圖四：Online DDL 虛擬列操作

??

?

3.4.1 新增/刪除Generated虛擬列

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

?

pt-osc：直接無視

?

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

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

?

pt-osc：首選

?

3.5 外鍵操作

圖五：Online DDL外鍵操作

??

?

如果表存在外鍵依賴，后期對父表進行各種DDL操作時，數據庫會有較大的風險，嚴重的甚至會鎖表，所以不建議用外鍵。

?

3.6 表操作

圖六：Online DDL表操作

??

?

3.6.1 修改表名

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

?

pt-osc：不支持

?

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

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

?

pt-osc：首選

?

3.6.3 其他相關的表操作

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

?

3.7 表空間操作

圖七：Online DDL表空間操作

??

?

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

?

3.8 表分區操作

圖八：Online DDL表分區操作

??

?

表分區相關的操作較多，咱就挑比較常用的進行分析，其他操作不做贅述。

?

3.8.1 普通表轉分區表

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

?

pt-osc：必選

?

3.8.2 新增分區、刪除分區、TRUNCATE分區

Online DDL：

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

?

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

?

TRUNCATE分區操作，對應系統底層的操作，類似于直接將分區對應的物理文件進行清空，操作時，如果文件足夠大，系統IO會瞬間暴漲，繼而影響業務，所以建議在業務低峰期間進行。

?

pt-osc：直接無視

?

四、結束語

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

審核編輯 黃宇