推荐工具
RoboMongo
推荐书目
50 Tips and Tricks for MongoDB Developers
Related Blogs
Snail in a Turtleneck
JasonLaw
V2EX  ›  MongoDB

MongoDB Aggregation Pipeline 并没有使用索引

  •  
  •   JasonLaw · May 31, 2020 · 3230 views
    This topic created in 2174 days ago, the information mentioned may be changed or developed.

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

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

    Supplement 1  ·  May 31, 2020

    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.

    2 replies    2020-06-01 09:10:58 +08:00
    JasonLaw
        1
    JasonLaw  
    OP
       May 31, 2020
    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
        2
    JasonLaw  
    OP
       Jun 1, 2020
    ?我有一点疑问,这个问题已经有答案了(见附言),为什么还有人收藏呢?
    About   ·   Help   ·   Advertise   ·   Blog   ·   API   ·   FAQ   ·   Solana   ·   3725 Online   Highest 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 36ms · UTC 00:43 · PVG 08:43 · LAX 17:43 · JFK 20:43
    ♥ Do have faith in what you're doing.