mysql添加列 mysql添加列的原理

转载

mob64ca14082604 2023-10-07 18:28:47

文章标签 mysql添加列 mysql 字段服务器 文章分类 MySQL 数据库

背景

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

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

新的即时（instant）算法

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

ALTER TABLE table_name [alter_specification], ALGORITHM=INSTANT;

INSTANT算法—优势
1、仅在数据字典中进行元数据更改。
2、SE更改期间无需获取元数据锁定，也不会touch表中的数据。
3、此更改也会影响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

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

目前,Innodb的即时DDL支持如下操作
1、Change index option
2、Rename table (in ALTER way)
3、SET/DROP DEFAULT
4、MODIFY COLUMN
5、Add/drop virtual columns
6、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)