MySQL 8.4快速DDL在线表结构变更：ALTER TABLE ALGORITHM=INSTANT与INPLACE

MySQL 8.4 快速DDL：ALGORITHM=INSTANT 与 INPLACE

大家好，今天我们来聊聊 MySQL 8.4 中关于在线表结构变更 (Online DDL) 的一个重要特性：ALTER TABLE ALGORITHM=INSTANT 和 ALGORITHM=INPLACE。在线 DDL 允许我们在修改表结构的同时，尽可能地减少对业务的影响，避免长时间的锁表操作，从而保证服务的可用性。MySQL 8.0 对在线 DDL 做了很多改进，而 8.4 版本更是引入了 ALGORITHM=INSTANT，进一步提升了 DDL 的效率。

DDL 操作的背景

在传统的数据库操作中，修改表结构（比如添加列、修改列类型等）通常需要较长时间，并且会阻塞对表的读写操作。这对于高并发、高可用的系统来说是不可接受的。早期 MySQL 版本的 DDL 操作通常采用以下模式：

1. 创建临时表: 创建一张与原表结构相似的临时表。
2. 数据拷贝: 将原表的数据复制到临时表中。
3. 交换表名: 删除原表，将临时表重命名为原表的名字。

这种方式耗时且锁表，严重影响业务。因此，在线 DDL 技术应运而生，旨在尽可能减少 DDL 操作对业务的影响。

ALGORITHM 的选择

ALGORITHM 子句用于指定 DDL 操作使用的算法。在 MySQL 中，主要有以下几种算法：

• COPY: 这是最原始的算法，会创建临时表并复制数据，锁表时间最长。
• INPLACE: 允许在原表上直接进行修改，避免数据拷贝，减少锁表时间。
• INSTANT: MySQL 8.0 引入，8.4 进一步增强。几乎不需要修改表数据，只需要修改元数据，锁表时间最短，甚至接近零。

在选择 ALGORITHM 时，需要考虑以下因素：

• 锁表时间: INSTANT < INPLACE < COPY。
• 支持的操作: 并非所有 DDL 操作都支持所有算法。
• 空间需求: COPY 需要额外的磁盘空间来创建临时表。

ALGORITHM=INSTANT 的原理与限制

ALGORITHM=INSTANT 的核心思想是尽可能避免修改表数据，而是通过修改元数据来实现 DDL 操作。这意味着它对 DDL 操作的类型有严格的限制。

支持的操作:

• 添加列 (ADD COLUMN)：仅限于在表末尾添加列，并且需要满足以下条件：
  • 不能有 DEFAULT 子句（允许 DEFAULT NULL）。
  • 不能是 NOT NULL 列。
  • 不能是 PRIMARY KEY 或 UNIQUE 索引的一部分。
  • 不能是 FULLTEXT 或 SPATIAL 索引的一部分。
  • 不能有 VIRTUAL 或 STORED 生成列。
• 删除列 (DROP COLUMN)：仅删除列的元数据，实际数据仍然存在，但对用户不可见。
• 重命名表 (RENAME TABLE)：仅修改元数据，速度非常快。
• 修改列的注释 (MODIFY COLUMN COMMENT)：仅修改元数据。
• 改变列的顺序 (CHANGE COLUMN)：仅改变元数据。可以和ADD COLUMN结合使用，先ADD COLUMN，再CHANGE COLUMN改变位置。

限制:

• 必须在表的末尾添加列。
• 不能用于修改已存在的列的类型、长度等属性。
• 不能用于创建或删除索引。
• 不能用于修改表的主键或唯一键。
• 不支持带 DEFAULT 值的 NOT NULL 列的添加。
• 不支持 FULLTEXT 和 SPATIAL 索引。
• 不支持 VIRTUAL 和 STORED 生成列。
• 不支持分区表和临时表。

如何使用:

ALTER TABLE my_table ADD COLUMN new_column INT ALGORITHM=INSTANT;

如果操作不满足 INSTANT 的条件，MySQL 会报错。

示例：添加列

假设我们有一个名为 users 的表：

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(255)
);

我们可以使用 ALGORITHM=INSTANT 添加一个 created_at 列：

ALTER TABLE users ADD COLUMN created_at TIMESTAMP NULL ALGORITHM=INSTANT;

这个操作会立即完成，不会阻塞对 users 表的读写操作。

示例：删除列

ALTER TABLE users DROP COLUMN email ALGORITHM=INSTANT;

这个操作也会立即完成，但实际上 email 列的数据并没有被删除，只是对用户不可见了。如果要真正删除数据，需要使用 ALGORITHM=INPLACE 或 ALGORITHM=COPY。

示例：重命名表

ALTER TABLE users RENAME TO user_accounts ALGORITHM=INSTANT;

这是一个非常快速的操作，因为只需要修改元数据。

ALGORITHM=INPLACE 的原理与使用

ALGORITHM=INPLACE 允许在原表上直接进行修改，避免数据拷贝，从而减少锁表时间。与 ALGORITHM=INSTANT 相比，INPLACE 支持更多的 DDL 操作，但仍然有一些限制。

支持的操作:

