本文为原文前 6,000 字符的节选翻译，完整内容请查看原文。

MySQL ALTER TABLE: Which Changes Lock the Table, and How to Make One Fail Instead of Copying

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples. TL;DR: On MySQL 8.4, adding, dropping or renaming a column and changing a default are metadata-only, and an index or NOT NULL builds while writes continue. Other type changes, including a VARCHAR that crosses 255 bytes, a CHECK and a foreign key with checks on copy the table and hold back writes until the copy ends. Every ALTER TABLE, instant ones included, first waits for an exclusive metadata lock, and new queries on the table queue behind it.

披露：我开发了一款名为 Schemity 的桌面 ERD 工具——本文源自我们的博客，并使用该工具进行示例演示。简而言之：在 MySQL 8.4 中，添加、删除或重命名列以及更改默认值仅涉及元数据操作；而创建索引或添加 NOT NULL 约束时，写入操作仍可继续。其他类型的变更，包括 VARCHAR 长度超过 255 字节、添加 CHECK 约束以及开启检查的外键约束，都会复制整张表，并阻塞写入直到复制完成。每一次 ALTER TABLE 操作（包括即时操作）首先都会等待排他元数据锁，导致新的查询在表上排队等待。

On MySQL 8.4, most ALTER TABLE statements do not hold the table for their whole run. Adding, dropping or renaming a column and changing a default only edit metadata, and adding an index or a NOT NULL builds while reads and writes continue. Among the common changes, the ones that copy the table, and hold back writes until the copy ends, are a data type change (including a VARCHAR that grows past 255 bytes), a new CHECK and a foreign key added with checks on. And every one of them, instant ones too, first needs an exclusive metadata lock, which is how a 0.1-second ADD COLUMN can stop reads on the table for 17 seconds.

在 MySQL 8.4 中，大多数 ALTER TABLE 语句不会在整个运行期间锁定表。添加、删除或重命名列以及更改默认值仅修改元数据，而添加索引或 NOT NULL 约束时，读写操作均可继续。在常见的变更中，会导致复制整表并阻塞写入的操作包括：数据类型变更（包括 VARCHAR 长度超过 255 字节）、新增 CHECK 约束以及开启检查的外键约束。所有这些操作（包括即时操作）首先都需要排他元数据锁，这就是为什么一个 0.1 秒的 ADD COLUMN 操作可能会导致表读取阻塞长达 17 秒的原因。

Everything below ran on MySQL 8.4.11 in a throwaway Docker container on a laptop, against a users table of 1,000,000 rows (id INT AUTO_INCREMENT, email VARCHAR(50) NOT NULL, name VARCHAR(100) NULL, status VARCHAR(20), created_at DATETIME, all utf8mb4) and an orders table of 1,000,000 rows pointing at it. Each change in the table below ran on a fresh copy of users with no foreign key on it, so every row starts from the same table. Read the timings as ratios between kinds of change, not as what your server will do.

以下所有内容均在笔记本电脑的临时 Docker 容器中运行，基于 MySQL 8.4.11 版本。测试对象为一张包含 100 万行数据的 users 表（字段包括 id INT AUTO_INCREMENT, email VARCHAR(50) NOT NULL, name VARCHAR(100) NULL, status VARCHAR(20), created_at DATETIME，字符集均为 utf8mb4）以及一张关联的包含 100 万行数据的 orders 表。下表中的每次变更均在不含外键的 users 表副本上运行，确保每项测试的初始状态一致。请将测试时间视为不同变更类型之间的比例参考，而非您服务器上的实际执行时间。

Does MySQL ALTER TABLE lock the table? Briefly, always; for the whole operation, only when MySQL decides to copy the table. When a statement names no algorithm, the ALTER TABLE documentation says MySQL “uses ALGORITHM=INSTANT” where it is supported, “otherwise, ALGORITHM=INPLACE is used. If ALGORITHM=INPLACE is not supported, ALGORITHM=COPY is used.” INSTANT edits only the data dictionary. INPLACE builds without copying rows into a new table and usually lets writes continue. COPY builds a new table row by row, and while it does, reads go through, except for a moment at the final swap, and writes wait. Which one a change gets is decided by the change, not by you.

MySQL 的 ALTER TABLE 会锁定表吗？简而言之：总是会短暂锁定；而对于整个操作过程，仅在 MySQL 决定复制表时才会锁定。当语句未指定算法时，ALTER TABLE 文档指出，MySQL 在支持的情况下会“使用 ALGORITHM=INSTANT”，否则“使用 ALGORITHM=INPLACE。如果不支持 ALGORITHM=INPLACE，则使用 ALGORITHM=COPY”。INSTANT 仅编辑数据字典。INPLACE 在不将行复制到新表的情况下构建，通常允许写入继续。COPY 则逐行构建新表，在此期间读取操作可以进行（最终交换时刻除外），但写入操作会等待。具体采用哪种算法由变更类型决定，而非由用户决定。

The “writes during it” column was measured, not read off the documentation. On the same table grown to 4,000,000 rows, a single-row INSERT sent 1.5 seconds into the run waited 6.3 seconds for the VARCHAR(50) to VARCHAR(100) copy and 5.9 seconds for the CHECK, while a SELECT by primary key sent at the same moment came back in about 0.1 second. During the NOT NULL rebuild and the index build, the same INSERT came back in 0.12 and 0.22 seconds. The NOT NULL row needs one more caveat: it is in-place, but with the default strict SQL mode it still fails with ERROR 1138 (22004): Invalid use of NULL value if any existing row holds a NULL.

