MongoDB 聚合计数太慢

Question

我在 users 集合中有大约 60,000 个文档，并且有以下查询:

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 响应速度比计数快。

下面是解释的结果:

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

Answer 1

$lookup 操作很慢，因为它们模仿来自 DOCS 的左连接行为:

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

因此，如果用于join 的字段中没有索引，Mongodb 会强制执行集合扫描。

为 foreignField 属性添加索引应该可以防止集合扫描并显着提高性能