如何为指定SQL查询创建正确的originSystemName等三字段复合索引？

索引失效原因 #

你当前的复合索引字段顺序不符合SQL查询逻辑：

• 你的SQL中，WHERE子句优先用originSystemName做等值匹配，再用nativeMeDn做IN查询，最后按latestOccurUtc降序排序
• 你将nativeMeDn放在索引的第一个位置，originSystemName在第二个位置，违背了等值过滤字段优先、范围/IN字段次之、排序字段最后的复合索引设计原则，导致索引无法同时覆盖过滤条件和排序需求，因此无法生效。

正确实现代码 #

调整三个字段在索引组中的order顺序，让等值匹配的originSystemName排在最前，然后是nativeMeDn，最后是排序字段latestOccurUtc：

@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "group_idx", order = 1)})
private String originSystemName;

@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "group_idx", order = 2)})
private String nativeMeDn;

@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "group_idx", descending = true, order = 3)})
private long latestOccurUtc;

关键说明 #

1. 复合索引的顺序直接决定查询能否利用索引：等值条件字段放在最前面，能快速定位到符合条件的数据子集；
2. IN查询属于范围类操作，放在等值字段之后，不会中断索引的前缀匹配；
3. 排序字段放在索引最后，并且设置descending=true，这样索引本身的顺序就能满足ORDER BY LATESTOCCURUTC DESC的需求，避免数据库执行额外的排序操作，提升查询效率。

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