为什么Mongo提示使查询运行速度提高了10倍？ [英] Why does Mongo hint make a query run up to 10 times faster?

问题描述

如果我使用explain（）从shell运行mongo查询，获取所用索引的名称，然后再次运行相同的查询，但使用hint（）指定要使用的相同索引 - 来自millis字段解释计划显着减少

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

例如

没有提供提示：

>>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字段

The only difference is "millis" field

有谁知道为什么会这样？

Does anyone know why is that?

更新：选择哪个索引使用不解释它，因为mongo，据我所知，为每个X（100？）运行选择索引，所以它应该与下一个提示（X-1）运行一样快

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

推荐答案

Mongo使用算法确定在没有提供提示时使用哪个索引，然后缓存用于下次1000次调用的类似查询的索引

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

但是每当你解释一个mongo查询时，它总会运行索引选择算法，因此带有提示的explain（）将始终为t与没有提示的explain（）相比，花费更少的时间。

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.

这里回答类似的问题
了解mongo db explain

这篇关于为什么Mongo提示使查询运行速度提高了10倍？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！