复合索引的顺序在MongoDB中的表现如何？ [英] How does the order of compound indexes matter in MongoDB performance-wise?

问题描述

我们需要以与查询参数相同的顺序创建复合索引。这个订单在性能方面是否重要？

想象一下，我们拥有地球上所有人类的集合，其指数 sex （99.9％的时间是男性或女性，但字符串（不是二进制））和名称上的索引。

如果我们希望能够选择具有某个名称性别的所有人c $ c>，例如所有名为John的男性，最好是一个带有 sex 的复合索引或 name 先？为什么（不是）？

解决方案

Redsandro，

你必须考虑

创建复合索引时， 1索引将包含多个字段。因此，如果我们通过 {sex：1，name：1} 索引集合，索引将大致如下：

  [男性，Rick]  - > 0x0c965148 
 [男性，约翰]  - > 0x0c965149 
 [男性，肖恩]  - > 0x0cdf7859 
 [男性，兄弟]  - >> 0x0cdf7859 
 ... 
 [女性，凯特]  - > 0x0c965134 
 [女性，凯蒂]  - > 0x0c965126 
 [女性，Naji]  - > 0x0c965183 
 [女性，琼]  - > 0x0c965191 
 [女性，Sara]  - > 0x0c965103 
  

如果我们通过 {name索引集合：1，性：1} ，索引看起来大致如下：

  [John，男性]  - > 0x0c965148 
 [John，female]  - > 0x0c965149 
 [John，male]  - > 0x0cdf7859 
 [Rick，male]  - > 0x0cdf7859 
 ... 
 [Kate，female]  - > 0x0c965134 
 [凯蒂，女性]  - > 0x0c965126 
 [Naji，female]  - > 0x0c965183 
 [Joan，female]  - > 0x0c965191 
 [Sara，女性]  - > 0x0c965103 
  

拥有 {name：1} as 前缀将更好地使用复合索引。关于这个主题还有更多内容可以阅读，我希望这可以提供一些清晰度。

We need to create a compound index in the same order as the parameters are being queried. Does this order matter performance-wise at all?

Imagine we have a collection of all humans on earth with an index on sex (99.9% of the time "male" or "female", but string nontheless (not binary)) and an index on name.

If we would want to be able to select all people of a certain sex with a certain name, e.g. all "male"s named "John", is it better to have a compound index with sex first or name first? Why (not)?

解决方案

Redsandro,

You must consider Index Cardinality and Selectivity.

---

1. Index Cardinality

The index cardinality refers to how many possible values there are for a field. The field sex only has two possible values. It has a very low cardinality. Other fields such as names, usernames, phone numbers, emails, etc. will have a more unique value for every document in the collection, which is considered high cardinality.

• Greater Cardinality

  The greater the cardinality of a field the more helpful an index will be, because indexes narrow the search space, making it a much smaller set.

  If you have an index on sex and you are looking for men named John. You would only narrow down the result space by approximately %50 if you indexed by sex first. Conversely if you indexed by name, you would immediately narrow down the result set to a minute fraction of users named John, then you would refer to those documents to check the gender.

• Rule of Thumb

  Try to create indexes on high-cardinality keys or put high-cardinality keys first in the compound index. You can read more about it in the section on compound indexes in the book:

  MongoDB The Definitive Guide

---

2. Selectivity

Also, you want to use indexes selectively and write queries that limit the number of possible documents with the indexed field. To keep it simple, consider the following collection. If your index is {name:1}, If you run the query { name: "John", sex: "male"}. You will have to scan 1 document. Because you allowed MongoDB to be selective.

{_id:ObjectId(),name:"John",sex:"male"}
{_id:ObjectId(),name:"Rich",sex:"male"}
{_id:ObjectId(),name:"Mose",sex:"male"}
{_id:ObjectId(),name:"Sami",sex:"male"}
{_id:ObjectId(),name:"Cari",sex:"female"}
{_id:ObjectId(),name:"Mary",sex:"female"}

Consider the following collection. If your index is {sex:1}, If you run the query {sex: "male", name: "John"}. You will have to scan 4 documents.

{_id:ObjectId(),name:"John",sex:"male"}
{_id:ObjectId(),name:"Rich",sex:"male"}
{_id:ObjectId(),name:"Mose",sex:"male"}
{_id:ObjectId(),name:"Sami",sex:"male"}
{_id:ObjectId(),name:"Cari",sex:"female"}
{_id:ObjectId(),name:"Mary",sex:"female"}

Imagine the possible differences on a larger data set.

---

A little explanation of Compound Indexes

It's easy to make the wrong assumption about Compound Indexes. According to the MongoDB Indexes Guide.

MongoDB supports compound indexes, where a single index structure holds references to multiple fields within a collection’s documents. The following diagram illustrates an example of a compound index on two fields:

When you create a compound index, 1 Index will hold multiple fields. So if we index a collection by {"sex" : 1, "name" : 1}, the index will look roughly like:

["male","Rick"] -> 0x0c965148
["male","John"] -> 0x0c965149
["male","Sean"] -> 0x0cdf7859
["male","Bro"] ->> 0x0cdf7859
...
["female","Kate"] -> 0x0c965134
["female","Katy"] -> 0x0c965126
["female","Naji"] -> 0x0c965183
["female","Joan"] -> 0x0c965191
["female","Sara"] -> 0x0c965103

If we index a collection by {"name" : 1, "sex" : 1}, the index will look roughly like:

["John","male"] -> 0x0c965148
["John","female"] -> 0x0c965149
["John","male"] -> 0x0cdf7859
["Rick","male"] -> 0x0cdf7859
...
["Kate","female"] -> 0x0c965134
["Katy","female"] -> 0x0c965126
["Naji","female"] -> 0x0c965183
["Joan","female"] -> 0x0c965191
["Sara","female"] -> 0x0c965103

Having {name:1} as the Prefix will serve you much better in using compound indexes. There is much more that can be read on the topic, I hope this can offer some clarity.

这篇关于复合索引的顺序在MongoDB中的表现如何？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！