当前位置：首页 > article >正文

MySQL 表结构在线变更：优雅地解决停机问题

article 2025/1/7 4:22:54

MySQL 表结构在线变更：优雅地解决停机问题

在日常开发中，我们经常会遇到这样的需求：要在 MySQL 的表里新增一列、修改索引或者调整字段类型。然而，数据库的表结构调整（Schema 变更）一不小心就可能让服务停摆，尤其是数据量特别大的时候。这时候，在线变更（Schema Online） 就显得格外重要了。今天，我带你一起看看 MySQL 是怎么做到“边跑业务边调整表结构”的，同时分享一些常用工具和操作小贴士。

什么是 Schema Online？

通俗点说，Schema Online 就是：在不影响数据库正常工作的前提下，对表结构进行修改。

比如：

添加一个字段。
新增一个索引，优化查询性能。
修改字段类型或顺序。

如果用传统的方式直接跑一条 ALTER TABLE 命令，那可能会锁住整个表，所有访问数据库的请求都得等着它处理完，轻则导致卡顿，重则业务直接“罢工”。而在线变更的目标，就是让这一切变得“无感”，服务照常运行，改表悄悄完成。

在线变更都有哪些方法？

1. MySQL 自带的 Online DDL

从 MySQL 5.6 开始，官方就支持了一部分在线变更操作。这种方法最简单，不需要借助任何额外工具。

它的优点是：

不用锁表：绝大部分情况下，业务查询和写入可以继续进行。
操作简单：一条 ALTER TABLE 搞定。

它的限制是：

并不是所有操作都支持在线，比如调整主键还是需要锁表。
对于特别大的表，依然有性能开销。

示例

假设我们需要给 employees 表新增一个字段：

ALTER TABLE employees ADD COLUMN department_id INT, ALGORITHM=INPLACE, LOCK=NONE;

这里的几个参数需要注意：

ALGORITHM=INPLACE：表示尽量避免创建临时表。
LOCK=NONE：表示不加全表锁，让业务继续运行。

2. Percona Toolkit 的 `pt-online-schema-change`

如果 MySQL 自带的 Online DDL 满足不了需求，你可以试试 Percona Toolkit 提供的工具。

它是怎么工作的？

创建一个新表，新表的结构包含你想要修改的内容。
把原表的数据一点一点复制到新表里。
通过触发器实时同步变更时的新增或修改数据。
数据复制完后，把原表替换成新表。

实际操作

比如你想给 employees 表新增一个字段：

pt-online-schema-change --alter "ADD COLUMN department_id INT" D=my_database,t=employees --execute

D=my_database：指定数据库名称。
t=employees：指定表名称。
--execute：真正执行（不加这个参数只是预演）。

优点

支持的操作范围更广，像修改索引、删除字段都可以。
对业务的影响极小，特别适合生产环境。

缺点

对小白不太友好，需要了解工具的用法。
数据量特别大的时候，性能开销会稍大。

3. GitHub 的 `gh-ost`

gh-ost 是 GitHub 开发的一款在线变更工具，专为高并发、大数据场景设计。

它有什么特别之处？

不使用触发器，性能更好。
支持实时增量复制，适合海量数据的表。
可以动态暂停或调整速度，灵活性更强。

示例

假设我们想给 orders 表新增一个索引：

gh-ost \
  --user="root" \
  --password="password" \
  --host="127.0.0.1" \
  --database="my_database" \
  --table="orders" \
  --alter="ADD INDEX idx_created_at (created_at)" \
  --execute

不同场景如何选择？

场景	推荐方法	理由
表结构调整简单（如新增字段）	MySQL Online DDL	简单直接，不需要额外工具。
表数据量较大，调整稍复杂	`pt-online-schema-change`	适配性强，操作过程稳定。
高并发、大数据场景	`gh-ost`	性能优秀，业务无感知。

一个完整的案例：为大表新增索引

假设我们有一个 orders 表，包含上千万条订单数据，现在我们要给 created_at 字段加个索引，优化查询速度。

使用 Percona 的 `pt-online-schema-change`

pt-online-schema-change --alter "ADD INDEX idx_created_at (created_at)" D=my_database,t=orders --execute

使用 GitHub 的 `gh-ost`

gh-ost \
  --user="root" \
  --password="password" \
  --host="127.0.0.1" \
  --database="my_database" \
  --table="orders" \
  --alter="ADD INDEX idx_created_at (created_at)" \
  --execute