为什么 Mongohint 可以使查询运行速度提高 10 倍？
如果我使用explain()从shell运行mongo查询，获取所使用的索引的名称，然后再次运行相同的查询，但使用hint()指定要使用的相同索引 - 解释计划中的“millis”字段是显着减少，
例如
没有提供提示：
>>db.event.find({ "type" : "X", "active" : true, "timestamp" : { "$gte" : NumberLong("1317498259000") }, "count" : { "$gte" : 0 } }).limit(3).sort({"timestamp" : -1 }).explain();
{
"cursor" : "BtreeCursor my_super_index",
"nscanned" : 599,
"nscannedObjects" : 587,
"n" : 3,
"millis" : 24,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : true,
"indexOnly" : false,
"indexBounds" : { ... }
}
提供提示：
>>db.event.find({ "type" : "X", "active" : true, "timestamp" : { "$gte" : NumberLong("1317498259000") }, "count" : { "$gte" : 0 } }).limit(3).sort({"timestamp" : -1 }).hint("my_super_index").explain();
{
"cursor" : "BtreeCursor my_super_index",
"nscanned" : 599,
"nscannedObjects" : 587,
"n" : 3,
"millis" : 2,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : true,
"indexOnly" : false,
"indexBounds" : { ... }
}
唯一的区别是“millis”字段
有人知道为什么吗？
更新：“选择要使用的索引”并没有解释它，因为据我所知，mongo为每个X（100？）运行选择索引，所以它应该与提示下一个（X-1）一样快运行
If I run a mongo query from the shell with explain(), get the name of the index used and then run the same query again, but with hint() specifying the same index to be used - "millis" field from explain plan is decreased significantly
for example
no hint provided:
>>db.event.find({ "type" : "X", "active" : true, "timestamp" : { "$gte" : NumberLong("1317498259000") }, "count" : { "$gte" : 0 } }).limit(3).sort({"timestamp" : -1 }).explain();
{
"cursor" : "BtreeCursor my_super_index",
"nscanned" : 599,
"nscannedObjects" : 587,
"n" : 3,
"millis" : 24,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : true,
"indexOnly" : false,
"indexBounds" : { ... }
}
hint provided:
>>db.event.find({ "type" : "X", "active" : true, "timestamp" : { "$gte" : NumberLong("1317498259000") }, "count" : { "$gte" : 0 } }).limit(3).sort({"timestamp" : -1 }).hint("my_super_index").explain();
{
"cursor" : "BtreeCursor my_super_index",
"nscanned" : 599,
"nscannedObjects" : 587,
"n" : 3,
"millis" : 2,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : true,
"indexOnly" : false,
"indexBounds" : { ... }
}
The only difference is "millis" field
Does anyone know why is that?
UPDATE: "Selecting which index to use" doesn't explain it, because mongo, as far as I know, selects index for each X (100?) runs, so it should be as fast as with hint next (X-1) runs
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
Mongo 使用一种算法来确定在没有提供提示时使用哪个索引，然后缓存用于接下来 1000 次调用的类似查询的索引，
但是每当您解释 mongo 查询时，它总是会运行索引选择算法，因此解释（与不带提示的explain()相比，带提示的explain()总是花费更少的时间。
类似的问题在这里得到了回答
了解 mongo db 解释
Mongo uses an algorithm to determine which index to be used when no hint is provided and then caches the index used for the similar query for next 1000 calls
But whenever you explain a mongo query it will always run the index selection algorithm, thus the explain() with hint will always take less time when compared with explain() without hint.
Similar question was answered here
Understanding mongo db explain
从扫描的对象数量可以看出，Mongo 两次都进行了相同的搜索。您还可以看到使用的索引是相同的（看看“光标”条目），两者都已经使用了您的 my_super_index 索引。
“hint”仅告诉 Mongo 使用它在第一个查询中已自动执行的特定索引。
第二次搜索简单更快，因为所有数据可能已经在缓存中。
Mongo did the same search both times as you can see from the number of scanned objects. Also you can see that the used index was the same (take a look at the "cursor" entry), both used already your my_super_index index.
"hint" only tells Mongo to use that specific index which it already automatically did in the first query.
The second search was simple faster because all the data was probably already in the cache.
我很难为同样的事情寻找理由。我发现当我们有很多索引时，mongo确实比使用hint花费更多的时间。 Mongo 基本上花了很多时间来决定使用哪个索引。考虑一个场景，您有 40 个索引并且执行一个查询。 Mongo 需要做的第一个任务是哪个索引最适合用于特定查询。这意味着 mongo 需要扫描所有键，并在每次扫描中进行一些计算，以找到一些性能索引（如果使用此键）。提示肯定会加速，因为将保存索引键扫描。
I struggled finding reason for same thing. I found that when we have lots of indexes, mongo is indeed taking more time than using hint. Mongo basically is taking lot of time deciding which index to use. Think of a scenario where you have 40 indexes and you do a query. First task which Mongo needs to do is which index is best suited to be used for particular query. This would imply mongo needs to scan all the keys as well as do some computation in every scan to find some performancce index if this key is used. hint will definitely speed up since index key scan will be saved.
我会告诉你如何找出更快的方法
1) 无索引
它将把每个文档拉入内存以获得结果
2）有索引
如果该集合有很多索引，它将从缓存中获取索引
3）与.hint（_index）
它将采用您
在hint() 中提到的特定索引，而无需hint()
两次你都.explain("executionStats")
使用hint()，那么您可以检查totalKeysExamined值，该值将与totalDocsExamined匹配
如果没有hint()，您可以看到 totalKeysExamined 值大于 totalDocsExamined
totalDocsExamined 这个结果在大多数情况下将与结果计数完美匹配。
I will tell you how to find out how it's faster
1) without index
It will pull every document into memory to get the result
2) with index
If you have a lot of index for that collection it will take index from the cache memory
3) with .hint(_index)
It will take that specific index which you have mention
with hint() without hint()
both time you do .explain("executionStats")
with hint() then you can check totalKeysExamined value that value will match with totalDocsExamined
without hint() you can see totalKeysExamined value is greter then totalDocsExamined
totalDocsExamined this result will perfectly match with result count most of the time.