mongodb 对多个字段进行排序 mongo DB

Sorting on Multiple fields mongo DB

提问by Global Warrior

I have a query in mongo such that I want to give preference to the first field and then the second field.

我在 mongo 中有一个查询，因此我想优先考虑第一个字段，然后是第二个字段。

Say I have to query such that

说我必须这样查询

db.col.find({category: A}).sort({updated: -1, rating: -1}).limit(10).explain()

So I created the following index

所以我创建了以下索引

db.col.ensureIndex({category: 1, rating: -1, updated: -1})

It worked just fined scanning as many objects as needed, i.e. 10.

它可以根据需要扫描尽可能多的对象，即10.

But now I need to query

但现在我需要查询

db.col.find({category: { $ne: A}}).sort({updated: -1, rating: -1}).limit(10)

So I created the following index:

所以我创建了以下索引：

 db.col.ensureIndex({rating: -1, updated: -1})

but this leads to scanning of the whole document and when I create

但这会导致扫描整个文档以及当我创建

 db.col.ensureIndex({ updated: -1 ,rating: -1})

It scans less number of documents:

它扫描的文件数量较少：

I just want to ask to be clear about sorting on multiple fields and what is the order to be preserved when doing so. By reading the MongoDB documents, it's clear that the field on which we need to perform sorting should be the last field. So that is the case I assumed in my $nequery above. Am I doing anything wrong?

我只想要求清楚对多个字段进行排序以及这样做时要保留的顺序是什么。通过阅读MongoDB文档，很明显，我们需要对其进行排序的字段应该是最后一个字段。这就是我在$ne上面的查询中假设的情况。我做错了什么吗？

回答by Stennie

The MongoDB query optimizerworks by trying different plans to determine which approach works best for a given query. The winning plan for that query pattern is then cached for the next ~1,000 queries or until you do an explain().

MongoDB查询优化器通过尝试不同的计划来确定哪种方法最适合给定查询。然后，该查询模式的获胜计划将被缓存以供接下来的约 1,000 次查询使用，或者直到您执行explain().

To understand which query plans were considered, you should use explain(1), eg:

要了解考虑了哪些查询计划，您应该使用explain(1)，例如：

db.col.find({category:'A'}).sort({updated: -1}).explain(1)

The allPlansdetail will show all plans that were compared.

该allPlans细节将显示进行比较，所有的计划。

If you run a query which is not very selective (for example, if many records match your criteria of {category: { $ne:'A'}}), it may be faster for MongoDB to find results using a BasicCursor (table scan) rather than matching against an index.

如果您运行的查询不是很有选择性（例如，如果许多记录与您的条件匹配{category: { $ne:'A'}}），MongoDB 使用 BasicCursor（表扫描）查找结果可能比与索引匹配更快。

The order of fields in the querygenerally does not make a difference for the index selection (there are a few exceptions with range queries). The order of fields in a sortdoes affect the index selection. If your sort()criteria does not match the index order, the result data has to be re-sorted after the index is used (you should see scanAndOrder:truein the explain output if this happens).

查询中字段的顺序通常不会对索引选择产生影响（范围查询有一些例外）。场中的顺序排序会影响该指数的选择。如果您的sort()条件与索引顺序不匹配，则必须在使用索引后重新排序结果数据（scanAndOrder:true如果发生这种情况，您应该在解释输出中看到）。

It's also worth noting that MongoDB will only use one index per query(with the exception of $ors).

还值得注意的是，MongoDB每个查询只会使用一个索引（$ors除外）。

So if you are trying to optimize the query:

因此，如果您正在尝试优化查询：

db.col.find({category:'A'}).sort({updated: -1, rating: -1})

You will want to include all three fields in the index:

您需要在索引中包含所有三个字段：

db.col.ensureIndex({category: 1, updated: -1, rating: -1})

FYI, if you want to force a particular query to use an index (generally not needed or recommended), there is a hint()option you can try.

仅供参考，如果您想强制特定查询使用索引（通常不需要或不推荐），hint()您可以尝试一个选项。

回答by Sammaye

That is true but there are two layers of ordering you have here since you are sorting on a compound index.

确实如此，但由于您正在对复合索引进行排序，因此这里有两层排序。

As you noticed when the first field of the index matches the first field of sort it worked and the index was seen. However when working the other way around it does not.

正如您注意到的，当索引的第一个字段与它工作的排序的第一个字段匹配时，就会看到索引。但是，当以相反的方式工作时，它不会。

As such by your own obersvations the order needed to be preserved is query order of fields from first to last. The mongo analyser can sometimes move around fields to match an index but normally it will just try and match the first field, if it cannot it will skip it.

因此，根据您自己的观察，需要保留的顺序是字段从头到尾的查询顺序。mongo 分析器有时可以移动字段以匹配索引，但通常它只会尝试匹配第一个字段，如果不能，它将跳过它。

回答by Gajender Singh

try this code it will sort data first based on name then keeping the 'name' in key holder it will sort 'filter'

试试这个代码，它将首先根据名称对数据进行排序，然后将“名称”保留在密钥持有人中，它将对“过滤器”进行排序

 var cursor = db.collection('vc').find({   "name" :   { $in: [ /cpu/, /memo/ ]   }     }, { _id: 0, }).sort( { "name":1  ,  "filter": 1 } );