MySQL在线DDL的魔法变身术 🔧

一、开篇故事：图书馆的装修难题 🏗️

想象图书馆要装修：

传统装修（老式DDL）

馆长："我们要给书架刷漆！"

做法：
  1. 关闭图书馆 🚫
  2. 读者全部离开
  3. 慢慢刷漆（3小时）
  4. 等油漆干（2小时）
  5. 重新开放 ✅
  
问题：
  → 5小时不能营业
  → 读者抱怨
  → 损失巨大！

现代装修（Online DDL）

馆长："我们要给书架刷漆，但不能停业！"

做法：
  1. 图书馆继续营业 ✅
  2. 分批施工（一个书架一个书架刷）
  3. 读者可以继续借书（避开施工区域）
  4. 油漆干的书架立即可用
  5. 全程不停业！
  
好处：
  → 不影响读者
  → 不损失收入
  → 逐步完成装修
  → 这就是Online DDL！

---

二、什么是Online DDL？ 🎯

2.1 定义

Online DDL（在线数据定义语言）：在不锁表或只短暂锁表的情况下，对表结构进行修改。

2.2 传统DDL vs Online DDL

-- 传统DDL（MySQL 5.5及之前）
ALTER TABLE users ADD COLUMN age INT;

-- 执行过程：
-- 1. 锁表（读写均不可）🔒
-- 2. 创建新表 tmp_users（带age列）
-- 3. 复制数据：INSERT INTO tmp_users SELECT *, NULL FROM users
-- 4. 重命名表：RENAME TABLE users TO users_old, tmp_users TO users
-- 5. 删除旧表：DROP TABLE users_old
-- 6. 解锁 🔓

-- 问题：
--   - 大表操作时间长（几小时甚至几天）
--   - 期间无法读写
--   - 业务完全停止！💀

-- Online DDL（MySQL 5.6+）
ALTER TABLE users ADD COLUMN age INT, ALGORITHM=INPLACE, LOCK=NONE;

-- 执行过程：
-- 1. 短暂加元数据锁（毫秒级）
-- 2. 在原表上修改结构（不复制数据）✅
-- 3. 期间允许DML操作（INSERT、UPDATE、DELETE）✅
-- 4. 短暂加锁提交变更
-- 5. 完成

-- 好处：
--   - 不阻塞业务
--   - 不复制全表数据
--   - 速度快！

---

三、Online DDL的三种算法 🔍

3.1 COPY算法（最慢）

原理： 创建临时表，复制所有数据

ALTER TABLE users ADD COLUMN age INT, ALGORITHM=COPY;

-- 执行步骤：
-- 1. 创建临时表 tmp_users（新结构）
-- 2. 锁表（允许读，禁止写）🔒
-- 3. 复制数据：INSERT INTO tmp_users SELECT * FROM users
-- 4. 切换表名
-- 5. 删除旧表

-- 适用场景：
--   - MySQL 5.5及之前（只有这种方式）
--   - 某些复杂变更（必须COPY）

-- 特点：
--   ✅ 兼容性好
--   ❌ 慢（复制全表）
--   ❌ 锁表时间长
--   ❌ 占用双倍存储空间

---

3.2 INPLACE算法（推荐）⭐⭐⭐⭐⭐

原理： 在原表上直接修改，不复制数据

ALTER TABLE users ADD COLUMN age INT, ALGORITHM=INPLACE, LOCK=NONE;

-- 执行步骤：
-- 1. 加元数据锁（毫秒级，prepare阶段）
-- 2. 创建row log文件（记录DDL期间的DML操作）
-- 3. 修改表结构（在原表上操作）
-- 4. 应用row log（重放DDL期间的DML）
-- 5. 短暂加锁提交（毫秒级，commit阶段）

-- 适用场景：
--   - 添加/删除索引
--   - 添加列（尾部添加）
--   - 修改列默认值
--   - 重命名列
--   - 等等

-- 特点：
--   ✅ 快（不复制数据）
--   ✅ 不锁表（或只短暂锁表）
--   ✅ 不占用额外存储
--   ✅ 推荐使用！

INPLACE流程详解：

时间轴：
├─ T1: Prepare阶段（加元数据锁，毫秒级）
│   └─ 禁止其他DDL操作
│
├─ T2-T3: Execute阶段（主体工作，不锁表）
│   ├─ 创建row log（记录DML变更）
│   ├─ 修改表结构（如添加索引）
│   └─ 允许DML操作（INSERT、UPDATE、DELETE）✅
│
├─ T4: Apply阶段（应用row log）
│   └─ 重放DDL期间的DML操作
│
└─ T5: Commit阶段（短暂加锁，毫秒级）
    └─ 提交变更，释放锁