“期间写入情况”一列是通过测量得出的，而非摘自文档。在表数据量增加到 400 万行时，在操作运行 1.5 秒后发送的单行 INSERT 语句，在 VARCHAR(50) 转 VARCHAR(100) 的复制过程中等待了 6.3 秒，在 CHECK 约束添加过程中等待了 5.9 秒，而同时发送的基于主键的 SELECT 查询在大约 0.1 秒内返回。在 NOT NULL 重建和索引构建期间，同样的 INSERT 操作分别在 0.12 秒和 0.22 秒内返回。关于 NOT NULL 还需要注意一点：虽然它是原地（in-place）操作，但在默认的严格 SQL 模式下，如果现有行中包含 NULL 值，它仍会报错 ERROR 1138 (22004): Invalid use of NULL value。

The foreign key row is the one most often missed. The online DDL reference says adding one supports INPLACE only “when foreign_key_checks is disabled. Otherwise, only the COPY algorithm is supported.” With checks off, the same statement took 0.01 seconds, and it validated nothing: on a small test table, a key added that way was accepted over a child row pointing at a parent that does not exist.

外键行是最容易被忽略的部分。在线 DDL 参考文档指出，仅在“禁用 foreign_key_checks 时”添加外键才支持 INPLACE。否则，仅支持 COPY 算法。在关闭检查的情况下，同样的语句仅耗时 0.01 秒，且不会进行任何验证：在小型测试表中，通过这种方式添加的键即使指向不存在的父行，子行也会被接受。

Why does widening a VARCHAR sometimes copy the table? Because the copy is triggered by the column’s length prefix changing from one byte to two, and MySQL counts that at the character set’s widest character, 4 bytes for utf8mb4. A VARCHAR value carries a prefix saying how long it is. Up to 255 bytes it is one byte; from 256 bytes it is two. The documentation states the consequence: in-place ALTER TABLE “does not support increasing the size of a VARCHAR column from less than 256 bytes to a size equal to or greater than 256 bytes.” At 4 bytes per character, utf8mb4 VARCHAR(63) is 252 bytes and VARCHAR(64) is 256.

为什么扩大 VARCHAR 有时会触发全表复制？因为复制是由列的长度前缀从一字节变为两字节触发的，而 MySQL 是按字符集的最宽字符（对于 utf8mb4 为 4 字节）来计算的。VARCHAR 值带有一个表示其长度的前缀。255 字节以内占用一字节；256 字节及以上占用两字节。文档指出了后果：原地 ALTER TABLE “不支持将 VARCHAR 列的大小从小于 256 字节增加到等于或大于 256 字节”。按每个字符 4 字节计算，utf8mb4 下的 VARCHAR(63) 为 252 字节，而 VARCHAR(64) 为 256 字节。

So two changes that look the same in a migration file behave differently: — name was VARCHAR(100): 400 bytes, already a two-byte prefix ALTER TABLE users MODIFY COLUMN name VARCHAR(200) NULL, ALGORITHM=INPLACE, LOCK=NONE; — Query OK — email was VARCHAR(50): 200 bytes, one-byte prefix; 100 characters is 400 bytes ALTER TABLE users MODIFY COLUMN email VARCHAR(100) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE; — ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. — Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

因此，在迁移文件中看起来相同的两个变更，实际表现却截然不同： — name 原为 VARCHAR(100)：400 字节，已是两字节前缀 ALTER TABLE users MODIFY COLUMN name VARCHAR(200) NULL, ALGORITHM=INPLACE, LOCK=NONE; — 执行成功 — email 原为 VARCHAR(50)：200 字节，一字节前缀；100 个字符即 400 字节 ALTER TABLE users MODIFY COLUMN email VARCHAR(100) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE; — 报错 ERROR 1846 (0A000): ALGORITHM=INPLACE 不受支持。— 原因：无法原地更改列类型。请尝试 ALGORITHM=COPY。

The boundary held exactly in the container: utf8mb4 VARCHAR(60) to VARCHAR(63) ran in place and VARCHAR(63) to VARCHAR(64) was refused; latin1 VARCHAR(100) to VARCHAR(255) ran in place and VARCHAR(255) to VARCHAR(256) was refused; VARBINARY(255) to VARBINARY(256) was refused too. Shrinking a VARCHAR, growing a CHAR, and VARCHAR to TEXT always copied. So the cost depends on where a column started. A utf8mb4 VARCHAR(50) copies the table the first t

该边界在容器中得到了精确验证：utf8mb4 下 VARCHAR(60) 到 VARCHAR(63) 为原地执行，而 VARCHAR(63) 到 VARCHAR(64) 被拒绝；latin1 下 VARCHAR(100) 到 VARCHAR(255) 为原地执行，而 VARCHAR(255) 到 VARCHAR(256) 被拒绝；VARBINARY(255) 到 VARBINARY(256) 同样被拒绝。缩小 VARCHAR、扩大 CHAR 以及将 VARCHAR 转为 TEXT 总是会触发复制。因此，成本取决于列的初始状态。一个 utf8mb4 VARCHAR(50) 在第一次…时会复制整表。