[翻译] 优化 MongoDB 复合索引翻译者认真的态度可见一斑，功力还需加深。赞一个！

原文： Optimizing MongoDB Compound Indexes

How do you create the best index for a complex MongoDB query? I’ll present a method specifically for queries that combine equality tests, sorts, and range filters, and demonstrate the best order for fields in a compound index. We’ll look at the explain() output to see exactly how well it performs, and we’ll see how the MongoDB query-optimizer selects an index.

你是如何为复杂的 MongoDB 查询构造最好的索引的？我将会给出一个专门为结合了等值查询、排序、范围过滤的查询，并示范复合索引中的最佳字段顺序。我们将会通过 explain() 的结果看出查询的性能究竟如何以及 MongoDB 的查询优化器是如何选择索引的。

The Setup

Let’s pretend I’m building a comments system like Disqus on MongoDB. (They actually use Postgres, but I’m asking you to use your imagination.) I plan to store millions of comments, but I’ll begin with four. Each has a timestamp and a quality rating, and one was posted by an anonymous coward:

假设我们正在用 MongoDB 构建一个类似 Disqus 的评论系统（ Disqus 实际上用的是 Postgres ，但我们可以假象一下）。这个系统预计将会存储百万的评论，但现在假设里边只有有四条数据。每一条有一个 timestamp 、评分 rating 和一个表示是否是匿名的字段 anonymous

{ timestamp: 1, anonymous: false, rating: 3 }
{ timestamp: 2, anonymous: false, rating: 5 }
{ timestamp: 3, anonymous:  true, rating: 1 }
{ timestamp: 4, anonymous: false, rating: 2 }

I want to query for non-anonymous comments with timestamps from 2 to 4, and order them by rating. We’ll build up the query in three stages and examine the best index for each using MongoDB’s explain() .

我想要查询 timestamps 从 2 到 4 的非匿名的评论，并且根据 rating 排序。我们将从三个阶段构造查询，并通过 explain() 解释每一个阶段的最佳索引。

Range Query

We’ll start with a simple range query for comments with timestamps from 2 to 4:

我们先从一个简单的范围过滤查询 timestamps 从 2 到 4 的评论：

> db.comments.find( { timestamp: { $gte: 2, $lte: 4 } } )

There are three, obviously. explain() shows how Mongo found them:

很明显有三条记录， explain() 显示 Mongo 是如何找到这三条的：

> db.comments.find( { timestamp: { $gte: 2, $lte: 4 } } ).explain()
{
    "cursor" : "BasicCursor",
    "n" : 3,
    "nscannedObjects" : 4,
    "nscanned" : 4,
    "scanAndOrder" : false
    // ... snipped output ...
}

Here’s how to read a MongoDB query plan: First look at the cursor type. "BasicCursor" is a warning sign: it means MongoDB had to do a full collection scan. That won’t work once I have millions of comments, so I add an index on timestamp:

我们从头开始看 MongoDB 的查询计划：首先可以看到 cursor 的值， BasicCursor 是一个危险的信号：它表示 MongoDB 得扫描整个 Document 。当有百万条评论时这么做直接就挂了，因此我们将在 timestamps 上加一个索引：

> db.comments.createIndex( { timestamp: 1 } )

The explain() output is now:

现在 explain() 的结果：

> db.comments.find( { timestamp: { $gte: 2, $lte: 4 } } ).explain()
{
    "cursor" : "BtreeCursor timestamp_1",
    "n" : 3,
    "nscannedObjects" : 3,
    "nscanned" : 3,
    "scanAndOrder" : false
}

Now the cursor type is "BtreeCursor" plus the name of the index I made. "nscanned" fell from 4 to 3, because Mongo used an index to go directly to the documents it needed, skipping the one whose timestamp is out of range.

现在 cursor 的值为： BtreeCursor 加上刚刚加上的索引的名字。 nscanned 从 4 降到了 3，因为 Mongo 使用索引跳过那些不在范围内的部分直接找到需要的位置。

For indexed queries, nscanned is the number of index keys in the range that Mongo scanned, and nscannedObjects is the number of documents it looked at to get to the final result. nscannedObjects includes at least all the documents returned, even if Mongo could tell just by looking at the index that the document was definitely a match. Thus, you can see that nscanned >= nscannedObjects >= n always. For simple queries you want the three numbers to be equal. It means you’ve created the ideal index and Mongo is using it.

