MongoDB data paging and sorting limit, skip, sort user

Sort by sort, which is equivalent to order by. The ascending order is represented by 1, and the descending order is represented by-1.
For example, sort by age field in ascending order:

The code is as follows:	Copy code
Db. user. find (). sort ({"age": 1 })

If there are multiple fields, sort by name in descending order and age in ascending order

The code is as follows:	Copy code
Db. user. find (). sort ({"name":-1, "age": 1 })

Limit and skip are used together, which is equivalent to the limit in mysql:

The code is as follows:	Copy code
B. user. find (). skip (1). limit (1)

Equivalent to limit (0th) in mysql, that is, 1st entries are skipped, and only one entry is returned starting from.
Skip and limit can implement paging, but if there are too many skip addresses, it will affect performance and should be avoided as much as possible. For example, obtaining a value of the last document of the current page as a condition (such as a date) to query, you do not need to use skip.

No matter what database is used, there are usually some common paging and ranking methods. This article will introduce the performance of MongoDB in this aspect through some test data.

First, we create a page. In MongoDB, the sample data is not as follows:

The code is as follows:	Copy code
Db. scores. find (); {Lid: ObjectId ("4fe506dabb2bfa742d000001"), score: 1, name: 'user _ 1 '} {Lid: ObjectId ("4fe506dabb2bfa742d000001"), score: 2, name: 'user _ 2 '} {Lid: ObjectId ("4fe506dabb2bfa742d000001"), score: 3, name: 'user _ 3 '} {Lid: ObjectId ("4fe506dabb2bfa742d000001"), score: 4, name: 'user _ 4 '}

The lid field is used to differentiate different latitudes and is mainly used for filtering. In the test collection, there are five different lid values, each of which corresponds to 1,200,000 pieces of data, totaling 6,000,000 pieces of data. The index is on the lid and score. (The following query can use the index)

Then we conduct the following performance tests:

The code is as follows:

Copy code

Collection = Mongo: Connection. new. db ('test'). collection ('scores ') Benchmark. bmbm do | x | X. report ("mongo small") do 100. times do | I | Collection. find ({: lid => lids. sample },{: fields =>{:_ id => false,: score => true,: user => true }}). sort ({: score =>-1 }). limit (20 ). skip (I * 20 ). to_a End End X. report ("mongo medium") do 100. times do | I | Collection. find ({: lid => lids. sample },{: fields =>{:_ id => false,: score => true,: user => true }}). sort ({: score =>-1 }). limit (20 ). skip (I * 1000 ). to_a End End X. report ("mongo large") do 100. times do | I | Collection. find ({: lid => lids. sample },{: fields =>{:_ id => false,: score => true,: user => true }}). sort ({: score =>-1 }). limit (20 ). skip (I * 10000 ). to_a End End End

In the above three cases, the number of skips is relatively small, the size is medium, and the size is very large. Limit specifies that 20 data records are obtained in the same way. In these three cases, the test results are as follows: 0.6 seconds, 17 seconds, and 173 seconds.

We can see that for MongoDB, the size of the skip seriously affects the performance, and it is necessary to strictly avoid large skip operations.

The ranking function is similar to the paging function. The difference is that the ranking is done by calculating the number of entries greater than a certain value.

For example:

The code is as follows:	Copy code
// SQL Select count (*) from scores where lid = $1 and score> $2 // Mongo Db. scores. find ({lid: lid, score: {$ gt: score}). count ()

Because ranking is similar to paging implementation, the results are actually similar. The test results are as follows:

Mongo top rank 1.155847
Mongo average 22.291007

The conclusion above is a comparison. What is the question in this article?

First, try to avoid large skip operations in MongoDB. For example, if you want to know the score of the data to be retrieved on the page, the following method may be used to obtain the data you want, rather than a large skip operation.

Db. scores. find ({lid: lid, score :{$ lt: last_score }}). sort ({score:-1 }). limit (20) in addition, if you need to perform a relatively large number of skip operations or count, you can consider using Redis Sorted Sets