单列索引与联合索引选型：如何兼顾最佳实践与查询性能提升？

你给出的两种方案都不是最优选择，具体分析如下：

方案二（仅创建联合索引index(c1,c2,c3)）不可行 #

联合索引遵循最左前缀匹配原则，只能匹配索引列从左到右的连续前缀：

• 可以覆盖where c1=? and c2=?、where c1=? and c2=? and c3=?这两类查询
• 完全无法覆盖where c3=?的查询，这类查询还是会走全表扫描，无法解决卡顿问题

方案一（创建4个独立+联合索引）冗余度过高 #

该方案虽然能覆盖所有三类查询，但存在大量不必要的损耗：

• 存在index(c1,c2,c3)的前提下，单独的index(c1)完全冗余，联合索引的最左第一列已经可以满足所有单独用c1作为查询条件的需求
• 你给出的三类查询中没有仅以c2作为查询条件的场景，单独的index(c2)没有任何作用，属于无效索引
• 多余的索引会占用额外的磁盘空间，同时会大幅增加INSERT/UPDATE/DELETE操作的索引维护成本，拖慢写性能，还会提升查询优化器的索引选择计算开销

最优方案 #

针对你给出的三类查询，仅需创建2个索引即可覆盖所有场景，兼顾查询性能和写入开销：

• 联合索引 idx_c1_c2_c3(c1, c2, c3)：覆盖c1+c2、c1+c2+c3两类查询
• 单值索引 idx_c3(c3)：覆盖仅用c3作为查询条件的场景

如果后续业务新增仅以c2作为查询条件的需求，再单独补充idx_c2(c2)即可。

内容的提问来源于stack exchange，提问作者Phoebe