MySQL / 系统课时
建立常见 ALTER 能力矩阵
建立常见 ALTER 能力矩阵
线上执行 ALTER TABLE 前,最先要确认的不是语法,而是它在当前表条件下会选择 INSTANT、INPLACE 还是 COPY。本课把 MySQL 8.4 常见变更整理成能力矩阵,并用显式算法把“预期成本”变成可验证的上线约束。
学习目标
- 判断加列、删列、二级索引、默认值和类型变更的首选算法
- 区分“支持
INPLACE”与“不会重建表”这两个概念 - 用显式
ALGORITHM在执行前阻止意外降级到更重的算法
先看算法选择顺序
MySQL 8.4 在省略 ALGORITHM 时,会优先使用 INSTANT;不支持时尝试 INPLACE;再不支持才使用 COPY。这个自动降级对临时操作方便,但对生产发布并不安全:同一条 SQL 在不同表属性、索引状态或列定义下,实际成本可能不同。
三种算法也不能简单理解成“快、中、慢”。INSTANT 主要修改数据字典或行版本元数据;INPLACE 不创建传统整表副本,但某些操作仍会重建并重写整表;COPY 会建立新表结构并复制数据,且通常不允许并发 DML。
常见 ALTER 能力矩阵
下表按 InnoDB 与 MySQL 8.4 的常见情况整理。星号表示还要检查表结构或数据类型限制。
| 变更 | 首选/可用算法 | 是否重建表 | 关键限制 |
|---|---|---|---|
| 添加普通列 | INSTANT*;否则 INPLACE/COPY |
INSTANT 否;INPLACE/COPY 是 |
ROW_FORMAT=COMPRESSED、存在 FULLTEXT、临时表、行版本上限等会限制 INSTANT |
| 删除普通列 | INSTANT*;否则 INPLACE |
INSTANT 否;INPLACE 是 |
与添加列类似;非 INSTANT 路径会重建表 |
| 添加二级索引 | INPLACE |
否 | 需要扫描数据并构建索引,但可允许并发 DML |
| 删除二级索引 | INPLACE |
否 | 主要修改元数据 |
| 设置或删除默认值 | INSTANT |
否 | 只修改元数据 |
| 修改列数据类型 | COPY |
是 | 类型变化通常需要复制并转换数据 |
扩大 VARCHAR |
INPLACE* |
否 | 字节长度编码必须保持同一档;跨越长度字节边界需 COPY |
缩小 VARCHAR |
COPY |
是 | 需要复制并校验数据 |
| 调整列顺序 | INPLACE |
是 | FIRST、AFTER 会重组记录 |
NULL 与 NOT NULL 互改 |
INPLACE* |
是 | 改为 NOT NULL 需要严格 SQL 模式,且现有数据不能含 NULL |
| 添加主键 | INPLACE* |
是 | 聚簇索引重组,成本高 |
| 删除主键且不补新主键 | COPY |
是 | 不能仅靠 INPLACE 完成 |
用显式算法做上线断言
生产 DDL 不应依赖自动降级。可以把“这次必须是 Instant”直接写进 SQL;如果当前表不满足条件,MySQL 会报错,而不是偷偷切换到 INPLACE 或 COPY。
CREATE TABLE ddl_matrix_lab (
id BIGINT PRIMARY KEY,
name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;
ALTER TABLE ddl_matrix_lab
ADD COLUMN status TINYINT NOT NULL DEFAULT 0,
ALGORITHM=INSTANT;
ALTER TABLE ddl_matrix_lab
ALTER COLUMN status SET DEFAULT 1,
ALGORITHM=INSTANT;预期结果
Query OK, 0 rows affected (<TIME>)
Query OK, 0 rows affected (<TIME>)
Query OK, 0 rows affected (<TIME>)实际耗时会随环境变化。这里最重要的不是 0 rows affected,而是两条 ALTER 只有在 INSTANT 可用时才会成功,因此它们同时承担了能力校验的作用。
二级索引则应明确要求 INPLACE 与并发写能力:
ALTER TABLE ddl_matrix_lab
ADD INDEX idx_name (name),
ALGORITHM=INPLACE,
LOCK=NONE;预期结果
Query OK, 0 rows affected (<TIME>)这条语句不会重建整表,但仍需要读取现有记录并构建索引。大表上应继续评估执行时间、排序空间、I/O 与元数据锁,而不能因为“Online DDL”就视为零成本。
记住矩阵的边界
能力矩阵用于做第一轮筛选,最终算法仍由具体表状态决定。INSTANT 加删列会累积行版本;MySQL 8.4 单表最多允许 64 个此类行版本,达到上限后需要通过重建清零。VARCHAR 能否 INPLACE 取决于字符集下的字节长度,而不是只看字符数。
因此,上线单里最好同时记录“目标算法”和“若不满足则失败”的约束。对预期为 INSTANT 的变更显式写 ALGORITHM=INSTANT,对需要在线建索引的变更显式写 ALGORITHM=INPLACE, LOCK=NONE,再把会重建或只能 COPY 的操作提升到高风险变更流程。