V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
推荐工具
RoboMongo
推荐书目
50 Tips and Tricks for MongoDB Developers
Related Blogs
Snail in a Turtleneck
JasonLaw
V2EX  ›  MongoDB

MongoDB Aggregation Pipeline 并没有使用索引

  •  
  •   JasonLaw · 2020-05-31 13:54:31 +08:00 · 2557 次点击
    这是一个创建于 1398 天前的主题,其中的信息可能已经有所发展或是发生改变。

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

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

    2 条回复    2020-06-01 09:10:58 +08:00
    JasonLaw
        1
    JasonLaw  
    OP
       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
        2
    JasonLaw  
    OP
       2020-06-01 09:10:58 +08:00
    ?我有一点疑问,这个问题已经有答案了(见附言),为什么还有人收藏呢?
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2797 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 30ms · UTC 13:30 · PVG 21:30 · LAX 06:30 · JFK 09:30
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.