MySQL 8.0 InnoDB对即时加字段的会支持（instant add column）（译）

2022-10-04 19:42:08

原文地址：https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/

长期以来，即时DDL一直是最受欢迎的InnoDB功能之一。对于越来越大且快速增长的数据集，任何网络规模数据库中必须具备立即执行DDL的功能。

开发人员经常需要添加新列，以满足不断变化的业务需求。即时加字段（add column）的功能是我们一系列instantly DDL语句中的第一个。

在MySQL 8.0中迁移到新的事务数据字典使我们的这项工作变得容易得多。在MySQL 8.0之前，元数据（数据字典）存储在称为.frm文件的平面文件中， .frm文件是一种不可思议的格式，已近过时很久了。

该即时加列补丁是由腾讯游戏数据库管理员团队提供的，我们要感谢并感谢腾讯游戏所做的重要而及时的贡献。

背景

MySQL 5.6是第一个支持INPLACE DDL的版本。在MySQL 5.6之前，执行DDL的唯一方法是逐行复制行。
INPLACE DDL主要由InnoDB处理，而逐行COPY在服务器层处理。直到8.0（请参阅实验版本），InnoDB甚至通过为INPLACE DDL算法重建表来向表中添加列。

对于大型表，可能要花费很长时间，尤其是在复制环境中。
磁盘空间需求将增加一倍以上，大小与现有表大致相同。
DDL操作占用资源，并且对CPU，内存和IO提出了很高的要求，这从用户事务中争夺资源。
如果涉及复制，slave要一直要等待到DDL的完成，才能开始同步。

新的即时（instant）算法

许多用户向我们询问了如何避免耗时的schema changes。现在，可以通过（始终）指定ALGORITHM = INSTANT来实现，这将保证操作立即完成（如果不支持则无法完成）。
此外，如果根本未指定ALGORITHM，则服务器将首先尝试DEFAULT = INSTANT算法，如果无法完成，则服务器将尝试INPLACE算法；如果SE无法支持，服务器将最终尝试COPY算法。
新语法如下：

ALTER TABLE table_name [alter_specification], ALGORITHM=INSTANT;

INSTANT算法的优势在于，仅在数据字典中进行元数据更改。 SE更改期间无需获取元数据锁定，也不会touch表中的数据。
此更改也会影响LOCK = ...语义。无需为即时算法指定LOCK。如果使用ALGORITHM = INSTANT，则LOCK不能设置为DEFAULT以外的任何其他值，否则会出现错误：

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
# ALGORITHM=INSTANT and LOCK=DEFAULT are OK though.
ALTER TABLE t1 ALTER COLUMN i SET DEFAULT 13, ALGORITHM=INSTANT, LOCK=DEFAULT;

如果将ALGORITHM = INSTANT设置为无法不支持的DDL，则会出现错误，如下所示。这里的想法是不支持的情况下会直接失败，而不会默认转换并切换到幕后的另一种算法。

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的即时DDL支持如下操作

Change index option
Rename table (in ALTER way)
SET/DROP DEFAULT
MODIFY COLUMN
Add/drop virtual columns
Add columns(non-generated) – 我们称之为即时DDL

你可以在一个语句中指定不止一个即时（instant）操作，这里是一下即时（instant）操作的示例

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)

它的工作原理是什么

我们面临的问题是，在立即添加列后元数据发生更改后，如何解析页面上的物理记录？

请注意，此处的物理记录是指存储在聚集索引的叶页中的记录。聚簇索引的现有二级索引甚至非叶页（B树的内部节点）都不会受到影响。

InnoDB有两种主要的行格式，即冗余行和紧凑行格式。行格式动态是compact的一个较小变体。压缩及其派生的行格式从冗余行格式中删除了一些元数据，以节省空间。

由于这种“节省空间”的更改，当我们必须对页面上物理行中的数据进行反序列化时，我们总是需要从内部元数据结构中查找元数据。

为了使即时添加列起作用，我们需要为页面上的DYNAMIC和COMPACT行格式的物理记录添加一些元数据。 REDUNDANT行格式不需要此附加元数据，因为列数已存储在物理记录中。

额外的信息与数据字典中的一些元数据一起保留在物理记录中。

这与基于相同腾讯补丁的一些下游黑客的做法非常不同，后者在表空间的模糊和未使用的部分存储类似的元数据。

我们认为，将元数据存储在适当的数据字典表中并使其在事务上保持一致将使其更健壮且更自然。此新的元数据存储在物理记录中。

这个新的元数据包括一个存储在info_bits中的标志。 info_bits中的此新信息用于跟踪是否在第一个即时ADD COLUMN之后创建记录。

我们还使用info_bits跟踪物理记录中的字段/列数。当表经历第一个即时ADD COLUMN时的列数以及新添加的列的所有默认值都存储在数据字典中。

这两条信息存储在数据字典表的se_private_data列中。

