VillageSQL is a drop-in replacement for MySQL with extensions.
All examples on this page work on VillageSQL. Install Now →
ALTER TABLE 的工作方式
MySQL 的ALTER TABLE 有两种模式,取决于操作和版本:
重建式 DDL(旧行为):MySQL 使用新模式创建一个新表,复制所有行,交换两个表,然后删除原表。整个过程中表被锁定、禁止写入 — 在大表上需要几分钟到几小时。
在线 DDL:许多操作可以在不阻塞读写的情况下运行。MySQL 就地执行变更,或者在后台重建表,同时允许并发 DML。要探测某个具体操作会怎样执行,请显式添加 ALGORITHM 和 LOCK 子句 — 如果 MySQL 无法满足所请求的约束,它会拒绝该语句:
在线 DDL 锁模式
ALGORITHM 和 LOCK 子句控制 MySQL 执行变更的方式:
哪些快,哪些慢
使用ALGORITHM=INSTANT(MySQL 8.0)的操作几乎是瞬时完成的,与表大小无关:
- 在任意位置添加列(MySQL 8.0.29+)
- 添加/删除虚拟生成列
- 更改列的默认值
ALGORITHM=COPY 的操作会在整个过程中阻塞写入:
- 更改列的数据类型
- 转换字符集
- (添加全文索引不属于这一类 — 它以
INPLACE运行,但始终至少需要LOCK=SHARED,因此读取继续而写入阻塞;LOCK=NONE会被拒绝并报 ERROR 1846)
ALGORITHM=INPLACE, LOCK=NONE — 表的重建在后台进行,DML 照常执行。在 100GB 的表上添加索引需要时间,但不会阻塞您的应用程序。
生产环境中的大表迁移
对于那些需要ALGORITHM=COPY、而表又大到无法锁定的操作,请使用外部工具:
pt-online-schema-change(Percona Toolkit)
pt-osc 创建一个影子表,分批复制行,使用触发器复制持续进行的写入,然后交换两个表。应用程序没有停机时间。
gh-ost(GitHub)
gh-ost 使用二进制日志而不是触发器,这在高写入负载下更可靠。它是高流量表的首选工具。
两种工具都要求表有主键。
迁移工具与版本控制
在纳入版本控制的迁移文件中跟踪模式变更。常见工具:
每个工具都在数据库中维护一张迁移表,用于跟踪哪些迁移已经运行,因此重复运行是安全的。
常见问题
我可以在不锁表的情况下添加列吗?
在任意位置添加可为空的列使用ALGORITHM=INSTANT,无论表大小都在几毫秒内完成(MySQL 8.0.29+)。如果不行,请使用 ALGORITHM=INPLACE, LOCK=NONE,或者使用 pt-osc 或 gh-ost。
如何检查 ALTER TABLE 是否会锁定我的表?
在语句中添加ALGORITHM=INPLACE, LOCK=NONE。如果 MySQL 能够不加锁地执行,它就会执行;如果不能,它会返回错误 — 无论哪种方式,您都能得到明确的答案,无需猜测:
我应该在事务中运行迁移吗?
DDL 语句(ALTER TABLE、CREATE INDEX、DROP TABLE)在 MySQL 中会导致隐式提交 — 它们无法回滚。把它们放在 BEGIN/COMMIT 块中运行并不能保护您。请在应用程序层面规划回滚策略(双写、功能开关),而不是依赖事务回滚。
故障排除
另请参阅
- MySQL 备份策略 — 迁移前务必备份
- MySQL 中的事务 — 哪些迁移操作在 MySQL 中是事务性的
- MySQL 中的表分区 — 分区变更也是模式迁移
- 将 MySQL 与 ORM 一起使用:Django、Rails 和 Prisma — 安全地应用 ORM 生成的 DDL