问题描述
我正在尝试在我的 $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 而不是索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！