- Indexes >
- 索引交集
索引交集¶
2.6 新版功能.
MongoDB can use the intersection of multiple indexes to fulfill queries. [1] In general, each index intersection involves two indexes; however, MongoDB can employ multiple/nested index intersections to resolve a query.
To illustrate index intersection, consider a collection orders that has the following indexes:
{ qty: 1 }
{ item: 1 }
MongoDB can use the intersection of the two indexes to support the following query:
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.
| [1] | In previous versions, MongoDB could use only a single index to fulfill most queries. The exception to this is queries with $or clauses, which could use a single index for each $or clause. |
索引前缀交集¶
With index intersection, MongoDB can use an intersection of either the entire index or the index prefix. An index prefix is a subset of a compound index, consisting of one or more keys starting from the beginning of the index.
假设集合 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:
db.orders.find( { qty: { $gt: 10 } , status: "A" } )
索引交集和复合索引¶
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:
{ status: 1, ord_date: -1 }
那么复合索引可以支持如下查询:
db.orders.find( { status: { $in: ["A", "P" ] } } )
db.orders.find(
{
ord_date: { $gt: new Date("2014-02-01") },
status: {$in:[ "P", "A" ] }
}
)
但是, 下面的这两条查询就不会被支持了:
db.orders.find( { ord_date: { $gt: new Date("2014-02-01") } } )
db.orders.find( { } ).sort( { ord_date: 1 } )
相反, 如果一个集合有两条不同的索引如下:
{ 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.
索引交集和排序¶
Index intersection does not apply when the sort() operation requires an index completely separate from the query predicate.
例如, 集合 orders 有如下索引:
{ qty: 1 }
{ status: 1, ord_date: -1 }
{ status: 1 }
{ ord_date: -1 }
MongoDB cannot use index intersection for the following query with sort:
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.
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.
db.orders.find( { qty: { $gt: 10 } , status: "A" } ).sort( { ord_date: -1 } )