Database Manual / Indexes / Strategies

Use Indexes to Sort Query Results使用索引对查询结果进行排序

Since indexes contain ordered records, MongoDB can obtain the results of a sort from an index that includes the sort fields. MongoDB may use multiple indexes to support a sort operation if the sort uses the same indexes as the query predicate.由于索引包含有序记录，MongoDB可以从包含排序字段的索引中获取排序结果。如果排序使用与查询谓词相同的索引，MongoDB可能会使用多个索引来支持排序操作。

If MongoDB cannot use an index or indexes to obtain the sort order, MongoDB must perform an in-memory sort operation on the data.如果MongoDB无法使用一个或多个索引来获取排序顺序，则MongoDB必须对数据执行内存中的排序操作。

Starting in MongoDB 6.0, if the server requires more than 100 megabytes of memory for a pipeline execution stage, MongoDB automatically writes temporary files to disk unless that query specifies { allowDiskUse: false }.从MongoDB 6.0开始，如果服务器在管道执行阶段需要超过100兆字节的内存，MongoDB会自动将临时文件写入磁盘，除非该查询指定{ allowDiskUse: false }。

Sort operations that use an index often have better performance than in-memory sorts.使用索引的排序操作通常比内存中的排序具有更好的性能。

Note

When you sort based on an array field that is indexed with a multikey index, the query plan includes an in-memory sort stage unless both of the following are true:当您基于用多键索引索引的数组字段进行排序时，查询计划包括内存中的排序阶段，除非以下两个条件都为真：

  • The index boundaries for all sort fields are [MinKey, MaxKey].
  • No boundaries for any multikey-indexed field have the same path prefix as the sort pattern.任何多键索引字段的边界都不具有与排序模式相同的路径前缀。

Sort with a Single Field Index使用单个字段索引排序

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:例如，在字段a上为集合records创建升序索引：

db.records.createIndex( { a: 1 } )

This index can support an ascending sort on a:此索引可以支持对a进行升序排序：

db.records.find().sort( { a: 1 } )

The index can also support the following descending sort on a by traversing the index in reverse order:该索引还可以通过按相反顺序遍历索引来支持对a降序排序：

db.records.find().sort( { a: -1 } )

Sort on Multiple Fields在多个字段上排序

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}进行排序。

Sort and Index Prefix排序和索引前缀

If the sort keys correspond to the index keys or an index prefix, MongoDB can use the index to sort the query results. A prefix of a compound index is a subset that consists of one or more keys at the start of the index key pattern.如果排序键对应于索引键或索引前缀，MongoDB可以使用索引对查询结果进行排序。复合索引的前缀是由索引键模式开头的一个或多个键组成的子集。

For example, create a compound index on the data collection:例如，在data集合上创建一个复合索引：

db.data.createIndex( { a:1, b: 1, c: 1, d: 1 } )

Then, the following are prefixes for that index:然后，以下是该索引的前缀：

{ a: 1 }
{ a: 1, b: 1 }
{ a: 1, b: 1, c: 1 }

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.以下查询和排序操作使用索引前缀对结果进行排序。这些操作不需要对内存中的结果集进行排序。

Example示例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:考虑以下示例，其中索引的前缀键同时出现在查询谓词和排序中：

db.data.find( { a: { $gt: 4 } } ).sort( { a: 1, b: 1 } )

In such cases, MongoDB can use the index to retrieve the documents in order specified by the sort. As the example shows, the index prefix in the query predicate can be different from the prefix in the sort.在这种情况下，MongoDB可以使用索引按排序指定的顺序检索文档。如示例所示，查询谓词中的索引前缀可以与排序中的前缀不同。

Sort and Non-prefix Subset of an Index索引的排序和无前缀子集

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数据具有以下索引：

{ a: 1, b: 1, c: 1, d: 1 }

The following operations can use the index to get the sort order:以下操作可以使用索引来获取排序顺序：

Example示例Index Prefix索引前缀
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上不包含相等匹配项：

db.data.find( { a: { $gt: 2 } } ).sort( { c: 1 } )
db.data.find( { c: 5 } ).sort( { c: 1 } )

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 }，甚至可能无法使用索引检索文档。

Index Sort Order索引排序顺序

A collection of indexed documents may have multiple data types in the key field. When an index has a key with multiple data types, the index is sorted according to the BSON type sort order.索引文档的集合在键段中可能有多种数据类型。当一个索引具有多个数据类型的键时，该索引将根据BSON类型排序顺序进行排序。

See the index sorting example.请参阅索引排序示例。

