MySQL 8.0 大表加列：INSTANT / INPLACE / COPY 实测，以及 Waiting for table metadata lock 怎么查
在 MySQL 8.0.46 测试实例、100 万行合成数据上实测三种 ALTER 算法的耗时和报错，再复现「一个没提交的事务让 0.1 秒的 INSTANT 加列卡住，连普通 SELECT 也排队」的元数据锁问题，附只读诊断 SQL 和可复跑的演示脚本。
INSTANT 加列约 0.1 秒，INPLACE 约 1 秒，COPY 约 2 秒；但只要有一个开着事务不提交的连接，再快的 DDL 也会卡住，并把后面所有查询堵在 Waiting for table metadata lock。上线 DDL 前先查长事务，再设 lock_wait_timeout。
适用对象
- 8.0 加列优先写 ALGORITHM=INSTANT：实测 0.11–0.22 秒，与表大小基本无关；INPLACE 约 1.0 秒、COPY 约 2.0 秒，会随表变大线性变慢。
- 显式写 ALGORITHM，做不到时 MySQL 会直接报错（比如改 DECIMAL 精度时 INPLACE 报 1846），而不是悄悄降级成锁表的 COPY。
- INSTANT 也要拿元数据排他锁。会话 A 只是 SELECT 了一行、事务没提交，会话 B 的 INSTANT 加列就等满 5 秒报 1205，期间会话 C 的普通 SELECT 也被堵了 4 秒。
- 上线 DDL 前：先用只读 SQL 查运行超过 60 秒的事务，再 SET SESSION lock_wait_timeout = 5，拿不到锁就失败重试，别让 DDL 在队列里堵住全部读写。
三种算法加同一个可空列，差多少
同一张表依次执行 ADD COLUMN（VARCHAR(64) NULL），分别指定 ALGORITHM=INSTANT、INPLACE（LOCK=NONE）、COPY；再加一个二级索引；最后把 amount 从 DECIMAL(10,2) 改成 DECIMAL(12,2)。
INSTANT 只改数据字典，不碰数据页，所以表再大也是这个量级；INPLACE 和 COPY 都要重建表，100 万行要 1–2 秒，换成几十 GB 的表就是几十分钟甚至更久。COPY 期间还会阻塞写入。
本节判断
- 我的习惯是永远显式写 ALGORITHM。不写的话，MySQL 会自动选它能做到的最快算法，改字段类型这种只能 COPY 的操作就会悄悄锁表跑完。写了 INPLACE，做不到会立刻报 1846，你就有机会改在低峰期执行。
- INSTANT 加列有次数上限：从 8.0.29 起，一张表最多 64 个行版本，用完之后下一次 INSTANT 会报错，需要重建表（OPTIMIZE 或 COPY）清零。演示里可以看到 INSTANT 之后 TOTAL_ROW_VERSIONS 从 0 变成 1，而任何一次重建都会把它重置为 0。
| 操作 | 第 1 轮 | 第 2 轮 | 结果 |
|---|---|---|---|
| ADD COLUMN … ALGORITHM=INSTANT | 0.221 | 0.108 | 成功；TOTAL_ROW_VERSIONS 变为 1 |
| ADD COLUMN … ALGORITHM=INPLACE, LOCK=NONE | 1.015 | 0.959 | 成功（重建表） |
| ADD COLUMN … ALGORITHM=COPY | 1.950 | 1.982 | 成功（复制全表） |
| ADD INDEX … ALGORITHM=INPLACE, LOCK=NONE | 0.791 | 0.783 | 成功 |
| MODIFY DECIMAL(10,2)→(12,2) ALGORITHM=INPLACE | 0.075 | 0.073 | 报错 1846：Cannot change column type INPLACE. Try ALGORITHM=COPY. |
| 同上 ALGORITHM=COPY | 3.157 | 3.009 | 成功 |
一个没提交的 SELECT，让 0.1 秒的 DDL 卡住整张表
复现步骤：会话 A 执行 BEGIN; SELECT id FROM orders WHERE id=1; 然后什么都不做（连接处于 Sleep，事务开着）。会话 B 设 lock_wait_timeout=5，执行同样的 INSTANT 加列。会话 C 在 B 之后 1 秒执行一条最普通的 SELECT COUNT(*) … WHERE id <= 10。
结果两轮一致：B 等满 5.09 秒后报 ERROR 1205 Lock wait timeout exceeded；C 本来几毫秒的查询等了 4.09 秒，直到 B 超时放弃才执行。A 提交后再跑同样的 INSTANT 加列，0.14–0.16 秒完成。
原因是 MDL 的排队规则：A 持有共享读锁（SHARED_READ）；B 要的排他锁（EXCLUSIVE）和它冲突，只能排队；C 要的共享读锁本来和 A 兼容，但排在一个等待中的排他锁后面，也得等。所以真正可怕的不是 DDL 慢，而是 DDL 在等。
| 会话 | 动作 | 第 1 轮 | 第 2 轮 | 结果 |
|---|---|---|---|---|
| B | INSTANT 加列（lock_wait_timeout=5） | 5.096 | 5.088 | ERROR 1205 |
| C | SELECT COUNT(*) WHERE id <= 10 | 4.095 | 4.086 | 被堵到 B 放弃才返回 |
| B' | A 提交后同样的 INSTANT 加列 | 0.142 | 0.157 | 成功 |
用只读 SQL 找到根源会话
等待期间我跑了下载区的只读诊断 SQL（只查 performance_schema / information_schema，不 KILL、不改配置）。第一段列出所有 Waiting for table metadata lock 的会话：B 的 ALTER 等了 3 秒，C 的 SELECT 等了 2 秒。
第二段找根源：对「有人在等」的表持有已授予的 MDL、自己却没在等任何锁的会话。输出只有一行——就是会话 A：COMMAND=Sleep，持有 SHARED_READ，事务已开 6 秒，current_sql 为空。在生产上，这一行通常就是连接池里忘了提交的事务，或者某个开着事务跑报表的客户端。
第三段列出运行超过 60 秒的事务，即使当下没人在等，它们也是下一次 DDL 卡死的种子。
本节判断
- 处置顺序：先找到这个连接属于哪个应用，能让应用提交或回滚就别 KILL；确需 KILL，只 KILL 根源会话，不要去 KILL 排队的 ALTER 和查询，那样只会让下一个 DDL 再排一次。
- sys.schema_table_lock_waits 也能看等待关系，但它会把所有持锁会话都列出来；先用「自己没在等」这个条件过滤，找根源更快。
我现在每次做 DDL 前的检查
业务时间做表结构变更的最小流程
- 01
1
查长事务
跑只读 SQL 第三段，看有没有运行超过 60 秒的事务；有就先处理，或者换时间。
- 02
2
显式写算法
加列写 ALGORITHM=INSTANT，加索引写 ALGORITHM=INPLACE, LOCK=NONE。报错就说明这个变更需要重建表，改到低峰期或用 gh-ost / pt-osc。
- 03
3
设锁等待上限
SET SESSION lock_wait_timeout = 5;（默认值是一年）。拿不到锁就失败，间隔几秒重试几次，而不是无限排队。
- 04
4
盯住执行窗口
执行时开着第一段 SQL 观察；出现大量 Waiting for table metadata lock 就立刻中止 DDL。
参考依据
以下来源用于确认市场趋势、政策背景和术语边界；具体落地方案仍以客户的数据范围、权限和交付目标为准。
常见问题
INSTANT 加列有什么限制？
8.0.29 起可以加在任意位置，也可以 INSTANT 删列；每张表最多 64 个行版本，用完需要重建表。带全文索引的表、某些行格式不支持 INSTANT，这时显式写 ALGORITHM=INSTANT 会直接报错，正好用来提前发现。
lock_wait_timeout 设多少合适？
我一般在 DDL 会话里设 3–10 秒，配合应用侧的查询超时。它只影响当前会话的元数据锁等待，不影响其他连接。
这些数字能代表生产环境吗？
不能。这是笔记本 Docker、100 万行合成数据的测试，只说明量级和方向。生产上表更大、并发更高，INPLACE / COPY 的耗时会成倍增加，MDL 等待的影响也更大。