mysql如何进行索引重建_mysql索引维护与优化

1次阅读

alter table … engine=innodb 会重建整张表(含聚簇索引和所有二级索引),而非仅重建索引;mysql 5.6+ 虽支持在线 ddl,但该操作仍强制表拷贝。

mysql如何进行索引重建_mysql索引维护与优化

ALTER TABLE … ENGINE=InnoDB 会重建索引吗

会,但不是“只重建索引”,而是重建整张表(含聚簇索引 + 所有二级索引)。MySQL 5.6+ 支持 ALGORITHM=INPLACE 的在线 DDL,但 ENGINE=InnoDB 强制触发表拷贝(即使当前就是 InnoDB),所有索引都会被重新生成。

常见误操作:ALTER TABLE t ENGINE=InnoDB; 本意是“修复碎片”,结果引发长事务阻塞、磁盘爆满。真正需要的往往是更轻量的操作。

  • 仅整理碎片、释放空闲页:用 OPTIMIZE TABLE t;(InnoDB 下等价于 ALTER TABLE t FORCE, ALGORITHM=INPLACE
  • 想重建特定二级索引(比如怀疑 idx_name 损坏):先 DROP INDEX idx_name ON t;,再 CREATE INDEX idx_name ON t (col);
  • MySQL 8.0+ 可用 ALTER TABLE t ALTER INDEX idx_name REBUILD; —— 这才是真正的“只重建该索引”

什么时候必须重建索引

索引不是越勤重建越好。大多数情况下,InnoDB 自动管理 B+ 树分裂与合并,无需人工干预。真正需重建的信号很明确:

  • 执行 SHOW INDEX FROM t; 发现 Cardinality 列长期为 0 或远低于实际行数(说明统计信息严重失真,可能影响执行计划)
  • select 明显走错索引,EXPLAIN 显示 key 是预期索引但 rows 高得离谱,且 ANALYZE TABLE t; 后无改善
  • 表被大量 delete(尤其范围删除)后,data_free 占比持续 > 25%,且查询响应变慢(碎片影响缓存命中)
  • 错误日志出现 InnoDB: index tree corruptionindex is marked as corrupted

ANALYZE TABLE 和重建索引的区别

ANALYZE TABLE 不动索引结构,只采样页并更新统计信息(mysql.innodb_table_stats 等);而重建索引(如 DROP + CREATEREBUILD)会重新组织 B+ 树物理结构,清理删除标记、合并页、重排顺序。

二者常被混淆,但效果完全不同:

  • 如果 EXPLAIN 显示走了索引但性能差,先试 ANALYZE TABLE t; —— 90% 场景够用
  • 如果 SELECT count(*) 结果和 information_schema.TABLES.DATA_LENGTH / AVG_ROW_LENGTH 推算值偏差极大(比如差 3 倍以上),才考虑重建
  • ANALYZE TABLE 是秒级操作,REBUILD 可能锁表或耗时数分钟,别在高峰期跑

MySQL 8.0+ 的 ALTER INDEX REBUILD 实操细节

这是最接近“精准重建索引”的原生能力,但有几个硬约束必须注意:

  • 仅支持二级索引(PRIMARY KEY 不支持 REBUILD
  • 要求表引擎为 InnoDB,且 MySQL 版本 ≥ 8.0.12
  • 执行时仍需获取 S(共享)锁,允许读,但会阻塞写入(INSERT/UPDATE/DELETE
  • 语法必须带 ON 表名:ALTER TABLE t ALTER INDEX idx_status REBUILD;,不能写成 ALTER INDEX ... ON t REBUILD
  • 若索引包含前缀字段(如 name(50)),REBUILD 后前缀长度保持不变

重建完成后,建议立刻执行 ANALYZE TABLE t; 更新统计信息——REBUILD 本身不触发统计更新。

真正麻烦的从来不是“怎么重建”,而是判断“是否真有必要”。多数慢查询背后是查询写法、JOIN 顺序或缺失索引,而不是索引坏了。盯着 EXPLAINtypeExtra 字段,比盲目重建管用得多。

text=ZqhQzanResources