MySQL8.0大表秒加欄位，是真的嗎？

前言：

很早就聽說 MySQL8.0 支援快速加列，可以實現大表秒級加欄位。筆者自己本地也有8.0環境，但一直未進行測試。本篇文章我們就一起來看下 MySQL8.0 快速加列到底要如何操作。

1.瞭解背景資訊

表結構的變更是業務執行過程中比較常見的需求之一，在 MySQL 的環境中，可以使用 Alter 語句來完成這些操作，這些 Alter 語句對應的操作通常也稱之為 DDL 操作。通常情況下大表的 DDL 操作都會對業務有很明顯的影響，需要在業務空閒，或者是維護的時候做。MySQL 5.7 支援 Online DDL，大部分 DDL 不影響對錶的讀取和寫入，但是依然會消耗非常多的時間，且佔用額外的磁碟空間，並會造成主從延遲。所以大表 DDL 仍是一件令 DBA 頭痛的事。

聽聞 MySQL 8.0 解決了這件令 DBA 頭痛的事，那讓我們來詳細瞭解下吧。想了解新功能，最簡單的方法就是查閱官方文件。查閱官方文件得知，快速加列即 Instant Add Column ，該功能自 MySQL 8.0.12 版本引入，是由騰訊遊戲DBA團隊貢獻。注意一下，此功能只適用於 InnoDB 表。

2.快速加列測試

快速加列採用的是 instant 演算法，使得新增列時不再需要 rebuild 整個表，只需要在表的 metadata 中記錄新增列的基本資訊即可。在 alter 語句後增加 ALGORITHM=INSTANT 即代表使用 instant 演算法， 如果未明確指定，則支援 instant 演算法的操作會預設使用。如果 ALGORITHM=INSTANT 指定但不支援，則操作立即失敗並顯示錯誤。

關於列的 DDL 操作，是否支援 instant 等演算法，官方文件給出了一個表格，現整理如下，星號表示不是全部支援，有依賴項。

操作	Instant	In Place	Rebuilds Table	允許併發DML	僅修改後設資料
新增列	Yes*	Yes	No*	Yes*	No
刪除列	No	Yes	Yes	Yes	No
重新命名列	No	Yes	No	Yes*	Yes
更改列順序	No	Yes	Yes	Yes	No
設定列預設值	Yes	Yes	No	Yes	Yes
更改列資料型別	No	No	Yes	No	No
擴充套件VARCHAR列大小	No	Yes	No	Yes	Yes
刪除列預設值	Yes	Yes	No	Yes	Yes
更改自動增量值	No	Yes	No	Yes	No*
設定列為null	No	Yes	Yes*	Yes	No
設定列not null	No	Yes*	Yes*	Yes	No
修改ENUM/SET列的定義	Yes	Yes	No	Yes	Yes

instant 演算法使用最廣泛的應該是新增列了，可以看到使用該演算法還是有些限制的，一些限制如下：

• 如果 alter 語句包含了 add column 和其他的操作，其中有操作不支援 instant 演算法的，那麼 alter 語句會報錯，所有的操作都不會執行。
• 只能順序加列, 僅支援在最後新增列，而不支援在現有列的中間新增列。
• 不支援壓縮表，即該錶行格式不能是 COMPRESSED。
• 不支援包含全文索引的表。
• 不支援臨時表。
• 不支援那些在資料字典表空間中建立的表。

說的再多不如實際來測下，下面我們以 8.0.19 版本為例來實際驗證下：

# 利用sysbench生成一張1000W的大表
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.19    |
+-----------+
1 row in set (0.00 sec)
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
# 增加無預設值的列
mysql> alter table sbtest1 add column col1 varchar(20), algorithm=instant;
Query OK, 0 rows affected (0.63 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 增加有預設值的列
mysql> alter table sbtest1 add column create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間', algorithm=instant;
Query OK, 0 rows affected (0.58 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 不顯式指定instant演算法
mysql> alter table sbtest1 add column col2 varchar(20);
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 設定列的預設值
mysql> alter table sbtest1 alter column col1 set default 'sql',algorithm=instant;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 指定In Place演算法新增列，(5.7版本新增列使用該演算法)
mysql> alter table sbtest1 add column col_inplace varchar(20),algorithm=inplace;
Query OK, 0 rows affected (1 min 23.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

通過以上測試，我們可以發現，使用 instant 演算法新增列基本都在 1s 內完成，對於大表來說這個速度是非常快的，業務基本無感知。當使用 5.7 版本的 inplace 演算法時，則新增列的時間上升至數分鐘。對比看來 8.0 版本的快速加列功能確實非常實用！

總結：

雖然快速加列存在一些限制， instant 演算法也只適用於部分 DDL 操作，但 8.0 的這項新功能已經足以令人興奮，很大程度上解決了大表加欄位的大難題。通過這篇文章，希望各位能瞭解到這項新功能，是不是想升級到 8.0 了呢，可以著手準確起來了。