跳转到主要内容

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →
在线上 MySQL 数据库上进行模式变更,既可能微不足道(向小表添加一个可为空的列),也可能十分危险(修改一个每秒处理 50K 写入的 500GB 表上的列)。区别在于 MySQL 是否锁表,以及锁多久。

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 块中运行并不能保护您。请在应用程序层面规划回滚策略(双写、功能开关),而不是依赖事务回滚。

故障排除

另请参阅