導讀

MySQL的Online DDL長期飽受詬病，8.0之后有沒有好一些呢...

本文重點討論常見的幾種Online DDL需求：

• 增加新列(ADD COLUMN)
• 修改列定義(MODIFY COLUMN)
• 增加/刪除索引(ADD/DROP INDEX)

其他的DDL操作相對比較少，所以本文就不討論了。

此外，本文也不討論非InnoDB引擎以及非普通索引(如全文索引、空間索引)的場景。

我們先看下ALTER TABLE時ALGORITHM可以指定的幾種方式：

• COPY ，是指DDL時，會生成(臨時)新表，將原表數(shù)據(jù)逐行拷貝到新表中，在此期間會阻塞DML
• INPLACE，無需拷貝全表數(shù)據(jù)到新表，但可能還是需要IN-PLACE方式(原地，無需生成新的臨時表)重建整表。這種情況下，在DDL的初始準備和最后結束兩個階段時通常需要加排他MDL鎖(metadata lock，元數(shù)據(jù)鎖)，除此外，DDL期間不會阻塞DML
• INSTANT，只需修改數(shù)據(jù)字典中的元數(shù)據(jù)，無需拷貝數(shù)據(jù)也無需重建整表，同樣，也無需加排他MDL鎖，原表數(shù)據(jù)也不受影響。整個DDL過程幾乎是瞬間完成的，也不會阻塞DML。這個新特性是8.0.12引入的，再次感謝騰訊互娛DBA團隊的貢獻

執(zhí)行DDL操作時，ALGORITHM選項可以不指定，這時候MySQL按照INSTANT、INPLACE、COPY的順序自動選擇合適的模式。也可以指定ALGORITHM=DEFAULT，也是同樣的效果。如果指定了ALGORITHM選項，但不支持的話，會直接報錯。

當采用COPY模式時，這時表里任何的修改數(shù)據(jù)操作，DDL都會被阻塞。COPY模式下會生成臨時新表，操作完成后原表會被刪除，新表被重命名為原表名。當DDL開始后，原表上僅能只讀，其他的DML操作也都會被阻塞。COPY過程中，唯一會阻塞只讀的時機是在清理舊表結構和表定義緩存時。

1、以下是支持INPLACE模式的幾種情況：

• 本身是InnoDB表支持的online DDL操作
• 表重命名
• 以下幾種只修改表元數(shù)據(jù)而不修改表數(shù)據(jù)的操作
  • 字段類型為CHAR、VARCHAR、TEXT、ENUM
  • 字符集從UTF8MB3變成UTF8MB4，或者從其他任何字符集改成binary
  • 修改字符集的字段上沒有索引
  • 字段重命名
  • 修改字段默認值
  • 索引重命名
  • 新增、刪除輔助索引
  • 修改索引的可見性
  • ENUM/SET類型字段追加新值，例如('a','b')后面增加'c'，變成('a','b','c')
  • 從8.0.14開始，下面幾種只修改字符集的情況
  • 從8.0.14開始，InnoDB表虛擬列(generated column)上不修改定義類型、表達式、是否允許為NULL約束，例如只修改字段備注內(nèi)容(特么的這有卵用啊)
  • 修改字段的默認值，且該字段不包含在生成虛擬列的表達式內(nèi)，例如只修改字段的NULL屬性

2、以下是支持INSTANT模式的幾種情況：

• 在表最后新增一個字段
• 新增或刪除虛擬列
• 新增或刪除字段默認值
• 修改ENUM/SET字段定義，上面也有提到過
• 修改索引類型
• 表重命名

3、幾種需要用COPY模式的情況

當執(zhí)行ALTER TABLE ADD COLUMN、CHANGE COLUMN、MODIFY COLUMN、ADD INDEX、FORCE 等操作時，會將5.5版本之前的時間類型相關字段強制升級到高版本，這個升級需要重建整個表，只能用COPY方式。這時如果指定 ALGORITHM=INPLACE 就會報錯了。

當有聯(lián)合索引并用于表分區(qū)時，如果修改了聯(lián)合索引列順序的話，也需要用COPY模式。

4、最后用一個表格說明幾種常見操作的模式

最后有兩個提醒

一般DDL操作最好都采用pt-osc或gh-ost這樣的工具來實施，并且實施之前務必要先檢查當前目標表上是否有事務或大查詢未結束，避免嚴重的MDL鎖等待

除了8.0以上版本，除了追加式新增列、表改名、新增虛擬列這三種支持INSTANT的操作可以直接跑DDL，其余的都統(tǒng)統(tǒng)采用pt-osc/gh-osc工具，相對更不容易出狀

執(zhí)行ALTER TABLE DDL時，不要節(jié)外生枝指定ALGORITHM=?, LOCK=?選項，因為MySQL會自行判斷該采用哪種方式。本來可以INPLACE的，可能不小心給指定成COPY就悲劇了