Index Use and Collation索引使用和排序

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.要使用索引进行字符串比较，操作还必须指定相同的排序规则。也就是说，如果操作指定了不同的排序规则，则具有排序规则的索引无法支持对索引字段执行字符串比较的操作。

Warning

Because indexes that are configured with collation use ICU collation keys to achieve sort order, collation-aware index keys may be larger than index keys for indexes without collation.因为配置了排序规则的索引使用ICU排序键来实现排序顺序，所以对于没有排序规则的指数，具有排序规则意识的索引键可能比索引键大。

A restaurants collection has the following documents:restaurants集合有以下文档：

db.restaurants.insertMany( [
   { _id: 1, category: "café", status: "Open" },
   { _id: 2, category: "cafe", status: "open" },
   { _id: 3, category: "cafE", status: "open" }
] )

The restaurants collection has an index on a string field category with the collation locale "fr".restaurants集合在排序规则为"fr"的字符串category类别上有一个索引。

db.restaurants.createIndex( { category: 1 }, { collation: { locale: "fr" } } )

The following query, which specifies the same collation as the index, can use the index:以下查询指定了与索引相同的排序规则，可以使用索引：

db.restaurants.find( { category: "cafe" } ).collation( { locale: "fr" } )

However, the following query operation, which by default uses the "simple" binary collator, cannot use the index:但是，以下查询操作（默认情况下使用“简单”二进制排序器）不能使用索引：

db.restaurants.find( { category: "cafe" } )

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 restaurants 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:例如，集合餐厅在数字字段score 和price以及字符串字段category上有一个复合索引；索引是使用排序规则区域设置"fr"创建的，用于字符串比较：

db.restaurants.createIndex(
   { score: 1, price: 1, category: 1 },
   { collation: { locale: "fr" } } )

The following operations, which use "simple" binary collation for string comparisons, can use the index:以下使用"simple"二进制排序规则进行字符串比较的操作可以使用索引：

db.restaurants.find( { score: 5 } ).sort( { price: 1 } )
db.restaurants.find( { score: 5, price: { $gt: Decimal128( "10" ) } } ).sort( { price: 1 } )

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"二进制排序规则对索引category字段进行字符串比较，可以使用索引仅完成查询的score: 5部分：

db.restaurants.find( { score: 5, category: "cafe" } )

To confirm whether a query used an index, run the query with the explain() option.要确认查询是否使用了索引，请使用explain()选项运行查询。

Important

Matches against document keys, including embedded document keys, use simple binary comparison. This means that a query for a key like "type.café" will not match the key "type.cafe", regardless of the value you set for the strength parameter.与文档键（包括嵌入式文档键）的匹配使用简单的二进制比较。这意味着，无论您为强度参数设置了什么值，对类似"type.café"的键的查询都不会与键"type.cafe"匹配。

Examples示例

The following example demonstrates sorting when index keys have the same or different types.以下示例演示了索引键具有相同或不同类型时的排序。

Create the keyTypes collection:创建keyTypes集合：

db.keyTypes.insertMany( [
  { seqNum: 1, seqType: null, type: "null" },
  { seqNum: 29, seqType: null, type: "null" },
  { seqNum: 2, seqType: Int32("10"), type: "Int32"  },
  { seqNum: 28, seqType: Int32("10"), type: "Int32"  },
  { seqNum: 3, seqType: Long("10"), type: "Long" },
  { seqNum: 27, seqType: Long("10"), type: "Long" },
  { seqNum: 4, seqType: Decimal128("10"), type: "Decimal128" },
  { seqNum: 26, seqType: Decimal128("10"), type: "Decimal128" },
  { seqNum: 5, seqType: Double("10"), type: "Double" },
  { seqNum: 25, seqType: Double("10"), type: "Double"  },
  { seqNum: 6, seqType: String("10"), type: "String"  },
  { seqNum: 24, seqType: String("10"), type: "String" },
  { seqNum: 7, seqType: [ "1", "2", "3" ], type: "Array" },
  { seqNum: 23, seqType: [ "1", "2", "3" ], type: "Array" },
  { seqNum: 8, seqType: [ [1], [2], [3] ], type: "Array" },
  { seqNum: 22, seqType: [ [1], [2], [3] ], type: "Array " },
  { seqNum: 9, seqType: [ 1, 2, 3 ], type: "Array" },
  { seqNum: 21, seqType: [ 1, 2, 3 ], type: "Array" },
  { seqNum: 10, seqType: true, type: "Boolean" },
  { seqNum: 11, seqType: new Timestamp(), type: "Timestamp" },
  { seqNum: 12, seqType: new Date(), type: "Date" },
  { seqNum: 13, seqType: new ObjectId(), type: "ObjectId" },
] )

