原文地址：https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/
長期以來，即時DDL一直是最受歡迎的InnoDB功能之一，對于越來越大且快速增長的資料集，任何網路規模資料庫中必須具備立即執行DDL的功能， 開發人員經常需要添加新列，以滿足不斷變化的業務需求，即時加欄位（add column）的功能是我們一系列instantly DDL陳述句中的第一個， 在MySQL 8.0中遷移到新的事務資料字典使我們的這項作業變得容易得多，在MySQL 8.0之前，元資料（資料字典）存盤在稱為.frm檔案的平面檔案中， .frm檔案是一種不可思議的格式，已近過時很久了， 該即時加列補丁是由騰訊游戲資料庫管理員團隊提供的，我們要感謝并感謝騰訊游戲所做的重要而及時的貢獻，背景
MySQL 5.6是第一個支持INPLACE DDL的版本，在MySQL 5.6之前，執行DDL的唯一方法是逐行復制行，
INPLACE DDL主要由InnoDB處理，而逐行COPY在服務器層處理，直到8.0（請參閱實驗版本），InnoDB甚至通過為INPLACE DDL演算法重建表來向表中添加列，
- 對于大型表，可能要花費很長時間，尤其是在復制環境中，
- 磁盤空間需求將增加一倍以上，大小與現有表大致相同，
- DDL操作占用資源，并且對CPU，記憶體和IO提出了很高的要求，這從用戶事務中爭奪資源，
- 如果涉及復制，slave要一直要等待到DDL的完成，才能開始同步，
許多用戶向我們詢問了如何避免耗時的schema changes，現在，可以通過（始終）指定ALGORITHM = INSTANT來實作，這將保證操作立即完成（如果不支持則無法完成），
此外，如果根本未指定ALGORITHM，則服務器將首先嘗試DEFAULT = INSTANT演算法，如果無法完成，則服務器將嘗試INPLACE演算法；如果SE無法支持，服務器將最終嘗試COPY演算法，
新語法如下：
ALTER TABLE table_name [alter_specification], ALGORITHM=INSTANT;INSTANT演算法的優勢在于，僅在資料字典中進行元資料更改， SE更改期間無需獲取元資料鎖定，也不會touch表中的資料，
此更改也會影響LOCK = ...語意，無需為即時演算法指定LOCK，如果使用ALGORITHM = INSTANT，則LOCK不能設定為DEFAULT以外的任何其他值，否則會出現錯誤：
ALTER TABLE t1 ALTER COLUMN i SET DEFAULT 11, ALGORITHM=INSTANT, LOCK=NONE; ERROR HY000: Incorrect usage of ALGORITHM=INSTANT and LOCK=NONE/SHARED/EXCLUSIVE # ALGORITHM=INSTANT and LOCK=DEFAULT are OK though. ALTER TABLE t1 ALTER COLUMN i SET DEFAULT 13, ALGORITHM=INSTANT, LOCK=DEFAULT;如果將ALGORITHM = INSTANT設定為無法不支持的DDL，則會出現錯誤，如下所示，這里的想法是不支持的情況下會直接失敗，而不會默認轉換并切換到幕后的另一種演算法，
ALTER TABLE t1 ALTER COLUMN i SET DEFAULT 12, DROP COLUMN j, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE
當前,Innodb的即時DDL支持如下操作
- Change index option
- Rename table (in ALTER way)
- SET/DROP DEFAULT
- MODIFY COLUMN
- Add/drop virtual columns
- Add columns(non-generated) – 我們稱之為即時DDL
mysql> CREATE TABLE t1 (a INT, b INT, KEY(b)); Query OK, 0 rows affected (0.70 sec) mysql> # Modify the index can be instant if it's a trivial change mysql> ALTER TABLE t1 DROP KEY b, ADD KEY b(b) USING BTREE, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> # Rename the table through ALTER TABLE can be instant mysql> ALTER TABLE t1 RENAME TO t2, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.26 sec) mysql> # SET DEFAULT to a column can be instant mysql> ALTER TABLE t2 ALTER COLUMN b SET DEFAULT 100, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> # DROP DEFAULT to a column can be instant mysql> ALTER TABLE t2 ALTER COLUMN b DROP DEFAULT, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> # MODIFY COLUMN can be instant mysql> ALTER TABLE t2 ADD COLUMN c ENUM('a', 'b', 'c'), ALGORITHM = INSTANT; Query OK, 0 rows affected (0.35 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE t2 MODIFY COLUMN c ENUM('a', 'b', 'c', 'd', 'e'), ALGORITHM=INSTANT; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> # ADD/DROP virtual column can be instant mysql> ALTER TABLE t2 ADD COLUMN (d INT GENERATED ALWAYS AS (a + 1) VIRTUAL), ALGORITHM = INSTANT; Query OK, 0 rows affected (0.38 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE t2 DROP COLUMN d, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.40 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> # Do two operations instantly in the same statement mysql> ALTER TABLE t2 ALTER COLUMN a SET DEFAULT 20, ALTER COLUMN b SET DEFAULT 200, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DROP TABLE t2; Query OK, 0 rows affected (0.36 sec)
它的作業原理是什么
我們面臨的問題是，在立即添加列后元資料發生更改后，如何決議頁面上的物理記錄？ 請注意，此處的物理記錄是指存盤在聚集索引的葉頁中的記錄，聚簇索引的現有二級索引甚至非葉頁（B樹的內部節點）都不會受到影響， InnoDB有兩種主要的行格式，即冗余行和緊湊行格式，行格式動態是compact的一個較小變體，壓縮及其派生的行格式從冗余行格式中洗掉了一些元資料，以節省空間， 由于這種“節省空間”的更改，當我們必須對頁面上物理行中的資料進行反序列化時，我們總是需要從內部元資料結構中查找元資料， 為了使即時添加列起作用，我們需要為頁面上的DYNAMIC和COMPACT行格式的物理記錄添加一些元資料， REDUNDANT行格式不需要此附加元資料，因為列數已存盤在物理記錄中， 額外的資訊與資料字典中的一些元資料一起保留在物理記錄中， 這與基于相同騰訊補丁的一些下游黑客的做法非常不同，后者在表空間的模糊和未使用的部分存盤類似的元資料， 我們認為，將元資料存盤在適當的資料字典表中并使其在事務上保持一致將使其更健壯且更自然，此新的元資料存盤在物理記錄中， 這個新的元資料包括一個存盤在info_bits中的標志， info_bits中的此新資訊用于跟蹤是否在第一個即時ADD COLUMN之后創建記錄， 我們還使用info_bits跟蹤物理記錄中的欄位/列數，當表經歷第一個即時ADD COLUMN時的列數以及新添加的列的所有默認值都存盤在資料字典中， 這兩條資訊存盤在資料字典表的se_private_data列中， 有了這些額外的資訊，現在可以立即執行ADD COLUMN操作，而無需修改表中的任何行，如果沒有即時的ADD COLUMN，則表中的所有行將采用與以前相同的格式， 即時發出ADD COLUMN后，對該表的任何更新都將以新格式寫入行，從資料字典中查找默認值（如果有），在每個即時ADD COLUMN中，都會分別跟蹤新添加的列的默認值，這些列的默認值可以隨時更改，因此，在重建或截斷表之后，可以丟棄即時列數和默認值，此外，可以像以前一樣將表中的行更改為舊格式， 如果該表是磁區表，則不同的磁區可能具有不同數量的即時列，并且需要不同數量的默認值， 如果某些磁區被重建，截斷或重新創建，則磁區中的行也可以像以前一樣更改為舊格式，
譯者注：
即時DDL的原理實作描述的確實比較晦澀，其原理下面兩張圖大概也能表述出來，簡單說就是：相對原始的新增欄位就將整張表重建相比，instant加欄位的方式進修改元資料來提升性能， 以下截圖來自于：https://opensource.actionsky.com/20190620-mysql-add-column/，侵刪 1，非“即時”加欄位的程序：基于行的存盤規則發生變化之后（增加欄位），整個表的所有行都需要做一次重建（重新生成）
2，“即時”加欄位的程序：基于行的存盤規則發生變化之后（增加欄位），僅修改元資料
,
3，“即時”加欄位之后，查詢的處理程序，
4，“即時”加欄位后，新增資料的處理
如何觀察（Instant column） 用戶可以通過information_schema中的視圖觀察即時添加列的結果，更具體地說，一些新欄位將添加到information_schema.innodb_tables和information_schema.innodb_columns， 請注意，對于可以立即完成的其他操作，無需提供新的觀察狀態，請參見下面的示例：
mysql> CREATE TABLE t1 (a INT, b INT); Query OK, 0 rows affected (0.06 sec) mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%t1%'; +----------+---------+--------------+ | table_id | name | instant_cols | +----------+---------+--------------+ | 1065 | test/t1 | 0 | +----------+---------+--------------+ 1 row in set (0.22 sec) mysql> SELECT table_id, name, has_default, default_value FROM information_schema.innodb_columns WHERE table_id = 1065; +----------+------+-------------+---------------+ | table_id | name | has_default | default_value | +----------+------+-------------+---------------+ | 1065 | a | 0 | NULL | | 1065 | b | 0 | NULL | +----------+------+-------------+---------------+ 2 rows in set (0.38 sec)可以看到，在innodb_tables中引入了一個新的名為“ instant_cols”的列，該列代表即時列的數量， 而在innodb_columns中引入了兩個有關默認值的新列，分別名為“ has_default”和“ default_value”，
mysql> ALTER TABLE t1 ADD COLUMN c INT, ADD COLUMN d INT DEFAULT 1000, ALGORITHM=INSTANT; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%t1%'; +----------+---------+--------------+ | table_id | name | instant_cols | +----------+---------+--------------+ | 1065 | test/t1 | 2 | +----------+---------+--------------+ 1 row in set (0.03 sec) mysql> SELECT table_id, name, has_default, default_value FROM information_schema.innodb_columns WHERE table_id = 1065; +----------+------+-------------+---------------+ | table_id | name | has_default | default_value | +----------+------+-------------+---------------+ | 1065 | a | 0 | NULL | | 1065 | b | 0 | NULL | | 1065 | c | 1 | NULL | | 1065 | d | 1 | 800003e8 | +----------+------+-------------+---------------+ 4 rows in set (0.36 sec)請注意，table_id不變，這不再是table的重建！正如我們所看到的，'instant_cols'現在設定為2，這意味著在第一個即時ADD COLUMN發生時表中有a列和b列， 在innodb_columns中記住c和d列的默認值，現在，如果has_default為1，則用戶可以知道是否立即添加了列， 此外，如果“ has_default”為1，則此列的默認值存盤在“ default_value”欄位中，d的default_value設定為值1000的內部二進制格式，
mysql> ALTER TABLE t1 ADD COLUMN e VARCHAR(100) DEFAULT 'Hello MySQL!'; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%t1%'; +----------+---------+--------------+ | table_id | name | instant_cols | +----------+---------+--------------+ | 1065 | test/t1 | 2 | +----------+---------+--------------+ 1 row in set (0.03 sec) mysql> SELECT table_id, name, has_default, default_value FROM information_schema.innodb_columns WHERE table_id = 1065; +----------+------+-------------+--------------------------+ | table_id | name | has_default | default_value | +----------+------+-------------+--------------------------+ | 1065 | a | 0 | NULL | | 1065 | b | 0 | NULL | | 1065 | c | 1 | NULL | | 1065 | d | 1 | 800003e8 | | 1065 | e | 1 | 48656c6c6f204d7953514c21 | +----------+------+-------------+--------------------------+ 5 rows in set (0.36 sec)又過一會兒再添加一列，table_id依舊次保持不變， “ instant_cols”將保持不變，并且還會記住e列的默認值，
Instant column的副作用和權衡 由于即時ADD COLUMN不會再重建表格，因此會有一些副作用：
- 在較舊的版本中，將預先檢查行大小，因此ADD COLUMN在開始時將失敗，但是，使用新的即時ADD COLUMN，行大小將僅在以后對行進行更新時進行檢查，
- 在早期版本中，如果表或索引已損壞，則可以通過重建表來“修復”問題，使用即時添加列會帶來更多挑戰，我們正在尋找減輕這種情況的方法，
- 僅支持在一條陳述句中添加列，也就是說，如果同一條陳述句中還有其他非INSTANT操作，則無法立即完成
- 僅支持最后添加列，不支持在現有列中間
- 不支持很少使用的COMPRESSED行格式
- 不支持已經有全文索引的表
- 不支持DD表空間中的任何表（？？？）
- 不支持臨時表（隨COPY一起提供）
如何用原始的方式增加欄位 盡管支持即時添加列，但用戶仍然可以使用舊的方式（非instant）添加列， 也就是說，如果用戶打算通過重建表或復制行來添加列，那么他們仍然可以使用ALGORITHM = INPLACE / COPY發出ALTER TABLE，甚至可以指定FORCE關鍵字，通過這種方式，添加列將按照傳統方式進行，
總結 MySQL現在支持一種稱為INSTANT的ALTER TABLE的新演算法，與此相關的是，最令人興奮的部分是，現在可以在InnoDB中立即完成ADD COLUMN（最后一次添加）操作，因此不再是用戶的難題， 可以輕松觀察到新功能，請嘗試使用此令人興奮的新功能，期待反饋使用中遇到的任何問題！
感謝您使用MySQL！
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/42266.html
標籤：MySQL