MySQL 大表新增索引如何降低阻塞：在线变更算法、锁窗口与回滚检查

订单列表刚加了一个筛选条件，查询已经验证需要二级索引，但生产表有几千万行，直接执行 ALTER TABLE 最担心的不是语句写不出来，而是某个长事务把最后的锁窗口拖住。对 InnoDB 表，新增二级索引通常可以采用在线 DDL，让并发 DML 继续进行；不过 LOCK=NONE 不等于“全程没有锁”，最终更新表定义时仍可能等待 metadata lock。

把新增索引拆成“能力确认、低峰执行、锁窗口观察、结果验收”四段，并为 DDL 设置可停止的边界，通常比盲目追求一条永不阻塞的 SQL 更稳。

线上出现什么信号，说明新增索引要先停一下

典型现场是：查询优化器已经把 orders_customer_created 列为候选，但业务高峰不能停写；与此同时，连接池里出现等待，慢日志却没有明显的长 SQL。这个组合要先区分两件事：索引构建本身消耗磁盘和 CPU，metadata lock 则可能让 DDL 或后续访问排队。

先在独立会话查看活跃事务和等待者：

SHOW FULL PROCESSLIST;

SELECT OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION,
       LOCK_STATUS, OWNER_THREAD_ID
FROM performance_schema.metadata_locks
WHERE OBJECT_SCHEMA = 'shop' AND OBJECT_NAME = 'orders';

看到 Waiting for table metadata lock 时，不要马上杀掉最老的连接。先确认它是否正在提交订单、是否有未提交事务，以及业务是否允许终止。锁等待说明“谁挡住了表定义变化”，不等于已经证明新增索引的算法不支持。

先确认这张表能不能安全使用在线 DDL

这次目标是 InnoDB 的普通二级索引，不是主键重建，也不是把多个结构变化混在一条语句里。MySQL 8.4 手册列出的在线 DDL 能力显示，创建或添加二级索引支持 INPLACE，并允许并发 DML，但它不是只改数据字典的瞬时操作。

在变更窗口前核对引擎、索引现状和磁盘余量：

SHOW CREATE TABLE shop.orders;
SHOW INDEX FROM shop.orders;

ALTER TABLE shop.orders
  ADD INDEX orders_customer_created (customer_id, created_at),
  ALGORITHM=INPLACE,
  LOCK=NONE;

如果索引已经存在，不要靠重复执行来“试一下”。先从 SHOW INDEX 确认索引名与列顺序；如果表结构或存储引擎让指定算法不适用，宁可让语句明确失败，也不要静默退回不可接受的表复制路径。

处理步骤：把锁窗口和构建过程分开盯

1. 在低峰先清理可解释的长事务

按业务定义的阈值检查事务年龄。只处理已确认不会影响订单一致性的会话，并让应用侧先完成提交或回滚。不要把“连接很老”当成直接杀会话的理由。

2. 执行一条边界清楚的 ALTER TABLE

将本次变更限制为一个索引，记录开始时间、操作者和目标表。DDL 启动后同时观察 SHOW FULL PROCESSLIST 与服务器资源；即使 DML 能继续，构建索引仍会占用 I/O 和 CPU。

3. 收尾阶段重点看 metadata_locks

若构建已经接近完成但仍未返回，优先检查谁持有表的 metadata lock。MySQL 会在事务结束前保留相关元数据锁，DDL 可能因此等待；只有事务提交后，提交释放才会让等待链继续。确认阻塞者后，再依据发布预案选择等待、推动提交或停止本次变更。

回滚路径：失败后先看索引状态，再决定下一步

新增二级索引不是修改业务数据，通常不需要执行一条“反向事务”来回滚。若 DDL 明确失败，先查看：

SHOW INDEX FROM shop.orders
WHERE Key_name = 'orders_customer_created';

如果索引不存在，保留错误信息、阻塞会话和资源曲线，修正变更窗口或表结构后再重试；如果索引已经存在，则不要重复创建，应转入查询计划验收。删除索引也属于 DDL，需要重新评估锁窗口：

ALTER TABLE shop.orders
  DROP INDEX orders_customer_created,
  ALGORITHM=INPLACE,
  LOCK=NONE;

真正需要回退的往往不是“索引数据”，而是应用发布后对新索引的依赖。先回退查询提示或代码配置，再安排索引删除，避免应用和表结构同时处于半完成状态。

告警确认与最终验收

变更完成后至少核对三类结果：SHOW INDEX 能看到索引且列顺序正确；应用连接池的 metadata lock 等待消失；目标查询用 EXPLAIN 或 EXPLAIN ANALYZE 复查查询路径。不要只看 DDL 客户端返回成功，因为它不能替代查询计划和业务延迟复核。

EXPLAIN SELECT order_id, created_at
FROM shop.orders
WHERE customer_id = 90017
ORDER BY created_at DESC
LIMIT 50;

如果优化器仍未选中索引，先检查统计信息、过滤条件和排序方向，不能仅凭“索引已存在”断言查询一定变快。

常见问题

LOCK=NONE 是否代表 ALTER TABLE 完全不会阻塞？

不是。它表示尽量允许并发读写，但开始或最终更新表定义时仍可能等待排他 metadata lock。

新增二级索引失败后需要立刻 DROP INDEX 吗？

不需要。先用 SHOW INDEX 确认索引是否存在；只有确认已创建且业务不再需要时，才按独立变更计划删除。

为什么 DDL 等待时后面的 SELECT 也变慢？

待处理的排他元数据锁可能让后续请求排队。应结合 metadata_locks 和 SHOW FULL PROCESSLIST 定位持有者与等待者。

复盘清单

把本次索引名、ALTER TABLE 原文、表规模、执行窗口、阻塞事务、资源峰值和验收查询写进变更记录。下一次遇到类似筛选需求，可以先复用证据和阈值，而不是重新凭经验猜测锁风险。