【翻译】MySQL 8.0中的“即时增加和删除列”功能

发布日期：2023年3月9日 作者：Mayank Prasad
分类：InnoDB
标签：innodb
背景
在数据库中，每行数据都被持久化在磁盘上，对于每一行，列的值都存储在磁盘上。现在，如果要向表中添加新的列或从表中删除旧的列，那么现有行的内容应该被更改以反映表中存在的列的正确值。而且，随着表中行数的增加，因为添加/删除列而修改所有现有行以反映表定义更改所花费的时间也会增加。

InnoDB，MySQL的默认存储引擎，在这里也不例外。因此，MySQL用户最希望得到的功能请求之一，就是能够即时向InnoDB表中添加/删除新的/现有的列。

早期的努力
问题的根源很明确：“需要修改表中所有现有记录以添加/删除列”。因此，如果我们能够找到一种机制，在这种机制中，这种“需求”被省略掉，并且我们只通过修改元数据就能实现添加/删除列。换句话说，“不去触及任何行，只更新元数据”就是我们要解决问题的核心思想。

承认用户的请求，一种早期的使用ALGORITHM=INSTANT实现加列的实现在MySQL 8.0.12中被添加。这种特性使用户能够“即时”向表中添加新的列，而不论表的大小如何。

以下是MySQL 8.0.28中"ALTER TABLE … ADD COLUMN"的行为表现（在MySQL 8.0.29引入后优化方法的情况）：

欢迎使用MySQL监视器。命令结束于；或\g。
您的MySQL连接id为9
服务器版本：8.0.28源代码版本

版权所有(c) 2000，2022，甲骨文和/或其子公司。

Oracle是Oracle Corporation及其
子公司的注册商标。其他名称可能是其各自
所有者的商标。

键入’help;’或’\h’以获取帮助。键入’\c’以清除当前输入语句。

