MongoDB总数太慢 [英] MongoDB aggregate count is too much slow

问题描述

我在users集合中有大约6万个文档，并具有以下查询:

I have around 60 thousand document in users collection, and have the following query:

db.getCollection('users').aggregate([
    {"$match":{"userType":"employer"}},
    {"$lookup":{"from":"companies","localField":"_id","foreignField":"owner.id","as":"company"}},
    {"$unwind":"$company"},
    {"$lookup":{"from":"companytypes","localField":"company.type.id","foreignField":"_id","as":"companyType"}},
    {"$unwind":"$companyType"},
    { $group: { _id: null, count: { $sum: 1 } } }
])

计数大约需要12秒钟，即使我在列表函数之前调用计数函数，但具有limit: 10的列表函数的响应速度也要比计数快.

It takes around 12 seconds to count, even I call count function before list function, but my list function with limit: 10 response faster than count.

以下是explain结果:

{
    "stages" : [ 
        {
            "$cursor" : {
                "query" : {
                    "userType" : "employer"
                },
                "fields" : {
                    "company" : 1,
                    "_id" : 1
                },
                "queryPlanner" : {
                    "plannerVersion" : 1,
                    "namespace" : "jobs.users",
                    "indexFilterSet" : false,
                    "parsedQuery" : {
                        "userType" : {
                            "$eq" : "employer"
                        }
                    },
                    "winningPlan" : {
                        "stage" : "COLLSCAN",
                        "filter" : {
                            "userType" : {
                                "$eq" : "employer"
                            }
                        },
                        "direction" : "forward"
                    },
                    "rejectedPlans" : []
                }
            }
        }, 
        {
            "$lookup" : {
                "from" : "companies",
                "as" : "company",
                "localField" : "_id",
                "foreignField" : "owner.id",
                "unwinding" : {
                    "preserveNullAndEmptyArrays" : false
                }
            }
        }, 
        {
            "$match" : {
                "$nor" : [ 
                    {
                        "company" : {
                            "$eq" : []
                        }
                    }
                ]
            }
        }, 
        {
            "$group" : {
                "_id" : {
                    "$const" : null
                },
                "total" : {
                    "$sum" : {
                        "$const" : 1
                    }
                }
            }
        }, 
        {
            "$project" : {
                "_id" : false,
                "total" : true
            }
        }
    ],
    "ok" : 1.0
}

推荐答案

$lookup操作很慢，因为它们模仿

$lookup operations are slow since they mimic the left join behavior, from the DOCS:

$ lookup在localField上执行等于 from集合中的文档中的foreignField

$lookup performs an equality match on the localField to the foreignField from the documents of the from collection

因此，如果用于joining的字段中没有索引，则将强制Mongodb进行收集扫描.

Hence if there are no indexes in the fields used for joining the collections Mongodb is force to do a collection scan.

为foreignField属性添加索引应防止收集扫描并提高性能，甚至达到一定程度

Adding an index for the foreignField attributes should prevent a collection scan and increase the performance even of a magnitude

这篇关于MongoDB总数太慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！