Efficient Mysql Paging method and principle _mysql

First look at the basics of pagination:

Copy Code code as follows:

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:n ull rows:10020 extra:1 Row in Set (0.00 sec)

The meaning of the limit 10000,20 scan satisfies the condition of 10020 lines, throw away the preceding 10000 lines, return the last 20 lines, the problem is here, if it is limit 100000,100, need to scan 100100 rows, in a high concurrent application, Each query needs to scan more than 10W lines, performance must be greatly compromised. It is also mentioned that the limit n performance is fine because only n rows are scanned.

The article refers to a "clue" approach to provide some "clues" to the page, such as the SELECT * from message order by ID DESC, descending by ID, 20 per page, currently on page 10th, the current page entry ID is the largest 9527, the smallest is 9500 , if we only provide "prev", "Next" jump (do not provide to the nth page of the jump), then when processing the "previous page" the SQL statement can be:

Copy Code code as follows:

SELECT * from message WHERE ID > 9527 ORDER by ID ASC LIMIT 20;

When working with the next page, the SQL statement can be:

Copy Code code as follows:

SELECT * FROM: WHERE ID < 9500 ORDER by ID DESC LIMIT 20;

No matter how many pages are turned, only 20 rows are scanned for each query.

The disadvantage is that only "prev", "Next" link form, but our product managers like "< prev 1 2 3 4 5 6 7 8 9 Next page >" Such a link, how to do?

If limit m,n unavoidable, to optimize efficiency, only as far as possible to let M small, we extend the previous "clue" approach, or select * from message order by ID DESC, in descending by ID page, 20 per page, is currently 10th page, The current page entry ID is the largest 9527, the smallest is 9500, such as to jump to page 8th, I read the SQL statement can write:

Copy Code code as follows:

SELECT * FROM: WHERE ID > 9527 ORDER by ID ASC LIMIT 20, 20;

Jump to page 13th:

Copy Code code as follows:

SELECT * FROM: WHERE ID < 9500 ORDER by ID DESC LIMIT 40, 20;

The principle is the same, record the current page ID maximum and minimum value, calculate the jump page and the current page relative offset, because the page is similar, this offset is not very large, so the M value is relatively small, greatly reduce the number of rows scanned. In fact, the traditional limit m,n, the relative offset has always been the first page, so that the more turn to the back, the efficiency is worse, and the above method does not have such a problem.

Note the ASC and DESC in the SQL statement, and if it is the result of ASC, remember to invert it when displayed.

Tested in a table with a total of 60W of data, the effect is very obvious.