MongoDB Aggregation Pipeline 并没有使用索引

JasonLaw 2020-05-31 13:54:31 +08:00 2937 次点击

这是一个创建于 1959 天前的主题，其中的信息可能已经有所发展或是发生改变。

我有一个拥有五百万个文档的集合，文档的结构如下：

{ "_id" : ObjectId("5ed23bd292845eaf1e488965"), "categoryId" : 18790, "targetType" : 43, "targetId" : 433, "priority" : 0 }

集合拥有索引categoryId_1_priority_-1。

我期望如下的 Aggregation Pipeline 使用到索引，但是事实上并没有。

db.collection.aggregate([{$facet: {"category_1_order_by_priority_limit_2": [{$match: {categoryId: 1}}, {$sort: {priority: -1}}, {$limit: 2}],"category_2_order_by_priority_limit_3": [{$match: {categoryId: 2}}, {$sort: {priority: -1}}, {$limit: 3}]}}])

explain 的结果如下：

{ "stages" : [ { "$cursor" : { "query" : { }, "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.collection", "indexFilterSet" : false, "parsedQuery" : { }, "queryHash" : "8B3D4AB8", "planCacheKey" : "8B3D4AB8", "winningPlan" : { "stage" : "COLLSCAN", "direction" : "forward" }, "rejectedPlans" : [ ] } } }, { "$facet" : { ... } } ], "serverInfo" : { ... }, "ok" : 1 }

我应该怎么做才能使用到索引呢？

indexing - MongoDB Aggregation Pipeline $match $sort doesn't use index - Stack Overflow

第 1 条附言 2020-05-31 22:34:49 +08:00

From [SERVER-30474] leading $facet with each facet beginning with $match should add $match before $facet - MongoDB, $facet is not smart enough to use an index. if the only stage in a pipeline is $facet, the planning system will simply default to a COLLSCAN.

索引

categoryid

sort

pipeline

2 条回复 2020-06-01 09:10:58 +08:00

JasonLaw

2020-05-31 22:32:13 +08:00

From [\[SERVER-30474\] leading $facet with each facet beginning with $match should add $match before $facet - MongoDB]( https://jira.mongodb.org/browse/SERVER-30474), *$facet is not smart enough to use an index. if the only stage in a pipeline is $facet, the planning system will simply default to a COLLSCAN.*

JasonLaw

2020-06-01 09:10:58 +08:00

？我有一点疑问，这个问题已经有答案了（见附言），为什么还有人收藏呢？