MySQL paging Analysis Principle and efficiency improvement, mysql paging Efficiency Improvement

MySQL paging Analysis Principle and efficiency improvement, mysql paging Efficiency Improvement

MySQL paging Analysis Principle and Efficiency Improvement

On percona performance conference 2009, several Yahoo engineers 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: 1select_type: SIMPLEtable: messagetype: indexpossible_keys: NULLkey: PRIMARYkey_len: 4ref: NULLrows: 10020Extra: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 id ASC LIMIT 20;

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

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

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

The disadvantage is that only "previous" and "Next" links can be provided, however, our product manager is very fond of "<Previous Page 1 2 3 4 5 6 7 8 9 next page>". What should I do?

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 id ASC LIMIT 20,20;

Jump to page 13th:

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

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.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!