MySQL恢复后重建索引对性能提升的原因

先聊聊一个常见现象：有时候你执行 SHOW INDEX 明明看到索引存在，可一用 EXPLAIN 分析查询计划时，key 字段为空，type 直接显示 ALL—— 这就是全表扫描。问题通常出在数据库备份或还原后，索引的元数据与 B+ 树的物理结构没有对齐。如果还原后不进行重建，MySQL 优化器就始终无法识别出真正的索引能力，导致查询性能严重下降。

MySQL 还原后索引失效的根本原因：统计信息与页结构错位

MySQL 查询优化器在执行计划选择时，并不直接依赖 SHOW INDEX 的输出。它实际参考两个关键依据：一是 Cardinality 值（来源于 ANALYZE TABLE），二是 .ibd 文件中存储的 B+ 树页布局。当采用物理备份还原方式（例如直接复制 .ibd 文件，或使用 mysqldump 但未加 --single-transaction 参数）时，容易出现以下问题：

• Cardinality 全部变为 0 或 NULL，即使表中实际包含百万行数据
• ibd 文件的 LSN（日志序列号）出现断层，或存在未提交的事务残留，导致 InnoDB 启动时跳过部分页的校验
• SHOW INDEX 读取的是数据字典（如 .frm 或 mysql.innodb_table_stats），而执行计划使用的是内存中加载的页结构；两者不一致时，优化器便会放弃索引，选择全表扫描

ALTER TABLE ... FORCE 强制重建索引比 ENGINE=InnoDB 更可靠

很多开发者习惯用 ALTER TABLE t ENGINE=InnoDB 重建索引，但这个操作属于隐式重建，MySQL 会尝试复用旧 .ibd 的页结构。如果还原过程中已经出现轻微损坏（例如页校验失败或 LSN 不连续），MySQL 会静默跳过异常页，导致新索引树不完整。

ALTER TABLE t FORCE 则完全不同。它等价于执行 DROP + CREATE + INSERT SELECT，强制全量重写所有数据页和索引页，绕过所有缓存和旧页的解析逻辑。该方法对大表虽然仍会锁表，但比 REPAIR TABLE 更可控。使用时需注意两个要点：

• 执行完 ALTER TABLE t FORCE 后，必须立即运行 ANALYZE TABLE t，否则优化器仍会沿用旧的统计信息
• 不要省略 FORCE——单独使用 ENGINE=InnoDB 在很多还原失效场景下无法生效

MyISAM 表无法通过 ALTER TABLE 重建索引，需使用 REPAIR TABLE

MyISAM 的索引完全独立存储在 .MYI 文件中。ALTER TABLE 只会修改 .frm 和重写 .MYD，根本不会触及 .MYI。当遇到 Incorrect key file for table 这类错误时，必须使用 REPAIR TABLE 作为终极修复手段：

• 先执行 CHECK TABLE t 检查状态；如果出现 record delete-link chain broken 错误，则需要添加 EXTENDED 参数
• REPAIR TABLE t EXTENDED 会逐行扫描 .MYD 并重新构建 .MYI，耗时较长且 IO 压力较大
• 修复前务必确认 SELECT @@tmpdir 指向的路径有足够空间，临时文件 t.TMD 的大小约为原索引文件的两倍
• 修复完成后，必须执行 FLUSH TABLES 或重启 MySQL，否则缓存中的旧索引描述符仍会生效

验证索引重建效果：不可仅依赖 SHOW INDEX，需三步核查

重建索引不是让命令不报错就结束，最终目标必须是让查询变快。验证必须分三步走，缺一不可：

• 首先查询 SHOW INDEX FROM t，确认 Cardinality 不为零，且与实际行数规模匹配
• 其次执行 EXPLAIN SELECT * FROM t WHERE indexed_col = ?，确认 key 列显示正确的索引名称，且 type 不是 ALL
• 对于唯一索引，还需执行关键一步：尝试 INSERT INTO t (indexed_col) VALUES (existing_value)，必须返回 Duplicate entry 错误；若悄无声息地插入成功，说明唯一性约束未恢复

最后这一步最容易被忽略。许多重建操作表面上成功，但唯一性约束实际上并未恢复。如果忽视这一点，后续业务迟早会引发严重的数据一致性问题。