如何借助MySQL索引优化大幅缩短Online DDL操作的锁表时间

Online DDL并非零锁表，其最终“应用日志”阶段仍需毫秒级排他锁；锁时长短取决于并发DML强度与索引有效性——未走索引的慢查询会加剧日志膨胀，延长锁表窗口。

Online DDL 并不等于“零锁表”，锁表时间长短直接取决于索引是否被有效利用——尤其在加字段、改类型、建索引等操作中，索引设计会显著影响 Online DDL 的日志回放阶段耗时和最终锁表窗口。

为什么加字段（ADD COLUMN）还会锁表？

MySQL 5.6+ 的 ALGORITHM=INPLACE 模式确实避免了全程锁表，但最后一步“应用在线日志”（apply log）仍需短暂锁表（通常毫秒级）。如果并发 DML 太多、日志太大，或原表缺乏合适索引，这个阶段可能被拖长到秒级甚至超时失败。

• DB_ONLINE_LOG_TOO_BIG 错误本质是日志缓冲区溢出，而日志大小与 DML 频率正相关——没有索引的慢查询或大范围 UPDATE 会让日志暴涨
• 即使只是 ADD COLUMN，若表上存在未提交事务或正在执行全表扫描的慢 SQL（如没走索引的 UPDATE），DDL 会被阻塞，表现为“假锁表”
• 复合索引缺失会导致 WHERE 条件无法快速定位行，间接拉长 DML 持续时间，进而推高日志累积速度

如何让 Online DDL 尽量少锁、快锁？

关键不是“要不要索引”，而是“哪些索引能压缩 DML 执行时间 + 减少日志生成量”。重点落在 DDL 期间仍在运行的业务 SQL 上。

• 确保所有高频 UPDATE/DELETE 语句的 WHERE 条件都命中索引：用 EXPLAIN 确认 type 是 const/ref/range，且 key 列非 NULL
• 避免隐式转换：比如 user_id 是 INT，但写成 WHERE user_id = '1001' → 索引失效 → 全表扫描 → 大量行被锁 → 日志暴增
• 对范围查询（如 create_time > '2024-01-01'）建复合索引时，把过滤性最强的字段放最左：例如 (tenant_id, status, create_time) 比 (create_time, tenant_id, status) 更能截断扫描范围，减少单次 DML 锁行数
• DDL 前主动清理长事务：SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60，及时 kill 或协调业务方提交

innodb_online_alter_log_max_size 调多大才安全？

这个参数不是越大越好。它控制的是 Online DDL 过程中允许累积的最大在线日志量，默认 128MB。调大能容忍更多并发 DML，但代价是“应用日志”阶段更久——也就是那个最终的短暂锁表时间会变长。

• 观察实际日志增长：执行 SHOW ENGINE INNODB STATUS，在 LOG section 查看 Online DDL log size 当前值
• 若日常 DML 峰值下该值稳定在 20MB 以内，innodb_online_alter_log_max_size = 64M 就足够，比默认更稳
• 若业务写入密集（如订单库每秒数百 UPDATE），且 DDL 必须在白天执行，可设为 256M，但务必同步确认磁盘 I/O 和内存 buffer 能支撑后续日志回放
• 注意：调大后若 DDL 中断，未应用的日志会丢失，已执行的 DML 不会回滚（这是 InnoDB Online DDL 的原子性保障机制）

真正决定 Online DDL 锁表时间的，从来不是 DDL 语句本身，而是它执行期间你线上还在跑的那些没走索引的 UPDATE —— 它们才是日志膨胀和锁表延长的源头。