对于不同主机上的同一查询，MongoDB索引使用情况有所不同
对于不同主机上的同一查询，MongoDB索引使用情况有所不同
提问于 2020-05-20 04:33:31
我正在运行一个非常简单的查询：db.users.find({}, { _id: 1 }).sort({teamIds: 1}).limit(25)，其中teamIds是一个数组，它有一个升序索引。如果我在辅助服务器上运行此查询，explain将显示查看了25个键，并查看了25个文档(正如预期的那样)。如果我在主服务器上运行完全相同的查询，explain将显示所查看的50K+键和文档。我已经检查了主服务器和辅助服务器都有相同的索引，并且两者都被使用了。下面是两种情况下的explain输出。
什么可能会导致这种行为？
有没有可能在主服务器上，索引没有完全加载到内存中？主服务器使用了大约65%的内存，次服务器使用了大约55%的内存(都是8 8GB)。
编辑：
我刚刚发现主节点在3.6.8上，而次要节点(按照我想要的方式运行)在3.6.7上--这可能是原因吗？
主要的
rs0:PRIMARY> db.users.find({}, { _id: 1 }).sort({teamIds: 1}).limit(25).explain({verbosity: "executionStats"})
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "dbName.users",
"indexFilterSet" : false,
"parsedQuery" : {
},
"winningPlan" : {
"stage" : "PROJECTION",
"transformBy" : {
"_id" : 1
},
"inputStage" : {
"stage" : "SORT",
"sortPattern" : {
"teamIds" : 1
},
"limitAmount" : 25,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"teamIds" : 1
},
"indexName" : "teamIds_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"teamIds" : [
"teamIds"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"teamIds" : [
"[MinKey, MaxKey]"
]
}
}
}
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 25,
"executionTimeMillis" : 182,
"totalKeysExamined" : 52320,
"totalDocsExamined" : 51412,
"executionStages" : {
"stage" : "PROJECTION",
"nReturned" : 25,
"executionTimeMillisEstimate" : 180,
"works" : 52348,
"advanced" : 25,
"needTime" : 52322,
"needYield" : 0,
"saveState" : 408,
"restoreState" : 408,
"isEOF" : 1,
"invalidates" : 0,
"transformBy" : {
"_id" : 1
},
"inputStage" : {
"stage" : "SORT",
"nReturned" : 25,
"executionTimeMillisEstimate" : 170,
"works" : 52348,
"advanced" : 25,
"needTime" : 52322,
"needYield" : 0,
"saveState" : 408,
"restoreState" : 408,
"isEOF" : 1,
"invalidates" : 0,
"sortPattern" : {
"teamIds" : 1
},
"memUsage" : 25242,
"memLimit" : 33554432,
"limitAmount" : 25,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"nReturned" : 51412,
"executionTimeMillisEstimate" : 140,
"works" : 52322,
"advanced" : 51412,
"needTime" : 909,
"needYield" : 0,
"saveState" : 408,
"restoreState" : 408,
"isEOF" : 1,
"invalidates" : 0,
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 51412,
"executionTimeMillisEstimate" : 100,
"works" : 52321,
"advanced" : 51412,
"needTime" : 908,
"needYield" : 0,
"saveState" : 408,
"restoreState" : 408,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 51412,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 51412,
"executionTimeMillisEstimate" : 40,
"works" : 52321,
"advanced" : 51412,
"needTime" : 908,
"needYield" : 0,
"saveState" : 408,
"restoreState" : 408,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"teamIds" : 1
},
"indexName" : "teamIds_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"teamIds" : [
"teamIds"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"teamIds" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 52320,
"seeks" : 1,
"dupsTested" : 52320,
"dupsDropped" : 908,
"seenInvalidated" : 0
}
}
}
}
},
"allPlansExecution" : [ ]
},
"serverInfo" : {
"host" : "mongo1.justplayss.com",
"port" : 27017,
"version" : "3.6.8",
"gitVersion" : "6bc9ed599c3fa164703346a22bad17e33fa913e4"
},
"ok" : 1
}次要
rs0:SECONDARY> db.users.find({}, { _id: 1 }).sort({teamIds: 1}).limit(25).explain({verbosity: "executionStats"})
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "dbName.users",
"indexFilterSet" : false,
"parsedQuery" : {
},
"winningPlan" : {
"stage" : "LIMIT",
"limitAmount" : 25,
"inputStage" : {
"stage" : "PROJECTION",
"transformBy" : {
"_id" : 1
},
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"teamIds" : 1
},
"indexName" : "teamIds_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"teamIds" : [
"teamIds"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"teamIds" : [
"[MinKey, MaxKey]"
]
}
}
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 25,
"executionTimeMillis" : 6,
"totalKeysExamined" : 25,
"totalDocsExamined" : 25,
"executionStages" : {
"stage" : "LIMIT",
"nReturned" : 25,
"executionTimeMillisEstimate" : 0,
"works" : 26,
"advanced" : 25,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"limitAmount" : 25,
"inputStage" : {
"stage" : "PROJECTION",
"nReturned" : 25,
"executionTimeMillisEstimate" : 0,
"works" : 25,
"advanced" : 25,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 0,
"invalidates" : 0,
"transformBy" : {
"_id" : 1
},
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 25,
"executionTimeMillisEstimate" : 0,
"works" : 25,
"advanced" : 25,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 0,
"invalidates" : 0,
"docsExamined" : 25,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 25,
"executionTimeMillisEstimate" : 0,
"works" : 25,
"advanced" : 25,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 0,
"invalidates" : 0,
"keyPattern" : {
"teamIds" : 1
},
"indexName" : "teamIds_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"teamIds" : [
"teamIds"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"teamIds" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 25,
"seeks" : 1,
"dupsTested" : 25,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
},
"allPlansExecution" : [ ]
},
"serverInfo" : {
"host" : "ip-172-31-6-96",
"port" : 27017,
"version" : "3.4.15",
"gitVersion" : "52e5b5fbaa3a2a5b1a217f5e647b5061817475f9"
},
"ok" : 1
}回答 1
Stack Overflow用户
发布于 2020-05-20 11:27:17
这可以归结为：“通过包含数组的字段对文档进行排序是什么意思？”
考虑3个文档：
{a: [ 3, 4, 5 ]}
{a: [ 2, 1, 4 ]}
{a: [ 1, 3, 2 ]}如果我们对{a:1}进行排序，它们可能会以几种不同的方式返回：
按照数组的二进制表示进行排序，结果与比较每个数组的第一个元素的结果相同，如下所示：
{a: [ 1, 3, 2 ]}
{a: [ 2, 1, 4 ]}
{a: [ 3, 4, 5 ]}包含数组的字段上的索引对数组的每个元素都有单独的键，而不是数组值本身的键。
如果我们在字段a上创建一个索引，它将包含9个键：
1 - record 2
1 - record 3
2 - record 2
2 - record 3
3 - record 1
3 - record 3
4 - record 1
5 - record 1因此，如果我们使用了用于排序索引，我们将对文档进行排序：
{a: [ 2, 1, 4 ]}
{a: [ 1, 3, 2 ]}
{a: [ 3, 4, 5 ]}这意味着，如果我们使用限制(1)来仅查找第一个文档，我们的结果将根据可用索引和规划器选择的索引而变化，这可能会因查询部分而异。
排序顺序在find和aggregate之间也会有所不同。
在MongoDB 3.6中，通过限制何时可以使用多键索引进行排序，已发现并修复了此问题。结果是，数组字段上的大多数排序现在都阻塞了内存中的操作，这意味着为了获得一致的排序和结果，性能会显著降低。
包含的explain输出显示辅助节点正在使用索引进行排序，而主节点正在加载所有文档并执行内存中的排序。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/61900275
复制相关文章
相似问题
腾讯云开发者
