索引是加速查询的利器，但绝对不是越多越好。很多开发者在遇到性能问题时，第一反应就是“再加一个索引试试”，这种“索引万能论”反而可能把数据库推向另一个泥潭。理解索引的副作用，学会在查询加速和写入代价之间做权衡，才是成熟的优化思维。
索引的真正代价：不止多占一点磁盘
1. 写入性能下降
索引的本质是数据的冗余存储，每张表上的每一个索引，都是一棵独立的 B+ 树。当表发生 INSERT、UPDATE、DELETE 时，InnoDB 不仅要修改主键索引（聚簇索引）中的行数据，还要同步维护这张表上所有的二级索引。
具体来说，一次 INSERT 操作，如果表上有 5 个二级索引，InnoDB 就需要：
- 在 5 棵 B+ 树上分别找到插入位置
- 可能引发页分裂，产生额外的磁盘 I/O
- 在 Change Buffer 不够用或刷盘策略保守时，这些开销会更直接地体现在响应时间上
对于写密集型的业务（比如日志系统、埋点数据、实时计数），过多的索引会严重影响吞吐量。一个常见现象是：单条 INSERT 执行很快，但当并发写入量上来后，CPU 和磁盘 I/O 都被索引维护吃满，整个库的响应时间急剧上升。
2. 存储空间膨胀
每个索引都是一棵独立的 B+ 树，占用独立的磁盘空间。一个表中，所有索引占用的空间总和，甚至可能超过数据本身。曾经有案例：一张 20GB 的表，历史遗留下来十几个索引，索引空间占了将近 60GB，备份和恢复时间都因此大幅延长。
在云环境下，磁盘空间就是真金白银的成本。在自建机房环境，磁盘膨胀也会拉长备份时间、增加故障恢复窗口。这是索引过多带来的一种“沉默成本”，日常不会告警，但总在消耗资源。
3. 优化器可能选错索引
当一张表上索引过多时，优化器在生成执行计划时需要评估的路径数量会急剧增加。MySQL 优化器基于统计信息和成本模型做决策，但这些信息有时候并不完全准确：
- 索引统计信息（索引基数、列值分布）如果不及时更新，优化器可能高估或低估某个索引的选择性。
- 多个索引看起来都能用，优化器可能在它们之间“犹豫不决”，最终选了一个并非最优的，甚至因为成本估算偏差选了全表扫描。
- 过多索引还会导致
ANALYZE TABLE和OPTIMIZE TABLE的执行时间变长，而这两者是维持统计信息准确性的重要手段。
一个实际案例：某业务表上有 8 个二级索引，一个看起来简单的 SELECT * FROM t WHERE status = 1 AND user_id = 123 查询，优化器有时走 idx_status，有时又走 idx_user_id，性能表现忽快忽慢，排查后才发现是两个索引的统计信息在不同时间段有波动。最终通过合并为 (status, user_id) 联合索引并清理冗余索引，才让执行计划稳定下来。
4. 增大了锁竞争范围
这点容易被忽略。当执行 UPDATE 或 DELETE 时，InnoDB 不仅要在行上加锁，还需要修改对应的二级索引。虽然二级索引的修改不会对主键索引产生额外的行锁，但在某些情况下，多个事务的写操作可能会在二级索引的同一页上产生竞争，比如批量插入时，如果二级索引列是无序的，会在 B+ 树页上产生“热点”，造成锁等待。
5. 拖慢 DDL 操作
当你需要加列、改列、甚至 OPTIMIZE TABLE 重建表时，表上的索引越多，这些操作需要同步重建的 B+ 树就越多，执行时间相应延长。对于大表，这可能是小时级甚至更久的窗口，极大地影响运维灵活性。
取舍策略：不是加不加，而是加哪个
索引设计本质上是在读性能和写代价之间做平衡。掌握以下几个原则，有助于做出更合理的取舍。
原则一：只为高频且关键的查询服务
建索引之前先问三个问题：
- 这条 SQL 在业务中调用的频率有多高？是每次请求都会触发，还是一周才跑一次的报表？
- 这个查询对用户体验或系统核心链路的影响有多大？是用户登录页的查询，还是后台的一个导出功能？
- 目前是否有明显的性能问题？慢查询日志里它排名靠前吗？
如果一个查询低频、对业务影响小、响应时间尚可接受，那很可能不需要专门为它建索引。反之，才值得持续投入写入代价去维护索引。
原则二：能用一个联合索引覆盖多个查询，就不建多个单列索引
这是控制索引数量最直接有效的方法。比如查询中经常出现：
SELECT * FROM orders WHERE user_id = ?;
SELECT * FROM orders WHERE user_id = ? AND status = ?;
SELECT * FROM orders WHERE user_id = ? ORDER BY create_time DESC;创建 (user_id, status, create_time) 一个联合索引，就能覆盖以上所有查询，避免创建 idx_user_id、idx_status 等多个单列索引。最左前缀原则让一个联合索引可以充当多个索引的角色。
原则三：定期审查索引使用情况，清理冗余与无效索引
MySQL 从 5.6 开始提供了 performance_schema.table_io_waits_summary_by_index_usage 表，可以查看每个索引被使用的次数（读写、锁等）。此外还可以通过 sys.schema_unused_indexes 视图直接列出从未使用过的索引：
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'your_db';定期（比如每月）跑一遍这个查询，将确认无用的索引删除，就可以持续回收磁盘空间和写入损耗。但需要注意：有些索引可能只在季度报表时才用，判断“无用”之前最好观察足够长的时间。
同时要清理冗余索引。例如已经有 (A, B) 联合索引，再单独建 idx_A 就是冗余的（最左前缀已经覆盖了 A 列查询）。不仅浪费空间，还增加维护成本。
原则四：结合业务场景考虑读写比例
- 读多写少（典型 OLTP 业务）：可以适当多建索引，查询性能是主要矛盾。
- 写多读少（日志、流水表）：索引必须极度克制，甚至只保留主键。写入吞吐量是第一优先级，查询可以通过归档后在其他引擎完成。
- 读写均衡：找到最高频的查询模式，用最少的索引覆盖最多的场景，其余走覆盖或偶尔慢查询容忍。
原则五：用覆盖索引抵消一部分回表代价，而不是无限加索引
如果某个查询需要查的列很多，全部放入索引不现实，这时候可以选择把高频的过滤条件和返回列组成联合索引，让查询命中覆盖索引，避免回表。这比单独为每个查询各建一个索引更经济。
原则六：上线新索引前先在测试环境验证
在测试环境模拟生产数据量和并发压力，通过 EXPLAIN 确认执行计划符合预期，同时监控写入 TPS、延迟的变化。如果一个索引让写入性能下降超过 10% 而带来的查询提升不明显，就要重新评估是否值得。在 8.0 中，还可以先将索引设置为不可见（ALTER TABLE ... ALTER INDEX idx_name INVISIBLE），在优化器层面忽略该索引，观察业务表现再决定是否彻底删除。
总结
索引不是免费用品，每个索引都在持续消耗写入性能、磁盘空间和维护资源。优秀的索引设计是“少而精”的艺术，力求用最少的索引覆盖最关键的查询场景，同时保持对索引使用情况的持续监控，及时清理历史遗留的无效索引。在实际工作中，养成“先分析、再添加、定期审计”的习惯，比单纯背索引失效场景要管用得多。