如何高效利用InnoDB技术快速构建海量MySQL数据库索引？

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

由于InnoDB默认在原表上就地构建索引，全表加表级写锁（ALGORITHM=INPLACE），仅部分操作支持，但建立索引仍需大量I/O和排序（），因此所有INSERT/UPDATE/DELETE操作都会被阻塞。更糟糕的是，如果磁盘I/O、buffer pool不足或临时文件空间不足，可能会出现ERROR 1205 (HY000): Deadlock found when trying to get lock或ERROR 2013 (HY000): Lost connection to MySQL server during query等错误。

ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE 真的能无锁建索引吗？

不能一概而论。MySQL 5.6+ 的 ALGORITHM=INPLACE 对 ADD INDEX 是「仅元数据变更 + 后台异步构建」，但前提是：该索引列允许为 NULL、未启用 innodb_force_primary_key、且不是主键或唯一约束（唯一约束需校验重复值，必须扫描全表）。实测中，即使加了 LOCK=NONE，一旦后台线程触发 full table scan（比如建唯一索引），仍会隐式升级为 LOCK=SHARED，导致写入短暂阻塞。

• 推荐先用 SHOW CREATE TABLE table_name 检查字段是否含 NOT NULL 或 UNIQUE；若含，改用 ALGORITHM=COPY 配合从库操作更稳妥
• 确认 innodb_sort_buffer_size（建议调至 8M~32M）和 tmpdir 所在磁盘空间 ≥ 表数据大小 × 1.5
• 执行前用 SELECT COUNT(*) FROM table_name 预估耗时 —— 若返回要秒级以上，说明表太大，别硬扛

真正可行的线上快速建索引方案

核心思路是「避开主库、绕过锁、分阶段交付」。没有银弹，只有取舍：

• 首选从库建索引：STOP SLAVE; ALTER TABLE t ADD INDEX idx_col(col); START SLAVE;，完成后做主从切换（需提前演练切换脚本）
• 若必须主库操作，用 pt-online-schema-change（Percona Toolkit）：它创建影子表、逐步拷贝数据、用触发器同步增量，全程业务可写，但会增加主从延迟和磁盘压力
• 极大数据量（如 >5 亿行）且允许短时只读，用「临时表法」：CREATE TABLE t_new LIKE t; ALTER TABLE t_new ADD INDEX ...; INSERT INTO t_new SELECT * FROM t; RENAME TABLE t TO t_old, t_new TO t; —— 注意第三步的 INSERT ... SELECT 必须加 SET SESSION sort_buffer_size = 268435456; 并监控 SHOW PROCESSLIST 中的 Copying to tmp table 状态

建完索引后必须验证的三件事

建完不等于生效。InnoDB 的索引在 buffer pool 里冷启动时，首次查询依然慢。别跳过这三步：

• 用 EXPLAIN FORMAT=JSON SELECT ... WHERE col = ? 确认 key 字段命中新建索引，且 rows 显著下降（非 filtered: 100 却 rows 还是百万级，说明没走对）
• 检查 SHOW INDEX FROM table_name 中 Seq_in_index 和 Cardinality 是否合理（Cardinality 接近表行数才说明统计信息有效；若为 0 或极低，手动执行 ANALYZE TABLE table_name）
• 观察 information_schema.INNODB_METRICS 中 index_page_reorg_attempts 和 index_page_reorg_successful，比值过低说明 B+Tree 分裂频繁，可能需要调整 innodb_page_size 或拆分查询逻辑

最常被忽略的是索引统计信息滞后和首次缓存预热 —— 建完立刻压测，不如先跑一条 SELECT id FROM table_name WHERE indexed_col = 'xxx' LIMIT 1000 把叶子节点页加载进 buffer pool。