mysql>创建表t1 (c1 char （10）， c2 char （10））；
查询OK，0行受影响（0.00秒）

mysql>alter table t1 add column c3 char(10)， ALGORITHM=INSTANT；
查询OK，0行受影响（0.01秒）
记录：0重复：0警告：0

尽管在这个早期的实现中，我们有一些局限性。

• 使用ALGORITHM=INSTANT，新的列只能作为表的最后一列添加。
• ALGORITHM=INSTANT不支持删除列，仍然需要重建表。

欢迎使用MySQL监视器。命令结束于；或\g。
您的MySQL连接id为9
服务器版本：8.0.28源代码版本

版权所有(c) 2000，2022，甲骨文和/或其子公司。

Oracle是Oracle Corporation及其
子公司的注册商标。其他名称可能是其各自
所有者的商标。

键入’help;’或’\h’以获取帮助。键入’\c’以清除当前输入语句。

mysql> create table t1 (c1 char(10), c2 char(10));
查询OK，0行受影响（0.00秒）

mysql> alter table t1 add column c3 char(10) AFTER c1, ALGORITHM=INSTANT;
错误 1845 (0A000): ALGORITHM=INSTANT不支持此操作。尝试ALGORITHM=COPY/INPLACE。

mysql> alter table t1 add column c3 char(10) FIRST, ALGORITHM=INSTANT;
错误 1845 (0A000): ALGORITHM=INSTANT不支持此操作。尝试ALGORITHM=COPY/INPLACE。

mysql> alter table t1 drop column c2, algorithm=instant;
错误 1845 (0A000): ALGORITHM=INSTANT不支持此操作。尝试ALGORITHM=COPY/INPLACE。

介绍
我们重新开始白板讨论，设计出可以让DROP COLUMN也能够即时执行的方案。在这过程中，我们也考虑了早期INSTANT ADD实现的限制。最终我们提出了一个新的设计，允许用户从表的“任何位置”用ALGORITHM=INSTANT删除列。而且这个设计也可以很容易地被ADD COLUMN，ALGORITHM=INSTANT所使用。所以目标达成了！

这个设计在8.0.29中实现。使用这个新的实现，用户可以：

• 使用ALGORITHM=INSTANT在表的“任何位置”添加新的列
• 使用ALGORITHM=INSTANT从表的“任何位置”删除现有的列

这种设计也遵循了相同的思想，即“不触及任何行，只更新元数据”。因此，添加/删除列操作不再依赖于表的大小。换句话说，对于只有一行的表添加/删除新的/旧的列花费的时间与对拥有100M行的表添加/删除新的/旧的列花费的时间相同。

语法
ALTER TABLE <表名> ADD COLUMN <列名> <列类型> [DEFAULT default_value] [FIRST]/[AFTER 列名]， ALGORITHM=INSTANT;
ALTER TABLE <表名> DROP COLUMN <列名>， ALGORITHM=INSTANT;
注意：
此处ALGORITHM=INSTANT是可选的，因为默认情况下，所有的添加/删除列操作都是用ALGORITHM=INSTANT完成的。
在单个ALTER TABLE STATEMENT中可以添加/删除多个列。

这是怎么工作的？
在表元数据中引入了一个新的行版本的概念。这个特性的工作方式是，当行被插入时，行被标帜为当前表元数据中的行版本。如果表没有经历过任何ALTER TABLE … ADD/DROP COLUMN操作，那么表中的所有行都被认为是行版本0。在记录头部，有4位元数据，被称为“信息位”，其中一位是未使用的。我使用了这个位来表示记录有一个行版本。默认情况下，这个位总是未设置的。如果这个位被设置，则记录版本编号存储在记录头的这个位上。因此，可以很容易地执行“ALTER TABLE … ADD/DROP COLUMN”操作，而无需重建表。并且也使用了一个现有的位，因此升级后的表上的INSTANT ADD/DROP DDL也工作得很好。我认为这些低级细节的详细解释值得写一篇单独的博客，我将在我的后续博客中解释它们。敬请期待！

对于每一个ALTER TABLE … ADD/DROP COLUMN语句，都会创建一个新的行版本。并且在这条ALTER TABLE语句之后插入的任何新行都将被标记为这个新的行版本。所以一个可以猜测，一个表，它已经经过多次ALTER TABLE … ADD/DROP COLUMN操作，可能有多个具有不同行版本的行。一旦从这个表中获取了行，它们会从它们被标记的行版本转换为表元数据中的最新行版本。这个当前的行版本是表元数据，而记录所属的行版本，是行元数据。

注意：行版本只在ALTER TABLE语句中递增，而不是在每个列被添加/删除的情况下递增。也就是说，如果在ALTER TABLE … ADD/DROP COLUMN语句中，n列被添加，m列被删除，行版本只增加1。

版本限制
这种转换和保持这种行版本需要一些幕后的维护工作，因此行版本的数量是有限制的。现在的限制是64。这意味着，在一张表上，最多可以执行64次ALTER TABLE操作来添加/删除列。对于任何进一步的ALTER TABLE … ADD/DROP操作，将返回到旧的方式，即表的重建。我们开始的时候预期64可能是一个足够的数字，因为在现实世界的场景中，添加/删除列可能不是一个非常常规的操作。我们也在寻求反馈。如果这个64的上限显得过小，那么可能在以后的MySQL版本中会增加。注意：这个不可配置。

任何尝试在达到限制后执行与ALGORITHM=INSTANT一起的ADD/DROP操作，都会产生以下错误：

错误 4080 (HY000): 表test/t1已达到最大行版本。无法立即添加或删除更多的列。请尝试使用COPY/INPLACE。
注意：只有当在ALTER TABLE语句中明确地使用了ALGORITHM=INSTANT，上述错误才会被抛出。否则，在版本=64之前，INSTANT算法会被隐式地使用，之后则会隐式地回退到ALGORITHM=INPLACE。

观察结果
INFORMATION_SCHEM。INNODB_TABLES
在INFORMATION_SCHEM。INNODB_TABLES中，新增一列TOTAL_ROW_VERSIONS，显示表的当前行版本。

mysql> create table t1 (c1 char(10));
查询OK，0行受影响（0.05秒）

mysql> SELECT NAME,TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t1%";
+———+——————–+
| NAME | TOTAL_ROW_VERSIONS |
+———+——————–+
| test/t1 | 0 |
+———+——————–+
1行在数据集中（0.01秒）

mysql> alter table t1 add column c0 char(10) first, algorithm=instant;
查询OK，0行受影响（0.05秒）
记录：0 Duplicates：0 警告：0

mysql> SELECT NAME,TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t1%";
+———+——————–+
| NAME | TOTAL_ROW_VERSIONS |
+———+——————–+
| test/t1 | 1 |
+———+——————–+
1行在数据集中（0.01秒）

mysql> alter table t1 drop column c1, algorithm=instant;
查询OK，0行受影响（0.05秒）
记录：0 Duplicates：0 警告：0

mysql> SELECT NAME,TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t1%";
+———+——————–+
| NAME | TOTAL_ROW_VERSIONS |
+———+——————–+
| test/t1 | 2 |
+———+——————–+
1行在数据集中（0.01秒）

表重建（优化表， ALTER TABLE）和删除表
其他的ALTER TABLE操作（如：optimized_table）可能会导致表重建。一旦表被重建，INSTANT元数据就会被清除。也就是说，表会按好像没有执行过INSTANT ADD/DROP COLUMN。

mysql> SELECT NAME,TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t1%";
+———+——————–+
| NAME | TOTAL_ROW_VERSIONS |
+———+——————–+
| test/t1 | 2 |
+———+——————–+
1行在数据集中（0.01秒）

mysql> optimize table t1;
+———+———-+———-+——————————————————————-+
| Table | Op | Msg_type | Msg_text |
+———+———-+———-+——————————————————————-+
| test.t1 | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.t1 | optimize | status | OK |
+———+———-+———-+——————————————————————-+
2行在数据集中（0.11秒）

mysql> SELECT NAME,TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t1%";
+———+——————–+
| NAME | TOTAL_ROW_VERSIONS |
+———+——————–+
| test/t1 | 0 |
+———+——————–+
1行在数据集中（0.02秒）

对于TRUNCATE TABLE也是如此，因为表中不再有任何行，所以它就像一个新建的表一样。

mysql> SELECT NAME,TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t1%";
+———+——————–+
| NAME | TOTAL_ROW_VERSIONS |
+———+——————–+
| test/t1 | 2 |
+———+——————–+
1行在数据集中（0.01秒）

mysql> truncate table t1;
查询OK，0行受影响（0.04秒）

mysql> SELECT NAME,TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t1%";
+———+——————–+
| NAME | TOTAL_ROW_VERSIONS |
+———+——————–+
| test/t1 | 0 |
+———+——————–+
1行在数据集中（0.02秒）

它真的是即时的吗？
这是我在我的系统上试验的方法：

mysql> select count() from t1;
+———-+
| count() |
+———-+
| 8388608 |
+———-+
1行在数据集中（0.22秒）