MySQL 在线 DDL 怎样规划索引变更窗口

MySQL 的在线 DDL 不是“执行期间完全没有影响”。以 InnoDB 二级索引为例，创建索引通常可以在表继续读写时进行，但初始化和收尾仍可能等待元数据锁，在线变更还会消耗临时空间并记录并发 DML。规划窗口时，核心不是找一个“绝对安全”的时间，而是先锁定算法与并发级别，再把等待、磁盘和长事务列入退出条件。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-performance.html

对普通二级索引，先在测试环境确认 ALGORITHM=INPLACE, LOCK=NONE 可用；生产窗口只在长事务较少、临时空间充足、能观察元数据锁的时段执行。若声明的算法或锁级别不被当前操作支持，应让语句失败并重新评估，而不是接受未知的降级路径。

1. 先判断这次索引变更会走哪条路径

索引操作要先看对象和存储引擎。MySQL 8.4 文档把“添加二级索引”列为可 INPLACE、可并发 DML、但不是只改元数据的操作；删除或重命名普通索引的资源特征又不同。不要因为都写成 ALTER TABLE 就把它们当成同一种变更。

如果目标是普通二级索引，可以先用明确参数试运行一条最小变更语句。参数不兼容时让 MySQL 报错，比让默认策略替你选择一个未知路径更容易复盘。

2. 用 ALGORITHM 和 LOCK 把窗口边界写进语句

ALGORITHM 描述执行算法，LOCK 描述并发访问限制。对需要保持业务读写的普通二级索引，常见起点如下；它表达的是“若不能满足这两个条件就失败”，不是对所有表都适用的保证。

-- 只对目标表添加普通二级索引，并拒绝不满足预期的路径
ALTER TABLE orders
  ADD INDEX idx_orders_customer_created (customer_id, created_at),
  ALGORITHM=INPLACE,
  LOCK=NONE;

-- 执行前先记录当前连接和表名，便于把等待定位到同一变更
SELECT CONNECTION_ID() AS connection_id, DATABASE() AS database_name;

-- 仅查看元数据锁等待，不把这条查询当作线上验证替代品
SELECT OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS
FROM performance_schema.metadata_locks
WHERE OBJECT_NAME = 'orders';

LOCK=NONE 允许并发查询和 DML，但语句仍可能在开始或结束阶段等待短暂的排他元数据锁。LOCK=SHARED 会阻塞 DML，LOCK=EXCLUSIVE 则会阻塞查询和 DML。窗口规划时应把锁级别当作业务可接受的停顿上限，而不是只看 ALTER 主体是否“在线”。

3. 把长事务、磁盘和在线日志放进窗口设计

真正容易被忽略的是变更周边的资源。创建二级索引时，MySQL 需要临时排序文件和在线变更日志；并发 DML 很重时，在线日志可能增长到上限。另一方面，一个迟迟不提交的事务可能让 DDL 在最终切换处等待，即使主体阶段一直允许业务写入。

-- 观察 InnoDB 事务年龄，优先处理明显拖尾的长事务
SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

-- 执行前确认临时目录和数据目录仍有可用空间
SHOW VARIABLES LIKE 'tmpdir';
SHOW VARIABLES LIKE 'innodb_tmpdir';

-- 记录在线变更日志上限，避免只盯着 ALTER 的会话输出
SHOW VARIABLES LIKE 'innodb_online_alter_log_max_size';

这几项不是要求你提前猜出完成时间，而是帮助定义停止条件：磁盘逼近安全线、在线日志持续增长、元数据锁等待超过业务容忍值，或长事务无法解释时，暂停新增变更并保留现场信息。索引 DDL 不能像普通事务那样靠 ROLLBACK 还原已完成的结构变化，所谓回滚预案通常是停止本次操作、保留旧索引并另行设计反向 DDL。

4. 用一张执行清单决定是否进入窗口

1. 变更对象确认：表是 InnoDB，索引是普通二级索引，列顺序和命名已评审。
2. 算法确认：用 ALGORITHM=INPLACE 和 LOCK=NONE 表达最低可接受条件。
3. 业务确认：窗口内没有批量导入、归档、重试风暴或大量长事务。
4. 资源确认：数据目录、tmpdir、innodb_tmpdir 和在线日志上限有余量。
5. 退出确认：明确谁观察元数据锁、谁记录错误、何时停止后续变更。
6. 结果确认：完成后再检查索引定义和执行计划，不把“命令返回成功”当成查询收益证明。

对于一次只添加普通二级索引的变更，适合把“语句失败”作为安全边界，把“等待超过阈值”作为窗口退出信号，把“索引是否改善目标查询”作为后续独立评估。这样做比单纯安排一个凌晨时间更可靠。

常见问题

在线 DDL 为什么还会卡住？主体阶段可以并发 DML，但开始或结束阶段仍需要元数据锁；长事务或其他 DDL 会让这一步等待。

为什么不直接省略 ALGORITHM 和 LOCK？默认策略会尽量选择可行路径，但显式写出最低接受条件能让不兼容场景直接失败，减少窗口内的意外降级。

添加索引失败后能不能 ROLLBACK？不要把 ALTER 当成业务事务设计。应根据错误保留现场、停止后续变更，并按当前索引状态设计下一次操作。