总时间：T1-T5（大部分时间在T2-T4，不影响业务）
锁表时间：T1 + T5（毫秒级，可忽略）

---

3.3 INSTANT算法（最快）⭐⭐⭐⭐⭐

原理： 只修改元数据，不修改数据文件（MySQL 8.0+）

-- MySQL 8.0+
ALTER TABLE users ADD COLUMN age INT DEFAULT 0, ALGORITHM=INSTANT;

-- 执行步骤：
-- 1. 修改表的元数据（数据字典）
-- 2. 完成！

-- 特点：
--   ✅ 瞬间完成（毫秒级）
--   ✅ 不复制数据
--   ✅ 不需要row log
--   ✅ 几乎无锁

-- 限制：
--   ❌ 只支持部分操作：
--      - 添加列（尾部，有默认值）
--      - 删除列（虚拟删除）
--      - 添加/删除虚拟列
--      - 修改索引可见性
--   ❌ MySQL 8.0.12+才支持

INSTANT示例：

-- 1亿行数据的表
CREATE TABLE big_table (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50),
    created_at DATETIME
);
-- 1亿行数据

-- 传统INPLACE：可能需要几小时
ALTER TABLE big_table ADD COLUMN status INT DEFAULT 0, ALGORITHM=INPLACE;
-- 执行时间：2小时（需要修改数据文件）

-- INSTANT：瞬间完成
ALTER TABLE big_table ADD COLUMN status INT DEFAULT 0, ALGORITHM=INSTANT;
-- 执行时间：0.05秒（只改元数据）✅

-- 原理：
--   - 新列的默认值存储在元数据中
--   - 查询时动态计算：
--     SELECT id, name, status FROM big_table;
--     → 如果行是老数据，status返回元数据中的默认值0
--     → 如果行是新数据（INSERT），status返回实际值

---

3.4 算法对比

算法	复制数据	支持并发DML	速度	适用场景	推荐度
COPY	✅ 是	❌ 否（锁表）	慢	MySQL 5.5兼容	⭐
INPLACE	❌ 否	✅ 是	快	大部分DDL（推荐）	⭐⭐⭐⭐⭐
INSTANT	❌ 否	✅ 是	极快	添加列、删除列等	⭐⭐⭐⭐⭐

---

四、Online DDL的四种锁模式 🔐

4.1 LOCK=NONE（无锁）⭐⭐⭐⭐⭐

ALTER TABLE users ADD INDEX idx_name (name), LOCK=NONE;

-- 特点：
--   ✅ 允许并发读（SELECT）
--   ✅ 允许并发写（INSERT、UPDATE、DELETE）
--   ✅ 最优选择

-- 适用操作：
--   - 添加/删除普通索引
--   - 添加列（尾部）
--   - 修改列默认值
--   - 重命名列

---

4.2 LOCK=SHARED（共享锁）

ALTER TABLE users ADD COLUMN age INT, LOCK=SHARED;

-- 特点：
--   ✅ 允许并发读（SELECT）
--   ❌ 禁止并发写（INSERT、UPDATE、DELETE被阻塞）
--   
-- 适用操作：
--   - 某些不支持NONE的操作

---

4.3 LOCK=EXCLUSIVE（排他锁）

ALTER TABLE users DROP PRIMARY KEY, LOCK=EXCLUSIVE;

-- 特点：
--   ❌ 禁止并发读
--   ❌ 禁止并发写
--   等同于传统锁表

-- 适用操作：
--   - 删除主键
--   - 修改列类型（某些情况）

---

4.4 LOCK=DEFAULT（默认）

ALTER TABLE users ADD COLUMN age INT;  -- 不指定LOCK

-- 特点：
--   → MySQL自动选择最宽松的锁模式
--   → 如果支持NONE，则用NONE
--   → 如果不支持NONE但支持SHARED，则用SHARED
--   → 依此类推

---

五、各类DDL操作的支持情况 📊

5.1 添加列

-- ✅ INPLACE + LOCK=NONE（尾部添加）
ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INPLACE, LOCK=NONE;

-- ⚠️ COPY（中间插入）
ALTER TABLE t ADD COLUMN c INT AFTER a, ALGORITHM=COPY;

-- ✅ INSTANT（MySQL 8.0+，有默认值）
ALTER TABLE t ADD COLUMN c INT DEFAULT 0, ALGORITHM=INSTANT;

---

5.2 删除列

-- ✅ INPLACE + LOCK=NONE
ALTER TABLE t DROP COLUMN c, ALGORITHM=INPLACE, LOCK=NONE;

