mysql数据库版本升级后的索引优化与重建_技术教程

MySQL升级至8.0后索引问题核心在于统计信息滞后、降序索引语义变化、ALGORITHM=INPLACE空间策略差异及工具兼容性不足，需手动刷新统计、重建混合排序索引、评估空间并谨慎使用检测工具。

MySQL 升级后 `INFORMATION_SCHEMA.STATISTICS` 显示的索引统计可能过期

MySQL 5.7 升级到 8.0 后，ANALYZE TABLE 不再自动触发采样更新（尤其在 innodb_stats_auto_recalc=OFF 或表无变更时），导致优化器仍用旧统计选择低效执行计划。这不是“索引损坏”，而是统计信息滞后。

检查是否启用自动重算：
```
SELECT @@innodb_stats_auto_recalc;
```
若为 OFF，需手动触发或改配置
强制刷新单表统计：
```
ANALYZE TABLE `orders`;
```
注意：该操作会加 MDL 读锁，大表慎在高峰执行
升级后建议批量刷新：生成脚本遍历所有表，跳过 information_schema 和系统库

8.0 中 `descending index` 支持让旧复合索引可能失效

MySQL 8.0 支持在 CREATE INDEX 中显式指定 DESC，而 5.7 实际忽略该关键字、全按升序存储。若原查询依赖 ORDER BY a DESC, b ASC，5.7 下建的 INDEX idx(a,b) 可能被复用；但 8.0 优化器更严格匹配排序方向，可能放弃使用该索引。

用 EXPLAIN 对比升级前后执行计划，重点关注 key_len 和 Extra 是否出现 Using filesort

对含混合排序方向的查询，重建索引：

CREATE INDEX idx_orders_status_created ON orders (status DESC, created_at ASC);

注意：8.0 的降序索引不兼容 5.7，回滚版本前需删掉这类索引

`ALTER TABLE ... ALGORITH M=INPLACE` 在 8.0 中对索引重建更激进

MySQL 8.0 默认启用 innodb_defragment=ON 和更强的页合并策略，ALGORITHM=INPLACE 重建索引时可能触发 B+ 树重组、填充因子调整，导致磁盘空间临时增长 2–3 倍——这和 5.7 的“就地”行为不一致。

重建前确认空闲空间：

SELECT table_schema, table_name, round(((data_length + index_length) / 1024 / 1024), 2) AS size_mb FROM information_schema.tables WHERE table_schema = 'mydb' ORDER BY size_mb DESC LIMIT 5;

对超大表，改用 ALGORITHM=COPY + LOCK=NONE（需确保 binlog_format=ROW）更可控
避免在 innodb_file_per_table=OFF 的老实例上重建，否则索引数据写入系统表空间，无法释放

升级后 `pt-duplicate-key-checker` 可能误报“冗余索引”

Percona Toolkit 的 pt-duplicate-key-checker 基于索引列前缀匹配判断冗余，但 MySQL 8.0 引入隐藏主键（row_id）、函数索引、不可见索引等新特性，工具未适配时会把 UNIQUE KEY (email) 和 INDEX (email, created_at) 判定为冗余，而实际业务中后者用于范围查询，不可删。