mongo queryPlanner 使用正确的索引，即使查询字段顺序与索引字段顺序不匹配

发布于 2025-01-14 08:27:03 字数 760 浏览 1 评论 0 原文

在 mongo 的文档中，它说顺序字段对于索引很重要：

复合索引中列出的字段的顺序很重要。该索引将包含对首先按项目字段的值排序的文档的引用，并且在项目字段的每个值中，按库存字段的值排序。有关详细信息，请参阅排序顺序。

当我尝试此查询时：

db.getCollection('foobar').find({       
'target.propertyId': {
    $in: [
        ObjectId('01'), ///...
    ]
},
// NOTICE ownerId at end of query but queryPlanner still know which index to hit the one with ownerId at first
ownerId: ObjectId('02')} 
)
.explain('executionStats')

如果我检查 queryPlanner，我会看到它自动选择我希望它选择的正确索引：开头有 ownerId 的索引。那么这是否意味着字段顺序不需要 100% 来匹配索引？

原文

In mongo's docu it says that order fields is important for index:

The order of the fields listed in a compound index is important. The index will contain references to documents sorted first by the values of the item field and, within each value of the item field, sorted by values of the stock field. See Sort Order for more information.

source

When I try this query:

db.getCollection('foobar').find({       
'target.propertyId': {
    $in: [
        ObjectId('01'), ///...
    ]
},
// NOTICE ownerId at end of query but queryPlanner still know which index to hit the one with ownerId at first
ownerId: ObjectId('02')} 
)
.explain('executionStats')

If I check queryPlanner I see it automatically selects the correct index I want it to select: the one that has ownerId at beginning. So does this means field order is not 100% needed to match an index?