導(dǎo)讀

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

本文重點(diǎn)討論常見的幾種Online DDL需求：

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

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

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

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

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

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

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

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

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

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

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

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

當(dāng)執(zhí)行ALTER TABLE ADD COLUMN、CHANGE COLUMN、MODIFY COLUMN、ADD INDEX、FORCE 等操作時(shí)，會(huì)將5.5版本之前的時(shí)間類型相關(guān)字段強(qiáng)制升級(jí)到高版本，這個(gè)升級(jí)需要重建整個(gè)表，只能用COPY方式。這時(shí)如果指定 ALGORITHM=INPLACE 就會(huì)報(bào)錯(cuò)了。

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

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

最后有兩個(gè)提醒

一般DDL操作最好都采用pt-osc或gh-ost這樣的工具來實(shí)施，并且實(shí)施之前務(wù)必要先檢查當(dāng)前目標(biāo)表上是否有事務(wù)或大查詢未結(jié)束，避免嚴(yán)重的MDL鎖等待

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

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