Create indexes on the sequence number ( seqNum ) and sequence type ( seqType ) fields:在序列号（seqNum）和序列类型（seqType）字段上创建索引：

db.keyTypes.createIndex( { seqNum: 1 } )

db.keyTypes.createIndex( { seqType: 1 } )

Query the collection using find(). The projection document, { _id: 0 }, suppresses the _id field in the output display.使用find()查询集合。投影文档{_id:0}在输出显示中抑制_id字段。

db.keyTypes.find( {}, { _id: 0 } )

The documents are returned in insertion order:文件按插入顺序返回：

{ seqNum: 1, seqType: null, type: 'null' },
{ seqNum: 29, seqType: null, type: 'null' },
{ seqNum: 2, seqType: 10, type: 'Int32' },
{ seqNum: 28, seqType: 10, type: 'Int32' },
{ seqNum: 3, seqType: Long("10"), type: 'Long' },
{ seqNum: 27, seqType: Long("10"), type: 'Long' },
{ seqNum: 4, seqType: Decimal128("10"), type: 'Decimal128' },

// Output truncated

The sequence number ( seqNum ) index has values of the same type. Use the seqNum index to query the keyTypes collection:序列号（seqNum）索引具有相同类型的值。使用seqNum索引查询keyTypes集合：

db.keyTypes.find( {}, { _id: 0 } ).sort( { seqNum: 1} )

The seqNum keys are integers. The documents are returned in numerical order:seqNum键是整数。文件按数字顺序返回：

{ seqNum: 1, seqType: null, type: 'null' },
{ seqNum: 2, seqType: 10, type: 'Int32' },
{ seqNum: 3, seqType: Long("10"), type: 'Long' },
{ seqNum: 4, seqType: Decimal128("10"), type: 'Decimal128' },
{ seqNum: 5, seqType: 10, type: 'Double' },
{ seqNum: 6, seqType: '10', type: 'String' },
{ seqNum: 7, seqType: [ '1', '2', '3' ], type: 'Array' },

// Output truncated

The sequence type ( seqType ) index has values of the different types. Use the seqType index to query the keyTypes collection:序列类型（seqType）索引具有不同类型的值。使用seqType索引查询keyTypes集合：

db.keyTypes.find( {}, { _id: 0 } ).sort( { seqType: 1} )

The documents are returned in BSON type sort order:文档以BSON类型的排序顺序返回：

{ seqNum: 1, seqType: null, type: 'null' },
{ seqNum: 29, seqType: null, type: 'null' },
{ seqNum: 9, seqType: [ 1, 2, 3 ], type: 'Array' },
{ seqNum: 21, seqType: [ 1, 2, 3 ], type: 'Array' },
{ seqNum: 2, seqType: 10, type: 'Int32' },
{ seqNum: 28, seqType: 10, type: 'Int32' },
{ seqNum: 3, seqType: Long("10"), type: 'Long' },
{ seqNum: 27, seqType: Long("10"), type: 'Long' },
{ seqNum: 4, seqType: Decimal128("10"), type: 'Decimal128' },
{ seqNum: 26, seqType: Decimal128("10"), type: 'Decimal128' },
{ seqNum: 5, seqType: 10, type: 'Double' },
{ seqNum: 25, seqType: 10, type: 'Double' },
{ seqNum: 7, seqType: [ '1', '2', '3' ], type: 'Array' },
{ seqNum: 23, seqType: [ '1', '2', '3' ], type: 'Array' },
{ seqNum: 6, seqType: '10', type: 'String' },
{ seqNum: 24, seqType: '10', type: 'String' },
{ seqNum: 8, seqType: [ [ 1 ], [ 2 ], [ 3 ] ], type: 'Array' },
{ seqNum: 22, seqType: [ [ 1 ], [ 2 ], [ 3 ] ], type: 'Array ' },
{
  seqNum: 13,
  seqType: ObjectId("6239e3922604d5a7478df071"),
  type: 'ObjectId'
},
{ seqNum: 10, seqType: true, type: 'Boolean' },
{
  seqNum: 12,
  seqType: ISODate("2022-03-22T14:56:18.100Z"),
  type: 'Date'
},
{
  seqNum: 11,
  seqType: Timestamp({ t: 1647960978, i: 1 }),
  type: 'Timestamp'
}
  • Numerical types (Int32, Long, Decimal128, Double) are equivalent when compared with other types.与其他类型相比，数字类型（Int32、Long、Decimal128、Double）是等效的。

  • Within the Numbers BSON type, numerical types are sorted:在Numbers BSON类型中，对数字类型进行排序：

    • Int32
    • Long
    • Decimal128
    • Double