数据库工程化——在线表结构变更怎么不停服 / Database Engineering and Online Schema Changes
📅 创建时间：2026-05-08 🏷️ 标签：#数据库迁移 #在线DDL #主从延迟 #数据备份 #Flyway 📚 前置知识：[[00-backend-overview]] [[09-mysql-optimization]]（MySQL 基础） 📚 相关知识：[[/03-web/06-databases-and-data-access/01-mysql]]（MySQL 进阶）
场景：产品经理说，给 orders 表加一个字段
┌─────────────────────────────────────────────────────────────┐
│ │
│ 产品经理：给 orders 表加一个 coupon_code 字段。 │
│ DBA：好的。ALTER TABLE orders ADD COLUMN coupon_code VARCHAR(50); │
│ │
│ 执行时间：ALTER TABLE ... │
│ 线上表 5000 万行，ALTER 要 30 分钟 │
│ │
│ 影响： │
│ ❌ ALTER 期间表锁，整个 orders 表不可写 │
│ ❌ 主从延迟：从库延迟 30 分钟 │
│ ❌ 业务中断：下单接口超时，用户无法下单 │
│ │
│ DBA：...能不停服吗？ │
│ │
└─────────────────────────────────────────────────────────────┘这一章，我们理解数据库工程化的核心问题：在线变更、主从延迟、备份恢复。
第1节：在线表结构变更——pt-osc 和 gh-ost
问题：ALTER TABLE 为什么会锁表
┌─────────────────────────────────────────────────────────────┐
│ MySQL ALTER 的过程 │
├─────────────────────────────────────────────────────────────┤
│ │
│ MySQL 5.6 之前： │
│ ALTER TABLE orders ADD COLUMN coupon_code VARCHAR(50); │
│ → 重建整张表 │
│ → 全局写锁（MDL X Lock） │
│ → 30 分钟内无法写入 │
│ │
│ MySQL 5.6+ Online DDL： │
│ → 允许并发 DML（部分场景） │
│ → 但仍可能需要全表锁 │
│ → 无法保证不锁表 │
│ │
└─────────────────────────────────────────────────────────────┘pt-online-schema-change（Percona Toolkit）
┌─────────────────────────────────────────────────────────────┐
│ pt-osc 原理 │
├─────────────────────────────────────────────────────────────┤
│ │
│ Step 1：创建新表（空表） │
│ orders_new (包含新字段 coupon_code) │
│ │
│ Step 2：创建触发器（同步增量数据） │
│ → INSERT 触发器：INSERT INTO orders_new VALUES (NEW.*) │
│ → UPDATE 触发器：UPDATE orders_new SET ... = NEW.... │
│ → DELETE 触发器：DELETE FROM orders_new WHERE ... │
│ │
│ Step 3：拷贝历史数据 │
│ SELECT * FROM orders ORDER BY id LIMIT 1000 │
│ → INSERT INTO orders_new VALUES (...) │
│ （分批拷贝，不锁表） │
│ │
│ Step 4：切换表名 │
│ RENAME TABLE orders TO orders_old, orders_new TO orders │
│ （元数据锁，极短） │
│ │
│ Step 5：删除旧表和触发器 │
│ DROP TABLE orders_old │
│ │
└─────────────────────────────────────────────────────────────┘bash
# pt-osc 命令
pt-online-schema-change \
--alter "ADD COLUMN coupon_code VARCHAR(50) DEFAULT NULL" \
--user=root \
--password=xxx \
--charset=utf8mb4 \
--execute \
D=mydb,t=orders
# 关键参数
--alter-foreign-keys-method=rebuild_constraints # 外键处理
--max-load="Threads_running=50" # 超过 50 并发时暂停
--critical-load="Threads_running=100" # 超过 100 并发时退出
--chunk-size=1000 # 每批处理行数
--max-lag=5 # 允许的最大延迟gh-ost（GitHub Online Schema Change）
┌─────────────────────────────────────────────────────────────┐
│ gh-ost vs pt-osc 的区别 │
├─────────────────────────────────────────────────────────────┤
│ │
│ pt-osc： │
│ → 使用触发器同步增量数据 │
│ → 触发器有性能开销 │
│ → 对外键处理复杂 │
│ │
│ gh-ost（推荐）： │
│ → 不使用触发器，用 Binlog 同步增量 │
│ → 性能更好 │
│ → 支持暂停/恢复 │
│ → 支持动态调整速率 │
│ │
│ gh-ost 原理： │
│ 1. 分析表结构，创建新表 │
│ 2. 从旧表拷贝数据（binlog 记录偏移量） │
│ 3. 监听 Binlog，用 Binlog 同步增量 │
│ 4. 切换表名 │
│ │
└─────────────────────────────────────────────────────────────┘bash
# gh-ost 命令
gh-ost \
--database="mydb" \
--table="orders" \
--alter="ADD COLUMN coupon_code VARCHAR(50)" \
--execute \
--user="root" \
--password="xxx" \
--host="mysql-master" \
--allow-on-master \
--chunk-size=1000 \
--max-load=Threads_running=50 \
--critical-load=Threads_running=100 \
--postpone-cut-over-flag-file=/tmp/gh-ost-pause
# 支持动态暂停
touch /tmp/gh-ost-pause # 暂停
rm /tmp/gh-ost-pause # 恢复第2节：数据库迁移管理——Flyway
问题：多个环境的数据库版本不一致
┌─────────────────────────────────────────────────────────────┐
│ │
│ 开发环境：orders 表有 20 个字段 │
│ 测试环境：orders 表有 18 个字段 │
│ 生产环境：orders 表有 19 个字段 │
│ │
│ 为什么？ │
│ → 开发手动改了测试环境的表结构 │
│ → 没有记录改了什么 │
│ → 现在不知道生产环境和测试环境的差异在哪里 │
│ │
│ 解决：数据库迁移工具（Flyway / Liquibase） │
│ │
└─────────────────────────────────────────────────────────────┘Flyway 工作原理
┌─────────────────────────────────────────────────────────────┐
│ Flyway 版本管理 │
├─────────────────────────────────────────────────────────────┤
│ │
│ SQL 迁移文件命名规范： │
│ V1__create_orders_table.sql │
│ V2__add_user_index.sql │
│ V3__add_coupon_code_column.sql │
│ V4__create_order_items_table.sql │
│ │
│ Flyway 工作流程： │
│ 1. 检查 flyway_schema_history 表 │
│ 2. 找出未执行的迁移（V1-V4 中缺失的） │
│ 3. 按顺序执行迁移 │
│ 4. 记录到 flyway_schema_history │
│ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ flyway_schema_history: │ │
│ │ version | description | executed_at │ │
│ │ -------+---------------------------+----------- │ │
│ │ 1 | create_orders_table | 2026-05-01 │ │
│ │ 2 | add_user_index | 2026-05-02 │ │
│ │ 3 | add_coupon_code_column | 2026-05-03 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘Flyway 迁移文件示例
sql
-- V1__create_orders_table.sql
CREATE TABLE orders (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'pending',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_user_id (user_id),
INDEX idx_status (status),
INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;sql
-- V2__add_coupon_code_column.sql
-- 注意：使用 gh-ost 兼容语法
ALTER TABLE orders
ADD COLUMN coupon_code VARCHAR(50) DEFAULT NULL
COMMENT '使用的优惠券编码'
AFTER total_amount;sql
-- V3__create_order_cancellation_log.sql
-- 数据迁移：如果有取消订单的记录，需要迁移
CREATE TABLE order_cancellation_log (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
order_id BIGINT NOT NULL,
reason VARCHAR(500),
cancelled_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX idx_order_id (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 数据迁移（如果需要）
INSERT INTO order_cancellation_log (order_id, reason, cancelled_at)
SELECT id, cancel_reason, updated_at
FROM orders
WHERE status = 'cancelled' AND cancel_reason IS NOT NULL;python
# Flyway Python API
from flyway import Flyway
flyway = Flyway()
flyway.setDataSource(
"jdbc:mysql://localhost:3306/mydb",
"username",
"password"
)
# 迁移
flyway.migrate()
# 只检查当前版本（不执行迁移）
flyway.info()
# 清理（危险！）
flyway.clean() # 删除所有表，慎用第3节：主从延迟——从库数据比主库慢
问题：读写分离后，数据不一致
┌─────────────────────────────────────────────────────────────┐
│ │
│ 架构：主库写入，从库读取 │
│ │
│ 用户下单： │
│ POST /order → 写入主库 → 返回"下单成功" │
│ │
│ 用户立刻查询： │
│ GET /order → 读取从库 → 订单不存在！ │
│ │
│ 原因：从库比主库慢了 5 秒 │
│ │
└─────────────────────────────────────────────────────────────┘主从延迟的原因
┌─────────────────────────────────────────────────────────────┐
│ MySQL 主从复制原理 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 主库： │
│ 事务提交 → 写入 Binlog → 返回客户端 │
│ │
│ 从库 IO Thread： │
│ 读取主库 Binlog → 写入 Relay Log │
│ │
│ 从库 SQL Thread： │
│ 读取 Relay Log → 执行 SQL → 更新从库 │
│ │
│ 延迟来源： │
│ 1. SQL Thread 是单线程执行（MySQL 5.6 之前） │
│ 2. 大事务：一个事务写入 100 万行 │
│ 3. 从库负载高：查询多，CPU/IO 繁忙 │
│ 4. 网络延迟：Binlog 传输慢 │
│ │
└─────────────────────────────────────────────────────────────┘解决主从延迟
┌─────────────────────────────────────────────────────────────┐
│ 解决主从延迟的方案 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 方案 1：并行复制（MySQL 5.7+） │
│ → 从库 SQL Thread → 多个 Worker Thread │
│ → 同一事务的 SQL 并行执行 │
│ → 大幅减少延迟 │
│ → 配置：slave_parallel_workers=8 │
│ │
│ 方案 2：读从库失败时读主库 │
│ def read_from_replica(user_id): │
│ try: │
│ return replica.query(f"SELECT * FROM users WHERE id={user_id}") │
│ except ReplicationLagException: │
│ return master.query(f"SELECT * FROM users WHERE id={user_id}") │
│ │
│ 方案 3：强制读主库（关键场景） │
│ → 下单后立即展示订单 → 强制读主库 │
│ → 其他场景读从库 │
│ │
│ 方案 4：等主从同步完成后再返回 │
│ → select wait_for_master_to_sync() │
│ → 延迟可接受（通常 <1 秒） │
│ │
└─────────────────────────────────────────────────────────────┘第4节：数据备份与恢复
三种备份策略
┌─────────────────────────────────────────────────────────────┐
│ 数据库备份策略 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 1. 全量备份 │
│ → 备份整个数据库 │
│ → 恢复简单，但备份时间长（GB/TB 级） │
│ → 频率：每周一次 │
│ │
│ 2. 增量备份（Binlog 备份） │
│ → 备份自上次备份以来的 Binlog │
│ → 备份快，但恢复慢（需要逐个应用 Binlog） │
│ → 频率：每小时一次 │
│ │
│ 3. 增量备份（LSN 备份） │
│ → 备份特定 LSN 之后的变更 │
│ → XtraBackup 使用此方式 │
│ │
│ 推荐组合： │
│ 每周全量 + 每日增量 + 实时 Binlog │
│ │
└─────────────────────────────────────────────────────────────┘XtraBackup 备份实战
bash
# 全量备份
xtrabackup --backup \
--target-dir=/backup/full \
--user=root \
--password=xxx \
--compress \
--parallel=4
# 增量备份（基于上次备份）
xtrabackup --backup \
--target-dir=/backup/incr_1 \
--incremental-basedir=/backup/full \
--user=root \
--password=xxx
# 恢复
# 1. 解压
xtrabackup --decompress --target-dir=/backup/full
# 2. 应用日志
xtrabackup --prepare --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/incr_1 --incremental-basedir=/backup/full
# 3. 恢复数据
xtrabackup --copy-back --target-dir=/backup/full第5节：数据库变更流程规范
┌─────────────────────────────────────────────────────────────┐
│ 数据库变更 SOP │
├─────────────────────────────────────────────────────────────┤
│ │
│ Step 1：评审 │
│ → DDL 是否会影响业务 │
│ → 预计耗时 │
│ → 是否需要停服窗口 │
│ │
│ Step 2：测试环境验证 │
│ → 在测试环境执行，观察影响 │
│ → 记录执行时间 │
│ │
│ Step 3：预发布环境验证 │
│ → 使用 gh-ost / pt-osc 执行 │
│ → 监控主从延迟 │
│ │
│ Step 4：生产环境执行 │
│ → 低峰期执行（通常凌晨） │
│ → DBA 监控 │
│ → 准备好回滚方案 │
│ │
│ Step 5：验证 │
│ → 检查表结构 │
│ → 检查数据完整性 │
│ → 确认应用日志无异常 │
│ │
│ 一句话： │
│ 变更前备份，变更后验证，出问题立即回滚。 │
│ │
└─────────────────────────────────────────────────────────────┘"AI 可查 vs 必须理解"清单
AI 可查：
✅ pt-osc / gh-ost 的具体命令参数
✅ Flyway 的配置参数
✅ XtraBackup 的备份命令
必须理解：
🔴 pt-osc 和 gh-ost 的原理区别
🔴 为什么主从复制会有延迟
🔴 并行复制如何减少延迟
🔴 全量备份 + 增量备份的组合策略学习状态：🟡 开始学习