对于使用了索引之后的查询， nscanned 是 Mongo 扫描的索引键值的数量， nscannedObjects 是得到最后的查询结果而扫描的数据数量。即使 Mongo 可以通过索引知道那些完全匹配的数据， nscannedObjects 也至少包含了所返回的数据条数。因此我们总是能看到 nscanned >= nscannedObjects >= n 。对于简单的查询来说我们是希望中文三个值相等的。现在这表明我们已经创建了理想的索引且 Mongo 使用了。

Equality Plus Range Query

When would nscanned be greater than n? It’s when Mongo had to examine some index keys pointing to documents that don’t match the query. For example, I’ll filter out anonymous comments:

nscanned 在什么时候会大于等于 n 呢？这是当 Mongo 必须审查索引指向的不满足查询条件的数据。比如我要过滤掉匿名的评论：

> db.comments.find(
...     { timestamp: { $gte: 2, $lte: 4 }, anonymous: false }
... ).explain()
{
    "cursor" : "BtreeCursor timestamp_1",
    "n" : 2,
    "nscannedObjects" : 3,
    "nscanned" : 3,
    "scanAndOrder" : false
}

Although n has fallen to 2, nscanned and nscannedObjects are still 3. Mongo scanned the timestamp index from 2 to 4, which includes both the signed comments and the cowardly one, and it couldn’t filter out the latter until it had examined the document itself.

即使最后 n 的结果是 2， nscanned 和 nscannedObjects 的结果仍为 3。 Mongo 通过 timestamp 索引查询从 2 到 4 的数据，其中包含了匿名的和非匿名的评论，那么就只能在审查了这几条数据之后才能将匿名的评论过滤掉。

How do I get my ideal query plan back, where nscanned = nscannedObjects = n? I could try a compound index on timestamp and anonymous:

我如何找回理想的 nscanned = nscannedObjects = n 的查询？我们可以尝试 timestamp 和 anonymous 的复合索引。

> db.comments.createIndex( { timestamp:1, anonymous:1 } )
> db.comments.find(
...     { timestamp: { $gte: 2, $lte: 4 }, anonymous: false }
... ).explain()
{
    "cursor" : "BtreeCursor timestamp_1_anonymous_1",
    "n" : 2,
    "nscannedObjects" : 2,
    "nscanned" : 3,
    "scanAndOrder" : false
}

This is better: nscannedObjects has dropped from 3 to 2. But nscanned is still 3! Mongo had to scan the range of the index from (timestamp 2, anonymous false) to (timestamp 4, anonymous false), including the entry (timestamp 3, anonymous true). When it scanned that middle entry, Mongo saw it pointed to an anonymous comment and skipped it, without inspecting the document itself. Thus the incognito comment is charged against nscanned but not against nscannedObjects, and nscannedObjects is only 2.

这样一来好了一些： nscannedObjects 从 3 降到了 2。但是 nscanned 仍旧为 3 ！ Mongo 必须从索引 (timestamp 2, anonymous false) 扫描到 (timestamp 4, anonymous false)，其中包含了 (timestamp 3, anonymous true)。当扫描到中间的那条索引数据时， Mongo 发现这条索引数据指向了一条匿名的数据所以就跳过这条，而不需要审查这条数据本身。因此这条匿名的数据影响到了 nscanned 却没有影响 nscannedObjects，所以 nscannedObjects 的结果只是 2 。

Can I improve this plan? Can I get nscanned down to 2, also? You probably know this: the order I declared the fields in my compound index was wrong. It shouldn’t be "timestamp, anonymous" but "anonymous, timestamp":

我要如何优化它呢？我能让 nscanned 也降到 2 吗？你可能已经知道了：声明复合索引的两列有问题，不应该是 timestamp, anonymous 而是 anonymous, timestamp ：

> db.comments.createIndex( { anonymous:1, timestamp:1 } )
> db.comments.find(
...     { timestamp: { $gte: 2, $lte: 4 }, anonymous: false }
... ).explain()
{
    "cursor" : "BtreeCursor anonymous_1_timestamp_1",
    "n" : 2,
    "nscannedObjects" : 2,
    "nscanned" : 2,
    "scanAndOrder" : false
}

Order matters in MongoDB compound indexes, as with any database. If I make an index with "anonymous" first, Mongo can jump straight to the section of the index with signed comments, then do a range-scan from timestamp 2 to 4.

就像其他的数据库一样，MongoDB 复合索引中的顺序是有意义的。如果我将 anonymous 在索引中靠前， Mongo 可以直接跳到非匿名的部分做 timestamp 从 2 到 4 的范围过滤。

So I’ve shown the first part of my heuristic: equality tests before range filters!

Let’s consider whether including "anonymous" in the index was worth it. In a system with millions of comments and millions of queries per day, reducing nscanned might seriously improve throughput. Plus, if the anonymous section of the index is rarely used, it can be paged out to disk and make room for hotter sections. On the other hand, a two-field index is larger than a one-field index and takes more RAM, so the win could be outweighed by the costs. Most likely, the compound index is a win if a significant proportion of comments are anonymous, otherwise not.

这里我将给出第一个启示：等值查询要在范围过滤之前！

我们来思考一下在索引中包含 anonymous 是否有价值。对一个有百万条评论且每天有百万次查询的系统来说，减少 nscanned 确确实实能提升吞吐量。同时，如果复合索引中的 anonymous 很少被使用，它将会被放到磁盘里以便将空间留给更热的数据。另一方面，有两列字段的索引会比只有一个字段的索引占用更多的内存，所以成本或许会大于收益。所以如果 anonymous 是评论的重要部分的话复合索引将会是利大于弊，反之则不是。

Digression: How MongoDB Chooses An Index

Let’s not skip an interesting question. In the previous example I first created an index on "timestamp", then on "timestamp, anonymous", and finally on "anonymous, timestamp". Mongo chose the final, superior index for my query. How?

MongoDB’s optimizer chooses an index for a query in two phases. First it looks for a prima facie "optimal index" for the query. Second, if no such index exists it runs an experiment to see which index actually performs best. The optimizer remembers its choice for all similar queries. (Until a thousand documents are modified or an index is added or removed.)

What does the optimizer consider an "optimal index" for a query? The optimal index must include all the query’s filtered fields and sort fields. Additionally, any range-filtered or sort fields in the query must come after equality fields. (If there are multiple optimal indexes, Mongo chooses one arbitrarily.) In my example, the "anonymous, timestamp" index is clearly optimal, so MongoDB chooses it immediately.

This isn’t a terrifically exciting explanation, so I’ll describe how the second phase would work. When the optimizer needs to choose an index and none is obviously optimal, it gathers all the indexes relevant to the query and pits them against each other in a race to see who finishes, or finds 101 documents, first.

Here’s my query again:

这有一个有趣的问题。在上一个例子中我们一开始创建 timestamp 索引，然后再创建 timestamp, anonymous 索引，最后创建 anonymous, timestamp 索引。 Mongo 在查询时选择了最后创建的效果最好的索引。这是如何做到的？

MongoDB 的优化器为查询选择索引有两个步骤。首先它为查询寻找初步的“最优索引”。其次，如果没有这有的索引它会做个试验确定哪一个索引性能最好。优化器会记录选择的结果供之后相似的查询使用。（直到有上千条记录被修改或索引被添加或修改）

优化器是如何确定一个查询的 “最优索引”的？最优索引必须包含所有查询的过滤字段和排序字段。此外，任何范围过滤或排序字段都必须在等值测试字段之后。（如果有多个最优索引， Mongo 随意选择一个）在上边的例子里， anonymous, timestamp 索引确实是最优的，所以 MongoDB 直接选择了它。

这不是一个让人满意的解释，所以我将会解释第二个步骤是如何进行的。当优化器需要选出一个索引且没有一个索引是明显最优的时候，它会收集这条查询相关的索引，让它们彼此竞争看看谁先完成，或者先去找 101 文档。

再来看看之前的查询：

db.comments.find({ timestamp: { $gte: 2, $lte: 4 }, anonymous: false })

All three indexes are relevant, so MongoDB lines them up in an arbitrary order and advances each index one entry in turn:

之前建的三条索引都与之相关，所以 MongoDB 会将他们放到一起一次一条数据依次比较他们：

