mongodb - 为什么 MongoDB 不使用索引交集？

Question

我创建了一个包含单个集合的数据库，该集合存储只有 2 个字段(和一个 id)的文档:

public class Hamster
{
    public ObjectId Id;
    public string Name;
    public int Age;
}

我还为每个字段创建了一个索引。

当我对两个字段执行查询过滤时，我希望它使用 Index Intersection 组合两个索引以减少集合扫描并提高性能。这是从来没有的情况。我还没有设法诱导索引交集。

那么，是什么阻止了 MongoDB 应用索引交集？

Answer 1

当您使用 explain(true) 时您可以看到优化器考虑使用索引交集并选择不使用:

"cursor" : "BtreeCursor Age", // Chosen plan.
...
"allPlans" : [
   {
       "cursor" : "BtreeCursor Age",
       ...
   },
   {
       "cursor" : "BtreeCursor Name",
       ...
   },
   {
       "cursor" : "Complex Plan", // Index intersection.
       ...
   }
]

如果有足够的复合索引，

MongoDB 永远不会选择交集。可以在 Jira ticket for Index Intersection 上找到其他限制。 :

The query optimizer may select index intersection plans when the following conditions hold:
1. Most of the documents in the relevant collection are disk-resident. The advantage of index intersection is that it can avoid fetching complete documents when the size of the intersection is small. If the documents are already in memory, there is nothing to gain by avoiding fetches.
2. The query predicates are single point intervals, rather than range predicates or a set of intervals. Queries over single point intervals return documents sorted by disk location, which allows the optimizer to select plans that compute the intersection in a non-blocking fashion. This is generally faster than the alternative mode of computing the intersection, which is to build a hash table with the results from one index, and then probe it with the results from the second index.
3. Neither of the indices to be intersected are highly selective. If one of the indices is selective then the optimizer will choose a plan which simply scans this selective index.
4. The size of the intersection is small relative to the number of index keys scanned by either single-index solution. In this case the query executor can look at a smaller set of documents using index intersection, potentially allowing us to reap the benefits of fewer fetches from disk.

MongoDB对交集有很多限制，不太可能被实际使用。