-- ✅ INSTANT（MySQL 8.0.29+）
ALTER TABLE t DROP COLUMN c, ALGORITHM=INSTANT;

---

5.3 修改列

-- ✅ INPLACE + LOCK=NONE（只改名）
ALTER TABLE t CHANGE COLUMN old_name new_name INT, ALGORITHM=INPLACE, LOCK=NONE;

-- ✅ INPLACE + LOCK=NONE（修改默认值）
ALTER TABLE t ALTER COLUMN c SET DEFAULT 100, ALGORITHM=INPLACE, LOCK=NONE;

-- ❌ COPY（修改类型，某些情况）
ALTER TABLE t MODIFY COLUMN c VARCHAR(100), ALGORITHM=COPY;

-- ⚠️ INPLACE（修改类型，某些情况，如INT → BIGINT）
ALTER TABLE t MODIFY COLUMN c BIGINT, ALGORITHM=INPLACE, LOCK=NONE;

---

5.4 添加/删除索引

-- ✅ INPLACE + LOCK=NONE（普通索引）
ALTER TABLE t ADD INDEX idx_name (name), ALGORITHM=INPLACE, LOCK=NONE;

-- ✅ INPLACE + LOCK=NONE（唯一索引）
ALTER TABLE t ADD UNIQUE INDEX uk_email (email), ALGORITHM=INPLACE, LOCK=NONE;

-- ✅ INPLACE + LOCK=NONE（全文索引，MySQL 5.6+）
ALTER TABLE t ADD FULLTEXT INDEX ft_content (content), ALGORITHM=INPLACE, LOCK=NONE;

-- ❌ EXCLUSIVE（删除主键）
ALTER TABLE t DROP PRIMARY KEY, ALGORITHM=INPLACE, LOCK=EXCLUSIVE;

-- ✅ INPLACE + LOCK=NONE（删除普通索引）
ALTER TABLE t DROP INDEX idx_name, ALGORITHM=INPLACE, LOCK=NONE;

---

5.5 修改表属性

-- ✅ INSTANT（修改AUTO_INCREMENT值）
ALTER TABLE t AUTO_INCREMENT = 10000, ALGORITHM=INSTANT;

-- ✅ INPLACE + LOCK=NONE（修改表注释）
ALTER TABLE t COMMENT = '用户表', ALGORITHM=INPLACE, LOCK=NONE;

-- ❌ COPY（修改存储引擎）
ALTER TABLE t ENGINE=InnoDB, ALGORITHM=COPY;

-- ⚠️ INPLACE（修改字符集，MySQL 8.0+）
ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4, ALGORITHM=INPLACE;

---

六、Online DDL的注意事项 ⚠️

6.1 空间占用

问题：
  INPLACE虽然不复制全表，但仍需要额外空间：
  
  1. row log文件（记录DDL期间的DML）
     → 如果DDL期间有大量写入，row log会很大
  
  2. 临时文件（某些操作）
     → 如添加索引，需要排序空间
  
建议：
  ✅ 确保磁盘空间充足（至少50%剩余）
  ✅ 监控磁盘使用率
  ✅ DDL期间避免大量写入

-- 查看表空间大小
SELECT 
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.TABLES
WHERE table_schema = 'your_db'
  AND table_name = 'users';

-- 假设表大小：10GB
-- INPLACE添加索引可能需要额外：5-8GB临时空间

---

6.2 性能影响

影响1：row log应用阶段
  → DDL快完成时，需要重放row log
  → 如果row log很大，应用时间长
  → 会短暂阻塞DML

影响2：系统资源占用
  → CPU：构建索引需要大量计算
  → IO：读取数据、排序、写入
  → 内存：排序缓冲区
  
建议：
  ✅ 在业务低峰期执行
  ✅ 限制并发DDL数量（一次一个）
  ✅ 监控系统负载

---

6.3 元数据锁等待

-- 场景：DDL等待元数据锁
-- 原因：有长事务未提交

-- 事务A（未提交）
START TRANSACTION;
SELECT * FROM users WHERE id = 1;
-- ... 10分钟后还没提交 ...

-- 事务B（DDL，等待中）
ALTER TABLE users ADD COLUMN age INT;
-- ⏳ 等待事务A释放元数据锁

-- 检查阻塞
SELECT * FROM information_schema.INNODB_TRX;  -- 查看未提交事务
SELECT * FROM performance_schema.metadata_locks;  -- 查看元数据锁

-- 解决方案
KILL {transaction_id};  -- 杀掉阻塞的事务

---

6.4 超时设置

