对多个字段进行排序 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 将只使用 每个查询一个索引($ors 除外).

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})

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

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屋！