有了这些额外的信息，现在可以立即执行ADD COLUMN操作，而无需修改表中的任何行。如果没有即时的ADD COLUMN，则表中的所有行将采用与以前相同的格式。

即时发出ADD COLUMN后，对该表的任何更新都将以新格式写入行。从数据字典中查找默认值（如果有）。

在每个即时ADD COLUMN中，都会分别跟踪新添加的列的默认值。这些列的默认值可以随时更改。因此，在重建或截断表之后，可以丢弃即时列数和默认值，此外，可以像以前一样将表中的行更改为旧格式。

如果该表是分区表，则不同的分区可能具有不同数量的即时列，并且需要不同数量的默认值。

如果某些分区被重建，截断或重新创建，则分区中的行也可以像以前一样更改为旧格式。

译者注：
即时DDL的原理实现描述的确实比较晦涩，其原理下面两张图大概也能表述出来，简单说就是：相对原始的新增字段就将整张表重建相比，instant加字段的方式进修改元数据来提升性能。

以下截图来自于：https://opensource.actionsky.com/20190620-mysql-add-column/，侵删

1，非“即时”加字段的过程：基于行的存储规则发生变化之后（增加字段），整个表的所有行都需要做一次重建（重新生成）

2，“即时”加字段的过程：基于行的存储规则发生变化之后（增加字段），仅修改元数据
MySQL 8.0 InnoDB对即时加字段的会支持（instant add column）（译） ,
3，“即时”加字段之后，查询的处理过程。

4，“即时”加字段后，新增数据的处理
MySQL 8.0 InnoDB对即时加字段的会支持（instant add column）（译）

如何观察（Instant column）

用户可以通过information_schema中的视图观察即时添加列的结果。更具体地说，一些新字段将添加到information_schema.innodb_tables和information_schema.innodb_columns。

请注意，对于可以立即完成的其他操作，无需提供新的观察状态。请参见下面的示例：

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)

可以看到，在innodb_tables中引入了一个新的名为“ instant_cols”的列，该列代表即时列的数量，

而在innodb_columns中引入了两个有关默认值的新列，分别名为“ has_default”和“ default_value”。

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不变。这不再是table的重建！正如我们所看到的，‘instant_cols‘现在设置为2，这意味着在第一个即时ADD COLUMN发生时表中有a列和b列。

在innodb_columns中记住c和d列的默认值。现在，如果has_default为1，则用户可以知道是否立即添加了列。

此外，如果“ has_default”为1，则此列的默认值存储在“ default_value”字段中。d的default_value设置为值1000的内部二进制格式。

mysql> ALTER TABLE t1 ADD COLUMN e VARCHAR(100) DEFAULT ‘Hello MySQL!‘;
Query OK, 0 rows affected (0.06 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                 |
|     1065 | e    |           1 | 48656c6c6f204d7953514c21 |
+----------+------+-------------+--------------------------+
5 rows in set (0.36 sec)

又过一会儿再添加一列，table_id依旧次保持不变。 “ instant_cols”将保持不变，并且还会记住e列的默认值。

Instant column的副作用和权衡

由于即时ADD COLUMN不会再重建表格，因此会有一些副作用：

在较旧的版本中，将预先检查行大小，因此ADD COLUMN在开始时将失败。但是，使用新的即时ADD COLUMN，行大小将仅在以后对行进行更新时进行检查。
在早期版本中，如果表或索引已损坏，则可以通过重建表来“修复”问题。使用即时添加列会带来更多挑战，我们正在寻找减轻这种情况的方法。

Instant column的局限性

当前存在一些局限性：

仅支持在一条语句中添加列，也就是说，如果同一条语句中还有其他非INSTANT操作，则无法立即完成
仅支持最后添加列，不支持在现有列中间
不支持很少使用的COMPRESSED行格式
不支持已经有全文索引的表
不支持DD表空间中的任何表（？？？）
不支持临时表（随COPY一起提供）

如何用原始的方式增加字段

尽管支持即时添加列，但用户仍然可以使用旧的方式（非instant）添加列。

也就是说，如果用户打算通过重建表或复制行来添加列，那么他们仍然可以使用ALGORITHM = INPLACE / COPY发出ALTER TABLE，甚至可以指定FORCE关键字。通过这种方式，添加列将按照传统方式进行。

总结

MySQL现在支持一种称为INSTANT的ALTER TABLE的新算法。与此相关的是，最令人兴奋的部分是，现在可以在InnoDB中立即完成ADD COLUMN（最后一次添加）操作，因此不再是用户的难题。

可以轻松观察到新功能。请尝试使用此令人兴奋的新功能，期待反馈使用中遇到的任何问题！

感谢您使用MySQL！

MySQL 8.0 InnoDB对即时加字段的会支持（instant add column）（译）

码农公寓

它的工作原理是什么

相关文章