-- 设置锁等待超时（默认1年，太长了！）
SET SESSION lock_wait_timeout = 60;  -- 60秒超时

-- DDL执行
ALTER TABLE users ADD INDEX idx_name (name);
-- 如果60秒内拿不到锁，自动失败

-- 或者使用NOWAIT（MySQL 8.0+）
ALTER TABLE users ADD INDEX idx_name (name), LOCK=NONE, NOWAIT;
-- 立即失败，不等待

---

6.5 回滚风险

问题：
  → DDL执行到一半，如果失败（如磁盘满、断电）
  → 可能需要回滚
  → 大表回滚非常慢！
  
建议：
  ✅ DDL前做全备份
  ✅ 在从库测试DDL
  ✅ 使用pt-online-schema-change等工具（更安全）

---

七、生产环境最佳实践 💡

7.1 使用pt-online-schema-change（推荐）⭐⭐⭐⭐⭐

原理： Percona Toolkit提供的工具，更安全的Online DDL

# 安装
yum install percona-toolkit

# 使用
pt-online-schema-change \
  --alter "ADD COLUMN age INT" \
  --execute \
  h=localhost,D=mydb,t=users,u=root,p=password

# 工作原理：
# 1. 创建新表：_users_new（带age列）
# 2. 创建触发器：同步原表的DML操作到新表
#    - AFTER INSERT触发器
#    - AFTER UPDATE触发器
#    - AFTER DELETE触发器
# 3. 分批复制数据：
#    - 每次复制1000行（可配置）
#    - 避免长时间锁表
# 4. 切换表名：
#    - RENAME TABLE users TO users_old, _users_new TO users
# 5. 删除旧表和触发器

# 优点：
#   ✅ 更安全（有检查点，可暂停/恢复）
#   ✅ 资源可控（限速、分批）
#   ✅ 可监控进度
#   ✅ 可中途取消

---

7.2 gh-ost（GitHub开源）⭐⭐⭐⭐⭐

# 安装
wget https://github.com/github/gh-ost/releases/download/v1.1.5/gh-ost-binary-linux-amd64-20220208130242.tar.gz
tar -xzf gh-ost-binary-linux-amd64-20220208130242.tar.gz

# 使用
gh-ost \
  --user="root" \
  --password="password" \
  --host="localhost" \
  --database="mydb" \
  --table="users" \
  --alter="ADD COLUMN age INT" \
  --execute

# 工作原理：
# 1. 创建新表：_users_gho（ghost表）
# 2. 不使用触发器（避免影响性能）
# 3. 通过binlog同步数据：
#    - 解析binlog，重放到ghost表
# 4. 分批复制历史数据
# 5. 切换表名

# 优点：
#   ✅ 无触发器（性能更好）
#   ✅ 可暂停/恢复/取消
#   ✅ 可动态调整参数
#   ✅ 可测试（test-on-replica）
#   ✅ GitHub在用（生产验证）

---

7.3 DDL执行checklist ✅

执行前：
  □ 确认表大小和数据量
  □ 确认磁盘空间（至少50%剩余）
  □ 在从库测试DDL
  □ 准备回滚方案
  □ 评估执行时间
  □ 选择业务低峰期
  □ 通知相关人员

执行中：
  □ 监控DDL进度
  □ 监控系统负载（CPU、IO、内存）
  □ 监控磁盘使用率
  □ 监控慢查询
  □ 随时准备中止DDL

执行后：
  □ 验证表结构正确
  □ 验证数据完整性
  □ 验证索引正确
  □ 验证业务功能
  □ 观察性能指标
  □ 清理临时文件

---

7.4 在从库执行DDL

策略：
  1. 在从库执行DDL（不影响主库）
  2. 等待从库完成
  3. 主从切换
  4. 在新从库（原主库）执行DDL
  
优点：
  ✅ 主库不受影响
  ✅ 可随时回滚（切回原主库）
  
步骤：
  1. 停止从库复制：STOP SLAVE;
  2. 在从库执行DDL
  3. 验证从库
  4. 主从切换（keepalived、VIP等）
  5. 在新从库执行DDL
  6. 恢复复制：START SLAVE;

---

八、实战案例 💼

案例1：给大表添加索引

-- 表信息：
--   表名：orders
--   数据量：1亿行
--   大小：50GB
--   业务：7×24小时运行

-- 需求：添加索引 idx_user_id_time (user_id, create_time)

-- 方案1：直接ALTER（不推荐生产环境）
ALTER TABLE orders 
ADD INDEX idx_user_id_time (user_id, create_time),
ALGORITHM=INPLACE, LOCK=NONE;

