MySQL 在线加索引怎么降低锁表风险：ALGORITHM 与 LOCK 选项的选择

线上订单表准备加一个组合索引时，最容易误判的是把“在线”理解成“完全不影响业务”。MySQL 的 ALGORITHM 决定变更采用哪种 DDL 算法，LOCK 决定允许多大程度的并发访问；真正上线前，还要确认表结构、存储引擎和当前会话能否支持这两个选项。

大表加索引时，ALGORITHM 和 LOCK 各管什么

ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at) 描述的是结果，ALGORITHM 和 LOCK 描述的是实现边界。前者影响表重建或原地变更的路线，后者约束 DDL 期间读写是否能继续。两者放在同一条语句里，才方便让上线行为可检查。

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

这条语句表达的是：优先采用 INPLACE，并要求尽量不阻塞并发读写。它不是承诺任何表都能无锁完成；如果当前表结构或操作类型不支持，MySQL 可能直接报错。生产环境更需要这个“失败得明确”的特性。

为什么不能只把 LOCK=NONE 当成保险

LOCK=NONE 主要限制并发访问的锁级别，但开始和结束阶段仍可能需要短暂的元数据锁。只要有一个长事务一直持有 orders 的元数据访问，DDL 就可能在切换阶段等待；这时业务查询看起来正常，变更却迟迟不结束。

因此上线前要把两个问题分开：第一，当前索引操作是否支持目标算法；第二，变更时是否有长事务挡住表定义切换。只验证第一项，仍然可能在生产卡住。

先在低峰环境验证同一条 ALTER TABLE

验证表应尽量接近生产表结构和数据量，至少确认索引列顺序、字符集、已有索引名没有冲突。建议先执行：

SHOW CREATE TABLE orders;
SHOW INDEX FROM orders;

再执行带有明确算法和锁级别的 DDL。记录开始时间、结束时间、错误信息以及期间的查询延迟；如果出现“不支持算法”或锁等待，先改方案，不要直接删除 ALGORITHM 和 LOCK 让数据库自行选择。

DDL 卡住时，按 metadata lock 路径排查

当终端或发布系统显示 ALTER TABLE 长时间没有完成，先查看会话，而不是再次提交同一条语句：

SHOW PROCESSLIST;

重点看等待中的 ALTER TABLE、持有事务时间很长的会话，以及是否存在针对 orders 的 metadata lock。找到阻塞源后，由值班人员结合事务归属决定提交、回滚或终止会话。这里别急着杀掉所有连接，误杀业务事务会把一次索引变更扩大成数据恢复问题。

三种选择放在一个决策表里

常见问题与边界

LOCK=NONE 是否代表完全不会阻塞读写？

不是。它约束的是允许的锁级别，DDL 开始和结束时仍可能等待元数据锁，长事务也会拉长等待时间。

不写 ALGORITHM 会更安全吗？

不一定。省略后由 MySQL 自行选择实现方式，可能得到与预期不同的资源消耗。对生产变更，更适合先明确可接受的算法并让不支持时快速失败。

ALTER TABLE 卡住了要不要马上重试？

不要。先用 SHOW PROCESSLIST 定位等待关系，确认是否为 metadata lock 或长事务，再决定处理阻塞源还是取消变更。

把索引变更做成可验收的操作

一次稳妥的在线加索引，不是把命令贴进发布窗口就结束，而是先验证结构和算法，再用明确的 LOCK 约束并发影响，最后为元数据锁等待准备观察和回退动作。这样即使变更不能按计划执行，也会在可控的位置失败。