MySQL 8.0 instant 新增和刪除列

資料庫工作筆記發表於2024-02-20
MySql

來源：myabc


MySQL 8.0.12 開始, 以下的 alter table 操作支援 ALGORITHM=INSTANT 演演算法：

1.新增新的列。即 instant add column

2.新增或者刪除虛擬列

3.新增或者刪除列的預設值

4.修改 enum、set 型別列的定義

5.修改索引型別

6.重新命名錶

 

支援 ALGORITHM=INSTANT 演演算法的操作，只會修改資料字典的後設資料。不需要在表上施加後設資料鎖，也不影響表的資料，從而使得操作可以瞬間完成。

即使沒有顯式的加上 ALGORITHM=INSTANT 選項，只要操作支援 instant 操作，就會預設使用該特性；如果是不支援的操作，顯式加上 ALGORITHM=INSTANT 選項會失敗並報錯。

 

MySQL 8.0.28 開始, InnoDB 支援 ALTER TABLE ... RENAME COLUMN 操作使用 ALGORITHM=INSTANT。

MySQL 8.0.29 開始, InnoDB 支援 ALTER TABLE ... DROP COLUMN 操作使用 ALGORITHM=INSTANT。在 8.0.29 之前， instant 新增列，只能是加在表的最後，從8.0.29開始，可以在表的任意位置新增。

ALGORITHM=INSTANT 是如何工作的

表後設資料中引入了行版本這一新概念。該特性的工作原理是，在插入一條記錄時，會給該記錄打上"行版本"的印記，也就是在表後設資料中當前的行版本。如果表沒有經過任何 alter table ... add/drop column, 表中所有行的行版本號就是0。在記錄的頭部，有4個位(bit)用於後設資料，即 info-bits，其中有一個 bit 是沒有使用的。因此該 bit 被用來表示記錄是否有行版本。預設情況下，該 bit 是沒有被置位的。如果該 bit 被置位了，則會在記錄頭部儲存記錄版本號。這樣，就容易實現在不重構表的情況下，執行 "alter table ... add/drop column"，此外使用已經存在的一個 bit，instant add/drop ddl 在升級表的時候也可以正常工作。

 

每個 "alter table ... add/drop column" 語句，都會建立一個行版本。在 alter table 語句執行後插入的記錄會使用新的行版本號標記。因此，執行過多次 alter table 操作的表，可能存在多個不同的行版本。一旦記錄被讀取，就會轉換成表後設資料的最新的行版本號。當前的行版本是表的後設資料資訊，而記錄的行版本是行後設資料。

 

行版本只是繫結了 alter table 操作，而不是繫結了每個被新增、刪除的列。比如，一個"alter table ... add/drop column" 新增了n個列，刪除了m個列，當時行版本只是增加一。

版本限制

instant 新增或刪除列，行版本號後設資料最多支援64個版本。可以透過information_schema.innodb_tables.total_row_versions 列來檢視。如果"alter table ... add/drop column"超過64次，就會切換成老的方式，進行表的重構。所以，超過 64 次之後還顯式指定 algorithm=instant ，會報如下錯誤：


ERROR 4080 (HY000): Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE.

行版本演示






































root@localhost abc>create table myabc(id int);
Query OK, 0 rows affected (0.01 sec)
root@localhost abc>select name,total_row_versions from information_schema.innodb_tables where name like '%myabc';+-----------+--------------------+| name      | total_row_versions |+-----------+--------------------+| abc/myabc |                  0 |+-----------+--------------------+1 row in set (0.03 sec)
root@localhost abc>alter table myabc add column name char(10) first, algorithm=instant;Query OK, 0 rows affected (0.05 sec)Records: 0  Duplicates: 0  Warnings: 0
root@localhost abc>select name,total_row_versions from information_schema.innodb_tables where name like '%myabc';+-----------+--------------------+| name      | total_row_versions |+-----------+--------------------+| abc/myabc |                  1 |+-----------+--------------------+1 row in set (0.02 sec)
root@localhost abc>alter table myabc drop column name, algorithm=instant;Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0
root@localhost abc>select name,total_row_versions from information_schema.innodb_tables where name like '%myabc';+-----------+--------------------+| name      | total_row_versions |+-----------+--------------------+| abc/myabc |                  2 |+-----------+--------------------+1 row in set (0.00 sec)
root@localhost abc>

表重建和 truncate 表

其它型別的 alter table 操作，比如 optimizer table 會重建表。一旦表被重建，instant 後設資料就會被清除。



























root@localhost abc>select name,total_row_versions from information_schema.innodb_tables where name like '%myabc';+-----------+--------------------+| name      | total_row_versions |+-----------+--------------------+| abc/myabc |                  2 |+-----------+--------------------+1 row in set (0.00 sec)
root@localhost abc>optimize table myabc;+-----------+----------+----------+-------------------------------------------------------------------+| Table     | Op       | Msg_type | Msg_text                                                          |+-----------+----------+----------+-------------------------------------------------------------------+| abc.myabc | optimize | note     | Table does not support optimize, doing recreate + analyze instead || abc.myabc | optimize | status   | OK                                                                |+-----------+----------+----------+-------------------------------------------------------------------+2 rows in set (0.03 sec)
root@localhost abc>select name,total_row_versions from information_schema.innodb_tables where name like '%myabc';+-----------+--------------------+| name      | total_row_versions |+-----------+--------------------+| abc/myabc |                  0 |+-----------+--------------------+1 row in set (0.02 sec)
root@localhost abc>

truncate 表也是如此。

 

是否真是瞬間完成

可以透過時間對比，來確認是否真是瞬間完成。
















root@localhost abc> select count(*) from myabc;+----------+| count(*) |+----------+|  9000000 |+----------+1 row in set (0.32 sec) root@localhost abc> alter table myabc add column name char(10), algorithm=copy;Query OK, 9000000 rows affected (39.01 sec)Records: 9000000  Duplicates: 0  Warnings: 0 root@localhost abc> alter table myabc add column name2 char(10), algorithm=instant;Query OK, 0 rows affected (0.10 sec)Records: 0  Duplicates: 0  Warnings: 0


來自 “ ITPUB部落格 ” ，連結：https://blog.itpub.net/70027826/viewspace-3006815/，如需轉載，請註明出處，否則將追究法律責任。

相關文章