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>call pro_test(); mysql>call pro_test(); mysql>call pro_test(); mysql>call pro_test(); mysql>call pro_test(); 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;
+-------+---------------------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| NAME | varchar(50) | NO | | NULL | |
| addr | varchar(10) | YES | | NULL | |
| 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; +-------+---------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | NAME | varchar(50) | NO | MUL | NULL | | | addr | varchar(10) | YES | | NULL | | | 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)
