在多个字段上排序mongo DB [英] Sorting on Multiple fields mongo DB

问题描述

我在mongo中有一个查询，因此我希望优先选择第一个字段，然后再选择第二个字段.

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

说我必须这样查询

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

所以我创建了以下索引

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

它只是根据需要扫描了许多对象，即10.

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

但是现在我需要查询

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:

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

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 $ne query above. Am I doing anything wrong?

推荐答案

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

The MongoDB query optimizer works 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().

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

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

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

allPlans详细信息将显示所有比较过的计划.

The allPlans detail will show all plans that were compared.

如果您运行的查询不是非常有选择性(例如，如果许多记录符合您的{category: { $ne:'A'}}条件)，则MongoDB使用BasicCursor(表扫描)查找结果的速度可能要快于与索引.

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.

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

The order of fields in the query generally does not make a difference for the index selection (there are a few exceptions with range queries). The order of fields in a sort does 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:true in the explain output if this happens).

还值得注意的是，MongoDB仅会使用每个查询一个索引($or除外).

It's also worth noting that MongoDB will only use one index per query (with the exception of $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.

这篇关于在多个字段上排序mongo DB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！