MongoDB:为什么不按多个键排序使用索引? [英] MongoDB: why doesn't sorting by multiple keys use an index?
问题描述
问题:
我有一个非常大的集合,按字段 ts
索引:(时间戳)
> db.events.ensureIndex({'ts': -1})
我想获得最后5条目。令我惊讶的是,查询不使用索引,因此非常慢:
I want to get last 5 entries. What surprises me is that the query doesn't use the index and is thus very slow:
> db.events.find().sort({'ts': -1, '_id': -1}).limit(5)
然而,仅按 ts
排序或其他字段使用索引:
However, sorting just by ts
or the other field uses index as it should:
> db.events.find().sort({'ts': -1}).limit(5)
> db.events.find().sort({'_id': -1}).limit(5)
这是MongoDB中的错误,这确实是一个记录在案的功能还是我做错了什么?
Is this a bug in MongoDB, is this indeed a documented feature or am I doing something wrong?
其他信息:
> db.events.find().sort({'ts': -1, '_id': -1}).limit(5).explain()
{
"cursor" : "BasicCursor",
"nscanned" : 795609,
"nscannedObjects" : 795609,
"n" : 5,
"scanAndOrder" : true,
"millis" : 22866,
"nYields" : 73,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
}
}
> db.events.find().sort({'ts': -1}).limit(5).explain()
{
"cursor" : "BtreeCursor ts_-1",
"nscanned" : 5,
"nscannedObjects" : 5,
"n" : 5,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"ts" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
}
}
推荐答案
阅读索引的索引策略部分是值得的。建议&常见问题维基页面。
It's worth having a read of the Indexing Strategies section of the Indexing Advice & FAQ wiki page.
您可能会遗漏一些注意事项:
There are a few considerations that you may be missing:
-
MongoDB每个查询只使用一个索引
MongoDB only uses one index per query
使用的 sort
列必须是索引中的最后一列
the sort
column used must be the last column in the index
因此,对于您的示例,您应该在上添加复合索引ts
和 _id
:
So, for your example you should add a compound index on ts
and _id
:
db.events。 ensureIndex({'ts': - 1,'_ id': - 1});
db.events.ensureIndex({'ts':-1, '_id':-1});
..并以<$ c确认$ c> explain()排序现在使用预期的索引:
.. and confirm with explain()
that the sort is now using the expected index:
> db.events.find().sort({'ts': -1, '_id':-1}).limit(5).explain()
{
"cursor" : "BtreeCursor ts_-1__id_-1",
"nscanned" : 5,
"nscannedObjects" : 5,
"n" : 5,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"ts" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
],
"_id" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
}
}
这篇关于MongoDB:为什么不按多个键排序使用索引?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!