添加复合索引为何先提升读查询性能，超阈值后反而变慢？

这种情况确实存在，核心原因主要有以下几点：

• 索引选择开销增大：数据库查询优化器执行读查询时，需要从多个候选复合索引中评估最优执行计划。当索引数量超过临界点后，优化器要花费更多时间计算每个索引的选择性、匹配度、扫描成本等指标，这个评估过程的额外开销会抵消索引带来的查询加速效果，甚至导致整体耗时增加。
• 内存与磁盘资源挤占：过多复合索引会占用大量内存和磁盘空间。当内存无法容纳所有索引时，查询需要频繁从磁盘读取索引数据，引发IO瓶颈；同时冗余索引会加剧磁盘碎片化，进一步降低读操作的效率。
• 优化器误判风险提升：索引数量过多时，优化器可能误选非最优索引——比如选择了一个部分匹配但需要大量回表的索引，而非能直接覆盖查询的索引，导致查询执行路径变长，性能下降。
• 缓存资源竞争：有限的数据库缓存会被大量索引占用，原本用于缓存热点业务数据的空间被压缩，使得查询时需要更多地从磁盘读取原始数据，拖慢读查询速度。

内容的提问来源于stack exchange，提问作者Bear Bile Farming is Torture