-- 预计时间：2-3小时
-- 风险：
--   - 占用大量IO
--   - row log可能很大
--   - 影响业务

-- 方案2：使用gh-ost（推荐）✅
gh-ost \
  --user="root" \
  --password="xxx" \
  --host="localhost" \
  --database="ecommerce" \
  --table="orders" \
  --alter="ADD INDEX idx_user_id_time (user_id, create_time)" \
  --max-load=Threads_running=50 \  # 负载高时暂停
  --critical-load=Threads_running=100 \  # 负载过高时中止
  --chunk-size=1000 \  # 每批1000行
  --throttle-control-replicas="slave1:3306" \  # 监控从库延迟
  --max-lag-millis=2000 \  # 从库延迟>2秒时暂停
  --execute

-- 预计时间：4-5小时（但可控、可暂停）
-- 优点：
--   ✅ 安全可控
--   ✅ 可随时暂停
--   ✅ 不影响业务

---

案例2：修改列类型

-- 表信息：
--   表名：users
--   数据量：5000万行
--   字段：phone VARCHAR(11)

-- 需求：扩容phone字段 VARCHAR(11) → VARCHAR(20)

-- 分析：
--   VARCHAR长度变化，如果新长度<=255，可能INPLACE
--   如果新长度>255，需要COPY（255是VARCHAR长度阈值）

-- 方案1：先在测试环境验证
ALTER TABLE users_test 
MODIFY COLUMN phone VARCHAR(20),
ALGORITHM=INPLACE, LOCK=NONE;
-- 如果报错，说明需要COPY

-- 方案2：使用pt-osc（如果需要COPY）
pt-online-schema-change \
  --alter "MODIFY COLUMN phone VARCHAR(20)" \
  --chunk-size=2000 \
  --max-load=Threads_running=30 \
  --critical-load=Threads_running=50 \
  --execute \
  h=localhost,D=mydb,t=users,u=root,p=xxx

---

九、面试高频问题 🎤

Q1: 什么是Online DDL？

答： Online DDL是MySQL 5.6+引入的特性，允许在不锁表或只短暂锁表的情况下修改表结构。它有三种算法：

1. COPY：创建临时表，复制数据（最慢）
2. INPLACE：在原表上修改，不复制数据（推荐）
3. INSTANT：只改元数据，瞬间完成（MySQL 8.0+，最快）

Q2: INPLACE算法的原理是什么？

答：

1. Prepare阶段：短暂加元数据锁
2. Execute阶段：创建row log记录DML操作，同时修改表结构
3. Apply阶段：重放row log
4. Commit阶段：短暂加锁提交

期间允许DML操作，只在prepare和commit阶段短暂锁表。

Q3: 哪些DDL操作支持LOCK=NONE？

答：

• 添加/删除普通索引
• 添加列（尾部）
• 删除列
• 重命名列
• 修改列默认值
• 修改AUTO_INCREMENT值
• 添加/删除FULLTEXT索引（MySQL 5.6+）

不支持的：删除主键、修改列类型（某些情况）等。

Q4: Online DDL有哪些注意事项？

答：

1. 空间占用：需要额外空间存储row log和临时文件
2. 性能影响：占用CPU、IO资源
3. 元数据锁等待：长事务会阻塞DDL
4. 回滚风险：大表回滚很慢
5. 超时设置：需要合理设置lock_wait_timeout

Q5: 生产环境如何安全执行DDL？

答：

1. 使用工具：pt-online-schema-change或gh-ost（推荐）
2. 从库测试：先在从库验证DDL
3. 业务低峰期：选择合适的时间窗口
4. 监控：监控进度、负载、磁盘
5. 回滚方案：准备回滚脚本
6. 分批执行：大表分批处理

---

十、总结口诀 📝

Online DDL真神奇，
不锁表来改结构。
三种算法要记牢，
COPY、INPLACE、INSTANT。

INPLACE最常用，
row log记变更。
INSTANT速度快，
只改元数据成。

生产环境要小心，
空间性能要关注。
pt-osc和gh-ost，
工具安全又可控。

从库测试先验证，
低峰期里再执行。
监控回滚要准备，
安全第一记心中！

---

参考资料 📚

• MySQL官方文档 - Online DDL
• Percona Toolkit
• gh-ost GitHub
• 《高性能MySQL》第4章 - Schema与数据类型优化

---

下期预告： 152-数据库连接池的核心参数和调优 🏊

---

编写时间：2025年
作者：技术文档小助手 ✍️
版本：v1.0

愿你的DDL如魔法般优雅！ ✨🔧