如何在线为大表高效添加普通索引？

本文共计684个文字，预计阅读时间需要3分钟。

MySQL 5.6 及以上版本支持 Online DDL，可在不阻塞写操作（INSERT/UPDATE/DELETE）的情况下，提前为大型表添加普通索引。关键在于使用 ALGORITHM=INPLACE 和 LOCK=NONE（或尽可能宽松的锁级别），并确保满足前提前条件。

确认 MySQL 版本与存储引擎

添加索引在线化的前提是：

• MySQL 版本 ≥ 5.6（推荐 5.7 或 8.0，稳定性与并发控制更好）
• 表使用 InnoDB 存储引擎（MyISAM 不支持真正的 Online DDL）
• 未启用 innodb_file_per_table=OFF（否则无法使用 INPLACE 算法）

执行在线加索引的标准语法

直接使用 ALTER TABLE ... ADD INDEX 即可，MySQL 会自动选择最优算法（只要满足条件）：

ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

如需显式控制行为（增强可预期性），可指定：

ALTER TABLE orders ADD INDEX idx_user_status (user_id, status) ALGORITHM=INPLACE, LOCK=NONE;

若提示 LOCK=NONE 不被允许（例如涉及主键、唯一约束变更，或存在外键），MySQL 会降级为 LOCK=SHARED（允许读、阻塞写），仍比 LOCK=EXCLUSIVE（完全锁表）友好得多。

提前验证与规避风险

加索引前建议做三件事：

• 检查表结构与索引现状：用 SHOW CREATE TABLE orders\G 查看当前索引、字符集、行格式（需为 Dynamic 或 Compressed）
• 预估空间占用：新索引大小 ≈ 行数 × （字段总字节数 + 6~8 字节开销）。确保 innodb_temp_data_file_path 所在磁盘有足够临时空间（尤其对上百 GB 表）
• 避开业务高峰执行：即使 LOCK=NONE，大量 I/O 和 CPU 消耗仍可能拖慢查询响应；可配合 pt-online-schema-change（Percona Toolkit）做更平滑的灰度切换

监控执行过程与结果

执行期间可通过以下方式观察进度：

• SHOW PROCESSLIST; —— 查看是否处于 altering table 状态
• SELECT * FROM information_schema.INNODB_TRX\G —— 确认无长事务阻塞 DDL
• MySQL 5.7+ 支持 sys.schema_table_statistics 或性能模式中的 events_stages_current 跟踪阶段耗时

完成后用 SHOW INDEX FROM orders; 验证索引已生效，并用 EXPLAIN 测试目标查询是否命中新索引。