Mysql paging principle and efficient mysql paging query statement _ MySQL

Mysql paging principle and efficient mysql paging query statement bitsCN.com

In the past, I used the limit, 20 Method for paging in mysql. I believe you are the same, but to improve the efficiency, make the paging code more efficient and faster, what should we do?

Part 1: Let's take a look at the basic principles of paging:

mysql explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20***************** 1. row **************id: 1select_type: SIMPLEtable: messagetype: indexpossible_keys: NULLkey: PRIMARYkey_len: 4ref: NULLrows: 10020Extra:1 row in set (0.00 sec)

Description of the above mysql statement: limit, 20 means to scan 10020 rows that meet the conditions, 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, each query needs to scan more than rows, and the performance is definitely compromised. The performance of limit n is okay because only n rows are scanned.

Part 2: according to several Yahoo engineers, an article about extended content of the Efficient Pagination Using MySQL report is provided: a clue approach is mentioned in this article, which provides clues for turning pages, for example, SELECT * FROM message order by id DESC, which is divided BY id in descending ORDER. There are 20 entries on each page. the current page is 10th pages. the current page has a maximum of 1020 entries and a minimum of 1000 entries, if we only provide the previous and next page jumps (not to page N), the SQL statement can be:

SELECT * FROM message WHERE id> 1020 order by id asc limit 20; // Next page

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

SELECT * FROM message WHERE id <1000 order by id desc limit 20; // Previous Page

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

The disadvantage is that the previous page and next page can only be provided, but our product manager prefers the "Previous Page 1 2 3 4 5 6 7 8 9 Next Page" method. 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 practice, or SELECT * FROM message order by id DESC, list by id in descending order. There are 20 entries on each page. Currently, there are 10th pages. The maximum number of entries on the current page is 2519, and the minimum value is 2500;

The SQL statement on page 1 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 the 9th page, the SQL statement can be written as follows:

SELECT * FROM tb_goods_info WHERE auto_id<2500 ORDER BY auto_id desc LIMIT 0,20

For example, to jump to the 8th page, the SQL statement can be written as follows:

SELECT * FROM tb_goods_info WHERE auto_id<2500 ORDER BY auto_id desc LIMIT 20,20

For example, to jump to the 7th page, the SQL statement can be written as follows:

SELECT * FROM tb_goods_info WHERE auto_id<2500 ORDER BY auto_id desc LIMIT 40,20

Jump to page 11th:

SELECT * FROM tb_goods_info WHERE auto_id >2519 ORDER BY auto_id asc LIMIT 0,20

Jump to page 12th:

SELECT * FROM tb_goods_info WHERE auto_id >2519 ORDER BY auto_id asc LIMIT 20,20

Jump to page 13th:

SELECT * FROM tb_goods_info WHERE auto_id >2519 ORDER BY auto_id asc 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.

BitsCN.com

The above is the mysql paging principle and the highly efficient mysql paging query statement _ MySQL content. For more information, see The PHP Chinese network (www.php1.cn )!