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。

sql
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;

预期结果

text
Query OK, 0 rows affected (<TIME>)
Query OK, 0 rows affected (<TIME>)
Query OK, 0 rows affected (<TIME>)

实际耗时会随环境变化。这里最重要的不是 0 rows affected,而是两条 ALTER 只有在 INSTANT 可用时才会成功,因此它们同时承担了能力校验的作用。

二级索引则应明确要求 INPLACE 与并发写能力:

sql
ALTER TABLE ddl_matrix_lab
  ADD INDEX idx_name (name),
  ALGORITHM=INPLACE,
  LOCK=NONE;

预期结果

text
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 的操作提升到高风险变更流程。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

建立常见 ALTER 能力矩阵

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通