SoFunction
Updated on 2025-04-13

Solve the problem of MySQL depth pagination

1. The root cause of the in-depth paging problem

When usedLIMITandOFFSETMySQL must be scanned when pagingOFFSET + LIMITOK, then discard beforeOFFSETOK. This means that as the page deepens, MySQL will need to scan more and more rows, resulting in a degradation in query performance.

For example, the following query is used to obtain data from lines 10001 to 10010:

SELECT * FROM table_name ORDER BY age LIMIT 10 OFFSET 10000;

In this case, MySQL must scan 10010 rows, even if only 10 rows are returned. This kind of scan and drop operations can lead to a large number of I/O operations, especially when the table data is large.

2. How to optimize in-depth pagination?

2.1 Optimize query using indexes

Make sure to create appropriate indexes on the columns used for sorting and filtering, which can significantly reduce the number of rows MySQL needs to scan.

For example, ifWHEREThe query statement containsidColumn sorting, ensureidColumns are index columns. Otherwise, MySQL may scan all rows, resulting in performance degradation.

SELECT * FROM table_name ORDER BY id LIMIT 10 OFFSET 10000;

Using index optimization query method by avoiding useOFFSET, reducing unnecessary row scans.

2.2 Using Overwrite Index

In MySQL, try to query as much as possible. If the query only involves a small number of columns, you can use overwrite indexes to improve performance. The overwrite index contains all columns required for the query, so you can avoid back-table operations.

-- Create a column1, column2 Combination index of
CREATE INDEX idx_cover ON table_name (column1, column2);

-- Query with overwrite index column1, column2
SELECT column1, column2 FROM table_name WHERE column1 = ? AND column2 = ?;

In the example above, the query simply takes data from the index and does not need to access the data page of the table, so it can avoid back-table operations, thereby improving performance.

2.3 Using tagged pages

Tag paging is implemented by saving the tag (usually a unique identifier) ​​of the last record of the last query. This method does not useOFFSET, but useWHEREClause to get the data on the next page:

SELECT * FROM table_name
WHERE id > last_id
ORDER BY id
LIMIT 20;

This approach is especially suitable for ordered, continuous paging requests.

2.4 Partition table

If the dataset is very large, consider using table partitioning. Partitions can divide tables into smaller chunks, reducing the amount of data that needs to be scanned per query. MySQL supports a variety of partitioning methods, such as range partitions, list partitions, etc.

2.4.1 Create tables and partition by scope

Suppose there is a table containing sales recordssales, there is a columnsale_date, indicating the date of sale. We want to partition this table by year to make query more efficient.

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    quantity INT,
    sale_date DATE
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024)
);

In this example,salesThe table is divided into three partitions:

  • p2021Include allsale_dateRecords in 2021.
  • p2022Include allsale_dateRecords in 2022.
  • p2023Include allsale_dateRecords in 2023.

Each partition is an independent physical storage unit, so the query can access only the relevant partitions.

2.4.2 Insert data

When inserting data, MySQL will use thesale_dateAutomatically put records into the corresponding partition.

INSERT INTO sales (sale_id, product_id, quantity, sale_date) VALUES
(1, 101, 5, '2021-06-15'),
(2, 102, 10, '2022-07-20'),
(3, 103, 8, '2023-03-10');

2.4.3 Query partition table

When querying the partition table, MySQL automatically determines which partitions to access. For example:

SELECT * FROM sales WHERE sale_date BETWEEN '2022-01-01' AND '2022-12-31';

In this query, MySQL will only accessp2022Partitioning, thereby improving query performance.

2.4.4 Other partition types

In addition to Range Partitioning (RANGE), MySQL supports several other partition types, including:

  • List Partition (LIST): Partition according to the discrete value list.
  • Hash partition (HASH): Use the hash function to distribute data to multiple partitions.
  • Key Partition (KEY): Similar to hash partition, but uses MySQL's internal hash algorithm.
  • Linear hash partition (LINEAR HASH): A special hash partition suitable for specific load and data distribution.

2.5 Cache results

If the results of the paging query do not change frequently, you can consider cache the query results. Caching can significantly reduce the load on the database, especially in high concurrency scenarios.

2.6 Using external search engines

For particularly complex or data-intensive scenarios, consider using external search engines such as Elasticsearch or Solr. These tools are designed to handle large data sets and complex queries and are often more efficient than traditional databases.

3. Things to note in practice

3.1 Reasonably select the page size

The pagination size directly affects query performance and user experience. A smaller paging size reduces the burden on each query, but increases the number of paging requests. Choosing the right page size requires weighing the relationship between the two.

3.2 Monitoring and analyzing query performance

Use MySQL's performance monitoring tools (e.g.EXPLAINand slow query logs) to analyze the execution plan and performance bottlenecks of the query.

3.3 Consider user experience

In some cases, users may not need very precise paging data. Consider using the Load More button or infinite scrolling instead of traditional paging.

4. Summary

This article analyzes the deep paging problem and its solutions for MySQL. For deep paging in MySQL, we can improve query efficiency through reasonable optimization strategies. The specific solution to choose needs to be analyzed based on the specific scenario, but the core lies in understanding the working principle of the database, using indexing, optimizing query strategies, using optimization technologies such as tagged paging, partitioning tables, and cache results. Through these methods, the performance of paging query can be significantly improved and the user experience can be improved.

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