MySQL paging and paging performance query optimization

One, the most common MySQL most basic way to paging:

The code is as follows	Copy Code
SELECT * from content ORDER BY id desc limit 0, 10

In the case of small and medium data, such SQL is sufficient, the only problem to be aware of is to ensure that the index is used. As the amount of data increases, the number of pages becomes more numerous, and the next few pages of SQL may look similar:

The code is as follows	Copy Code
SELECT * from content ORDER BY id DESC limit 10000, 10

Word, is the more backward page, the limit statement will be the greater the offset, the speed will be significantly slower.

MySQL Paging performance optimization

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:

The code is as follows	Copy Code
SELECT * from message WHERE ID > 9527 ORDER by ID ASC LIMIT 20;

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

The code is as follows	Copy Code
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:

The code is as follows	Copy Code
SELECT * FROM: WHERE ID > 9527 ORDER by ID ASC LIMIT 20, 20;

Jump to page 13th:

The code is as follows	Copy Code
SELECT * FROM: WHERE ID < 9500 ORDER by ID DESC LIMIT 40, 20;

And netizens say you can use subqueries to manipulate

First, the Sub-query page to improve paging efficiency, floating in the use of SQL statements are as follows:

The code is as follows	Copy Code
SELECT * from ' content ' WHERE ID <= (SELECT ID from ' content ' ORDER by id desc LIMIT.) ($page-1) * $pagesize. ", 1) Order BY id desc LIMIT $pagesize

Why is that? Because the subquery is done on the index, and the normal query is done on the data file, the index file is usually much smaller than the data file, so it will be more efficient to operate. (via) found through explain SQL statements: Subqueries Use the index!

The code is as follows	Copy Code
ID select_type table Type possible_keys key Key_len ref rows Extra 1 PRIMARY Content Range PRIMARY PRIMARY 4 NULL 6264 Using where 2 subquery content index NULL PRIMARY 4 null 27085 Using Index

After drifting easily measured, the use of the sub-page method of paging efficiency than pure limit increased by 14-20 times!

Second, join paging way

The code is as follows	Copy Code
SELECT * from ' content ' as T1 JOIN (SELECT ID from ' content ' ORDER by id desc LIMIT. $page-1) * $pagesize. ", 1) as T2 WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

After my test, the efficiency of join paging and subquery paging is basically one level, and the time consumed is basically the same. Explain SQL statement:

The code is as follows	Copy Code
ID select_type table Type possible_keys key Key_len ref rows Extra 1 PRIMARY <derived2> system null NULL nulls 1 1 PRIMARY T1 range PRIMARY PRIMARY 4 NULL 6264 Using where 2 DERIVED content index NULL PRIMARY 4 null 27085 Using Index

We can also use the paging stored procedure Code

The code is as follows

Copy Code

DELIMITER $$; DROP PROCEDURE IF EXISTS ' sp_page ' $$ CREATE PROCEDURE ' sp_page ' (  in _pagecurrent int,/* Current page */   In _pagesize int,/* the number of records per page */  in _ifelse varchar (1000),/* Display field */  in _where varchar (1000),/* Condition */ & Nbsp;in _order varchar (1000)/* sort */ ) COMMENT ' Paging stored procedure ' BEGIN  if _pagesize<=1 then   Set _pagesize=20;  end if;  if _pagecurrent < 1 then   set _pagecurrent = 1;  end if;    set @strsql = concat (' Select ', _ifelse, ' from ', _where, ', _order, ' limit ', _pagecurrent*_pagesize- _pagesize, ', ', _pagesize);  prepare stmtsql from @strsql;  execute Stmtsql;  deallocate prepare stmtsql; Set @strsqlcount =concat (' Select COUNT (1) as Count from ', _where);/*count (1) This field is preferably the primary key. Prepare stmtsqlcount from @strsqlcount; Execute stmtsqlcount; deallocate prepare Stmtsqlcount; end$$ DELIMITER; $$.

Calling code

Invocation Example 1 called sp_page (1,3, ' * ', ' test ', ' ORDER by id desc ');

Invocation Example 2 called Sp_page (1,3, ' * ', ' Test where sort=1 ', ' ORDER by id desc ');

Invocation Example 3 called Sp_page (1,3, ' id,title ', ' Test where sort=1 ', ' ORDER by id desc ');