零停机迁移
问题
如何在不停服务的情况下安全地变更数据库 Schema？
答案
一、为什么零停机迁移重要
在生产环境中，直接执行 DDL（如 ALTER TABLE）可能导致：
| 风险 | 说明 |
|---|---|
| 表锁 | MySQL 部分 DDL 会锁表，阻塞所有读写 |
| 长事务 | 大表 DDL 执行时间可能超过几小时 |
| 服务中断 | 锁表期间所有请求超时 |
| 主从延迟 | DDL 在主库执行后，从库需要重放 |
二、MySQL Online DDL
MySQL 5.6+ 支持 Online DDL，部分操作不再需要锁表：
| 操作 | 算法 | 是否允许并发 DML |
|---|---|---|
| 添加列 | INPLACE / INSTANT (8.0) | ✅ |
| 删除列 | INPLACE | ✅ |
| 添加索引 | INPLACE | ✅ |
| 修改列类型 | COPY（锁表） | ❌ |
| 修改列名 | INPLACE | ✅ |
| 添加外键 | INPLACE | ✅（LOCK=NONE） |
-- MySQL 8.0 INSTANT DDL（毫秒级，不锁表）
ALTER TABLE users ADD COLUMN age INT DEFAULT 0, ALGORITHM=INSTANT;
-- 指定 INPLACE 算法
ALTER TABLE users ADD INDEX idx_email (email), ALGORITHM=INPLACE, LOCK=NONE;
INSTANT DDL 限制
- MySQL 8.0.12+ 支持，但仅限在最后添加列
- MySQL 8.0.29+ 支持任意位置添加列
- 不支持修改列类型、删除主键等操作
三、Online DDL 工具
当 MySQL 原生 Online DDL 不满足需求时（如修改列类型、超大表），使用专用工具：
gh-ost（GitHub 出品）
工作原理：
- 创建影子表（
_users_gho），包含新 Schema - 逐行拷贝原表数据到影子表
- 监听 binlog，同步期间的增量变更
- 数据追平后，原子切换表名
# 添加列
gh-ost \
--host=127.0.0.1 --port=3306 \
--database=mydb --table=users \
--alter="ADD COLUMN age INT DEFAULT 0" \
--execute
# 限速（避免影响业务）
gh-ost \
--nice-ratio=0.5 \ # 每拷 1 秒休息 0.5 秒
--max-load="Threads_running=50" \ # 负载上限
--critical-load="Threads_running=200" # 超过则暂停
pt-online-schema-change（Percona）
pt-online-schema-change \
--alter "ADD COLUMN age INT DEFAULT 0" \
--execute \
D=mydb,t=users
工作原理类似 gh-ost，但使用触发器同步增量数据：
| 对比 | gh-ost | pt-osc |
|---|---|---|
| 增量同步 | binlog 监听 | 触发器 |
| 主库压力 | 低（读 binlog） | 中（触发器有开销） |
| 回滚 | 删除影子表即可 | 需要清理触发器 |
| 限速控制 | 灵活（可暂停） | 固定策略 |
四、安全的 Schema 变更策略
扩展-迁移-收缩（Expand-Migrate-Contract）
最安全的变更流程，适用于所有 DDL 变更：
示例：将 name 列拆分为 first_name + last_name
Phase 1 — Expand（扩展）
├── 添加 first_name、last_name 列（允许 NULL）
├── 部署代码：写入 name 的同时也写入 first_name + last_name
└── 回填历史数据（UPDATE ... SET first_name = ..., last_name = ...)
Phase 2 — Migrate（迁移）
├── 部署代码：读取从 first_name + last_name 读取
├── 验证数据一致性
└── 设置 NOT NULL 约束
Phase 3 — Contract（收缩）
├── 部署代码：移除对 name 列的引用
└── 删除 name 列
具体场景的安全操作
1. 添加列
-- ✅ 安全：添加可为 NULL 的列（无锁）
ALTER TABLE users ADD COLUMN age INT;
-- ⚠️ 注意：添加 NOT NULL + DEFAULT 在大表上可能锁表（MySQL < 8.0.12）
-- 安全做法：先添加可 NULL，回填数据后再加约束
ALTER TABLE users ADD COLUMN age INT;
UPDATE users SET age = 0 WHERE age IS NULL; -- 分批更新
ALTER TABLE users MODIFY COLUMN age INT NOT NULL DEFAULT 0;
2. 删除列
Step 1: 代码中停止读写该列
Step 2: 部署代码
Step 3: 等待确认无问题（至少 1 个发布周期）
Step 4: 删除列
3. 重命名列
❌ 直接 RENAME COLUMN（会导致依赖旧列名的代码报错）
✅ 安全做法：
Step 1: 添加新列 → 双写（新旧列同时写入）
Step 2: 回填历史数据
Step 3: 代码切换到读取新列
Step 4: 停止写入旧列
Step 5: 删除旧列
4. 修改列类型
✅ 安全做法（同重命名）：
新列 → 双写 → 回填 → 切读 → 停旧写 → 删旧列
五、大表数据回填
-- ❌ 一次更新所有行（锁表、binlog 暴涨）
UPDATE users SET age = 0 WHERE age IS NULL;
-- ✅ 分批更新
SET @batch_size = 1000;
SET @max_id = (SELECT MAX(id) FROM users);
SET @start_id = 0;
WHILE @start_id <= @max_id DO
UPDATE users
SET age = 0
WHERE age IS NULL AND id BETWEEN @start_id AND @start_id + @batch_size;
SET @start_id = @start_id + @batch_size + 1;
-- 每批之间休息，避免影响业务
DO SLEEP(0.1);
END WHILE;
常见面试问题
Q1: 如何在不停机的情况下给大表添加索引？
答案：
- MySQL 5.6+：
ALTER TABLE ... ADD INDEX ..., ALGORITHM=INPLACE, LOCK=NONE;原生 Online DDL 支持 - 超大表：使用 gh-ost / pt-osc 工具
- 从库先建索引：在从库建好索引后切换主从
Q2: 为什么不能直接删除一个列？
答案：
需要先确保代码不再使用该列：
- 如果代码中还有
SELECT *，删除列后会导致映射错误 - ORM 的 Entity/Model 如果引用了该列，会报错
- 如果有其他表的外键引用，需要先删除外键
安全步骤：代码移除引用 → 部署 → 等待验证 → 删除列。
Q3: gh-ost 和 pt-online-schema-change 的核心区别？
答案：
核心区别在于增量同步方式：
- gh-ost：通过读取 binlog 获取增量变更，对主库无额外写入压力
- pt-osc：通过触发器同步，每次 INSERT/UPDATE/DELETE 都触发额外写入
所以 gh-ost 对主库的压力更小，更适合高并发场景。但 gh-ost 需要 binlog 为 ROW 格式。
Q4: 如何回滚一个已执行的 DDL？
答案：
DDL 通常无法直接回滚（MySQL DDL 不支持事务），需要：
- 添加的列：执行
DROP COLUMN - 删除的列：无法恢复，需要从备份恢复数据
- gh-ost 变更：如果影子表还在，可以 rename 回原表
- 最佳实践：DDL 变更前先备份，使用扩展-迁移-收缩模式降低回滚需求