mongodb compund索引与索引相交 [英] mongodb compund index vs. index intersect

问题描述

集合中的各个分片中大约有30亿个文档. 假设我将使用索引无法完全满足的查询. 而且我没有使用按键进行排序.

There will be about 3 billion docs in the collection across shards. Assuming I would be using queries that is not fulfilled entirely by the index(es). And I am not using the keys for sorting.

具有如下索引(与查询匹配)是否有意义:

Does it make sense to have indexes as follows (which match the queries):

{"sid":1, "cid":1, "mid":1}
{"cid":1, "hid":1, "mid":1}
{"mid":1, "hid":1, "sid":1}
{"hid":1, "sid":1, "cid":1, "mid":1}
{"mid":1, "cid":1}
{"mid":1, "sid":1}

或者:

{sid:1}
{cid:1}
{hid:1}
{mid:1} 

推荐答案

这是一个有效的问题.您没有使用这些键进行排序，因此我假设您的查询不包含任何排序，因为您必须知道(说了什么)索引交叉部分不能跨排序工作.

This is a valid question. You are not using these keys for sorting so I will assume your queries contain no sort, since you must be aware (to have said what you did) that index inter-sectioning cannot work across sorts.

我会说:最好具有复合索引.

即使要维护的索引更多，它也可以为您的查询提供直接的答案，而不会产生交叉部分所需的计算问题.我的意思是，您正在处理超过3.5b条记录，但效果不佳.

Even though it is more indexes to maintain it makes for straight answers to your queries without the problematic calculations required for inter-sectioning. I mean you are doing this over 3.5b records, that won't end well.

复合索引方法将维护的重担放在了插入/更新方面，这比在查询方面要好得多.即使索引在任何给定的时间点仅部分满足查询.

The compound index approach puts the weight of maintenance on the insertion/updating side which is much better than on the query side. Even if the indexes only partially fulfill the queries at any given point in time.

MongoDB的交集似乎更加先进:

It seems that MongoDB's intersectioning has got more advanced:

通常，每个索引交集涉及两个索引；但是，MongoDB可以使用多个/嵌套索引交集来解决查询.

In general, each index intersection involves two indexes; however, MongoDB can employ multiple/nested index intersections to resolve a query.

http://docs.mongodb.org/manual/core/index-intersection/

因此，即使它可以使用两个以上的索引，使用3个交集仍然会导致嵌套交集的出现，这听起来并不令人愉快.

So even though it can use more than two indexes the usage of 3 intersections would still cause a nested intersection to occur which doesn't sound pleasant.

前一段时间，我确实回答了类似的问题，据10gen所说，交叉路口本质上是不得已而为之，而不是所希望的.

I did answer a similar question some time ago whereby it was said by 10gen that inter-sectioning is essentially a last resort, not something that is desirable.

这篇关于mongodb compund索引与索引相交的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！