Online DDL 实战

MySQL 在线DDL与Schema变更指南

生产环境中的Schema变更需要仔细规划。本指南涵盖Online DDL算法、大表策略和零停机方法。

1. 在线DDL算法

在线DDL算法:

ALGORITHM=INSTANT (子毫秒):
✅ 添加列（到末尾）
✅ 改变列默认值
✅ 删除列
✅ 重命名列
❌ 修改列类型
❌ 重新排列列

ALTER TABLE users
ADD COLUMN last_login DATETIME DEFAULT CURRENT_TIMESTAMP,
ALGORITHM=INSTANT;

ALGORITHM=INPLACE (几分钟到几小时):
✅ 添加/删除索引
✅ 更改列类型（某些情况）
✅ 重命名列
✅ 改变NOT NULL
❌ 完全重建表

ALTER TABLE orders
ADD INDEX idx_user_id (user_id),
ALGORITHM=INPLACE, LOCK=SHARED;

常见DDL变更:

添加列: ALTER TABLE users ADD COLUMN phone_number VARCHAR(20), ALGORITHM=INSTANT;

添加索引: ALTER TABLE orders ADD INDEX idx_user (user_id), ALGORITHM=INPLACE, LOCK=SHARED;

修改列类型: ALTER TABLE users CHANGE COLUMN age age INT UNSIGNED, ALGORITHM=INPLACE;

删除列: ALTER TABLE users DROP COLUMN deprecated, ALGORITHM=INSTANT;

重命名列: ALTER TABLE users RENAME COLUMN old_name TO new_name, ALGORITHM=INSTANT;

2. 大表变更策略

大表变更策略:

1. 使用INPLACE算法 + LOCK=SHARED
   -- 允许读操作，阻塞写操作

2. 非高峰期执行
   -- 晚上2点（流量最低）

3. 设置超时
   SET SESSION innodb_lock_wait_timeout = 600;  (10分钟)

4. 监控进度
   SHOW PROCESSLIST;  -- 显示完成百分比

5. 验证结果
   SELECT COUNT(*) FROM table;  -- 验证数据完整

Ghost表方式:

1. 创建新表
   CREATE TABLE orders_new LIKE orders;

2. 分块复制数据
   INSERT INTO orders_new
   SELECT * FROM orders WHERE id BETWEEN 1 AND 1000000;

3. 应用二进制日志
   (Percona工具包处理)

4. 原子切换
   RENAME TABLE orders TO orders_old, orders_new TO orders;

5. 删除旧表
   DROP TABLE orders_old;

优点：读者无停机时间
缺点：需要磁盘空间复制

金丝雀部署:

Day 1: 10%副本测试
Day 2: 50%副本验证
Day 4: 100%副本应用
Day 5: 主库应用（低流量时段）

3. 回滚与验证

验证清单:

[ ] 行数未变
[ ] 索引存在且被使用
[ ] 数据完整性（无意外NULL）
[ ] 性能未下降
[ ] 复制延迟正常
[ ] 应用仍可工作
[ ] 备份与新schema兼容

验证查询:

-- 检查表大小
SELECT table_name, data_length FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='users';

-- 验证索引
EXPLAIN SELECT * FROM users WHERE status='active'\G

-- 检查列定义
DESCRIBE users;

-- 验证数据
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE new_column IS NULL;

回滚:
如果需要回滚，执行反向ALTER或从备份恢复

-- 反向DDL示例
ALTER TABLE users DROP COLUMN phone_number;