MongoDB - 排序和限制范围查询时未使用索引 [英] MongoDB - Index not being used when sorting and limiting on ranged query
本文介绍了MongoDB - 排序和限制范围查询时未使用索引的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试对包含公告板数据的集合使用范围查询来获取项目的排序列表.一个线程"文档的数据结构是:
I'm trying to get a sorted list of items using a ranged query on a collection containing bulletin-board data. The data structure of a "thread" document is:
{
"_id" : ObjectId("5a779b47f4fa72412126526a"),
"title" : "necessitatibus tincidunt libris assueverit",
"content" : "Corrumpitvenenatis cubilia adipiscing sollicitudin",
"flagged" : false,
"locked" : false,
"sticky" : false,
"lastPostAt" : ISODate("2018-02-05T06:35:24.656Z"),
"postCount" : 42,
"user" : ObjectId("5a779b46f4fa72412126525a"),
"category" : ObjectId("5a779b31f4fa724121265164"),
"createdAt" : ISODate("2018-02-04T23:46:15.852Z"),
"updatedAt" : ISODate("2018-02-05T06:35:24.656Z")
}
查询是:
db.threads.find({
category: ObjectId('5a779b31f4fa724121265142'),
_id : { $gt: ObjectId('5a779b5cf4fa724121269be8') }
}).sort({ sticky: -1, lastPostAt: -1, _id: 1 }).limit(25)
我设置了以下索引来支持它:
I set up the following indexes to support it:
{ category: 1, _id: 1 }
{ category: 1, _id: 1, sticky: 1, lastPostAt: 1 }
{ sticky: 1, lastPostAt: 1, _id: 1 }
尽管如此，它仍然根据执行统计扫描数百个文档/密钥:
In spite of this, it's still scanning hundreds of documents/keys according to execution stats:
{
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 772,
"executionTimeMillis" : 17,
"totalKeysExamined" : 772,
"totalDocsExamined" : 772,
"executionStages" : {
"stage" : "SORT",
"nReturned" : 772,
"executionTimeMillisEstimate" : 0,
"works" : 1547,
"advanced" : 772,
"needTime" : 774,
"needYield" : 0,
"saveState" : 33,
"restoreState" : 33,
"isEOF" : 1,
"invalidates" : 0,
"sortPattern" : {
"sticky" : -1,
"lastPostAt" : -1,
"_id" : 1
},
"memUsage" : 1482601,
"memLimit" : 33554432,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"nReturned" : 772,
"executionTimeMillisEstimate" : 0,
"works" : 774,
"advanced" : 772,
"needTime" : 1,
"needYield" : 0,
"saveState" : 33,
"restoreState" : 33,
"isEOF" : 1,
"invalidates" : 0,
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 772,
"executionTimeMillisEstimate" : 0,
"works" : 773,
"advanced" : 772,
"needTime" : 0,
"needYield" : 0,
"saveState" : 33,
"restoreState" : 33,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 772,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 772,
"executionTimeMillisEstimate" : 0,
"works" : 773,
"advanced" : 772,
"needTime" : 0,
"needYield" : 0,
"saveState" : 33,
"restoreState" : 33,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"category" : 1,
"_id" : 1,
"sticky" : 1,
"lastPostAt" : 1
},
"indexName" : "category_1__id_1_sticky_1_lastPostAt_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"category" : [ ],
"_id" : [ ],
"sticky" : [ ],
"lastPostAt" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"category" : [
"[ObjectId('5a779b31f4fa724121265142'), ObjectId('5a779b31f4fa724121265142')]"
],
"_id" : [
"(ObjectId('5a779b5cf4fa724121269be8'), ObjectId('ffffffffffffffffffffffff')]"
],
"sticky" : [
"[MinKey, MaxKey]"
],
"lastPostAt" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 772,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
}
}
当我取出分拣台时，它只能正确扫描 25 个文档.无论我在排序函数中放置哪些字段，检查的键 (772) 都保持不变.
When I take out the sorting stage, it correctly scans only 25 documents. And the keys examined (772) remains the same no matter which fields I place in the sort function.
这是排序查询的完整explain():
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "database.threads",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"category" : {
"$eq" : ObjectId("5a779b31f4fa724121265142")
}
},
{
"_id" : {
"$gt" : ObjectId("5a779b5cf4fa724121269be8")
}
}
]
},
"winningPlan" : {
"stage" : "SORT",
"sortPattern" : {
"sticky" : -1,
"lastPostAt" : -1,
"_id" : 1
},
"limitAmount" : 25,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"category" : 1,
"_id" : 1,
"sticky" : 1,
"lastPostAt" : 1
},
"indexName" : "category_1__id_1_sticky_1_lastPostAt_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"category" : [ ],
"_id" : [ ],
"sticky" : [ ],
"lastPostAt" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"category" : [
"[ObjectId('5a779b31f4fa724121265142'), ObjectId('5a779b31f4fa724121265142')]"
],
"_id" : [
"(ObjectId('5a779b5cf4fa724121269be8'), ObjectId('ffffffffffffffffffffffff')]"
],
"sticky" : [
"[MinKey, MaxKey]"
],
"lastPostAt" : [
"[MinKey, MaxKey]"
]
}
}
}
}
},
"rejectedPlans" : [
{
"stage" : "SORT",
"sortPattern" : {
"sticky" : -1,
"lastPostAt" : -1,
"_id" : 1
},
"limitAmount" : 25,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"_id" : {
"$gt" : ObjectId("5a779b5cf4fa724121269be8")
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"category" : 1
},
"indexName" : "category_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"category" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"category" : [
"[ObjectId('5a779b31f4fa724121265142'), ObjectId('5a779b31f4fa724121265142')]"
]
}
}
}
}
},
{
"stage" : "SORT",
"sortPattern" : {
"sticky" : -1,
"lastPostAt" : -1,
"_id" : 1
},
"limitAmount" : 25,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"category" : 1,
"_id" : 1
},
"indexName" : "category_1__id_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"category" : [ ],
"_id" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"category" : [
"[ObjectId('5a779b31f4fa724121265142'), ObjectId('5a779b31f4fa724121265142')]"
],
"_id" : [
"(ObjectId('5a779b5cf4fa724121269be8'), ObjectId('ffffffffffffffffffffffff')]"
]
}
}
}
}
},
{
"stage" : "SORT",
"sortPattern" : {
"sticky" : -1,
"lastPostAt" : -1,
"_id" : 1
},
"limitAmount" : 25,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"category" : {
"$eq" : ObjectId("5a779b31f4fa724121265142")
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"_id" : 1
},
"indexName" : "_id_",
"isMultiKey" : false,
"multiKeyPaths" : {
"_id" : [ ]
},
"isUnique" : true,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"_id" : [
"(ObjectId('5a779b5cf4fa724121269be8'), ObjectId('ffffffffffffffffffffffff')]"
]
}
}
}
}
}
]
},
"serverInfo" : {
"host" : "CRF-MBP.local",
"port" : 27017,
"version" : "3.6.2",
"gitVersion" : "489d177dbd0f0420a8ca04d39fd78d0a2c539420"
},
"ok" : 1
}
这里是非排序查询的完整explain():
And here is the full explain() for the non-sorted query:
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "database.threads",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"category" : {
"$eq" : ObjectId("5a779b31f4fa724121265142")
}
},
{
"_id" : {
"$gt" : ObjectId("5a779b5cf4fa724121269be8")
}
}
]
},
"winningPlan" : {
"stage" : "LIMIT",
"limitAmount" : 25,
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"category" : 1,
"_id" : 1,
"sticky" : 1,
"lastPostAt" : 1
},
"indexName" : "category_1__id_1_sticky_1_lastPostAt_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"category" : [ ],
"_id" : [ ],
"sticky" : [ ],
"lastPostAt" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"category" : [
"[ObjectId('5a779b31f4fa724121265142'), ObjectId('5a779b31f4fa724121265142')]"
],
"_id" : [
"(ObjectId('5a779b5cf4fa724121269be8'), ObjectId('ffffffffffffffffffffffff')]"
],
"sticky" : [
"[MinKey, MaxKey]"
],
"lastPostAt" : [
"[MinKey, MaxKey]"
]
}
}
}
},
"rejectedPlans" : [
{
"stage" : "LIMIT",
"limitAmount" : 25,
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"_id" : {
"$gt" : ObjectId("5a779b5cf4fa724121269be8")
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"category" : 1
},
"indexName" : "category_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"category" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"category" : [
"[ObjectId('5a779b31f4fa724121265142'), ObjectId('5a779b31f4fa724121265142')]"
]
}
}
}
},
{
"stage" : "LIMIT",
"limitAmount" : 25,
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"category" : 1,
"_id" : 1
},
"indexName" : "category_1__id_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"category" : [ ],
"_id" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"category" : [
"[ObjectId('5a779b31f4fa724121265142'), ObjectId('5a779b31f4fa724121265142')]"
],
"_id" : [
"(ObjectId('5a779b5cf4fa724121269be8'), ObjectId('ffffffffffffffffffffffff')]"
]
}
}
}
},
{
"stage" : "LIMIT",
"limitAmount" : 25,
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"category" : {
"$eq" : ObjectId("5a779b31f4fa724121265142")
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"_id" : 1
},
"indexName" : "_id_",
"isMultiKey" : false,
"multiKeyPaths" : {
"_id" : [ ]
},
"isUnique" : true,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"_id" : [
"(ObjectId('5a779b5cf4fa724121269be8'), ObjectId('ffffffffffffffffffffffff')]"
]
}
}
}
}
]
},
"serverInfo" : {
"host" : "CRF-MBP.local",
"port" : 27017,
"version" : "3.6.2",
"gitVersion" : "489d177dbd0f0420a8ca04d39fd78d0a2c539420"
},
"ok" : 1
}
有人知道为什么这可能不能完全使用索引吗?
Does anyone have any idea why this might not fully use an index?
推荐答案
问题是您的索引实际上都没有帮助排序查询.这就是大量扫描对象和存在 SORT_KEY_GENERATOR 阶段(内存中排序，限制为 32MB)的原因.
The issue is that none of your indexes actually help with the sorted query. This is the reason for the high number of scanned objects and the presence of SORT_KEY_GENERATOR stage (in-memory sort, limited to 32MB).
另一方面，非排序查询可以使用 { category: 1, _id: 1 } 或 { category: 1, _id: 1, sticky: 1, lastPostAt: 1 } 索引.请注意，使用任何一个都是完全有效的，因为一个包含另一个的前缀.有关详细信息，请参阅前缀.
The non-sorted query, on the other hand, can use either the { category: 1, _id: 1 } or { category: 1, _id: 1, sticky: 1, lastPostAt: 1 } indexes. Note that it's perfectly valid to use either one, since one contains the prefix of the other. See Prefixes for more details.
MongoDB find() 查询通常只使用一个索引，因此单个 复合索引 应该满足您查询的所有参数.这将包括 find() 和 sort() 的参数.
MongoDB find() queries typically uses only one index, so a single compound index should cater for all the parameters of your query. This would include both the parameters of find() and sort().
Optimizing MongoDB Compound Indexes 中有一篇关于如何创建索引的好文章.我们来看看文章的重点，复合索引的排序应该是equality --> sort --> range:
A good writeup of how your index should be created is available in Optimizing MongoDB Compound Indexes. Let's take the main point of the article, where the compound index ordering should be equality --> sort --> range:
您的查询形状"是:
db.collection.find({category:..., _id: {$gt:...}})
.sort({sticky:-1, lastPostAt:-1, _id:1})
.limit(25)
我们看到:
category:...是平等sticky:-1, lastPostAt:-1, _id:1是 sort_id: {$gt:...}是范围
category:...is equalitysticky:-1, lastPostAt:-1, _id:1is sort_id: {$gt:...}is range
所以你需要的复合索引是:
So the compound index you need is:
{category:1, sticky:-1, lastPostAt:-1, _id:1}
具有上述索引的查询的 explain() 输出的获胜计划显示:
Where the winning plan of the explain() output of your query with the above index shows:
"winningPlan": {
"stage": "LIMIT",
"limitAmount": 25,
"inputStage": {
"stage": "FETCH",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"category": 1,
"sticky": -1,
"lastPostAt": -1,
"_id": 1
},
"indexName": "category_1_sticky_-1_lastPostAt_-1__id_1",
"isMultiKey": false,
"multiKeyPaths": {
"category": [ ],
"sticky": [ ],
"lastPostAt": [ ],
"_id": [ ]
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"category": [
"[ObjectId('5a779b31f4fa724121265142'), ObjectId('5a779b31f4fa724121265142')]"
],
"sticky": [
"[MaxKey, MinKey]"
],
"lastPostAt": [
"[MaxKey, MinKey]"
],
"_id": [
"(ObjectId('5a779b5cf4fa724121269be8'), ObjectId('ffffffffffffffffffffffff')]"
]
}
}
}
}
请注意，获胜计划不包含 SORT_KEY_GENERATOR 阶段.这意味着可以充分利用索引来响应排序查询.
Note that the winning plan doesn't contain a SORT_KEY_GENERATOR stage. This means that the index can be fully utilized to respond to the sorted query.
这篇关于MongoDB - 排序和限制范围查询时未使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
