How to implement paging query in mysql

Mysql paging query method: 1. Use specific fields instead of [*]; 2. Search the index first; 3. Use [between...and], the id must be continuously increasing; 4. Keep the previous page The id where the record is located.

Mysql paging query method:

Brief description

• Usually in MySQL, limit #{limit}, #{offset} is used to perform paging queries.

• When there are many records in the table and the number of pages (#{limit}) is large, the efficiency of paging query becomes slower.

• The reason for the slowdown is that when querying in pages, the limit offset records will be queried first, and then the subsequent offset records will be intercepted.

• This article takes the actual 140 million table big_table as an example to record how to optimize paging queries.

Test description

• Test table: big_table, data volume: 140 million.

Original SQL (4500ms)

-- 原始分页查询，耗时：4500ms
select * 
from big_table
limit 2000000,10;

Reasons for slowness:

1. The query condition is *

2. limit = 2000000 is too big

Optimization 1 (recommended): Use specific fields instead of * (1600ms)

-- 用明确字段代替*，耗时：1600ms
select id,uid,media_id,media_name,news_id,comment 
from big_table
limit 2000000,10;

Optimization 2: Search the index first (450ms)

-- 方法1：先对索引进行分页查询，耗时：450ms
select * from big_table AS h inner join
 (select id from big_table
  limit 2000000,10) AS ss 
 on h.id = ss.id;
-- 方法2：先查询出起始位置的索引，耗时：450ms
select * from big_table
where id > (
select id from big_table limit 2000000,1
)
limit 10;

Optimization 3: between ... and (5ms)

Restriction: id must be continuously increasing.

-- 上一页保留最后一条记录所在id，耗时：5ms
select * from big_table
where id between 4882489 and 4882489 + 10 ;

Optimization 4 (recommended): Keep the id of the record on the previous page (5ms)

Limitations: You need to keep the id of the last record on the previous page

-- 上一页需要保留最后一条记录所在id，耗时：5ms
select * from big_table
where id > 4882488
limit 10;

More related free learning recommendations: mysql tutorial(Video)

The above is the detailed content of How to implement paging query in mysql. For more information, please follow other related articles on the PHP Chinese website!