MongoDB如何处理关于单索引和复合索引的find().sort()查询? [英] How does MongoDB treat find().sort() queries with respect to single and compound indexes?

问题描述

所以我有一个典型的 find().sort()查询，该查询在mongo集合上运行.

  db.collection.find({field1:1}).sort({field2:1}) 

现在，说我对该集合有三个索引:

1. field1
上的单个索引
2. field2
上的单个索引
3. field1 和 field2 上的复合索引- {field1:1，field2:1}

现在我的问题是，mongoDB如何处理以上查询?像这样的查询将使用哪些索引-两个单一索引或一个复合索引?

如果我删除了复合索引，实际上它是否利用了两个单一索引但速度变慢了?

解决方案

如果我明白你的意思，这可能会有所帮助:

假设您有这些文档作为样本

  {栏位1:1栏位2:2}，{栏位1:2栏位2:3，}，{栏位1:1栏位2:4} 

第1步:您仅具有 filed1 的索引(索引的名称为 field1_1 )):执行: db.test3.find({field1:1}).sort({field2:1})

mongo使用 field1_1 索引搜索文档..explain()的结果是:

 "cursor":"BtreeCursor field1_1"，"isMultiKey":否，"n":2"nscannedObjects":2"nscanned":2 

第2步:添加复合索引，将其命名为 field1_1_field2_1 ，现在您为字段1设置了2个索引.

执行 find().sort()查询，您将拥有

 "cursor":"BtreeCursor field1_1_field2_1"，"isMultiKey":否，"n":2"nscannedObjects":2"nscanned":2 

凝聚力:

如果您使用 db.test3.find({field1:1}).sort({field2:1})，则mongo将使用 field1_1_field2_1 索引./p>

如果您使用 db.test3.find({field1:1})，则mongo将使用 field1_1 索引.

您的情况是，如果您只有 field1_1_field2_1 索引并且正在执行 db.test3.find({field1:1})，则mongo将使用 field1_1_field2_1 索引.

So I have a typical find().sort() query that I run on my mongo collection.

db.collection.find({field1:1}).sort({field2:1})

Now, say I have three indexes on this collection:

1. single index on field1
2. single index on field2
3. compound index on field1 and field2 - {field1:1,field2:1}

Now my question, how does mongoDB treat the above query? What are the indexes that will be used in a query like that- two single indexes or the one compound index?

If I remove the compound index, does it in fact make use of the two single indexes but slowing down?

解决方案

If I got your point, this might help:

Assuming you have these documents for sample

{
    field1 : 1,
    field2 : 2,
},
{
    field1 : 2,
    field2 : 3,
},
{
    field1 : 1,
    field2 : 4,
}

Step 1: you have index just for filed1 (name of index field1_1)}: perform the : db.test3.find({field1:1}).sort({field2:1})

the mongo uses field1_1 index to search in document. the result of .explain() is:

"cursor" : "BtreeCursor field1_1",
"isMultiKey" : false,
"n" : 2,
"nscannedObjects" : 2,
"nscanned" : 2,

Step 2: add your compound index, name it field1_1_field2_1, now you have 2 index for field 1.

perform find().sort() query, you will have

"cursor" : "BtreeCursor field1_1_field2_1",
"isMultiKey" : false,
"n" : 2,
"nscannedObjects" : 2,
"nscanned" : 2,

Concolusion:

if you use db.test3.find({field1:1}).sort({field2:1}), the mongo will use field1_1_field2_1 index.

if you use db.test3.find({field1:1}), the mongo will use field1_1 index.

I your case, If you have just field1_1_field2_1 index and you are performing db.test3.find({field1:1}), the mongo will use field1_1_field2_1 index as well.

这篇关于MongoDB如何处理关于单索引和复合索引的find().sort()查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！