MySQL 8.0 instant 添加和删除列

2024-02-20 22:11 abce 阅读(632) 评论(0) 编辑收藏举报

MySQL 8.0.12 开始, 以下的 alter table 操作支持 ALGORITHM=INSTANT 算法：

1.添加新的列。即 instant add column

2.添加或者删除虚拟列

3.添加或者删除列的默认值

4.修改 enum、set 类型列的定义

5.修改索引类型

6.重命名表

支持 ALGORITHM=INSTANT 算法的操作，只会修改数据字典的元数据。不需要在表上施加元数据锁，也不影响表的数据，从而使得操作可以瞬间完成。

即使没有显式的加上 ALGORITHM=INSTANT 选项，只要操作支持 instant 操作，就会默认使用该特性；如果是不支持的操作，显式加上 ALGORITHM=INSTANT 选项会失败并报错。

MySQL 8.0.28 开始, InnoDB 支持 ALTER TABLE ... RENAME COLUMN 操作使用 ALGORITHM=INSTANT。

MySQL 8.0.29 开始, InnoDB 支持 ALTER TABLE ... DROP COLUMN 操作使用 ALGORITHM=INSTANT。在 8.0.29 之前， instant 添加列，只能是加在表的最后，从8.0.29开始，可以在表的任意位置添加。

ALGORITHM=INSTANT 是如何工作的

表元数据中引入了行版本这一新概念。该特性的工作原理是，在插入一条记录时，会给该记录打上"行版本"的印记，也就是在表元数据中当前的行版本。如果表没有经过任何 alter table ... add/drop column, 表中所有行的行版本号就是0。在记录的头部，有4个位(bit)用于元数据，即 info-bits，其中有一个 bit 是没有使用的。因此该 bit 被用来表示记录是否有行版本。缺省情况下，该 bit 是没有被置位的。如果该 bit 被置位了，则会在记录头部存储记录版本号。这样，就容易实现在不重构表的情况下，执行 "alter table ... add/drop column"，此外使用已经存在的一个 bit，instant add/drop ddl 在升级表的时候也可以正常工作。

每个 "alter table ... add/drop column" 语句，都会创建一个行版本。在 alter table 语句执行后插入的记录会使用新的行版本号标记。因此，执行过多次 alter table 操作的表，可能存在多个不同的行版本。一旦记录被读取，就会转换成表元数据的最新的行版本号。当前的行版本是表的元数据信息，而记录的行版本是行元数据。

行版本只是绑定了 alter table 操作，而不是绑定了每个被添加、删除的列。比如，一个"alter table ... add/drop column" 添加了n个列，删除了m个列，当时行版本只是增加一。

版本限制

instant 添加或删除列，行版本号元数据最多支持64个版本。可以通过information_schema.innodb_tables.total_row_versions 列来查看。如果"alter table ... add/drop column"超过64次，就会切换成老的方式，进行表的重构。所以，超过 64 次之后还显式指定 algorithm=instant ，会报如下错误：

1	`ERROR 4080 (HY000): Maximum row versions reached` `for` `table` `test/t1.` `No` `more columns can be added` `or` `dropped instantly. Please use COPY/INPLACE.`

行版本演示

root@localhost abc>create table myabc(id int);
 
Query OK, 0 rows affected (0.01 sec)
 
root@localhost abc>select name,total_row_versions from information_schema.innodb_tables where name like '%myabc';
+-----------+--------------------+
| name      | total_row_versions |
+-----------+--------------------+
| abc/myabc |                  0 |
+-----------+--------------------+
1 row in set (0.03 sec)
 
root@localhost abc>alter table myabc add column name char(10) first, algorithm=instant;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
root@localhost abc>select name,total_row_versions from information_schema.innodb_tables where name like '%myabc';
+-----------+--------------------+
| name      | total_row_versions |
+-----------+--------------------+
| abc/myabc |                  1 |
+-----------+--------------------+
1 row in set (0.02 sec)
 
root@localhost abc>alter table myabc drop column name, algorithm=instant;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
root@localhost abc>select name,total_row_versions from information_schema.innodb_tables where name like '%myabc';
+-----------+--------------------+
| name      | total_row_versions |
+-----------+--------------------+
| abc/myabc |                  2 |
+-----------+--------------------+
1 row in set (0.00 sec)
 
root@localhost abc>

表重建和 truncate 表

其它类型的 alter table 操作，比如 optimizer table 会重建表。一旦表被重建，instant 元数据就会被清除。

root@localhost abc>select name,total_row_versions from information_schema.innodb_tables where name like '%myabc';
+-----------+--------------------+
| name      | total_row_versions |
+-----------+--------------------+
| abc/myabc |                  2 |
+-----------+--------------------+
1 row in set (0.00 sec)
 
root@localhost abc>optimize table myabc;
+-----------+----------+----------+-------------------------------------------------------------------+
| Table     | Op       | Msg_type | Msg_text                                                          |
+-----------+----------+----------+-------------------------------------------------------------------+
| abc.myabc | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| abc.myabc | optimize | status   | OK                                                                |
+-----------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.03 sec)
 
root@localhost abc>select name,total_row_versions from information_schema.innodb_tables where name like '%myabc';
+-----------+--------------------+
| name      | total_row_versions |
+-----------+--------------------+
| abc/myabc |                  0 |
+-----------+--------------------+
1 row in set (0.02 sec)
 
root@localhost abc>

truncate 表也是如此。

是否真是瞬间完成

可以通过时间对比，来确认是否真是瞬间完成。

root@localhost abc> select count(*) from myabc;
+----------+
| count(*) |
+----------+
|  9000000 |
+----------+
1 row in set (0.32 sec)
  
root@localhost abc> alter table myabc add column name char(10), algorithm=copy;
Query OK, 9000000 rows affected (39.01 sec)
Records: 9000000  Duplicates: 0  Warnings: 0
  
root@localhost abc> alter table myabc add column name2 char(10), algorithm=instant;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0