Shulou(Shulou.com)06/01 Report--

Use indexes to sort query results

In MongoDB, sorting operations can ensure the ordering of results by getting documents from the index in the order of the index. If the query planner (planner) cannot get the sort order from the index, it will need to sort the results in memory. Using an index has better performance than a sort operation that does not use an index. In addition, if a sort operation that does not use an index uses more than 32 megabytes of memory, the operation is terminated.

Sort using a single-key index

If an ascending or decreasing index is a single-key index, the sort operation on that key can be in any direction.

For example, create an incremental index on the a key of the collection records:

Db.records.ensureIndex ({a: 1})

The index can support incremental sorting on a

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

The index also supports the following descending sort on a, by traversing the index in reverse order:

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

Sort on multiple keys

Create a composite index to support sorting on multiple keys.

You can specify to sort on all or some of the keys of the index. However, the order of the sort keys must be the same as the order in which they are arranged in the index. For example, index {a: 1, b: 1} can support sorting {a: 1, b: 1} but not {b: 1, a: 1}.

In addition, the sort order of all keys specified in the sort (for example, increment / decrement) must be exactly the same or the opposite as the corresponding keys in the index. For example, index {a: 1, b: 1} can support sorting {a: 1, b: 1} and sorting {a:-1, b:-1}, but not sorting {a:-1, b: 1}.

Sort and index prefix

If the sorted key matches the key or prefix of the index, MongoDB can use the index to sort the query results. The prefix of a composite index refers to a subset of indexed keys, consisting of one or more keys at the beginning.

For example, create a composite index on the collection data:

Db.data.ensureIndex ({a 1, b: 1, c: 1, d: 1})

Then, the prefix for the index is as follows:

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

The following query and sort operations can use index prefixes to sort query 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}

Suppose you have the following example, where the prefix key of the index appears in the query condition and sort:

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

In this case, MongoDB can use the index to get the documents in the order specified by sort. As shown in the example, the index prefix in the query condition can be different from the prefix that appears in sort.

Sort with non-prefixed index keys

The index also supports sorting using keys that are not prefixed. In this case, for all keys arranged in front of the sort keys in the index, the query statement must contain conditions for their equal matches.

For example, the collection data has the following index:

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

The following operations can be sorted using an index:

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 those keys in the index that rank before the sort keys must have equal matching conditions in the query statement; other index keys can specify other matching conditions.

If the query statement does not specify equality matching conditions for prefix keys that are arranged in front of or overlap with the sort key, the operation will not effectively use the index. For example, the following operation specifies the sort {c: 1}, but the query statement does not specify an equal match for the prefix keys an and b:

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

These operations will not use the index {a: 1, b: 1, c: 1, d: 1} efficiently, and may not even use the index to get the document.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.