(I omitted the ratings for brevity; I’m just showing the documents' timestamps and anonymosity.)

All the indexes return

（为了方便阅读去掉了 ratings ，只是显示数据的 timestamps 和 anonymous ）

这些索引返回的结果

{ timestamp: 2, anonymous: false, rating: 5 }

first. On the second pass through the indexes, the left and middle return

一开始，在第二次比较的时候，图里左边和中间的索引返回了：

{ timestamp: 3, anonymous:  true, rating: 1 }

which isn’t a match, and our champion index on the right returns

并不满足查询条件，然后第三个索引返回了：

{ timestamp: 4, anonymous: false, rating: 2 }

which is a match. Now the index on the right is finished before the others, so it’s declared the winner and used until the next race.

In short: if there are several useful indexes, MongoDB chooses the one that gives the lowest nscanned.

Update: Betlista reminded me in the comments that you can do explain({ verbose: true }) to get all the plans Mongo tried. In this example, there are three relevant indexes, but the verbose explain will only show one plan, because one index is an "optimal index."

满足查询条件。这时右边的索引已经在前两条索引之前完成了查询，这就得到了最有的索引并会被一直使用到下一次选择竞赛。

简单来说：如果有多个可以使用的索引， MongoDB 会选择 nscanned 较低的那个。

更新：Betlista 在评论里提醒我可以通过 explain({ verbose: true }) 获得 Mongo 的尝试 plan。在上边的例子中，有三个相关的索引。但是 explain({ verbose: true }) 只会显示一个 plan ，因为有一个索引是 “最优索引”。

Equality, Range Query, And Sort

Now I have the perfect index to find signed comments with timestamps between 2 and 4. The last step is to sort them, top-rated first:

现在我们有了查询 timestamp 从 2 到 4 且非匿名的完美索引。最后一步就是根据 rating 逆序排序：

> db.comments.find(
...     { timestamp: { $gte: 2, $lte: 4 }, anonymous: false }
... ).sort( { rating: -1 } ).explain()
{
    "cursor" : "BtreeCursor anonymous_1_timestamp_1",
    "n" : 2,
    "nscannedObjects" : 2,
    "nscanned" : 2,
    "scanAndOrder" : true
}

This is the same access plan as before, and it’s still good: nscanned = nscannedObjects = n. But now "scanAndOrder" is true. This means MongoDB had to batch up all the results in memory, sort them, and then return them. Infelicities abound. First, it costs RAM and CPU on the server. Also, instead of streaming my results in batches, Mongo just dumps them all onto the network at once, taxing the RAM on my app servers. And finally, Mongo enforces a 32MB limit on data it will sort in memory. We’re only dealing with four comments now, but we’re designing a system to handle millions!

How can I avoid scanAndOrder? I want an index where Mongo can jump to the non-anonymous section, and scan that section in order from top-rated to bottom-rated:

这和上次一样，仍然良好： nscanned = scannedObjects = n 。但 scanAndOrder 字段是 true 。这表示 MongoDB 必须在内存中处理所有查询结果，对它们排序然后返回。不幸的是，这会暂用服务器的内存和 CPU 资源；其次 Mongo 得将数据一次性返回而不像往常那样通过 stream 发给，这将加载加重应用的负担。最后， Mongo 还限制在内存中排序的数据只能小于 32M 。现在这只处理 4 条数据，但这系统的目标是能处理百万条数据！

我们如何能避免 scanAndOrder 呢？我们可以创建一个索引让 Mongo 可以跳到非匿名评论部分，直接按 rating 顺序扫描数据：

> db.comments.createIndex( { anonymous: 1, rating: 1 } )

Will Mongo use this index? No, because it doesn’t win the race to the lowest nscanned. The optimizer does not consider whether the index helps with sorting.

I’ll use a hint to force Mongo’s choice:

Mongo 会使用这条索引吗？不会，因为这条索引的 nscanned 较高而不会被选中。优化器并不考了索引对排序的优化。

我们可以用 hint 强制 Mongo 选择：

> db.comments.find(
...     { timestamp: { $gte: 2, $lte: 4 }, anonymous: false }
... ).sort( { rating: -1 }
... ).hint( { anonymous: 1, rating: 1 } ).explain()
{
    "cursor" : "BtreeCursor anonymous_1_rating_1 reverse",
    "n" : 2,
    "nscannedObjects" : 3,
    "nscanned" : 3,
    "scanAndOrder" : false
}

The argument to hint is the same as createIndex. Now nscanned has risen to 3 but scanAndOrder is false. Mongo walks through the "anonymous, rating" index in reverse, getting comments in the correct order, and then checks each document to see if its timestamp is in range.

hint 的参数和创建索引的参数相同。现在 nscanned 升高到了 3 但 scanAndOrder 的值是 false 。 Mongo 通过 anonymous, rating 索引反向遍历获取评论，并审查每一条数据的 timestamp 是否在所查询范围内。

This is why the optimizer won’t choose this index, but prefers to go with the old "anonymous, timestamp" index which requires an in-memory sort but has a lower nscanned.

So I’ve solved the scanAndOrder problem, at the cost of a higher nscanned. I can’t reduce nscanned, but can I reduce nscannedObjects? I’ll put the timestamp in the index so Mongo doesn’t have to get it from each document:

这就是为什么优化器不选这条索引，而去选择需要在内存内排序却有着更低 nscanned 值的索引的原因。

所以我们用高 nscanned 的代价的方法解决 scanAndOrder 问题。我们优化不了 nscanned ，那能优化 nscannedObjects ？我们将 timestamp 放到索引中使得 Mongo 不必审查数据：

> db.comments.createIndex( { anonymous: 1, rating: 1, timestamp: 1 } )

Again, the optimizer won’t prefer this index so I have to force it:

同样，优化器不会选择这条索引因此我们得强制它使用：

> db.comments.find(
...     { timestamp: { $gte: 2, $lte: 4 }, anonymous: false }
... ).sort( { rating: -1 }
... ).hint( { anonymous: 1, rating: 1, timestamp: 1 } ).explain()
{
    "cursor" : "BtreeCursor anonymous_1_rating_1_timestamp_1 reverse",
    "n" : 2,
    "nscannedObjects" : 2,
    "nscanned" : 3,
    "scanAndOrder" : false,
}

This is as good as it gets. Mongo follows a similar plan as before, moonwalking across the "anonymous, rating, timestamp" index so it finds comments in the right order. But now, nscannedObjects is only 2, because Mongo can tell from the index entry alone that the comment with timestamp 1 isn’t a match.

情况良好。 Mongo 就像之前一样，遍历索引 anonymous, rating, timestamp 按序查询评论。但这次 nscannedObjects 的值只有 2 ，因为 Mongo 可以从索引中直接知道有一条数据的 timestamp 值为 1 不满足查询。

If my range filter on timestamp is selective, adding timestamp to the index is worthwhile; if it’s not selective then the additional size of the index won’t be worth the price.

如果对 timestamp 范围过滤是有选择性的，那么把 timestamp 加到索引里是值得的；反之如果没有选择性那么添加 timestamp 多出的空间将不值得。

Final Method

So here’s my method for creating a compound index for a query combining equality tests, sort fields, and range filters:

Equality Tests

Add all equality-tested fields to the compound index, in any order

Sort Fields (ascending / descending only matters if there are multiple sort fields)

Add sort fields to the index in the same order and direction as your query’s sort

Range Filters

First, add the range filter for the field with the lowest cardinality (fewest distinct values in the collection)

Then the next lowest-cardinality range filter, and so on to the highest-cardinality

You can omit some equality-test fields or range-filter fields if they are not selective, to decrease the index size—a rule of thumb is, if the field doesn’t filter out at least 90% of the possible documents in your collection, it’s probably better to omit it from the index. Remember that if you have several indexes on a collection, you may need to hint Mongo to use the right index.

That’s it! For complex queries on several fields, there’s a heap of possible indexes to consider. If you use this method you’ll narrow your choices radically and go straight to a good index.

这是我为包含等值测试、范围过滤和字段排序的查询创建索引的方法：

等值测试
- 将所有等值测试的字段加到复合索引中，顺序无所谓
字段排序（升序、降序只有在多列排序时才有意义）
- 将需要排序的字段按顺序加入索引，保证和查询的需求一致
范围过滤
- 首先，为基数最少的字段添加索引（数据中去重后的值最少）
- 然后按基数从低到高依次添加字段

可以取去掉那些没有选择性的等值测试或范围过滤字段，以减少索引的空间占用。经验是：如果那个字段不能过滤掉 90% 以上的数据，那么最好还是去掉它。请记住如果在数据库中又多条索引，你可能得提示 Mongo 用正确的那个。

就是这样！对于有几个字段的复杂查询，这有一堆可能的索引要考虑。如果你使用上边的方法，可以从根本上减少你的选择并直接指向较好的索引。

Gory details: the scanAndOrder query plan "anonymous, timestamp" wins over the pre-ordered plan "anonymous, rating," because it gets to the end of my small result set first. But if I had a larger result set, then the pre-ordered plan might win. First, because it returns data in the right order, so it crosses the finish line when it finds 101 documents, while a scanAndOrder query plan isn’t declared finished until it’s found all the results. Second, because a scanAndOrder plan quits the race if it reaches 32MB of data, leaving the pre-ordered plans to finish. I told you these details would be gory.

需要注意的细节： `scanAndOrder 查询索引 anonymous, timestamp 优于有序索引 anonymous, rating 是因为它在我们构造的小数据中最先结束。但如果我们的数据很多，有序索引有可能会更优。首选，这是因为它返回的数据是正确的顺序，当它完成查询时就直接结束了；而 scanAndOrder 查询得查找完所有结果才能宣告完成。其次，scanAndOrder 查询的数据超过 32M 时就会推出竞赛，那样就只剩下有序索引了。

PS：

这是我第一次翻译文章，有翻译得不好的地方请直接打脸。。。

我翻译了一半的时候发现有人翻译过，如果我这里有什么看不懂的可以去这里看： 10gen工程师谈MongoDB组合索引的优化