MongoDB索引在不同主机上的相同查询的用法不同

Question

我正在运行一个非常简单的查询：db.users.find({}, { _id: 1 }).sort({teamIds: 1}).limit(25)，其中teamIds是一个数组，上面有一个升序索引。如果我在辅助数据库上运行此查询，explain显示查看了 25 个键，并查看了 25 个文档(如预期的那样(。如果我运行，则在主服务器上执行完全相同的查询，explain显示查看了 50K+ 密钥和文档。我已经检查了主索引和辅助索引是否相同，并且两者都在使用中。以下是两种情况下解释的输出。

什么可能导致这种行为？

在主数据库上，索引是否有可能未完全加载到内存中？主数据库使用大约 65% 的内存，次要数据库使用大约 55% 的内存(均为 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
}

Answer 1

这归结为："按包含数组的字段对文档进行排序是什么意思？

考虑 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 ]}

这意味着，如果我们使用 limit(1( 只查找第一个文档，我们的结果会根据可用索引和计划器选择的索引而变化，这可能会因查询部分而异。

排序顺序在find和aggregate之间也有所不同。

在MongoDB 3.6中，通过限制何时可以使用多键索引进行排序来识别并修复此问题。 结果是，数组字段上的大多数排序现在都阻止了内存中的操作，这意味着性能显著降低，以换取一致的排序和结果。

您包含的说明输出显示辅助节点正在使用索引进行排序，而主节点正在加载所有文档并执行内存中排序。