Mysql paging and paging performance Query Optimization

Mysql directly uses limit for paging operations. If we do not add any processing, the data may be too big, and it will be very difficult, next I will introduce the mysql paging instance and paging performance optimization.

1. The most common paging Method for MYSQL:

The Code is as follows:	Copy code
Select * from content order by id desc limit 0, 10

In the case of small data volumes, such SQL statements are enough. The only problem that needs to be paid attention is that indexes are used. As the amount of data increases, the number of pages will increase. The SQL statements on the last few pages may be similar:

The Code is as follows:	Copy code
Select * from content order by id desc limit 10000, 10

In a word, the more paging goes, the larger the offset of the LIMIT statement, and the slower the speed.

MYSQL paging Performance Optimization

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:

The Code is as follows:	Copy code
SELECT * FROM message WHERE id> 9527 order by id asc limit 20;

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

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

The Code is as follows:	Copy code
SELECT * FROM message WHERE id> 9527 order by id asc limit 20, 20;

Jump to page 13th:

The Code is as follows:	Copy code
SELECT * FROM message WHERE id <9500 order by id desc limit;

Some netizens say that subqueries can be used for operations.

1. The paging method of subqueries improves paging efficiency. The SQL statements that are easy to use 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? Because subqueries are completed on indexes, while normal queries are completed on data files, index files are usually much smaller than data files, therefore, the operation will be more efficient. (Via) The explain SQL statement shows that the subquery uses an 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 4 NULL 6264 Using where 2 SUBQUERY content index null primary 4 NULL 27085 Using index

After easy measurement, the efficiency of the subquery paging method is 14-20 times higher than that of the pure LIMIT method!

Ii. JOIN paging Mode

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;

In my tests, the efficiency of join paging and subquery paging is basically at the same level, and the consumed time 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 1 1 PRIMARY t1 range 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,/* Number of records per page */ In _ ifelse varchar (1000),/* display field */ In _ where varchar (1000),/* condition */ In _ order varchar (1000)/* sort */ ) COMMENT 'paging stored Process' 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; $

Call Code

Call 1 call sp_Page (1, 3, '*', 'test', 'order by id desc ');

Call Example 2 call sp_Page (1, 3, '*', 'test where sort = 1', 'order by id desc ');

Call Example 3 call sp_Page (1, 3, 'Id, title', 'test where sort = 1', 'order by id desc ');