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 提供的工具。

它是怎么工作的？
  1. 创建一个新表，新表的结构包含你想要修改的内容。
  2. 把原表的数据一点一点复制到新表里。
  3. 通过触发器实时同步变更时的新增或修改数据。
  4. 数据复制完后，把原表替换成新表。
实际操作

比如你想给 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

总结

在线变更是数据库管理中的一项重要技能。

  • 如果表不大，操作简单，用 MySQL 原生的 Online DDL 就够了。
  • 如果数据量大，推荐使用 pt-online-schema-change 或 gh-ost。

希望通过这篇文章，你能找到适合自己场景的工具和方法，让你的数据库调整既安全又高效！如果你还有其他疑问或者有自己的实践经验，欢迎在评论区交流！ 😊