Mysql million-level paging optimization and mysql Paging

Mysql million-level paging optimization and 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 100001,20; //time 0.151sexplain SELECT * from user limit 100001,20;

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.003s

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.

Added: Performance Optimization of millions of paging queries in mysql

Prerequisites:

1. Unique index of the table

2. Million-level data

SQL statement:

select     c.*     FROM  (  SELECT   a.logid  FROM   tableA a  where 1 = 1     <#if phone?exists&& phone!="">       AND a.phone like "%":phone"%"      </#if>   ORDER BY    a.create_time DESC   limit :startIndex,:maxCount  ) b,tableA c  where 1 = 1 AND b.logid = c.logid

Where:

1: startIndex: indicates the start position of the data query.

2: maxCount: displays the number of data entries per page.

3: a. create_time DESC: sort in descending order. You need to create an index at create_time.

4: Put limiit inside, instead of outside the query, which improves the efficiency a lot.

5: logid: unique index