MySQL / 系统课时

理解 ALGORITHM=INSTANT

理解 ALGORITHM=INSTANT

本课解决一个上线前最关键的问题:哪些 ALTER TABLE 能真正做到不扫描、不重写现有行。你将用 MySQL 8.4 的 Instant DDL 观察元数据与行版本变化,并学会识别它的限制和锁风险。

学习目标

  • 解释 ALGORITHM=INSTANT 为什么能避免表重建
  • 用可复现实验验证 Instant ADD COLUMN 的行为
  • 在上线前识别不支持 Instant 的表特征与变更类型

INSTANT 到底改变了什么

对 InnoDB 而言,ALGORITHM=INSTANT 的核心是只修改数据字典中的元数据,不重写现有表数据。因此它不需要像重建型 DDL 那样扫描整张聚簇索引,也不需要创建一份完整的新表。MySQL 8.4 对支持 Instant 的变更会优先选择该算法,但生产变更仍建议显式写出 ALGORITHM=INSTANT:一旦目标操作不支持,语句直接失败,而不是悄悄退化到更重的算法。

Instant ADD/DROP COLUMN 通过行版本元数据让新旧物理记录继续共存。旧记录不会为了新增列立即重写;读取旧记录时,InnoDB 根据数据字典解释缺失列并返回定义中的默认值。后续新写入的记录使用新的行格式版本。每次 Instant ADD/DROP COLUMN 都会增加表的 TOTAL_ROW_VERSIONS。

Instant DDL 的元数据与行版本关系

用一次可复现实验观察行版本

下面在全新测试库中创建表,先确认行版本为 0,再用 Instant 增加带默认值的列。实验只针对 MySQL 8.4 InnoDB;请勿直接在生产表上照搬。

sql
CREATE DATABASE IF NOT EXISTS ddl_lab;
USE ddl_lab;

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  amount DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;

INSERT INTO orders VALUES
  (1, 99.00),
  (2, 199.00);

ALTER TABLE orders
  ADD COLUMN state VARCHAR(16) NOT NULL DEFAULT 'NEW',
  ALGORITHM=INSTANT;

SELECT id, state
FROM orders
ORDER BY id;

SELECT NAME, TOTAL_ROW_VERSIONS
FROM INFORMATION_SCHEMA.INNODB_TABLES
WHERE NAME = 'ddl_lab/orders';

预期结果

text
id  state
1   NEW
2   NEW

NAME            TOTAL_ROW_VERSIONS
ddl_lab/orders  1

这个结果说明两件事:旧记录立刻能读出 state='NEW',但并不是因为 MySQL 把两行重新写了一遍;同时 TOTAL_ROW_VERSIONS=1 记录了这次 Instant ADD COLUMN。若以后执行会重建表的 ALTER TABLE 或 OPTIMIZE TABLE,行版本会被折叠,计数重新归零。

哪些变更适合 INSTANT

MySQL 8.4 中,常见可 Instant 完成的操作包括增加或删除普通列、修改列默认值、增加或删除部分虚拟生成列、仅在不改变存储宽度的前提下扩展 ENUM/SET 尾部成员,以及重命名表等。是否支持必须以具体操作、表属性和版本为准,不能只看 SQL 外观。

以下情况尤其要警惕:ROW_FORMAT=COMPRESSED、带 FULLTEXT 索引的表、临时表等对 Instant ADD/DROP COLUMN 有限制;新增 AUTO_INCREMENT 列需要更重的算法;增加 STORED 生成列也不能 Instant 完成。Instant 增删列还受最大行大小、内部列数和行版本上限约束;MySQL 8.4 每张表最多保留 64 个此类行版本。

上线时仍要防元数据锁

“瞬时”不等于“完全无锁”。Instant DDL 仍需要获取表的独占 metadata lock,只是持有时间通常很短。如果前面存在长事务、未提交事务或长时间持有表元数据的语句,DDL 仍可能等待;排队中的 DDL 还可能影响后续访问。

上线前应同时确认两件事:变更本身支持 INSTANT,以及目标表没有会阻塞 MDL 的长事务。算法决定是否重写数据,锁等待决定语句何时能真正开始和提交;这两个维度必须分开评估。

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

目录与阅读设置

记录一个想法

理解 ALGORITHM=INSTANT

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

登录哪都通