1. 问题背景

MySQL 版本:8.0.30 测试表数据量:200 万

在 MySQL 中，研发人员最初执行了以下 SQL 语句，向表 t_email 中添加了一个允许为 NULL 的列 id3，并设置了默认值为 0：

ALTER TABLE t_email ADD COLUMN id3 INT NULL DEFAULT 0;

随后发现需要将该列修改为 NOT NULL，于是尝试执行以下语句：

ALTER TABLE t_email MODIFY COLUMN id3 INT NOT NULL DEFAULT 0;

然而，直接修改时发现无法使用 ALGORITHM=INSTANT，只能使用 ALGORITHM=INPLACE 或 ALGORITHM=COPY，导致操作耗时较长（14.67 秒）。

后续的测试中改为先删除列，再重新添加列的方式，成功使用了 ALGORITHM=INSTANT，速度显著提升。

2. 问题分析

ALGORITHM=INSTANT 的限制

8.0.12 新添加了 INSTANT 算法。

MySQL 的 ALGORITHM=INSTANT 是一种快速修改表结构的算法，但它仅支持特定的 DDL 操作。

添加和删除列是支持的,修改列的 NULL 或 NOT NULL 不支持.

在尝试将列从 NULL 修改为 NOT NULL 时，ALGORITHM=INSTANT 不支持此操作，因此 MySQL 只能使用 ALGORITHM=INPLACE 或 ALGORITHM=COPY，导致操作耗时较长。

Table 17.18 Online DDL Support for Column Operations

ALGORITHM=INPLACE 和 ALGORITHM=COPY 的开销

ALGORITHM=INPLACE：在表上直接修改元数据，但仍可能需要重建表数据。

ALGORITHM=COPY：创建新表并复制数据，完成后删除旧表。这种方式通常较慢，尤其是在表数据量较大时。

删除列并重新添加列的优化

通过删除列并重新添加列的方式，可以绕过 MODIFY COLUMN 的限制，直接使用 ALGORITHM=INSTANT，从而显著提升性能。

3. 解决方案

原始方案

添加允许为 NULL 的列 id3，并设置了默认值为 0

mysql> alter table t_email add column id3 int null default 0;Query OK, 0 rows affected (0.86 sec)Records: 0  Duplicates: 0  Warnings: 0

将列的属性修改为不允许为 NULL,其他不变

mysql> alter table t_email modify column id3 int not null default 0;Query OK, 0 rows affected (14.67 sec)Records: 0  Duplicates: 0  Warnings: 0

优化后的方案

添加允许为 NULL 的列 id3，并设置了默认值为 0。

mysql> alter table t_email add column id3 int null default 0, ALGORITHM=INSTANT;Query OK, 0 rows affected (0.85 sec)Records: 0  Duplicates: 0  Warnings: 0

删除 id3 列，耗时 0.98 秒。

mysql> alter table t_email drop column id3, ALGORITHM=INSTANT;Query OK, 0 rows affected (0.98 sec)Records: 0  Duplicates: 0  Warnings: 0

重新添加 id3 列，将属性改为 NOT NULL，并设置了默认值为 0，耗时 0.68 秒。

mysql> alter table t_email add column id3 int not null default 0, ALGORITHM=INSTANT;Query OK, 0 rows affected (0.68 sec)Records: 0  Duplicates: 0  Warnings: 0

4. 总结

ALGORITHM=INSTANT 的限制：不支持修改列的 NULL 属性。仅支持特定的 DDL 操作（如添加列、删除列）。虽然不会操作不会阻塞 DML,但是在 DDL 刚开始和结束时，都要获取 MDL，如果这两个阶段表上有事务，DDL 会因为获取不到 MDL 而被阻塞。

优化思路：对于不支持 ALGORITHM=INSTANT 的操作，可以尝试通过删除列并重新添加列的方式绕过限制。这种方式在表数据量越大时，性能提升越明显。

文章转载自：ideal_x

原文链接：https://www.cnblogs.com/idealx/p/18715577

体验地址：http://www.jnpfsoft.com/?from=001YH