MySQL paging principle and high-efficiency MySQL paging query statements

This article mainly introduces the MySQL paging principle and the efficient MySQL paging query statement, please refer to the use of it

I used to be in MySQL in the page is the limit 100000,20 this way, I believe you too, but to improve efficiency, so that the page code more efficient, faster, then what should we do?

Part I: Look at the basic principles of paging:

MySQL explain SELECT * from message ORDER by ID DESC LIMIT 10000, 20 ***************** 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)

Description of the above MySQL statement: The meaning of limit 10000,20 scan satisfies the condition of 10020 lines, throw away the previous 10000 rows, return the last 20 rows, the problem is here, if it is limit 100000,100, you need to scan 100100 rows, In a highly concurrent application, each query needs to scan more than 10W lines, the performance will be greatly compromised. It is also mentioned that limit n performance is not a problem, because only n rows are scanned.

Part II: According to several Yahoo engineers brought a efficient pagination Using MySQL report content extension: In the article refers to a clue approach, to provide some clues to the page, for example, or select * from the message ORDER By ID DESC, sorted by ID descending page, 20 per page, currently the 10th page, the current page entry ID The largest is 1020, the smallest is 1000, if we only provide a previous page, the next page such a jump (do not provide a jump to page N), then in the process of the previous page, the SQL statement can be:

The code is as follows:
SELECT * from Message WHERE id>1020 ORDER by ID ASC LIMIT 20;//next page

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

The code is as follows:
SELECT * from Message WHERE id<1000 ORDER by ID DESC LIMIT 20;//previous page

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

The disadvantage is that only the previous page, the next page of the link form, but our product manager very much like "Prev 1 2 3 4 5 6 7 8 9 Next" How to do this?

If limit m,n inevitable, to optimize efficiency, only if possible to let M small, we extend the previous clue practice, or select * from the message order by ID DESC, according to the ID descending page, 20 per page, is currently the 10th page, The maximum current page entry ID is 2519, the smallest is 2500;

When the 10th page of SQL is as follows:

The code is as follows:
SELECT * from Tb_goods_info WHERE auto_id >=2500 ORDER by auto_id ASC LIMIT 0,20
For example, to jump to page 9th, SQL statements can be written like this:
The code is as follows:
SELECT * from Tb_goods_info WHERE auto_id <2500 ORDER by auto_id desc LIMIT 0,20
For example, to jump to page 8th, SQL statements can be written like this:
The code is as follows:
SELECT * from Tb_goods_info WHERE auto_id <2500 ORDER by auto_id desc LIMIT 20,20
For example, to jump to page 7th, SQL statements can be written like this:
The code is as follows:
SELECT * from Tb_goods_info WHERE auto_id <2500 ORDER by auto_id desc LIMIT 40,20
Skip to page 11th:
The code is as follows:
SELECT * from Tb_goods_info WHERE auto_id >2519 ORDER by auto_id ASC LIMIT 0,20
Skip to page 12th:
The code is as follows:
SELECT * from Tb_goods_info WHERE auto_id >2519 ORDER by auto_id ASC LIMIT 20,20
Skip to page 13th:
The code is as follows:
SELECT * from Tb_goods_info WHERE auto_id >2519 ORDER by auto_id ASC LIMIT 40,20

The principle is still the same, record the current page ID of the 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 that the M value is relatively small, greatly reducing the number of scanned rows. In fact, the traditional limit m,n, the relative offset has been the first page, so the more turned to the back, the efficiency is worse, and the method given above does not have such a problem.

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

Tested in a table of total 60W data, and the results are very obvious

MySQL paging principle and high-efficiency MySQL paging query statements