Summary of four solutions for deep paging issues in MySQL

MySQL deep pagination problem:
This problem is very common in actual projects. When the data volume is large, the paging will be very slow (I did a pagination query for a dispatch log a few years ago, and it was simply useless)

Why is deep paging slow

Preface: N conditions are indexes, id is the primary key
Usually, the PageHelper plug-in is used in normal pagination, and in the end, SQL looks roughly like this:

select id,name from table_name where NConditions limit 100000,10;

Its execution process:

• First go to the secondary index to filter the data, and then find the primary key ID
• Query data through ID return to the table and retrieve the required columns
• Scan the 100010 that meets the conditions, discard the first 100000 items, and return

The obvious disadvantage here is that you only need to get 10 items, but you have indeed reported 100,000 times more.

optimization

1. Optimize through subquery

Optimize the number of tables

select id,name FROM table_name t1 where id >= (select id from table_name where update_time >= '2024-04-01 23:59:59' limit 100000, 1) AND update_time >= '2024-04-01 23:59:59' LIMIT 10;

Process: match the secondary index according to the conditions. After obtaining the result ID, the outer layer query can then search 10 backwards based on the result ID.

2. Optimize with INNER JOIN

Optimize the number of tables

SELECT , FROM table_name t1 INNER JOIN (SELECT  FROM table_name t2 WHERE t2.update_time >= '2024-04-01 23:59:59' ORDER BY t2.update_time LIMIT 100000, 10) AS t3 on = ;

The core points of the above two methods areOptimize the number of tablesOptimize this angle, but the scanned rows have not been reduced. There are two ways to start with reducing scanned rows, but they all have certain limitations.

3. Label recording method

Record the maximum ID of the last query and request the next page.

select id,name FROM table_name where id > 100000 order by id limit 10;

4. between...and...

select  id,name FROM table_name where id between 100000 and 100010 order by id;

limitation: Rely on continuous self-increment fields (if not continuous, you can order by )

Replenish

The tag recording method used in the project backend management system before, quickly locates the ID according to the conditions, and then scans the specified number of lines backwards according to the conditions. The front-end also transforms it together, prohibits inputting the number of pages, and allows clicking only the next page and the previous page [Since there are deep paging problems, the business does not need to support the user to jump to pages at will, because it has no meaning. What should he jump to page 8,531 to see? ]

This is the end of this article about the four solutions for the MySQL deep paging problem. For more related content on MySQL deep paging, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!