Docs Home → MongoDB Manual

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

On this page本页内容

Since indexes contain ordered records, MongoDB can obtain the results of a sort from an index that 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.阻止排序不会阻止对集合或数据库的并发操作。

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 }。In versions 4.4 and 5.0, if the server needs more than 100 megabytes of system memory for the blocking sort operation, MongoDB returns an error unless that query specifies cursor.allowDiskUse(). 在4.4和5.0版本中，如果服务器需要超过100兆字节的系统内存来执行阻塞排序操作，MongoDB会返回一个错误，除非该查询指定了cursor.allowDiskUse()。For details, see allowDiskUseByDefault.有关详细信息，请参阅allowDiskUseByDefault。

Sort operations that use an index often have better performance than blocking sorts.使用索引的排序操作通常比阻塞排序具有更好的性能。

Note

When you sort based on an array field that is indexed with a multikey index, the query plan includes a blocking sort stage unless both of the following are true:当您根据使用多键索引进行索引的数组字段进行排序时，除非以下两项均为真，否则查询计划将包括一个阻塞排序阶段：

  • The index boundaries for all sort fields are [MinKey, MaxKey].所有排序字段的索引边界都是[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:例如，为records集合在字段a上创建升序索引：

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. 如果排序键与索引键或索引前缀相对应，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集合上创建复合索引：

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. 在这种情况下，MongoDB可以使用索引按照排序指定的顺序检索文档。As the example shows, the index prefix in the query predicate can be different from the prefix in the sort.如示例所示，查询谓词中的索引前缀可能与排序中的前缀不同。

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类型排序顺序进行排序。
  • In array comparisons:数组内比较：

    • A less-than comparison, or an ascending sort, compares the smallest elements of the array according to the BSON type sort order.小于比较或升序根据BSON类型的排序顺序比较数组中最小的元素。
    • A greater-than comparison, or a descending sort, compares the largest elements of the array according to the reverse BSON type sort order.大于比较或降序根据BSON类型的反向排序顺序比较数组中最大的元素。
    • When comparing a field whose value is a one element array (example, [ 1 ]) with non-array fields (example, 2), the comparison is for 1 and 2.当将值为单元素数组的字段（例如[1]）与非数组字段（例如2）进行比较时，比较结果为1和2。
    • A comparison of an empty array (example, [ ]) considers the empty array as less than a null value or a missing field value.空数组（例如[ ]）的比较认为空数组小于null值或缺少字段值。

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

For example, the collection myColl has an index on a string field category with the collation locale "fr".例如，集合myColl在排序规则区域设置为"fr"的字符串字段category上有一个索引。

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

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

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

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

db.myColl.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 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以及字符串字段category有一个复合索引；索引是使用排序规则区域设置"fr"创建的，用于字符串比较：

db.myColl.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.myColl.find( { score: 5 } ).sort( { price: 1 } )
db.myColl.find( { score: 5, price: { $gt: NumberDecimal( "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.myColl.find( { score: 5, category: "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(). 使用find()查询集合。The projection document, { _id: 0 }, suppresses the _id field in the output display.投影文档{ _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'
}
  • In array comparisons:数组内比较：

    • A less-than comparison, or an ascending sort, compares the smallest elements of the array according to the BSON type sort order.小于比较或升序根据BSON类型的排序顺序比较数组中最小的元素。
    • A greater-than comparison, or a descending sort, compares the largest elements of the array according to the reverse BSON type sort order.大于比较或降序根据BSON类型的反向排序顺序比较数组中最大的元素。
    • When comparing a field whose value is a one element array (example, [ 1 ]) with non-array fields (example, 2), the comparison is for 1 and 2.当将值为单元素数组的字段（例如[1]）与非数组字段（例如2）进行比较时，比较结果为1和2。
    • A comparison of an empty array (example, [ ]) considers the empty array as less than a null value or a missing field value.空数组（例如[]）的比较认为空数组小于null值或缺少字段值。
  • 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