MySQL Online DDL，還是要謹(jǐn)慎
導(dǎo)讀
MySQL的Online DDL長期飽受詬病，8.0之后有沒有好一些呢...
本文重點(diǎn)討論常見的幾種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的初始準(zhǔn)備和最后結(jié)束兩個階段時通常需要加排他MDL鎖(metadata lock，元數(shù)據(jù)鎖)，除此外，DDL期間不會阻塞DML
- INSTANT，只需修改數(shù)據(jù)字典中的元數(shù)據(jù)，無需拷貝數(shù)據(jù)也無需重建整表，同樣，也無需加排他MDL鎖，原表數(shù)據(jù)也不受影響。整個DDL過程幾乎是瞬間完成的，也不會阻塞DML。這個新特性是8.0.12引入的，再次感謝騰訊互娛DBA團(tuán)隊(duì)的貢獻(xiàn)
執(zhí)行DDL操作時，ALGORITHM選項(xiàng)可以不指定，這時候MySQL按照INSTANT、INPLACE、COPY的順序自動選擇合適的模式。也可以指定ALGORITHM=DEFAULT，也是同樣的效果。如果指定了ALGORITHM選項(xiàng)，但不支持的話，會直接報錯。
當(dāng)采用COPY模式時，這時表里任何的修改數(shù)據(jù)操作，DDL都會被阻塞。COPY模式下會生成臨時新表，操作完成后原表會被刪除，新表被重命名為原表名。當(dāng)DDL開始后，原表上僅能只讀，其他的DML操作也都會被阻塞。COPY過程中，唯一會阻塞只讀的時機(jī)是在清理舊表結(jié)構(gòu)和表定義緩存時。
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模式的幾種情況：
- 在表最后新增一個字段
- 新增或刪除虛擬列
- 新增或刪除字段默認(rèn)值
- 修改ENUM/SET字段定義，上面也有提到過
- 修改索引類型
- 表重命名
3、幾種需要用COPY模式的情況
當(dāng)執(zhí)行ALTER TABLE ADD COLUMN、CHANGE COLUMN、MODIFY COLUMN、ADD INDEX、FORCE 等操作時，會將5.5版本之前的時間類型相關(guān)字段強(qiáng)制升級到高版本，這個升級需要重建整個表，只能用COPY方式。這時如果指定 ALGORITHM=INPLACE 就會報錯了。
當(dāng)有聯(lián)合索引并用于表分區(qū)時，如果修改了聯(lián)合索引列順序的話，也需要用COPY模式。
4、最后用一個表格說明幾種常見操作的模式
| 操作 | Instant | In Place | 重建表 | 可并行DML | 只修改元數(shù)據(jù) |
|---|---|---|---|---|---|
| 新增輔助索引 | 否 | 是 | 否 | 是 | 否 |
| 刪除輔助索引 | 否 | 是 | 否 | 是 | 是 |
| 修改索引名 | 否 | 是 | 否 | 是 | 是 |
| 新增主鍵 | 否 | 是 | 是 | 是 | 否 |
| 刪除主鍵 | 否 | 否 | 是 | 否 | 否 |
| 刪除并同時新增主鍵 | 否 | 是 | 是 | 是 | 否 |
| 新增字段 | 是（追加式） | 是 | 否 | 是 | 否 |
| 刪除字段 | 否 | 是 | 是 | 是 | 否 |
| 修改字段數(shù)據(jù)類型 | 否 | 否 | 是 | 否 | 否 |
| 擴(kuò)展VARCHAR列長度 | 否 | 是 | 否 | 是 | 是 |
| 新增STORED虛擬列 | 否 | 否 | 是 | 否 | 否 |
| 新增VIRTUAL虛擬列 | 是 | 是 | 否 | 是 | 是 |
| 轉(zhuǎn)換表字符集 | 否 | 否 | 是 | 否 | 否 |
| opitmize table | 否 | 是 | 是 | 是 | 否 |
| 修改表名 | 是 | 是 | 否 | 是 | 是 |
最后有兩個提醒
一般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工具，相對更不容易出狀
執(zhí)行ALTER TABLE DDL時，不要節(jié)外生枝指定ALGORITHM=?, LOCK=?選項(xiàng)，因?yàn)镸ySQL會自行判斷該采用哪種方式。本來可以INPLACE的，可能不小心給指定成COPY就悲劇了
