MySQL 在线 DDL评估加索引时的锁与空间的实现方法

给 InnoDB 大表加二级索引时，真正要评估的不是“在线 DDL 会不会锁表”，而是两个更具体的问题：DDL 在构建索引期间允许多少并发，以及最后提交新表定义时有没有长事务挡住元数据锁。空间也不能只看新索引本身，还要预留并发变更日志、临时排序文件，某些操作还会出现短暂的中间表文件。

先把在线 DDL 的目标写进 ALTER TABLE

如果只是希望“尽量在线”，直接执行默认 ALTER TABLE 很难在发布前证明它没有退化。更稳妥的做法是把算法和锁级别写出来，让 MySQL 在能力不满足时立即报错。

-- 只新增二级索引；如果当前表不支持这组约束，让语句立即失败
ALTER TABLE orders
  ADD INDEX idx_customer_created (customer_id, created_at),
  ALGORITHM=INPLACE,
  LOCK=NONE;

LOCK=NONE 的含义是允许并发查询和 DML；它不是“完全没有锁”，而是要求这项原地变更不能阻断正常读写。对新增索引来说，构建阶段会读取表数据并接收并发修改，结束时仍需要把这些修改合并到索引并提交新的表定义。

锁的风险集中在元数据提交，不只看 LOCK=NONE

MySQL 官方把在线 DDL 分为初始化、执行和提交表定义几个阶段。初始化会取得可升级的共享元数据锁；执行阶段主要构建索引；提交阶段需要升级为排他元数据锁，以替换旧的表定义。这个排他锁通常很短，但必须等持有表元数据锁的事务提交或回滚。

因此，发布前要查的不是有没有普通行锁，而是有没有“打开事务后长时间不结束”的会话。可以先从进程列表观察：

-- 查找 DDL 本身和可能阻塞它的会话；Time 较大时优先核对事务状态
SHOW FULL PROCESSLIST;

-- MySQL 8.0+ 可查看元数据锁依赖；只读查询，不会改变锁状态
SELECT OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION,
       LOCK_STATUS, OWNER_THREAD_ID
FROM performance_schema.metadata_locks
WHERE OBJECT_NAME = 'orders';

如果 DDL 显示 Waiting for table metadata lock，先定位持锁事务和业务连接池，而不是反复重跑 ALTER。还要注意排队效应：DDL 正在等待排他元数据锁时，后续访问同一表的事务也可能被它挡住，短 DDL 也会因此放大成请求延迟。

空间预算要覆盖三种临时对象

新增索引的磁盘准备可以按三类对象拆开。第一类是最终二级索引本身；第二类是在线 DDL 为记录并发 DML 而增长的临时日志，其上限受 innodb_online_alter_log_max_size 控制；第三类是索引构建过程的临时排序文件，它们通常写入 tmpdir 或 innodb_tmpdir。

对需要重建表的在线操作，官方还提示可能创建以 #sql-ib 开头的中间表文件，空间可能接近原表大小。新增二级索引常见的是在线索引构建，但不要只按“新索引大小”做统一承诺，应先在同版本、相近数据量的副本或克隆表上测量。

用小规模演练验证时间、行数和余量

生产表很大时，先克隆表结构，灌入一小批具有相似索引分布的数据，再执行同一条 ALTER。命令结束后的 rows affected 可以帮助判断是否发生了表数据复制：新增索引常见为 0 行受影响，而修改列类型等重建类操作会出现非零值。这个信号不是完整性能报告，却足以筛掉明显不适合在线窗口的方案。

演练记录四个数：DDL 总耗时、提交阶段等待时间、临时目录峰值、在线变更日志峰值。再把生产写入峰值代入复核。若磁盘余量只够静态索引、没有日志和排序空间，或者演练已经出现明显 metadata lock 等待，就应该改用副本逐台变更、低峰执行或专门的在线变更工具，而不是把 LOCK=NONE 当作保证。

常见问题

LOCK=NONE 是不是完全不会锁表？

不是。它允许并发读写，但提交表定义时仍可能短暂申请排他元数据锁，并等待长事务释放。

innodb_online_alter_log_max_size 越大越好吗？

不是。上限更大能容纳更多并发 DML，但收尾时需要应用更多变更，最终锁定阶段可能变长，也会增加空间预算。

加一个二级索引为什么还要检查临时目录？

索引创建可能使用临时排序文件，文件通常写入 MySQL 临时目录；目录空间不足会让在线 DDL 失败。

评估在线加索引时，把“锁”和“空间”放在同一张发布清单里：算法约束保证不会静默退化，元数据锁检查避免长事务卡住提交，临时对象预算则决定这次变更是否真的具备上线条件。