含JOIN、筛选、分组排序的SQL查询如何合理创建表索引

你的理解存在两处需要调整的问题，最终优化后的索引方案如下： #

1. Table A 联合索引的列顺序需要调整 #

你原设想的(ID, AAA, BBB, CCC)顺序不符合联合索引的最左前缀匹配规则，实际无法有效利用索引过滤数据：

• 正确的联合索引应该创建为 (AAA, BBB, CCC, ID)，理由如下：
  • 首位放等值匹配条件列AAA：WHERE AAA='3'是等值过滤，放在最左可以快速过滤掉99%以上不符合条件的行，大幅缩小后续扫描的数据集
  • 其次放范围过滤列BBB、CCC：可以直接在索引层完成两个时间范围的过滤，不需要回表判断数据是否符合条件
  • 末尾放JOIN关联列ID：整个索引包含了查询需要的AAA/BBB/CCC/ID四个列，属于覆盖索引，对表A的所有操作都可以直接在索引中完成，完全不需要回表查询聚簇索引，性能提升幅度最大
  • 额外收益：AAA在索引首位，天然满足ORDER BY A.AAA desc的排序需求，不需要数据库额外做排序操作

2. Table B 可以添加极低开销的覆盖索引获取额外收益 #

你认为表B体积小不需要建索引的判断不准确：

• 如果表B的ID列已经是主键/已有唯一索引，不需要额外创建
• 如果表B上没有ID相关索引，建议创建(ID, mycol)联合覆盖索引：
  • 表B仅100KB，索引占用空间不足几十KB，写入维护成本几乎为0
  • JOIN关联时可以直接通过ID快速匹配行，不需要全表扫描B
  • 索引包含mycol列，GROUP BY操作可以直接在索引层完成，不需要回表取数，哪怕表很小也能有可见的性能提升

是否需要创建多个索引？ #

不需要额外创建多个索引，上述两个索引（表B的索引仅在无ID索引时需要）已经可以完全覆盖该查询的所有执行环节，多余的索引只会增加表的写入开销。

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