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
