为什么索引的方向在 MongoDB 中很重要？

Why does direction of index matter in MongoDB?

提问by johndodo

To quote the docs:

引用文档：

When creating an index, the number associated with a key specifies the direction of the index, so it should always be 1 (ascending) or -1 (descending). Direction doesn't matter for single key indexes or for random access retrieval but is important if you are doing sorts or range queries on compound indexes.

创建索引时，与键关联的数字指定索引的方向，因此它应该始终为 1（升序）或 -1（降序）。方向对于单键索引或随机访问检索无关紧要，但如果您对复合索引进行排序或范围查询，则方向很重要。

However, I see no reason why direction of the index should matter on compound indexes. Can someone please provide a further explanation (or an example)?

但是，我认为没有理由为什么索引的方向对复合索引很重要。有人可以提供进一步的解释（或示例）吗？

采纳答案by Jared Kells

MongoDB concatenates the compound key in some way and uses it as the key in a BTree.

MongoDB 以某种方式连接复合键并将其用作 BTree 中的键。

When finding single items- The order of the nodes in the tree is irrelevant.

查找单个项目时- 树中节点的顺序无关紧要。

If you are returning a range of nodes- The elements close to each other will be down the same branches of the tree. The closer the nodes are in the range the quicker they can be retrieved.

如果您要返回一系列节点- 彼此靠近的元素将沿着树的相同分支向下。节点在范围内越近，检索它们的速度就越快。

With a single field index- The order won't matter. If they are close together in ascending order they will also be close together in descending order.

使用单个字段索引- 顺序无关紧要。如果它们以升序排列在一起，它们也会以降序排列在一起。

When you have a compound key- The order starts to matter.

当您拥有复合键时- 订单开始变得重要。

For example, if the key is A ascending B ascending the index might look something like this:

例如，如果键是 A 升序 B 升序，则索引可能如下所示：

Row   A B
1     1 1
2     2 6
3     2 7 
4     3 4
5     3 5
6     3 6
7     5 1

A query for A ascending B descending will need to jump around the index out of order to return the rows and will be slower. For example it will return Row 1, 3, 2, 6, 5, 4, 7

对 A 升序 B 降序的查询将需要乱序跳过索引以返回行，并且速度会更慢。例如它将返回行1, 3, 2, 6, 5, 4, 7

A ranged query in the same order as the index will simply return the rows sequentially in the correct order.

与索引顺序相同的范围查询将简单地以正确的顺序依次返回行。

Finding a record in a BTree takes O(Log(n)) time. Finding a range of records in order is only OLog(n) + k where k is the number of records to return.

在 BTree 中查找记录需要 O(Log(n)) 时间。按顺序查找记录范围只是 OLog(n) + k，其中 k 是要返回的记录数。

If the records are out of order, the cost could be as high as OLog(n) * k

如果记录乱序，成本可能高达 OLog(n) * k

回答by Zaid Masud

The simple answerthat you are looking for is that the direction only matters when you are sorting on two or more fields.

您正在寻找的简单答案是，方向仅在您对两个或多个字段进行排序时才重要。

If you are sorting on {a : 1, b : -1}:

如果您正在排序{a : 1, b : -1}：

Index {a : 1, b : 1}will be slower thanindex {a : 1, b : -1}

索引{a : 1, b : 1}会比索引慢{a : 1, b : -1}

回答by Somnath Muluk

Why indexes

为什么要索引

Understand two key points.

理解两个关键点。

1. While an index is better than no index, the correct index is much better than either.
2. MongoDB will only use one index per query, making compound indexes with proper field ordering what you probably want to use.

1. 虽然有索引总比没有索引好，但正确的索引比两者都好得多。
2. MongoDB 每个查询只会使用一个索引，从而使复合索引具有您可能想要使用的正确字段排序。

Indexes aren't free. They take memory, and impose a performance penalty when doing inserts, updates and deletes. Normally the performance hit is negligible (especially compared to gains in read performance), but that doesn't mean that we can't be smart about creating our indexes.

索引不是免费的。它们占用内存，并在执行插入、更新和删除时施加性能损失。通常，性能损失可以忽略不计（尤其是与读取性能的提升相比），但这并不意味着我们不能聪明地创建索引。

How Indexes

如何索引

Identifying what group of fields should be indexed together is about understanding the queries that you are running. The order of the fields used to create your index is critical. The good news is that, if you get the order wrong, the index won't be used at all, so it'll be easy to spot with explain.

确定哪些字段组应该一起索引是关于了解您正在运行的查询。用于创建索引的字段顺序至关重要。好消息是，如果您弄错了顺序，则根本不会使用索引，因此通过解释很容易发现。

Why Sorting

为什么要排序

Your queries might need Sorting. But sorting can be an expensive operation, so it's important to treat the fields that you are sorting on just like a field that you are querying. So it will be faster if it has index. There is one important difference though, the field that you are sorting must be the last field in your index. The only exception to this rule is if the field is also part of your query, then the must-be-last-rule doesn't apply.

您的查询可能需要排序。但是排序可能是一项代价高昂的操作，因此将排序所依据的字段视为要查询的字段非常重要。所以如果它有索引会更快。但是有一个重要的区别，您要排序的字段必须是索引中的最后一个字段。此规则的唯一例外是，如果该字段也是查询的一部分，则 must-be-last-rule 不适用。

How Sorting

如何排序

You can specify a sort on all the keys of the index or on a subset; however, the sort keys must be listed in the same order as they appear in the index. For example, an index key pattern { a: 1, b: 1 } can support a sort on { a: 1, b: 1 } but not on { b: 1, a: 1 }.

您可以对索引的所有键或子集指定排序；但是，排序键必须按照它们在索引中出现的相同顺序列出。例如，索引键模式 { a: 1, b: 1 } 可以支持对 { a: 1, b: 1 } 排序，但不支持对 { b: 1, a: 1 } 排序。

The sort must specify the same sort direction (i.e. ascending/descending) for all its keys as the index key pattern or specify the reverse sort direction for all its keys as the index key pattern. For example, an index key pattern { a: 1, b: 1 } can support a sort on { a: 1, b: 1 } and { a: -1, b: -1 } but not on { a: -1, b: 1 }.

排序必须为其所有键指定相同的排序方向（即升序/降序）作为索引键模式，或者为其所有键指定反向排序方向作为索引键模式。例如，索引键模式 { a: 1, b: 1 } 可以支持对 { a: 1, b: 1 } 和 { a: -1, b: -1 } 排序，但不支持对 { a: -1 , b: 1 }。

Suppose there are these indexes:

假设有这些索引：

{ a: 1 }
{ a: 1, b: 1 }
{ a: 1, b: 1, c: 1 }

Example                                                    Index Used
db.data.find().sort( { a: 1 } )                            { a: 1 }
db.data.find().sort( { a: -1 } )                           { a: 1 }
db.data.find().sort( { a: 1, b: 1 } )                      { a: 1, b: 1 }
db.data.find().sort( { a: -1, b: -1 } )                    { a: 1, b: 1 }
db.data.find().sort( { a: 1, b: 1, c: 1 } )                { a: 1, b: 1, c: 1 }
db.data.find( { a: { $gt: 4 } } ).sort( { a: 1, b: 1 } )   { a: 1, b: 1 }