在MongoDB中搜索值介于两个项目字段之间的记录

本文介绍了在MongoDB中搜索值介于两个项目字段之间的记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个MondoDB集合，其中包含超过500万个项目.每个项目都有一个包含整数值的开始"和结束"字段.

I have a MondoDB collection with over 5 million items. Each item has a "start" and "end" fields containing integer values.

项目的开头和结尾没有重叠.

Items don't have overlapping starts and ends.

例如这将无效:

{start:100, end:200} {start:150, end:250}

我正在尝试查找在开始和结束之间具有给定值的项目

I am trying to locate an item where a given value is between start and end

start <= VALUE <= end

以下查询有效，但需要5到15秒才能返回

The following query works, but it takes 5 to 15 seconds to return

db.blocks.find({ "start" : { $lt : 3232235521 }, "end" :{ $gt : 3232235521 }}).limit(1);

我添加了以下几项测试指标，但并没有什么改善

I've added the following indexes for testing with very little improvement

db.blocks.ensureIndex({start:1}); db.blocks.ensureIndex({end:1}); //also a compounded one db.blocks.ensureIndex({start:1,end:1});

**编辑**

查询中的explain()结果将导致:

The result of explain() on the query results in:

> db.blocks.find({ "start" : { $lt : 3232235521 }, "end" :{ $gt : 3232235521 }}).limit(1).explain(); { "cursor" : "BtreeCursor end_1", "nscanned" : 1160982, "nscannedObjects" : 1160982, "n" : 0, "millis" : 5779, "nYields" : 0, "nChunkSkips" : 0, "isMultiKey" : false, "indexOnly" : false, "indexBounds" : { "end" : [ [ 3232235521, 1.7976931348623157e+308 ] ] } }

加快此特定查询的最佳方法是什么?

What would be the best approach to speeding this specific query up?

推荐答案

我想compbound索引应该对您来说工作更快:

I guess compbound index should work faster for you:

db.blocks.ensureIndex({start:1, end:1});

您还可以使用说明来查看扫描对象的数量，等等，然后选择最佳索引.

You can also use explain to see number of scanned object, etc and choose best index.

如果您正在使用mongodb< 2.0，您需要更新到2.0+，因为那里的索引可以更快地工作. 另外，您可以限制结果以优化查询.

Also if you are using mongodb < 2.0 you need to update to 2.0+, because there indexes work faster. Also you can limit results to optimize query.