如何为4类关联过滤查询创建最优高效索引？

首先得先搞清楚为什么你之前建的(A,B,C)联合索引只对部分查询生效——这是联合索引的最左前缀匹配原则在起作用：联合索引是按照列的顺序依次构建的，只有当查询的过滤条件匹配索引的最左连续列时，数据库才能高效利用这个索引。

比如你的(A,B,C)索引，能支持的过滤场景是：

• 仅用A过滤
• 用A+B组合过滤
• 用A+B+C组合过滤

但对于A+C的查询，因为中间跳过了B列，数据库无法直接定位到索引中C的部分；而B+C的查询连最左的A都没用到，完全没法利用这个索引，这就是为什么后两个查询没得到加速的原因。

接下来针对你的场景，给你两种主流的优化方案，你可以根据自己的实际业务情况选择：

方案一：针对性创建三个两列联合索引 #

如果这三个两列组合的查询频率都很高，且你的表写入/更新操作不是特别频繁（索引越多，写入时的维护成本越高），直接创建三个独立的联合索引是最稳妥的选择：

• CREATE INDEX idx_ab ON your_table(A,B);
• CREATE INDEX idx_ac ON your_table(A,C);
• CREATE INDEX idx_bc ON your_table(B,C);

这种方案的优势是每个查询都能精准匹配到对应的索引，查询效率拉满；缺点是会增加一定的存储空间占用，并且在表进行插入、更新、删除操作时，数据库需要同时维护这三个索引，会消耗额外的性能。

方案二：权衡索引数量与查询效率（适合写入频繁的场景） #

如果你的表写入操作很多，不想维护太多索引，可以做以下调整：

1. 保留(A,B,C)联合索引，覆盖A、A+B、A+B+C的查询场景
2. 再单独创建(B,C)索引，覆盖B+C的查询场景
3. 对于A+C的查询，如果查询的字段都包含在(A,B,C)索引里（也就是覆盖索引），数据库可能会选择扫描这个联合索引来获取数据，虽然效率不如专门的(A,C)索引，但比全表扫描好很多；如果查询字段不在索引里，那还是建议补充(A,C)索引。

额外建议 #

不管选哪种方案，都一定要用EXPLAIN语句验证每个查询的索引使用情况，比如：

EXPLAIN SELECT * FROM your_table WHERE A = ? AND C = ?;

查看结果中的type字段（最好是ref或range）和key字段（是否是你创建的目标索引），确保索引确实在生效。

另外，创建索引时要把区分度高（基数大）的列放在前面，比如如果A列的不同值数量远多于B列，那(A,B)比(B,A)的索引效率更高。

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