Index Intersection索引交点

On this page本页内容

Important重要

This page documents cases where the query optimizer may be able to use index intersection.本页记录了查询优化器可能能够使用索引交集的情况。

In practice, the query optimizer rarely selects plans that use index intersection.实际上，查询优化器很少选择使用索引交集的计划。

Hash-based index intersection is disabled by default and sort-based index intersection is disfavored in plan selection. 默认情况下禁用基于散列的索引交叉，在计划选择中不支持基于排序的索引交叉。The optimizer behaves in this fashion in order to prevent bad plan selection.优化器以这种方式运行，以防止错误的计划选择。

Schema designs should not rely on index intersection. 架构设计不应依赖于索引交集。Instead, compound indexes should be used.相反，应使用复合索引。

Future improvements to the query optimizer may allow the system to better identify cases where an index intersection plan would be beneficial.查询优化器的未来改进可能允许系统更好地识别索引交叉点计划有益的情况。

MongoDB can use the intersection of multiple indexes to fulfill queries. MongoDB可以使用多个索引的交集来完成查询。In general, each index intersection involves two indexes; however, MongoDB can employ multiple/nested index intersections to resolve a query.通常，每个索引交叉点涉及两个索引；但是，MongoDB可以使用多个/嵌套索引交叉点来解析查询。

To illustrate index intersection, consider a collection orders that has the following indexes:为了说明索引交集，请考虑具有以下索引的集合orders：

{ qty: 1 }
{ item: 1 }

MongoDB can use the intersection of the two indexes to support the following query:MongoDB可以使用两个索引的交集来支持以下查询：

db.orders.find( { item: "abc123", qty: { $gt: 15 } } )

To determine if MongoDB used index intersection, run explain(); the results of explain() will include either an AND_SORTED stage or an AND_HASH stage.要确定MongoDB是否使用了索引交集，请运行explain()；explain()的结果将包括AND_SORTED阶段或AND_HASH阶段。

Index Prefix Intersection索引前缀交点

With index intersection, MongoDB can use an intersection of either the entire index or the index prefix. 使用索引交集，MongoDB可以使用整个索引或索引前缀的交集。An index prefix is a subset of a compound index, consisting of one or more keys starting from the beginning of the index.索引前缀是复合索引的子集，由从索引开头开始的一个或多个键组成。

Consider a collection orders with the following indexes:考虑具有以下索引的集合orders：

{ qty: 1 }
{ status: 1, ord_date: -1 }

To fulfill the following query which specifies a condition on both the qty field and the status field, MongoDB can use the intersection of the two indexes:为了实现以下查询，指定qty字段和status字段的条件，MongoDB可以使用两个索引的交集：

db.orders.find( { qty: { $gt: 10 } , status: "A" } )

Index Intersection and Compound Indexes索引交集和复合索引

Index intersection does not eliminate the need for creating compound indexes. 索引交集并不消除创建复合索引的需要。However, because both the list order (i.e. the order in which the keys are listed in the index) and the sort order (i.e. ascending or descending), matter in compound indexes, a compound index may not support a query condition that does not include the index prefix keys or that specifies a different sort order.但是，由于列表顺序（即索引中键的排列顺序）和排序顺序（即升序或降序）在复合索引中都很重要，复合索引可能不支持不包含索引前缀键或指定不同排序顺序的查询条件。

For example, if a collection orders has the following compound index, with the status field listed before the ord_date field:例如，如果集合orders具有以下复合索引，status字段列在ord_date字段之前：

{ status: 1, ord_date: -1 }

The compound index can support the following queries:复合索引可以支持以下查询：

db.orders.find( { status: { $in: ["A", "P" ] } } )
db.orders.find(
   {
     ord_date: { $gt: new Date("2014-02-01") },
     status: {$in:[ "P", "A" ] }
   }
)

But not the following two queries:但不包括以下两个查询：

db.orders.find( { ord_date: { $gt: new Date("2014-02-01") } } )
db.orders.find( { } ).sort( { ord_date: 1 } )

However, if the collection has two separate indexes:但是，如果集合有两个单独的索引：

{ status: 1 }
{ ord_date: -1 }

The two indexes can, either individually or through index intersection, support all four aforementioned queries.这两个索引可以单独或通过索引交集支持上述所有四个查询。

The choice between creating compound indexes that support your queries or relying on index intersection depends on the specifics of your system.创建支持查询的复合索引还是依赖索引交集取决于系统的具体情况。

Tip提示
See also: 参阅：

Index Intersection and Sort索引、交集和排序

Index intersection does not apply when the sort() operation requires an index completely separate from the query predicate.当sort()操作需要与查询谓词完全分离的索引时，索引交集不适用。

For example, the orders collection has the following indexes:例如，orders集合具有以下索引：

{ qty: 1 }
{ status: 1, ord_date: -1 }
{ status: 1 }
{ ord_date: -1 }

MongoDB cannot use index intersection for the following query with sort:MongoDB无法对以下具有排序的查询使用索引交集：

db.orders.find( { qty: { $gt: 10 } } ).sort( { status: 1 } )

That is, MongoDB does not use the { qty: 1 } index for the query, and the separate { status: 1 } or the { status: 1, ord_date: -1 } index for the sort.也就是说，MongoDB不使用{ qty: 1 }索引进行查询，而使用单独的{ status: 1 }或{ status: 1, ord_date: -1 }索引进行排序。

However, MongoDB can use index intersection for the following query with sort since the index { status: 1, ord_date: -1 } can fulfill part of the query predicate.但是，MongoDB可以使用索引交集进行以下带有排序的查询，因为索引{ status: 1, ord_date: -1 }可以满足部分查询谓词。

db.orders.find( { qty: { $gt: 10 } , status: "A" } ).sort( { ord_date: -1 } )
←  Rolling Index Builds on Sharded ClustersManage Indexes →