MongoDB查询索引扫描未按预期进行过滤
MongoDB查询索引扫描未按预期进行过滤
提问于 2020-06-04 05:00:35
我有一个使用索引的查询，但是在获取过程中查找了太多的文档。
有关的指数是：
{
“v” : 2,
“key” : {
“vw” : -1,
“if” : 1,
“sa” : 1,
“dd” : -1,
“ca” : 1
},
“name” : “Viewed_By_Category”,
“ns” : “redacted”,
“background” : false
}所涉问题如下：
db.stories.find({ 'if': {$ne: true}, 'sa': 2, 'dd': {$ne : null}, 'ca': 11}).skip(3990).limit(30).sort({'vw':-1}).explain('executionStats')*这是解释输出：
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "lushstories.stories",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"ca" : {
"$eq" : 11
}
},
{
"sa" : {
"$eq" : 2
}
},
{
"dd" : {
"$not" : {
"$eq" : null
}
}
},
{
"if" : {
"$not" : {
"$eq" : true
}
}
}
]
},
"winningPlan" : {
"stage" : "LIMIT",
"limitAmount" : 30,
"inputStage" : {
"stage" : "SKIP",
"skipAmount" : 0,
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"ca" : {
"$eq" : 11
}
},
{
"sa" : {
"$eq" : 2
}
},
{
"dd" : {
"$not" : {
"$eq" : null
}
}
},
{
"if" : {
"$not" : {
"$eq" : true
}
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"vw" : -1,
"if" : 1,
"sa" : 1,
"dd" : -1,
"ca" : 1
},
"indexName" : "Viewed_By_Category",
"isMultiKey" : false,
"multiKeyPaths" : {
"vw" : [ ],
"if" : [ ],
"sa" : [ ],
"dd" : [ ],
"ca" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"vw" : [
"[MaxKey, MinKey]"
],
"if" : [
"[MinKey, MaxKey]"
],
"sa" : [
"[MinKey, MaxKey]"
],
"dd" : [
"[MaxKey, MinKey]"
],
"ca" : [
"[MinKey, MaxKey]"
]
}
}
}
}
},
"rejectedPlans" : [
{
"stage" : "SKIP",
"skipAmount" : 3990,
"inputStage" : {
"stage" : "SORT",
"sortPattern" : {
"vw" : -1
},
"limitAmount" : 4020,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"dd" : {
"$not" : {
"$eq" : null
}
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"dd" : -1,
"if" : 1,
"sa" : 1,
"ca" : 1,
"ha" : 1
},
"indexName" : "Story_Visible_With_Audio",
"isMultiKey" : false,
"multiKeyPaths" : {
"dd" : [ ],
"if" : [ ],
"sa" : [ ],
"ca" : [ ],
"ha" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"dd" : [
"[MaxKey, null)",
"(null, MinKey]"
],
"if" : [
"[MinKey, true)",
"(true, MaxKey]"
],
"sa" : [
"[2.0, 2.0]"
],
"ca" : [
"[11.0, 11.0]"
],
"ha" : [
"[MinKey, MaxKey]"
]
}
}
}
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 30,
"executionTimeMillis" : 5500,
"totalKeysExamined" : 55743,
"totalDocsExamined" : 55743,
"executionStages" : {
"stage" : "LIMIT",
"nReturned" : 30,
"executionTimeMillisEstimate" : 5372,
"works" : 55744,
"advanced" : 30,
"needTime" : 55713,
"needYield" : 0,
"saveState" : 565,
"restoreState" : 565,
"isEOF" : 1,
"invalidates" : 0,
"limitAmount" : 30,
"inputStage" : {
"stage" : "SKIP",
"nReturned" : 30,
"executionTimeMillisEstimate" : 5372,
"works" : 55743,
"advanced" : 30,
"needTime" : 55713,
"needYield" : 0,
"saveState" : 565,
"restoreState" : 565,
"isEOF" : 0,
"invalidates" : 0,
"skipAmount" : 0,
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"ca" : {
"$eq" : 11
}
},
{
"sa" : {
"$eq" : 2
}
},
{
"dd" : {
"$not" : {
"$eq" : null
}
}
},
{
"if" : {
"$not" : {
"$eq" : true
}
}
}
]
},
"nReturned" : 4020,
"executionTimeMillisEstimate" : 5372,
"works" : 55743,
"advanced" : 4020,
"needTime" : 51723,
"needYield" : 0,
"saveState" : 565,
"restoreState" : 565,
"isEOF" : 0,
"invalidates" : 0,
"docsExamined" : 55743,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 55743,
"executionTimeMillisEstimate" : 80,
"works" : 55743,
"advanced" : 55743,
"needTime" : 0,
"needYield" : 0,
"saveState" : 565,
"restoreState" : 565,
"isEOF" : 0,
"invalidates" : 0,
"keyPattern" : {
"vw" : -1,
"if" : 1,
"sa" : 1,
"dd" : -1,
"ca" : 1
},
"indexName" : "Viewed_By_Category",
"isMultiKey" : false,
"multiKeyPaths" : {
"vw" : [ ],
"if" : [ ],
"sa" : [ ],
"dd" : [ ],
"ca" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"vw" : [
"[MaxKey, MinKey]"
],
"if" : [
"[MinKey, MaxKey]"
],
"sa" : [
"[MinKey, MaxKey]"
],
"dd" : [
"[MaxKey, MinKey]"
],
"ca" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 55743,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
}
},
"serverInfo" : {
"host" : "redacted",
"port" : 27017,
"version" : "4.0.9",
"gitVersion" : "fc525e2d9b0e4bceff5c2201457e564362909765"
},
"ok" : 1
}那么，为什么IXSCAN扫描阶段不使用任何谓词来过滤，indexBounds都使用MaxKey、MinKey
这是返回全部数量的记录55743，这些记录被输入到获取中。
关于这些索引，有什么我不明白的吗？
谢谢
回答 2
Stack Overflow用户
回答已采纳
发布于 2020-06-04 06:49:28
这是因为您的查询没有为索引中的第一个字段指定任何匹配条件。
MongoDB不分别存储索引的每个字段。它是一个连接索引条目的值的单一树。为了使用{"vw" : -1, "if" : 1, "sa" : 1, "dd" : -1, "ca" : 1}上的索引来处理该查询，它必须检查vw的每个值。
此索引允许查询执行器使用索引来满足排序，因此不需要内存中的排序。如果您使用"allPlansExecution"重新运行解释，您可以比较这比需要排序阶段的被拒绝的计划快多少。
查询最有效的索引遵循相等排序范围规则，这意味着应该首先列出查询将与确切值匹配的字段，然后列出排序字段，然后列出任何具有range或不相等谓词的字段。
如果在{"ca" : 1, "sa" : 1, "vw":-1, "dd" : -1, "if" : 1}上创建索引，则会发现查询完成速度快得多。
Stack Overflow用户
发布于 2020-06-04 07:22:51
Joe的回答完美地解释了这一点，只是为了在查询中添加一个条件。
{$ne : null}它仍然可以触发蒙戈(版本相关)的过滤操作。当前版本可以在索引扫描4.2.6中容纳它。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/62187150
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号