How to use MySQL limit and solve the problem of oversized paging

Preface

Daily development, we use mysql to achieve the paging function, will always use mysql limit syntax. And how to use is very particular, today to summarize 1.

limit syntax

The limit syntax supports two parameters, offset, which represents the offset, and limit, which represents fetching the previous limit data.

For example:

##  Returns the qualified pre- 10 Bar statement  
select * from user limit 10

##  Returns the eligible 11-20 Bar data 
select * from user limit 10,20

It can also be seen from the above that limit n is equivalent to limit 0 and n.

Performance analysis

In actual use, we will find that in the last 1 pages of paging, loading will slow down, that is to say:

select * from user limit 1000000,10

Statement execution is slow. So let's test 1 first.

First, take 100 pieces of data when offset is small (the total amount of data is about 200). Then gradually increase offset.

select * from user limit 0,100 --------- Time consuming 0.03s
select * from user limit 10000,100 --------- Time consuming 0.05s
select * from user limit 100000,100 --------- Time consuming 0.13s
select * from user limit 500000,100 --------- Time consuming 0.23s
select * from user limit 1000000,100 --------- Time consuming 0.50s
select * from user limit 1800000,100 --------- Time consuming 0.98s

It can be seen that with the increase of offset, the performance is getting worse and worse.

Why is this? Because the syntax of limit 10000, 10 is actually that mysql finds the first 10010 pieces of data and then discards the first 10000 rows, this step is actually wasted.

Optimization

Optimization with id

Find the largest ID of the last page, and then use the index on id to query, similar to select * from user where id > 1000000 limit 100.
This is very efficient because there is an index on the primary key, but this has a disadvantage, that is, ID must be contiguous, and the query cannot have where statements, because where statements will cause data filtering.

Optimization with overlay index

When the mysql query hits the index completely, it is called overwriting index, which is very fast, because the query only needs to look up the index, and then it can be returned directly without going back to the data table to get the data. Therefore, we can find out the ID of the index first, and then get the data according to Id.

select * from (select id from job limit 1000000,100) a left join job b on a.id = b.id;

It takes 0.2 seconds.

Summarize

It is really difficult to paginate a large amount of data with mysql, but there are also some methods that can be optimized, which need to be tested in combination with business scenarios.
When the user turns to 10000 pages, why don't we go back to empty directly? Is it so boring...