在线DDL详解

一、什么是在线DDL？

DDL（数据定义语言）用于修改表结构（如ALTER TABLE、CREATE INDEX、DROP COLUMN等）。传统DDL（如MySQL 5.6之前）采用COPY方式：

1. 创建新表（复制旧表结构）；
2. 复制旧表数据到新表；
3. 替换旧表。
   此过程会全程锁表（排他锁），导致业务读写完全中断，无法应对高并发场景。

在线DDL的核心目标是最小化锁表时间，允许在DDL执行过程中并发读写（仅在关键阶段短暂锁表）。其关键改进是INPLACE方式（无需全表复制），通过日志记录和增量同步实现。

二、在线DDL的发展历程

MySQL的在线DDL支持随版本逐步增强：

• MySQL 5.6：首次引入InnoDB在线DDL，支持添加列、添加辅助索引、修改列默认值等操作，锁表时间大幅缩短。
• MySQL 5.7：扩展支持修改列位置、添加主键（无主键表）、修改列字符集（部分情况）、在线删除索引等。
• MySQL 8.0：进一步增强，支持在线添加自增列、在线修改列类型（部分情况）、在线删除主键、并行DDL（如并行创建索引）等。

三、在线DDL的实现原理（以InnoDB为例）

InnoDB的在线DDL采用INPLACE算法，核心流程分为4个阶段，仅在准备和提交阶段短暂锁表：

1. 准备阶段（Short Lock）

• 操作：创建新表结构（.frm元数据文件、.ibd数据文件）；
• 锁：加共享锁（SHARED），禁止写操作（允许读）；
• 时间：极短（仅元数据初始化）。

2. 执行阶段（Concurrent Read/Write）

• 操作：
  a. 扫描旧表数据，复制到新表；
  b. 开启row log（行日志），记录此阶段对旧表的写操作（INSERT/UPDATE/DELETE）；
• 锁：无排他锁，允许并发读/写（共享锁不阻塞读，写操作被记录到row log）；
• 关键：row log确保DDL过程中，新表与旧表的数据一致性。

3. 应用日志阶段（Concurrent Read）

• 操作：复制完旧数据后，将row log中的增量修改应用到新表；
• 锁：允许读，写操作短暂阻塞（需同步增量数据，时间取决于row log大小）。

4. 提交阶段（Short Lock）

• 操作：替换旧表与新表（修改元数据指向新表）；
• 锁：加排他锁（EXCLUSIVE），禁止读/写；
• 时间：极短（仅元数据切换）。

四、在线DDL的关键参数

• ALGORITHM：指定DDL算法（必选）：
  • ALGORITHM=INPLACE：在线方式（推荐）；
  • ALGORITHM=COPY：传统方式（锁表，不推荐）；
  • ALGORITHM=DEFAULT：由MySQL自动选择（优先INPLACE）。
• LOCK：指定锁类型（可选）：
  • LOCK=NONE：允许并发读/写（最宽松，推荐）；
  • LOCK=SHARED：允许读，禁止写；
  • LOCK=EXCLUSIVE：禁止读/写（等同于COPY方式）；
  • LOCK=DEFAULT：由MySQL自动选择（优先NONE）。

示例：

ALTER TABLE users 
ADD COLUMN age INT NOT NULL DEFAULT 0, 
ALGORITHM=INPLACE, 
LOCK=NONE;

五、支持/不支持在线DDL的操作

1. 完全支持在线（INPLACE + LOCK=NONE）

• 添加列（如ADD COLUMN）；
• 添加辅助索引（如ADD INDEX）；
• 修改列默认值（如ALTER COLUMN ... SET DEFAULT）；
• 修改列位置（如MODIFY COLUMN ... AFTER name）；
• 删除列（如DROP COLUMN，MySQL 8.0+）；
• 删除索引（如DROP INDEX）；
• 修改表名（如RENAME TABLE，本质是元数据操作）。

2. 部分支持在线（需满足条件）

• 修改列类型：
  • 允许长度扩展（如varchar(10)→varchar(20)）、字符集兼容修改（如utf8→utf8mb4，需确保列长度足够）；
  • 禁止类型转换（如int→varchar）、长度缩短（如varchar(20)→varchar(10)），需用COPY方式。
• 添加主键：
  • 若表无主键，添加主键（如ADD PRIMARY KEY (id)）支持在线（MySQL 5.7+）；
  • 若表有主键，替换主键（如DROP PRIMARY KEY + ADD PRIMARY KEY）需用COPY方式（锁表）。
• 修改列字符集：
  • 若字符集兼容（如utf8→utf8mb4），且列长度足够（如varchar(255)→varchar(255)，因utf8mb4占4字节），支持在线；
  • 若字符集不兼容（如latin1→utf8），需用COPY方式。

3. 不支持在线（必须用COPY方式）

