0%

MySQL8.0新特性:Instant Add Column

在MySQL5.7中,Innodb通过重新生成表(即使采用INPLACE DDL算法)向表添加列,其会存在以下问题:

  • 对于大表,可能重建时间会很长
  • 由于过程中会产生临时表,会占用磁盘空间
  • DDL占用较多资源
  • 对于复制,将会造成较大的同步延迟

在MySQL8.0.12开始引入新的算法:INSTANT,这将保证要么立即完成操作,要么根本不进行操作。如果未显示指定ALGORITHM,那么会优先选择INSTANT算法,如果不行再使用INPLACE算法,如果不支持INPLACE算法则使用COPY的方式完成

1
ALTER TABLE table_name [alter_specification], ALGORITHM=INSTANT;

INSTANT算法的好处是仅修改数据字典元数据,在SE更改期间无需获取元数据锁,并且不涉及表数据。在使用INSTANT算法时,无需指定LOCK选项,如果设置为DEFAULT以外的值,将会接收到错误

1
2
SQL> 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

如果为无法立即完成的任何操作设置了INSTANT算法,也将接收到错误

1
2
3

SQL> 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支持INSTANT算法的操作如下:

  • 更改索引选项
  • 重命名表(ALTER)
  • 设置或删除default值
  • 修改列
  • 添加或删除虚拟列
  • 添加列(non-generated)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
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)

我们在使用INSTANT快速添加列之后,可以通过information_schema下的视图来观察表和列的信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
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)

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并未发生修改,因此并没有重建表。instant_cols表示表中存在2个通过INSTANT添加的列,has_default等于1表示INSTANT ADD,其默认值记录在default_value中

工作原理

InnoDB主要有两种行格式,redundant和compact格式。dynamic格式是compact的一个小变种。compact及其派生的行格式从redundant行格式中删除了一些元数据,以节省空间。由于这种”节省空间”的变化,当我们必须对页面上的物理行中的数据进行反序列化时,我们总是需要从内部的元数据结构中查找元数据。为了实现INSTANT ADD,我们需要针对dynamic和compact行格式添加一些元数据到page上的物理记录。redundant则不需要,因为列的数量已经保存在物理记录中。

额外的信息与数据字典中的一些元数据一起保存在物理记录中,其中包含一个存储在info_bits中的标志。info_bits中的信息用于跟踪记录是否在第一个即时添加列之后创建,并且也用于跟踪物理记录中的列数。当表经历第一个INSTANT ADD COULMN时的列数以及新添加的列的所有默认值都存储在数据字典中,这两条信息保存在数据字典表的SE_PRIVATE_DATA列中

如果表上从未发生过instant add column, 则行格式维持不变;如果发生过instant ddl, 那么所有新的记录上都被特殊标记了一个instant标志位, 同时在行头存储了列的个数。由于只支持往后顺序加列,通过列的个数就可以知道这个行记录中包含了哪些列的信息

限制

  • 仅支持在一个语句中添加列,也就是说,如果同一语句中还有其他非即时操作,则无法立即完成
  • 只支持向后添加列,而不是现有列的中间
  • 不支持COMPRESSED格式
  • 不支持已经有任何全文索引的表
  • 不支持驻留在DD表空间中的任何表
  • 不支持临时表
  • 当发生与INSTANT不兼容的DDL时,表数据就会进行重建

参考链接

1、MySQL 8.0: InnoDB now supports Instant ADD COLUMN