索引为>的搜索时间没有索引 [英] search time with index > without index

问题描述

我有一个集合数字"，其中包含200000个文档对象，其中{number:i} i = 1到200000.

I have one collection "numbers" with 200000 document object with {number: i} i = 1 to 200000.

没有任何索引$ gt:10000给出nscanned 200000和115 ms. 索引编号为$ gt:10000时，给出nscanned 189999和355 ms.

Without any index $gt: 10000 gives nscanned 200000 and 115 ms. With index on number $gt: 10000 gives nscanned 189999 and 355 ms.

为什么要花更多的时间进行索引?

Why more time with indexing?

> db.numbers.find({number: {$gt: 10000}}).explain()
{
    "cursor" : "BasicCursor",
    "nscanned" : 200000,
    "nscannedObjects" : 200000,
    "n" : 189999,
    "millis" : 115,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "isMultiKey" : false,
    "indexOnly" : false,
    "indexBounds" : {

    }
}

> db.numbers.ensureIndex({number: 1})

> db.numbers.find({number: {$gt: 10000}}).explain()
{
    "cursor" : "BtreeCursor number_1",
    "nscanned" : 189999,
    "nscannedObjects" : 189999,
    "n" : 189999,
    "millis" : 355,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "isMultiKey" : false,
    "indexOnly" : false,
    "indexBounds" : {
            "number" : [
                    [
                            10000,
                            1.7976931348623157e+308
                    ]
            ]
    }
}

推荐答案

在这种情况下，索引没有帮助，因为您匹配的结果集几乎包含整个集合.这意味着它必须加载到RAM中并遍历大部分索引，还必须加载到RAM中并遍历文档本身.

In this case, the index doesn't help because your matching result set consists of almost the entire collection. That means it has to load into RAM and traverse most of the index, as well as load into RAM and traverse the documents themselves.

没有索引，它将只进行表扫描，检查每个文档，如果匹配则返回.

Without the index, it would just do a table scan, inspecting each document and returning if matched.

在这种情况下，查询将返回几乎整个集合，索引可能无济于事.

In cases like this where a query is going to return almost an entire collection, an index may not be helpful.

添加.limit()将加快查询速度.您还可以强制查询优化器不要在.hint()中使用索引:

Adding a .limit() will speed the query up. You can also force the query optimizer to not use the index with .hint():

db.collection.find().hint({$natural:1})

您还可以通过将所选字段限制为仅已索引的字段来强制查询直接从索引本身提供结果值.这样可以避免在执行索引扫描后加载任何文档.

You could also force the query to provide the result values directly from the index itself by limiting the selected fields to only the ones you've indexed. This allows it to avoid the need to load any documents after doing the index scan.

尝试一下，看看说明输出是否指示"indexOnly":true

Try this and see if the explain output indicates "indexOnly":true

db.numbers.find({number: {$gt: 10000}}, {number:1}).explain()

详细信息在这里:

http://www.mongodb. org/display/DOCS/Retrieving + a + Subset + of + Fields#RetrievingaSubsetofFields-CoveredIndexes

这篇关于索引为>的搜索时间没有索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！