• 修改存储引擎（如ENGINE=MyISAM→InnoDB）；
• 改变列的NULL属性（如NOT NULL→NULL需检查数据，NULL→NOT NULL需更新数据）；
• 修改表的字符集（如ALTER TABLE ... CHARACTER SET utf8mb4，需转换全表数据）；
• 添加全文索引/空间索引（MySQL 5.7+支持在线，但资源消耗大）；
• 拆分/合并分区（如REORGANIZE PARTITION）。

六、在线DDL的注意事项

1. 性能影响

• IO/CPU消耗：复制数据、应用日志会占用大量IO和CPU，建议在业务低峰期执行；
• row log大小：innodb_online_alter_log_max_size（默认128M）控制row log容量，若满则暂停DDL，需调整此参数（如set global innodb_online_alter_log_max_size=256M）；
• 复制延迟：主库执行在线DDL后，从库需同步执行，可能导致从库延迟（建议用pt-online-schema-change监控延迟）。

2. 锁等待问题

• 长事务阻塞：若有长事务（如未提交的UPDATE）持有排他锁，DDL的准备阶段会等待（Lock wait timeout exceeded）；
• 解决方法：执行DDL前，用show processlist查看长事务，用kill结束（如kill 1234）。

3. 回滚风险

• 原子性：MySQL 5.7+支持DDL原子性（要么全成，要么全败），但回滚时间取决于DDL进度（如已复制90%数据，回滚需删除新表，恢复旧表，时间很长）；
• 备份建议：执行DDL前，务必做全量备份（如mysqldump、xtrabackup）。

4. 版本兼容性

• 不同版本支持的在线DDL操作不同（如MySQL 8.0支持在线添加自增列，5.7不支持），需确认版本支持性；
• 集群环境（如InnoDB Cluster）中，所有节点需版本一致，否则DDL可能失败。

七、在线DDL的优化技巧

1. 使用pt-online-schema-change工具

MySQL自带的在线DDL虽好，但pt-online-schema-change（Percona Toolkit）提供更灵活的控制：

• 优势：
  a. 回滚成本低（失败仅删除临时表）；
  b. 监控复制延迟（延迟超过阈值则暂停）；
  c. 分批次复制数据（减少对业务的冲击）；
  d. 支持更多操作（如修改主键）。
• 示例：

  pt-online-schema-change -u root -p password -h localhost \
  D=test,t=users \
  --alter "ADD COLUMN age INT NOT NULL DEFAULT 0" \
  --execute

  （注：D为数据库，t为表，--alter为DDL语句，--execute为执行操作）。

2. 拆分大表DDL

• 若表数据量极大（如1亿行），建议拆分DDL操作：
  a. 先添加列（在线）；
  b. 再添加索引（在线）；
  c. 避免一次性执行多个操作（如ADD COLUMN + ADD INDEX），减少资源消耗。

3. 并行DDL（MySQL 8.0+）

• innodb_parallel_create_index：允许并行创建索引（默认开启，set global innodb_parallel_create_index=1）；
• innodb_parallel_dml：允许并行DML（如插入数据，set global innodb_parallel_dml=1）；
• 并行DDL可大幅缩短大表索引创建时间（如1亿行表，并行创建索引时间缩短50%以上）。

4. 监控DDL进度

• information_schema.processlist：查看DDL进程状态（如ALTER TABLE的State为copy to tmp table）；
• performance_schema.ddl_log：查看DDL历史记录（如执行时间、状态）；
• show engine innodb status：查看InnoDB状态（如Online DDL部分的进度）。

八、常见问题解答

1. 在线DDL执行过程中，业务读写会被阻塞吗？

• 大部分时间不会：执行阶段（复制数据+记录row log）允许并发读/写；
• 短暂阻塞：准备阶段（共享锁，禁止写）、提交阶段（排他锁，禁止读/写），时间极短（毫秒级）。

2. 在线DDL失败后，表结构会回滚吗？

• MySQL 5.7+：支持DDL原子性，失败后表结构回滚到原始状态；
• MySQL 5.6及以下：不支持原子性，可能留下中间状态（如部分列添加成功），需手动恢复。

3. 在线添加索引会影响查询性能吗？

• 会：创建索引需扫描表数据，占用IO/CPU，可能导致查询延迟升高；
• 优化：用pt-online-schema-change的--max-load参数（如--max-load Threads_running=50），当负载超过阈值时暂停DDL。

九、总结

在线DDL是MySQL应对高并发场景的核心功能，其INPLACE算法通过日志记录和增量同步实现了最小化锁表。使用时需注意：

• 选择支持在线的操作（如添加列、添加索引）；
• 用ALGORITHM=INPLACE和LOCK=NONE指定在线方式；
• 避免在业务高峰期执行；
• 用pt-online-schema-change增强控制；
• 做好备份，防范回滚风险。

随着MySQL版本的升级，在线DDL的支持范围和性能不断提升，已成为后端开发中修改表结构的首选方式。