MongoDB Aggregation Pipeline 并没有使用索引

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

推荐工具

› RoboMongo

Related Blogs

› Snail in a Turtleneck

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

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

{
    "_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

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