MongoDB(和Mongoose.js):查询条件的顺序重要吗? [英] MongoDB (and Mongoose.js): Does the order of query conditions matter?

问题描述

创建简单的MongoDB查询时，我对查询中的条件顺序有疑问-例如(Mongoose.js语法):

When creating a simple MongoDB query, I have a question about the ordering of conditions in the query - for example (Mongoose.js syntax):

conditions = { archived: false, first_name: "Billy" };

vs.

conditions = { first_name: "Billy", archived: false };

..在一个简单的find()函数中:

..in a simple find() function:

User.find(conditions, function(err, users) { <some logic> });

..假设一种简单的单键索引策略:

..assuming a simple single-key indexing strategy:

UserSchema.index( { first_name: 1, archived: 1} );

..上面列出的条件的顺序重要吗?

..does the order of the conditions listed above matter?

重要提示::我知道复合索引的顺序很重要，但对于上述情况，我对单键索引查询感到好奇.由于我们在这里，因此对完全非索引查询的情况也很感兴趣. :)

IMPORTANT: I know the order DOES MATTER for compound indexes, but per above I am curious about single-key index queries. Also interested in cases of totally non-indexed queries since we're here. :)

替代说明:换句话说，假设有100个User s(已归档50个，未归档50个)，并给出了两种可能的内部MongoDB搜索策略:

ALTERNATE EXPLANATION: Put another way, assuming 100 Users (50 archived and 50 not), given two possible internal MongoDB searching strategies:

1. 首先过滤掉所有archived用户中的50个，然后搜索first_name值为比利"的其余50个未归档用户
2. 首先在全部100个User文档中搜索first_name值"Billy"，然后通过删除所有已归档的Billy来过滤找到的对象.

1. First filter out all 50 of the archived users, then search through the remaining 50 non-archived users with the first_name value of "Billy"
2. First search through ALL 100 User documents for the first_name value "Billy", and then filter the found objects by removing any Billys that are archived.

..我假设#1更快(在具有两个以上条件的大型查询中，可能更快).但是，不管哪个更快，为什么更快，肯定是其中之一.

..I would assume #1 to be faster (potentially MUCH faster in large queries with more than two conditions). But regardless of which is faster and why, surely one of them is.

核心问题:在庞大而强大的复合索引世界之外，MongoDB是否知道如何自动执行其最高效/快速的搜索/过滤器，而与哪个字段和顺序无关?还是我们需要以编程方式(通过显示的条件顺序等)告诉系统什么是最好的?

CORE QUESTION: Outside the vast and powerful world of compound indexes, does MongoDB know how to perform its most performant/quick searches/filters automatically, regardless of which fields and which ordering? Or do we need to tell the system what is best programmatically (via the order of conditions presented, etc)?

推荐答案

我对您的问题有点困惑，仅仅是因为您提供的({ first_name: 1, archived: 1 })索引是复合索引.以下所有查询都将使用该复合索引:

I'm a little confused by your question, simply because the index you provide ({ first_name: 1, archived: 1 }) is a compound index. All of the following queries will make use of that compound index:

conditions = { archived: false, first_name: "Billy" };
conditions = { first_name: "Billy", archived: false };
conditions = { first_name: "Billy" };

现在，假设我们有两个单独的索引，{ first_name: 1 }和{ archived: 1 }.在这种情况下，MongoDB将进行查询优化以确定哪个索引最有效地使用. 您可以在此处详细了解MongoDB执行的查询优化

Now, let's assume we have two separate indexes, { first_name: 1 } and { archived: 1 }. In this case, MongoDB will do query optimization to determine which index is the most efficient to use. You can read more about the query optimization performed by MongoDB here.

因此，MongoDB查询优化器可能会对您提供的两个多条件查询使用相同的索引:

The MongoDB query optimizer will thus likely use the same index for both of the multicondition queries you provided:

conditions = { archived: false, first_name: "Billy" };
conditions = { first_name: "Billy", archived: false };

或者，您可以使用 hint 来强制MongoDB使用您选择的索引.通常，这可能不是一个好主意.您还可以手动检查哪个索引对特定查询最有效详细信息.

Alternatively, you can use hint to force MongoDB to use an index of your choosing. In general, this is probably not a good idea. You can also manually check which index is the most efficient for a specific query as detailed here.

通过使用 .explain() 功能. (如果未使用索引，则会在结果文档中看到"cursor" : "BasicCursor".另一方面，如果正在使用复合索引，则会看到类似"cursor" : "BtreeCursor first_name_1_archived_1"的内容.如果有一个单字段索引使用过，您可能会看到"cursor" : "BtreeCursor archived_1".

You can see which index a query is using by using the .explain() functionality in the Mongo shell. (If no index is used, you'll see "cursor" : "BasicCursor" in the resulting document. On the other hand, if the compound index is being used, you'll see something like "cursor" : "BtreeCursor first_name_1_archived_1". If one of the single-field indexes was used, you might see "cursor" : "BtreeCursor archived_1".

此外，MongoDB的搜索策略如下所示:

Additionally, the search strategy for MongoDB works like this:

• 首先，遍历索引，使用索引范围过滤掉尽可能多的文档；
• 接下来，如果还有其他谓词无法使用索引满足，则
  • 获取文档
  • 应用谓词
  • 并包括/排除从结果中适当地找到文档.

  查询优化器并行运行所有可能的查询计划并选择最佳"查询计划，但是所有查询计划都遵循上述策略. (BasicCursor是一种简并的情况:它遍历所有文档并将谓词应用于每个文档.)

  The query optimizer runs all possible query plans in parallel and picks the "best" one, however all of the query plans follow the strategy above. (The BasicCursor is a degenerate case: it traverses all of the documents & applies the predicate to each one.)

  tl; dr?.Matcher足够聪明，可以按任意顺序显示相等谓词.

  tl;dr? The Matcher is smart enough to match equality predicates when they're presented in any order.

  这有意义吗?

  这篇关于MongoDB(和Mongoose.js):查询条件的顺序重要吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！