直接CREATE INDEX在千万级表上会卡住，因InnoDB默认就地构建索引并加表级写锁，导致DML阻塞，且易因I/O、buffer pool或临时空间不足引发死锁或连接丢失。
为什么直接 CREATE INDEX 在千万级表上会卡住？
因为 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。
