Efficient mysql paging method and principle

First of all, let's look at the basic principle of paging:

mysql> explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20G ***************** 1. row ************** id: 1 select_type: SIMPLE table: message type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 10020 Extra: 1 row in set (0.00 sec)

limit 10000,20 means scan the 10020 rows that satisfy the criteria, throw away the first 10000 rows, return the last 20 rows, that's the problem. If it's limit 100000,100, need to scan 100100 rows, in a high concurrency application, each query needs to scan more than 10W rows, the performance must be significantly reduced. The article also mentioned that limit n performance is fine, because only n rows are scanned.

Mentioned clue "1", to turn the page 1 "clue", such as or SELECT * FROM message ORDER BY id DESC, according to the id descending order page, each page of article 20, is the current page 10, the current page entry id maximum is 9527, the smallest is 9500, if we only provide "on page 1", "on page 1" jump (not provided to the first N jump of the page). When processing "previous 1", the SQL statement could be:

SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 20;

When processing "next page", the SQL statement can be:

SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 20;

No matter how many pages you turn, only 20 rows are scanned per query.

The disadvantage is that it can only provide the link form of "previous page" and "next page", but our product manager likes it very much. < On page 1, 1, 2, 3, 4, 5, 6, 7, 8, 9, page 1 > "Such a link, how to do?

If LIMIT m,n inevitable, to optimize efficiency, only make m as small as possible, we extend the previous "clue" practice, or SELECT * FROM message BY id DESC, according to id descending page, 20, currently is page 10, the current page entry id largest is 9527, the smallest is 9500, for example, to skip to page 8, I read the SQL statement like this:

SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 20,20;

Jump to page 13:

SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 40,20;

The principle is the same: record the maximum and minimum values of id on the current page, and calculate the relative deviation between the jump page and the current page. Since the page is similar, the deviation is not very large, so the value of m is relatively small, which greatly reduces the number of scanned lines. In fact, the relative offset of traditional limit m and n is 1 straight to page 1. In this way, the more you turn to the back, the less efficient you will be. However, the method given above does not have such a problem.

Note the ASC and DESC in SQL statements. If it is the result of ASC, remember to invert 1 when displaying.

It has been tested in the table of 60W data totals, and the effect is very obvious.