问题
什么是OnlineDDL？
答案
核心概念
Online DDL是MySQL 5.6引入的特性，允许在执行DDL操作（如加索引、修改列）时不阻塞DML操作（INSERT、UPDATE、DELETE），实现表结构变更期间业务的正常读写。
传统DDL的问题
MySQL 5.6之前：
ALTER TABLE users ADD INDEX idx_age(age);
执行过程：
- 创建临时表，按新结构定义
- 将原表数据逐行复制到临时表
- 复制期间锁表，禁止DML操作
- 替换原表
问题：
- 长时间锁表：大表添加索引可能耗时数小时
- 业务中断：锁表期间无法写入，导致业务不可用
- 双倍存储：临时表占用额外磁盘空间
Online DDL的改进
核心思想： 通过增量日志记录DDL期间的DML操作，最后合并应用，避免长时间锁表。
执行流程：
1. 加MDL读锁（Metadata Lock）
↓
2. 升级为MDL写锁（短暂）
↓
3. 降级为MDL读锁
↓
4. 执行DDL操作（如构建索引）
同时记录DML操作到 row log
↓
5. 升级为MDL写锁（短暂）
↓
6. 应用row log中的增量DML
↓
7. 释放MDL锁，完成DDL
关键点：
- 大部分时间持有MDL读锁，允许DML并发执行
- 仅在开始和结束时短暂持有MDL写锁，阻塞DML
- 使用row log记录DDL期间的数据变更
Online DDL的算法
MySQL支持三种DDL算法：
1. COPY算法（传统方式）
ALTER TABLE users ADD COLUMN status INT,
ALGORITHM=COPY;
- 创建临时表，复制数据
- 全程锁表，不允许DML
- 适用于所有DDL操作
2. INPLACE算法（Online DDL）
ALTER TABLE users ADD INDEX idx_age(age),
ALGORITHM=INPLACE, LOCK=NONE;
- 不创建临时表，原地修改
- 允许并发DML（取决于LOCK级别）
- 适用于大多数索引操作
3. INSTANT算法（MySQL 8.0+）
ALTER TABLE users ADD COLUMN status INT DEFAULT 0,
ALGORITHM=INSTANT;
- 仅修改元数据，不改数据文件
- 秒级完成，无论表多大
- 仅适用于特定操作（如末尾加列、修改默认值）
LOCK级别控制
通过LOCK子句控制DDL期间的并发行为：
-- 1. LOCK=NONE：允许并发读写（最优）
ALTER TABLE users ADD INDEX idx_age(age), LOCK=NONE;
-- 2. LOCK=SHARED：允许并发读，禁止写
ALTER TABLE users MODIFY COLUMN name VARCHAR(100), LOCK=SHARED;
-- 3. LOCK=EXCLUSIVE：禁止并发读写（等同于传统DDL）
ALTER TABLE users ADD COLUMN status INT, LOCK=EXCLUSIVE;
-- 4. LOCK=DEFAULT：由MySQL自动选择最宽松的锁级别
ALTER TABLE users ADD INDEX idx_email(email), LOCK=DEFAULT;
常见DDL操作分类
| DDL操作 | 算法支持 | 锁级别 | 是否重建表 |
|---|---|---|---|
| 添加索引 | INPLACE | NONE | 否 |
| 删除索引 | INPLACE | NONE | 否 |
| 添加列（末尾） | INSTANT | NONE | 否（8.0） |
| 删除列 | INPLACE | EXCLUSIVE | 是 |
| 修改列类型 | COPY | EXCLUSIVE | 是 |
| 修改列默认值 | INSTANT | NONE | 否 |
| 添加主键 | INPLACE | EXCLUSIVE | 是 |
| 删除主键 | COPY | EXCLUSIVE | 是 |
| 修改字符集 | COPY | EXCLUSIVE | 是 |
| OPTIMIZE TABLE | INPLACE | NONE | 是 |
示例说明
示例1：添加索引（INPLACE + NONE）
-- 表有1000万行数据
ALTER TABLE orders ADD INDEX idx_user_id(user_id),
ALGORITHM=INPLACE, LOCK=NONE;
-- 执行过程：
-- 1. 扫描主键索引，构建新索引（可能耗时数分钟）
-- 2. 同时允许INSERT、UPDATE、DELETE操作
-- 3. row log记录增量变更
-- 4. 最后合并row log，短暂阻塞（毫秒级）
示例2：添加列（INSTANT）
-- MySQL 8.0+
ALTER TABLE users ADD COLUMN last_login DATETIME DEFAULT NULL,
ALGORITHM=INSTANT;
-- 执行过程：
-- 1. 仅修改表的元数据（.frm文件）
-- 2. 不修改数据文件（.ibd）
-- 3. 秒级完成，业务无感知
示例3：修改列类型（COPY）
-- 扩大VARCHAR长度
ALTER TABLE users MODIFY COLUMN name VARCHAR(200),
ALGORITHM=COPY, LOCK=EXCLUSIVE;
-- 执行过程：
-- 1. 创建临时表
-- 2. 复制全表数据（耗时长，锁表）
-- 3. 替换原表
生产环境最佳实践
1. 使用pt-online-schema-change工具
# Percona Toolkit工具，实现更平滑的Online DDL
pt-online-schema-change \
--alter "ADD INDEX idx_age(age)" \
--execute \
D=test,t=users
# 原理：
# 1. 创建影子表（新结构）
# 2. 创建触发器同步数据
# 3. 分批复制数据
# 4. 最后原子切换表
2. 控制执行时间窗口
-- 低峰期执行DDL
ALTER TABLE orders ADD INDEX idx_create_time(create_time),
ALGORITHM=INPLACE, LOCK=NONE;
-- 监控执行进度
SHOW PROCESSLIST;
-- 查看State字段：altering table、committing
3. 设置超时保护
-- 防止MDL写锁等待时间过长
SET SESSION lock_wait_timeout = 5; -- 5秒超时
ALTER TABLE users ADD INDEX idx_email(email);
4. 分批次执行
-- 不好的做法：一次性多个DDL
ALTER TABLE users
ADD INDEX idx_age(age),
ADD INDEX idx_email(email),
ADD COLUMN status INT;
-- 推荐做法：拆分执行，观察影响
ALTER TABLE users ADD INDEX idx_age(age);
-- 观察业务指标，确认无影响后继续
ALTER TABLE users ADD INDEX idx_email(email);
注意事项
- 磁盘空间：INPLACE算法虽不创建临时表，但构建索引仍需额外空间（约原表大小）
- 主从延迟：DDL在主库执行完后，从库也要重放，可能加剧延迟
- 外键约束：有外键的表DDL可能退化为COPY算法
- 版本差异：MySQL 5.6/5.7/8.0的Online DDL能力逐步增强
- row log大小：DDL期间DML过多，row log可能占满tmpdir
监控与排查
-- 查看当前DDL进度（MySQL 8.0+）
SELECT * FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE 'stage/innodb/alter%';
-- 查看MDL锁等待
SELECT * FROM performance_schema.metadata_locks
WHERE OBJECT_SCHEMA = 'test' AND OBJECT_NAME = 'users';
-- 查看临时文件使用
SHOW VARIABLES LIKE 'innodb_tmpdir';
面试答题总结
Online DDL是MySQL 5.6+提供的不锁表执行DDL的能力，通过INPLACE算法和row log增量日志，允许DDL期间并发执行DML操作。核心是仅在开始和结束时短暂持有MDL写锁，大部分时间持有MDL读锁。支持三种算法：COPY（全表复制）、INPLACE（原地修改）、INSTANT（元数据修改）。生产环境建议使用ALGORITHM=INPLACE, LOCK=NONE，配合pt-osc工具，在低峰期执行，监控磁盘空间和主从延迟。