On this page本页内容
Since indexes contain ordered records, MongoDB can obtain the results of a sort from an index with which includes the sort fields. 由于索引包含有序记录,MongoDB可以从包含排序字段的索引中获取排序结果。MongoDB may use multiple indexes to support a sort operation if the sort uses the same indexes as the query predicate.如果排序使用与查询谓词相同的索引,MongoDB可以使用多个索引来支持排序操作。
If MongoDB cannot use an index or indexes to obtain the sort order, MongoDB must perform a blocking sort operation on the data. 如果MongoDB无法使用一个或多个索引来获取排序顺序,MongoDB必须对数据执行阻塞排序操作。A blocking sort indicates that MongoDB must consume and process all input documents to the sort before returning results. 阻塞排序表示MongoDB必须在返回结果之前使用并处理所有输入文档。Blocking sorts do not block concurrent operations on the collection or database.阻止排序不会阻止集合或数据库上的并发操作。
If MongoDB requires using more than 100 megabytes of system memory for the blocking sort operation, MongoDB returns an error unless the query specifies 如果MongoDB需要使用超过100 MB的系统内存来执行阻塞排序操作,MongoDB将返回一个错误,除非查询指定cursor.allowDiskUse()
(New in MongoDB 4.4). cursor.allowDiskUse()
(MongoDB 4.4中新增)。allowDiskUse()
allows MongoDB to use temporary files on disk to store data exceeding the 100 megabyte system memory limit while processing a blocking sort operation.允许MongoDB在处理阻塞排序操作时,使用磁盘上的临时文件存储超过100 MB系统内存限制的数据。
Sort operations that use an index often have better performance than blocking sorts. 使用索引的排序操作通常比阻塞排序具有更好的性能。For more information on creating indexes to support sort operations, see Use Indexes to Sort Query Results.有关创建索引以支持排序操作的更多信息,请参阅使用索引对查询结果进行排序。
Note
As a result of changes to sorting behavior on array fields in MongoDB 3.6, when sorting on an array indexed with a multikey index the query plan includes a blocking SORT stage. 由于MongoDB 3.6中对数组字段排序行为的更改,当对使用多键索引索引的数组进行排序时,查询计划包括阻塞排序阶段。The new sorting behavior may negatively impact performance.新的排序行为可能会对性能产生负面影响。
In a blocking SORT, all input must be consumed by the sort step before it can produce output. 在分块排序中,排序步骤必须先消耗所有输入,然后才能生成输出。In a non-blocking, or indexed sort, the sort step scans the index to produce results in the requested order.在非阻塞排序或索引排序中,排序步骤扫描索引,以按请求的顺序生成结果。
If an ascending or a descending index is on a single field, the sort operation on the field can be in either direction.如果单个字段上有升序或降序索引,则该字段上的排序操作可以是任意方向。
For example, create an ascending index on the field 例如,在集合a
for a collection records
:records
的字段a
上创建升序索引:
This index can support an ascending sort on 此索引可以支持在a
:a
上进行升序排序:
The index can also support the following descending sort on 通过按相反顺序遍历索引,该索引还可以支持在a
by traversing the index in reverse order:a
上降序排序:
Create a compound index to support sorting on multiple fields.创建复合索引以支持对多个字段进行排序。
You can specify a sort on all the keys of the index or on a subset; however, the sort keys must be listed in the same order as they appear in the index. 可以对索引的所有键或子集指定排序;但是,排序键必须按照它们在索引中出现的顺序列出。For example, an index key pattern 例如,索引键模式{ a: 1, b: 1 }
can support a sort on { a: 1, b: 1 }
but not on { b: 1, a: 1 }
.{a:1, b:1}
可以支持{a:1, b:1}
上的排序,但不支持{b:1, a:1}
上的排序。
For a query to use a compound index for a sort, the specified sort direction for all keys in the 对于使用复合索引进行排序的查询,cursor.sort()
document must match the index key pattern or match the inverse of the index key pattern. cursor.sort()
文档中所有键的指定排序方向必须与索引键模式匹配或与索引键模式相反。For example, an index key pattern 例如,索引键模式{ a: 1, b: -1 }
can support a sort on { a: 1, b: -1 }
and { a: -1, b: 1 }
but not on { a: -1, b: -1 }
or {a: 1, b: 1}
.{a:1,b:-1}
可以支持对{a:1,b:-1}
和{a:-1,b:1}
进行排序,但不能对{a:-1, b:-1}
或{a:1, b:1}
进行排序。
If the sort keys correspond to the index keys or an index prefix, MongoDB can use the index to sort the query results. 如果排序键对应于索引键或索引前缀,MongoDB可以使用索引对查询结果进行排序。A prefix of a compound index is a subset that consists of one or more keys at the start of the index key pattern.复合索引的前缀是由索引键模式开头的一个或多个键组成的子集。
For example, create a compound index on the 例如,在data
collection:data
集合上创建复合索引:
Then, the following are prefixes for that index:然后,以下是该索引的前缀:
The following query and sort operations use the index prefixes to sort the results. 以下查询和排序操作使用索引前缀对结果进行排序。These operations do not need to sort the result set in memory.这些操作不需要对内存中的结果集进行排序。
Index Prefix | |
---|---|
db.data.find().sort( { a: 1 } ) |
{ a: 1 } |
db.data.find().sort( { a: -1 } ) |
{ a: 1 } |
db.data.find().sort( { a: 1, b: 1 } ) |
{ a: 1, b: 1 } |
db.data.find().sort( { a: -1, b: -1 } ) |
{ a: 1, b: 1 } |
db.data.find().sort( { a: 1, b: 1, c: 1 } ) |
{ a: 1, b: 1, c: 1 } |
db.data.find( { a: { $gt: 4 } } ).sort( { a: 1, b: 1 } ) |
{ a: 1, b: 1 } |
Consider the following example in which the prefix keys of the index appear in both the query predicate and the sort:请考虑下面的示例,其中索引的前缀键出现在查询谓词和排序中:
In such cases, MongoDB can use the index to retrieve the documents in order specified by the sort. 在这种情况下,MongoDB可以使用索引按排序指定的顺序检索文档。As the example shows, the index prefix in the query predicate can be different from the prefix in the sort.如示例所示,查询谓词中的索引前缀可能与排序中的前缀不同。
An index can support sort operations on a non-prefix subset of the index key pattern. 索引可以支持对索引键模式的非前缀子集进行排序操作。To do so, the query must include equality conditions on all the prefix keys that precede the sort keys.为此,查询必须在排序键之前的所有前缀键上包含相等条件。
For example, the collection 例如,集合data
has the following index:data
具有以下索引:
The following operations can use the index to get the sort order:以下操作可以使用索引获取排序顺序:
db.data.find( { a: 5 } ).sort( { b: 1, c: 1 } ) |
{ a: 1 , b: 1, c: 1 } |
db.data.find( { b: 3, a: 4 } ).sort( { c: 1 } ) |
{ a: 1, b: 1, c: 1 } |
db.data.find( { a: 5, b: { $lt: 3} } ).sort( { b: 1 } ) |
{ a: 1, b: 1 } |
As the last operation shows, only the index fields preceding the sort subset must have the equality conditions in the query document; the other index fields may specify other conditions.正如上一个操作所示,在查询文档中,只有排序子集前面的索引字段必须具有相等条件;其他索引字段可以指定其他条件。
If the query does not specify an equality condition on an index prefix that precedes or overlaps with the sort specification, the operation will not efficiently use the index. 如果查询没有在排序规范之前或与排序规范重叠的索引前缀上指定相等条件,则操作将无法有效地使用索引。For example, the following operations specify a sort document of 例如,以下操作指定了{ c: 1 }
, but the query documents do not contain equality matches on the preceding index fields a
and b
:{c:1}
的排序文档,但查询文档在前面的索引字段a
和b
上不包含相等匹配项:
These operations will not efficiently use the index 这些操作不会有效地使用索引{ a: 1, b: 1, c: 1, d: 1 }
and may not even use the index to retrieve the documents.{ a: 1, b: 1, c: 1, d: 1 }
,甚至可能不会使用索引来检索文档。
To use an index for string comparisons, an operation must also specify the same collation. 要使用索引进行字符串比较,操作还必须指定相同的排序规则。That is, an index with a collation cannot support an operation that performs string comparisons on the indexed fields if the operation specifies a different collation.也就是说,如果操作指定了不同的排序规则,则具有排序规则的索引不能支持对索引字段执行字符串比较的操作。
For example, the collection 例如,集合myColl
has an index on a string field category
with the collation locale "fr"
.myColl
在排序规则区域设置为"fr"
的字符串字段category
上有一个索引。
The following query operation, which specifies the same collation as the index, can use the index:以下查询操作指定了与索引相同的排序规则,可以使用索引:
However, the following query operation, which by default uses the “simple” binary collator, cannot use the index:但是,以下查询操作(默认情况下使用“简单”二进制排序器)不能使用索引:
For a compound index where the index prefix keys are not strings, arrays, and embedded documents, an operation that specifies a different collation can still use the index to support comparisons on the index prefix keys.对于索引前缀键不是字符串、数组和嵌入文档的复合索引,指定不同排序规则的操作仍然可以使用索引来支持索引前缀键的比较。
For example, the collection 例如,集合myColl
has a compound index on the numeric fields score
and price
and the string field category
; the index is created with the collation locale "fr"
for string comparisons:myColl
在数字字段score
和price
以及字符串字段类别上有一个复合索引;索引是使用排序规则区域设置"fr"
创建的,用于字符串比较:
The following operations, which use 以下使用"simple"
binary collation for string comparisons, can use the index:"simple"
二进制排序规则进行字符串比较的操作可以使用索引:
The following operation, which uses 以下操作使用"simple"
binary collation for string comparisons on the indexed category
field, can use the index to fulfill only the score: 5
portion of the query:"simple"
二进制排序规则对索引类别字段进行字符串比较,可以使用索引只完成查询的score: 5
部分: