ITPub博客

首页 > 数据库 > MySQL > MySQL8.0.16秒加字段(instant add column)功能测试

MySQL8.0.16秒加字段(instant add column)功能测试

原创 MySQL 作者:chenfeng 时间:2019-05-22 11:27:56 0 删除 编辑

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)





来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15498/viewspace-2645164/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
交流MySQL,MongoDB和Redis技术。 微信或QQ:410294

注册时间:2015-12-07

  • 博文量
    738
  • 访问量
    1655559