• 修改列的数据类型和长度 (MODIFY COLUMN)：某些数据类型和长度的修改可以在原表上直接进行，而不需要复制数据。
• 添加索引 (ADD INDEX)：可以在原表上创建索引，但可能会短暂的锁表。
• 删除索引 (DROP INDEX)：可以在原表上删除索引，但可能会短暂的锁表。
• 优化表 (OPTIMIZE TABLE)：可以在原表上进行碎片整理，但可能会短暂的锁表。
• 修改表的字符集和排序规则 (ALTER TABLE … CONVERT TO CHARACTER SET)：某些字符集和排序规则的修改可以在原表上直接进行。

限制:

• 仍然可能需要锁表，尽管时间比 COPY 短。
• 某些操作可能需要在表上创建一个临时索引，需要额外的磁盘空间。
• 不支持所有的数据类型和长度的修改。 例如，将 INT 修改为 VARCHAR 通常需要复制数据。
• 不支持修改列的 NOT NULL 约束。 如果需要添加 NOT NULL 约束，通常需要先允许 NULL，然后更新数据，最后再添加 NOT NULL 约束。

如何使用:

ALTER TABLE my_table MODIFY COLUMN my_column VARCHAR(255) ALGORITHM=INPLACE;

如果操作不满足 INPLACE 的条件，MySQL 会降级到 COPY 算法，或者报错。

示例：修改列的数据类型

假设我们有一个名为 products 的表：

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10, 2)
);

我们可以使用 ALGORITHM=INPLACE 增加 name 列的长度：

ALTER TABLE products MODIFY COLUMN name VARCHAR(200) ALGORITHM=INPLACE;

这个操作可能会短暂的锁表，但不会复制数据。

示例：添加索引

ALTER TABLE products ADD INDEX idx_price (price) ALGORITHM=INPLACE;

这个操作会在原表上创建索引，可能会短暂的锁表。

示例：删除索引

ALTER TABLE products DROP INDEX idx_price ALGORITHM=INPLACE;

这个操作会在原表上删除索引，可能会短暂的锁表。

默认算法和显式指定

如果不指定 ALGORITHM 子句，MySQL 会根据 DDL 操作的类型和表的属性选择合适的算法。一般来说，MySQL 会尽可能选择 INSTANT 或 INPLACE 算法，以减少锁表时间。

但是，为了更好地控制 DDL 操作，建议显式指定 ALGORITHM 子句。这样可以避免 MySQL 选择错误的算法，导致意外的锁表或数据拷贝。

例如，如果希望使用 INSTANT 算法添加列，可以明确指定 ALGORITHM=INSTANT：

ALTER TABLE my_table ADD COLUMN new_column INT ALGORITHM=INSTANT;

如果 MySQL 无法使用 INSTANT 算法，会报错，而不是降级到 INPLACE 或 COPY 算法。

测试和验证

在生产环境中执行 DDL 操作之前，一定要在测试环境中进行充分的测试和验证。可以使用 EXPLAIN 语句来查看 MySQL 将要使用的算法。

例如：

EXPLAIN ALTER TABLE my_table ADD COLUMN new_column INT ALGORITHM=INSTANT;

EXPLAIN 语句会显示 MySQL 预计使用的算法，以及其他相关信息。

此外，可以使用 pt-online-schema-change 工具来进行更复杂的在线 DDL 操作。pt-online-schema-change 是 Percona Toolkit 中的一个工具，它通过创建影子表、复制数据、交换表名等步骤来实现在线 DDL，可以处理一些 INPLACE 算法无法处理的情况。

性能对比

为了更直观地了解 INSTANT 和 INPLACE 的性能差异，我们进行一个简单的测试。假设我们有一个包含 100 万条数据的表：

CREATE TABLE test_table (
  id INT PRIMARY KEY,
  data VARCHAR(255)
);

INSERT INTO test_table (id, data)
SELECT seq, MD5(RAND())
FROM seq_1_to_1000000;

我们分别使用 INSTANT 和 INPLACE 算法添加一个列：

-- 使用 INSTANT 算法
ALTER TABLE test_table ADD COLUMN new_column INT ALGORITHM=INSTANT;

-- 使用 INPLACE 算法
ALTER TABLE test_table ADD COLUMN new_column2 INT ALGORITHM=INPLACE;

通过观察执行时间和锁表时间，可以发现 INSTANT 算法的性能明显优于 INPLACE 算法。

算法	执行时间	锁表时间
INSTANT	< 1 秒	几乎为零
INPLACE	几秒到几分钟	几秒到几分钟

需要注意的是，实际的性能差异取决于 DDL 操作的类型、表的大小、硬件配置等因素。

总结：选择合适的 DDL 策略

ALGORITHM=INSTANT 和 ALGORITHM=INPLACE 是 MySQL 8.4 中非常重要的在线 DDL 特性，可以显著减少 DDL 操作对业务的影响。INSTANT 算法速度快，但支持的操作有限；INPLACE 算法支持的操作更多，但可能会短暂的锁表。在选择 ALGORITHM 时，需要根据实际情况权衡利弊，选择合适的策略。始终在生产环境操作前进行充分的测试，并对整个过程进行监控，确保数据安全和服务的可用性。

未来展望

MySQL 团队一直在不断改进在线 DDL 技术，相信在未来的版本中，会引入更多的优化和功能，使得 DDL 操作更加高效和安全。例如，可能会支持更多的 INSTANT 操作，或者进一步减少 INPLACE 算法的锁表时间。