Instant add column功能自MySQL 8.0.12版本引入，INSTANT操作僅修改資料字典中的後設資料。在準備和執行期間，

不會在表上採用獨佔後設資料鎖，並且表資料不受影響，從而使操作立即生效。允許併發DML。

InnoDB僅支援INSTANT進行如下操作：

Change index option 更改索引選項

Rename table (in ALTER way) 重新命名錶（以ALTER方式）

SET/DROP DEFAULT 設定/刪除預設值

Add columns(non-generated) – We call this instant ADD COLUMN 新增列（非生成） - 我們稱之為立即加列

MODIFY COLUMN 修改列

Add/drop virtual columns 新增/刪除虛擬列

新增新列作為表中的最後一列。

新增生成的虛擬列。

刪除生成的虛擬列。

設定現有列的預設值。

刪除現有列的預設值。

更改具有ENUM或SET資料型別的列所允許的值列表。要求是列的儲存大小不會更改。

instant功能存在的限制:

僅支援在一個語句中新增列，即如果同一語句中存在其他非INSTANT操作，則無法立即執行

innodb行格式不能是COMPRESSED。

該表上不能有全文索引。

即時新增的列不能是PK。

只能順序加列, 僅支援在最後新增列，而不支援在現有列的中間新增列

不支援壓縮表

不支援包含任何全文索引的表

不支援臨時表，臨時表只能使用copy的方式執行DDL

不支援那些在資料詞典表空間中建立的表

資料字典中的表不能使用instant演算法

實驗如下:

mysql> CREATE TABLE `test` (

-> `ID` int(11) NOT NULL AUTO_INCREMENT,

-> `NAME` varchar(50) NOT NULL,

-> PRIMARY KEY (`ID`)

-> ) AUTO_INCREMENT=1000;

Query OK, 0 rows affected (0.19 sec)

mysql>delimiter $$

mysql> create procedure pro_test()

-> begin

-> declare id int;

-> set id = 100000;

-> while id>0 do

-> insert into test(name) values ('love');

-> set id = id-1;

-> end while;

-> end $$

Query OK, 0 rows affected (0.04 sec)

mysql>delimiter ;

mysql>call pro_test();

多執行幾次,生成更多的資料量。

mysql>select count(*) from test;

+----------+

| count(*) |

+----------+

| 20547289 |

+----------+

1 row in set (1 min 6.85 sec)

秒加欄位測試:

mysql>alter table test add addr varchar(10),ALGORITHM=INSTANT;

Query OK, 0 rows affected (4.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql>ALTER TABLE test ADD COLUMN c ENUM('a', 'b', 'c'), ALGORITHM = INSTANT;

Query OK, 0 rows affected (0.12 sec)

Records: 0 Duplicates: 0 Warnings: 0

第一次用了4.06秒，第二次用了0.12秒

重新命名:

mysql>ALTER TABLE test RENAME TO t2, ALGORITHM = INSTANT;

Query OK, 0 rows affected (0.19 sec)

mysql>ALTER TABLE t2 RENAME TO test, ALGORITHM = INSTANT;

Query OK, 0 rows affected (0.10 sec)

設定列預設值:

mysql>ALTER TABLE test ALTER COLUMN name SET DEFAULT 100, ALGORITHM = INSTANT;

Query OK, 0 rows affected (0.09 sec)

Records: 0 Duplicates: 0 Warnings: 0

刪除列預設值：

mysql>ALTER TABLE test alter COLUMN name DROP DEFAULT, ALGORITHM = INSTANT;

Query OK, 0 rows affected (0.13 sec)

Records: 0 Duplicates: 0 Warnings: 0

修改列:

mysql>ALTER TABLE test MODIFY COLUMN c ENUM('a', 'b', 'c', 'd', 'e'), ALGORITHM=INSTANT;

Query OK, 0 rows affected (0.09 sec)

Records: 0 Duplicates: 0 Warnings: 0

更改索引，適用於表上已有索引：

mysql>>show index from test \G

*************************** 1. row ***************************

Table: test

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: ID

Collation: A

Cardinality: 19998192

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

Visible: YES

Expression: NULL

*************************** 2. row ***************************

Table: test

Non_unique: 1

Key_name: name

Seq_in_index: 1

Column_name: NAME

Collation: A

Cardinality: 1

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

Visible: YES

Expression: NULL

2 rows in set (0.04 sec)

mysql>ALTER TABLE test DROP index name, ADD index name(name),ALGORITHM = INSTANT;

Query OK, 0 rows affected (0.13 sec)

Records: 0 Duplicates: 0 Warnings: 0

但在其他無索引的列上加新索引是不支援的:

mysql>alter table test ADD index addr(addr),ALGORITHM = INSTANT;

ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation.

Try ALGORITHM=COPY/INPLACE.

增加虛擬列:

ALTER TABLE test ADD COLUMN (d INT GENERATED ALWAYS AS (1 + 1) VIRTUAL), ALGORITHM = INSTANT;

Query OK, 0 rows affected (2.83 sec)

Records: 0 Duplicates: 0 Warnings: 0

ysql>desc test;

+-------+---------------------------+------+-----+---------+-------------------+

+-------+---------------------------+------+-----+---------+-------------------+

| ID | int(11) | NO | PRI | NULL | auto_increment |

| ip | int(11) | YES | | NULL | |

| c | enum('a','b','c','d','e') | YES | | NULL | |

| d | int(11) | YES | | NULL | VIRTUAL GENERATED |

+-------+---------------------------+------+-----+---------+-------------------+

刪除虛擬列:

mysql>ALTER TABLE test DROP COLUMN d, ALGORITHM = INSTANT;

Query OK, 0 rows affected (0.48 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql>desc test;

+-------+---------------------------+------+-----+---------+----------------+

+-------+---------------------------+------+-----+---------+----------------+

| ID | int(11) | NO | PRI | NULL | auto_increment |

| ip | int(11) | YES | | NULL | |

| c | enum('a','b','c','d','e') | YES | | NULL | |

+-------+---------------------------+------+-----+---------+----------------+

5 rows in set (0.04 sec)

但刪除普通列不支援：

mysql>ALTER TABLE test DROP c, ALGORITHM = INSTANT;

ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation.

Try ALGORITHM=COPY/INPLACE.

另外，使用者還可以透過來自information_schema的檢視觀察即時ADD COLUMN的結果：

mysql>SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE 'test%';

+----------+----------------+--------------+

| table_id | name | instant_cols |

+----------+----------------+--------------+

| 1060 | test/child | 0 |

| 1064 | test/t1 | 0 |

| 1065 | test/tbl | 0 |

| 1068 | test/employees | 0 |

| 1072 | test/test_null | 0 |

| 1073 | test/test | 2 |

+----------+----------------+--------------+

6 rows in set (0.00 sec)

MySQL8.0.16秒加欄位(instant add column)功能測試

相關文章