Mysql million-level paging optimization, mysql Paging

Mysql million-level paging optimization, mysql Paging
Normal Paging

Data paging is very common in webpages. pages are generally limit start and offset, and start is calculated based on the page number.

Select * from user limit 1, 20

When the page size is several hundred thousand, the page efficiency will be relatively low. MySQL needs to calculate the page size from the beginning to the end, which greatly affects the efficiency.

SELECT * from user limit 0.151; // time sexplain SELECT * from user limit;

We can use the explain statement to analyze the statements without any indexes. The number of rows executed by MySQL is 16 W +, so we can use indexes to implement paging.

 

Optimize Paging

Use primary key index to optimize data Paging

Select * from user where id> (select id from user where id> = 100000 limit 1) limit 20; // time 0.003 s

Using the explain statement, the number of lines scanned by MySQL is more than, and the time is greatly shortened.

Explain select * from user where id> (select id from user where id> = 100000 limit 1) limit 20;

Summary

When the data volume is large, we try to optimize the statement by using indexes. If the id is not a primary key index, the query efficiency is lower than the first one. We can use the explain statement to analyze the statement execution sequence and performance.