mysql中避免重复索引与优化存储空间

mysql不会自动处理重复索引，而是全部保留，导致性能下降；需用show index或pt-duplicate-key-checker识别duplicate（完全重复）和redundant（被覆盖）索引，删除前须用explain验证执行计划。

什么是重复索引，MySQL 会怎么处理它

MySQL 不会自动拒绝或合并你创建的重复索引，而是照单全收——哪怕两个 INDEX 完全一样，或者一个 PRIMARY KEY 已经覆盖了另一个 UNIQUE INDEX 的字段组合。这会导致写入变慢、磁盘占用增加、优化器选择困难。

重复索引常见于：手动添加索引时没检查已有结构；ORM 自动生成索引后又人工补加；表结构多次迭代未清理旧索引。

• SHOW INDEX FROM table_name 是起点，但需人工比对字段顺序、类型、是否包含前缀（如 name(10)）
• 复合索引 (a, b) 和 (a) 不算严格重复，但后者常被前者“覆盖”，可删
• PRIMARY KEY (id) 和 UNIQUE KEY idx_id (id) 就是典型重复，后者必须删

用 pt-duplicate-key-checker 快速发现冗余索引

Percona Toolkit 的 pt-duplicate-key-checker 是目前最可靠的自动化检测工具，它不只比字段名，还模拟 MySQL 的索引使用逻辑（比如前缀长度、NULL 属性、排序方向）。

运行前确保有只读账号权限，且目标表无长事务阻塞元数据锁：

pt-duplicate-key-checker --host=localhost --user=readonly --password=xxx --databases=mydb

输出中重点关注 duplicate 和 redundant 标记：

• duplicate：完全一致的索引，留一个删其余
• redundant：被其他索引“覆盖”的索引，例如 (a) 对 (a, b)，通常可删
• 注意带 using HASH 的索引（Memory 引擎）或全文索引（FULLTEXT）不会被误判为冗余

删除索引前必须验证查询执行计划

别光看“冗余”就删。有些看似被覆盖的索引，实际支撑着 ORDER BY a LIMIT 10 这类只用首字段的排序需求——而 (a, b) 索引在 b 无约束时，不一定能高效服务该查询。

删之前用 EXPLAIN 对比关键 SQL：

EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 5;

• 删掉 INDEX idx_user (user_id) 前，确认 INDEX idx_user_created (user_id, created_at) 在该语句中 key 列确实命中，且 rows 估算合理
• 留意 Extra 字段：出现 Using filesort 或 Using temporary 往往意味着删错了
• 生产环境建议先在从库执行 EXPLAIN，再用 pt-query-digest 抽样分析慢日志中的真实执行路径

TEXT/BLOB 字段导致的隐式空间浪费

即使没建重复索引，TEXT 和 BLOB 类型也会在 InnoDB 中引发存储膨胀——尤其当定义了前缀索引（如 INDEX idx_title (title(255))）时，MySQL 会在聚簇索引之外额外维护一份前缀哈希 + 指针结构，且不压缩。

• 避免对 TEXT 字段建过长前缀索引：title(768) 在 utf8mb4 下占 3KB 内存/行，远超必要
• 改用生成列（generated column）+ 普通索引更可控：ALTER TABLE posts ADD COLUMN title_hash char(32) AS (MD5(title)) STORED，再建 INDEX idx_title_hash (title_hash)
• 若必须搜前缀内容，优先考虑 FULLTEXT 索引 + MATCH ... AGAINST，它用倒排索引，空间效率远高于前缀 B-Tree

真正难的不是发现重复索引，而是判断某个“冗余”索引是否承载着未被监控到的低频但关键的查询路径。线上删索引前，至少保留一周的 slow_log 和 performance_schema.events_statements_summary_by_digest 数据比对。