mongodb - $lookup 管道使用 COLLSCAN 而不是索引 [英] mongodb - $lookup pipeline using COLLSCAN instead of index

问题描述

我正在尝试在我的 $lookup 管道上使用索引，但它似乎没有按预期工作.

I'm trying to use indexing on my $lookup pipeline but it doesn't seem to be working as intended.

这是我的查询:

db.map_levels.explain().aggregate([
    {
        $lookup:
        {
            from: 
                "map_level_revisions",
            pipeline:
            [
                {
                    $match:
                    {
                        $expr:
                        {
                            $eq:
                            [
                                "$account_id",
                                ObjectId("5b66ca21d6b54f479bef62a4")
                            ]
                        }
                    }
                }
            ],
            as:
                "revisions"
        }
    },
])

这里是解释:

{
    "stages" : [
        {
            "$cursor" : {
                "query" : {

                },
                "queryPlanner" : {
                    "plannerVersion" : 1,
                    "namespace" : "test-creator.map_levels",
                    "indexFilterSet" : false,
                    "parsedQuery" : {

                    },
                    "winningPlan" : {
                        "stage" : "COLLSCAN",
                        "direction" : "forward"
                    },
                    "rejectedPlans" : [ ]
                }
            }
        },
        {
            "$lookup" : {
                "from" : "map_level_revisions",
                "as" : "revisions",
                "let" : {

                },
                "pipeline" : [
                    {
                        "$match" : {
                            "$expr" : {
                                "$eq" : [
                                    "$account_id",
                                    ObjectId("5b66ca21d6b54f479bef62a4")
                                ]
                            }
                        }
                    }
                ]
            }
        }
    ],
    "ok" : 1
}

如何让它改用索引?

请注意，查询确实返回文档.

Just a note, the query does return the document.

推荐答案

解释输出中的集合扫描指的是 map_levels 集合，如 queryPlanner.namespace值.$lookup 阶段将来自另一个集合的数据合并到当前管道中.由于您没有在 $lookup 之前指定任何查询阶段，map_levels 集合将使用集合扫描进行迭代.如果在没有任何过滤或排序条件的情况下加载整个集合，则集合扫描的开销比迭代索引和获取文档的开销要少.

The collection scan in your explain output is referring to the map_levels collection, as noted in the queryPlanner.namespace value. The $lookup stage merges data from another collection into the current pipeline. Since you haven't specified any query stages before the $lookup, the map_levels collection will be iterated using a collection scan. If an entire collection is being loaded without any filtering or sort criteria, a collection scan has less overhead than iterating an index and fetching the documents.

您可以通过在 $lookup 之前添加一个 $match 阶段来避免当前集合扫描(假设您不想处理完整的 map_levels 收藏).

You can avoid the current collection scan by adding a $match stage before your $lookup (assuming you don't want to process the full map_levels collection).

如何查看$lookup使用的索引?

不幸的是，查询解释输出(在 MongoDB 4.0 中)不指示 $lookup 阶段的索引使用情况.对此的解决方法是使用查找的 pipeline 作为顶级聚合查询来运行解释.

Unfortunately query explain output does not (as at MongoDB 4.0) indicate index usage for $lookup stages. A workaround for this would be running explain using your lookup's pipeline as a top level aggregation query.

在 MongoDB 问题跟踪器中有一个需要关注/投票的相关问题:SERVER-22622:改进 $查找解释以指示对来自"集合的查询计划.

There's a relevant issue to watch/upvote in the MongoDB Issue tracker: SERVER-22622: Improve $lookup explain to indicate query plan on the "from" collection.

这篇关于mongodb - $lookup 管道使用 COLLSCAN 而不是索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！