How to optimize MySQL Query statements, enable efficient paging, and easily respond to 60 million request _ MySQL

It teaches you how to optimize MySQL Query statements to implement efficient paging and easily respond to bitsCN of 60 million requests. on comPERCONA performance conference 2009, several engineers from Yahoo brought a "Efficient Pagination Using MySQL" report, which has many highlights. This article is a further extension based on the original article.
First, let's take a look at the basic principles of paging:
Mysql> explain SELECT * FROM message order by id desc limit 10000, 20/G
* *************** 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, 20 means to scan 10020 rows that meet the condition, discard the first 10000 rows, and 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, more than rows need to be scanned for each query, and the performance is definitely compromised. The performance of limit n is okay because only n rows are scanned.
This article introduces a "clue" approach to provide some "clues" for turning pages, such as SELECT * FROM message order by id DESC, paging BY id in descending ORDER, 20 entries per page, currently, there are 10th pages. The maximum id of the current page is 9527, and the minimum is 9500, if we only provide jumps such as "previous page" and "next page" (do not provide jumps to page N), the SQL statement can be:

SELECT * FROM message WHERE id> 9527 order by idASC LIMIT 20;
When processing the next page, the SQL statement can be:
SELECT * FROM message WHERE id <9500 order by idDESC LIMIT 20;

No matter how many pages are displayed, only 20 rows are scanned for each query.
The disadvantage is that we can only provide "previous" and "next" links, but our product managers like it very much" <上一页1 2 3 4 5 6 7 8 9 下一页> "What should I do with this link?
If LIMIT m and n are unavoidable, we need to optimize efficiency BY minimizing m as much as possible. we need to extend the previous "clue" approach, or SELECT * FROM message order by id DESC, page by id in descending order. There are 20 entries per page. Currently, there are 10th pages. The maximum id of the current page is 9527, and the minimum is 9500. for example, to jump to 8th pages, I can write the SQL statement as follows:
SELECT * FROM message WHERE id> 9527 order by idASC LIMIT 20, 20;
Jump to page 13th:
SELECT * FROM message WHERE id <9500 order by idDESC LIMIT;

The principle is the same. it records the maximum and minimum values of the current page id, and calculates the relative offset between the jump page and the current page. because the page is similar, this offset is not very large, in this case, the m value is relatively small, greatly reducing the number of scanned rows. In fact, the relative offset of the traditional limit m, n has always been the first page. the more we move to the next page, the worse the efficiency, and the above method does not have such a problem.
Note the ASC and DESC in the SQL statement. if the result is obtained by ASC, remember to put it upside down when it is displayed.
It has been tested in a table with a total data volume of 60 million, and the effect is very obvious.


From the PainsOnline column bitsCN.com