MySQL 在线加索引怎么降低锁表风险：算法、元数据锁与回滚检查

给线上 MySQL 大表加索引，风险从来不在「索引能不能最终建完」，而在DDL等待元数据锁的间隙会不会把后续全部业务请求都卡住。更稳妥的操作思路是先确认对应MySQL版本、存储引擎和适配的索引算法，选业务低峰窗口执行，全程跟进锁等待状态和变更进度，提前备好取消操作和回滚的完整预案。

在线 DDL 不是“完全无锁”：ALGORITHM=INPLACE 或 INSTANT 只能减少数据拷贝和长时间排他锁，开始与结束阶段仍可能等待元数据锁。先检查兼容性，再执行带 LOCK 约束的语句，风险才可控。

先把“在线”拆成三个可验证的条件

很多线上故障都是把在线DDL当成点一下就完事的开关导致的。实际操作前至少要拆成三件事确认：这次变更要不要重建全表、执行过程中能不能保持业务并发读写、DDL在启动和提交阶段会不会卡元数据锁。不同的MySQL版本、存储引擎和索引类型，最终表现的结果完全不一样。

变更前先确认表、索引和版本

先不要直接执行 ALTER。把目标表的真实结构保存下来，尤其关注表引擎、主键和字段长度。示例中的 orders 只是演示名，生产环境应替换成经过评审的表名和索引名。

SELECT VERSION();
SHOW CREATE TABLE orders\G
SHOW INDEX FROM orders;

SELECT TABLE_NAME, ENGINE, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'orders';

如果目标是给 user_id 和 created_at 建联合索引，先确认查询条件和排序方向确实需要它。索引名也要保持唯一，避免部署脚本重跑时把“已存在”误判为部分成功。

ALTER TABLE orders
  ADD INDEX idx_orders_user_created (user_id, created_at),
  ALGORITHM=INPLACE,
  LOCK=NONE;

把算法和锁级别明确写进ALTER语句里，是为了在数据库能力不满足要求的时候直接报错退出。不要省略这些参数依赖客户端默认值，默认策略会跟着MySQL版本、语句类型的不同发生变化，很容易踩坑。

用元数据锁检查真正的阻塞点

在线 DDL 仍然要在开始和结束阶段申请元数据锁。一个看似没有写入的会话，只要事务没有提交，就可能让 ALTER 长时间排队。MySQL 8.0 可以从 performance_schema.metadata_locks 和当前线程信息入手：

SELECT ml.OBJECT_SCHEMA, ml.OBJECT_NAME,
       ml.LOCK_TYPE, ml.LOCK_STATUS,
       p.PROCESSLIST_ID, p.PROCESSLIST_TIME,
       p.PROCESSLIST_STATE, p.PROCESSLIST_INFO
FROM performance_schema.metadata_locks AS ml
JOIN performance_schema.threads AS t
  ON ml.OWNER_THREAD_ID = t.THREAD_ID
JOIN information_schema.PROCESSLIST AS p
  ON t.PROCESSLIST_ID = p.ID
WHERE ml.OBJECT_SCHEMA = DATABASE()
  AND ml.OBJECT_NAME = 'orders';

重点看 LOCK_STATUS='PENDING' 的会话，以及持有锁时间很长的连接。先找到事务的业务归属，再决定提交、回滚或终止连接；不要看到一个 ID 就直接执行 KILL。

执行阶段如何观察进度和业务影响

变更执行期间同时盯三类指标：DDL进程是否还在正常跑、元数据锁有没有出现排队现象、业务侧接口的P95/P99延迟有没有超过预设阈值。不同MySQL版本对进度字段的展示逻辑并不统一，所以看到没有进度百分比，不能直接判定进程已经卡死。

SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS\G

SELECT trx_id, trx_started, trx_state, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

建议在变更操作开始前先记录一份基准值：当前活跃连接数、锁等待数、目标业务接口延迟和实例磁盘剩余空间。执行过程中只对比这些指标相对基线的变化，不要凭某一个瞬时值就下判断。

出现等待时先判断是在等谁

如果ALTER线程卡在等待元数据锁状态，优先排查清理持锁的长事务；如果已经进入表重建或者索引构建阶段，就重点检查磁盘占用增长、IO负载和主从复制延迟。前者一般通过结束异常会话就能恢复正常，后者完全不适合靠反复重试来“抢进度”。

取消、重试和回滚要提前写清楚

执行窗口必须提前定好明确的停止触发条件，比如目标接口P99延迟连续多次超过基线两倍、主从复制延迟超出业务容忍上限，或者磁盘剩余空间进入告警阈值。触达条件后第一时间停掉变更，先保留完整现场信息再排查问题，不要把同一条ALTER语句复制成多个会话同时跑。

对于支持在线算法的 DDL，中途取消后是否立即释放资源、是否需要等待清理，要以实际版本行为和现场状态为准。取消前记录线程 ID、开始时间和当前进程信息；取消后重新执行 SHOW INDEX，确认没有留下目标索引的半成品状态。

-- 仅在经过确认后取消当前 DDL 线程
KILL QUERY ;

-- 变更结束后复核
SHOW INDEX FROM orders;
SHOW CREATE TABLE orders\G

常见误区与可复用的检查清单

• 把 LOCK=NONE 理解成永远不阻塞：它只是要求不使用更强的锁，不能消除元数据锁窗口。
• 只看 SQL 返回成功，不核对索引定义：还应检查列顺序、基数和实际执行计划。
• 执行前没有记录基线：没有基线就很难区分 DDL 影响和正常流量波动。
• 把失败当成可重复重试：先保存错误信息，确认没有同名索引和残留线程，再决定是否重试。

EXPLAIN SELECT order_id, created_at
FROM orders
WHERE user_id = 10086
ORDER BY created_at DESC
LIMIT 20;

最终验收至少包括：索引定义正确、目标查询使用了预期索引、业务延迟恢复到基线附近、复制链路没有持续堆积，以及变更记录中保存了执行语句和现场指标。

相关问题

为什么在线 DDL 仍会让请求短暂卡住？

因为开始和结束阶段仍可能申请元数据锁；长事务或空闲但未提交的会话会把这段等待放大。

什么时候应该优先尝试 ALGORITHM=INSTANT？

当版本和具体变更类型明确支持瞬时元数据变更时可以优先尝试；若语句不兼容，应让它失败并重新评估，而不是默认降级。

加索引成功后为什么还要看 EXPLAIN？

索引存在不等于优化器一定采用。列顺序、选择性、排序需求和统计信息都会影响最终执行计划。

总结

降低 MySQL 在线加索引风险的关键不是寻找一条“绝对无锁”的语句，而是把算法兼容性、元数据锁、进度信号和停止条件串成一个可复核的工作流。明确写出 ALGORITHM 与 LOCK，先处理长事务，再执行和验收，才能让一次索引变更具备可回退、可解释的边界。