MySQL 深度分页优化：从原理到实践

最新推荐文章于 2026-04-01 22:48:02 发布

原创最新推荐文章于 2026-04-01 22:48:02 发布 · 1.3k 阅读

12 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#java

java 专栏收录该内容

783 篇文章

订阅专栏

在 Java 开发中，MySQL 作为常用的业务数据库，分页场景极为常见。但当数据量庞大，涉及深度分页（如查询第 10 万条数据以后的分页）时，性能问题就会凸显。今天，我们就来深入探讨 MySQL 深度分页的优化方法，并结合代码示例，让大家更好地理解和应用。

一、MySQL 分页基础

MySQL 中常用LIMIT进行分页，基本语法为LIMIT [offset,] rows。offset表示偏移量，即从结果集的第几行开始返回；rows表示返回的行数。例如，查询第一页（每页 10 条数据）：

SELECT * FROM your_table WHERE price > 50 LIMIT 0, 10;

这里LIMIT 0, 10等价于LIMIT 10，表示从第 1 条数据开始，返回 10 条数据。查询第二页则是：

SELECT * FROM your_table WHERE price > 50 LIMIT 10, 10;

即从第 11 条数据开始，返回 10 条数据。随着分页越往后，offset值越大，查询时扫描的数据量也越多，性能问题就逐渐暴露出来。

二、深度分页性能问题

当数据量达到几十万甚至更多，进行深度分页时，查询性能会急剧下降。例如，查询前几页数据可能仅需 5ms 左右，但查询第 10 万条数据以后的分页，耗时可能飙升到 700 多毫秒。这是因为随着offset增大，数据库需要扫描大量无关数据，然后抛弃前面不需要的部分，造成了资源的极大浪费。

三、优化方法

（一）减少回表

原理：在使用二级索引查询时，如果要查询所有字段，而部分字段在二级索引中不存在，就需要回到聚集索引（主键索引）获取剩余字段，这个回表过程会消耗大量性能。
优化方式：先从二级索引中查询出主键id，再通过主键索引查询完整数据。
代码示例：

-- 先从二级索引查询主键id，存入临时表
CREATE TEMPORARY TABLE temp_ids AS
SELECT id
FROM your_table
WHERE price > 50
ORDER BY id
LIMIT 10 OFFSET 100000;

-- 通过临时表与原表关联，查询完整数据
SELECT your_table.*
FROM your_table
         INNER JOIN temp_ids ON your_table.id = temp_ids.id;

-- 用完临时表后删除
DROP TEMPORARY TABLE temp_ids;

这种方式避免了大量回表操作，性能得到显著提升，原本 700 多毫秒的查询，优化后可能只需 90 多毫秒。

（二）游标分页

原理：利用唯一递增列（如自增主键id），通过记录上一页的最大id值，作为下一页查询的条件，减少扫描数据量。
代码示例：

-- 假设上一页最大id为max_id
SET @max_id = 104692;

-- 查询下一页数据
SELECT *
FROM your_table
WHERE price > 50
  AND id > @max_id
ORDER BY id
LIMIT 10;

这种方法让查询速度大幅提升，从原本 100 多毫秒缩短到 4ms 左右。但它依赖于唯一递增列，且每次查询需要记录上一页的最大id值。

（三）借助其他技术

Redis 缓存：可以将分页数据缓存到 Redis 中。但深度分页时，由于数据变化频繁，缓存命中率可能不高。

import redis.clients.jedis.Jedis;

public class RedisPagination {
    public static void main(String[] args) {
        Jedis jedis = new Jedis("localhost", 6379);
        // 假设分页数据已序列化存储在Redis中，这里获取分页数据
        String pageData = jedis.get("page_100");
        if (pageData != null) {
            // 处理获取到的分页数据
            System.out.println("从Redis获取到分页数据: " + pageData);
        } else {
            // 从数据库查询并缓存到Redis
            // 此处省略数据库查询代码
            String newPageData = "查询到的分页数据";
            jedis.set("page_100", newPageData);
            System.out.println("从数据库获取并缓存到Redis: " + newPageData);
        }
        jedis.close();
    }
}

Elasticsearch：如果项目中使用了 Elasticsearch 组件，可利用其强大的搜索和分页功能。Elasticsearch 适用于分布式搜索场景，能高效处理大规模数据的分页需求。

import org.apache.http.HttpHost;
import org.elasticsearch.action.search.SearchRequest;
import org.elasticsearch.action.search.SearchResponse;
import org.elasticsearch.client.RequestOptions;
import org.elasticsearch.client.RestClient;
import org.elasticsearch.client.RestHighLevelClient;
import org.elasticsearch.index.query.QueryBuilders;
import org.elasticsearch.search.builder.SearchSourceBuilder;
import org.elasticsearch.search.fetch.subphase.highlight.HighlightBuilder;

public class EsPagination {
    public static void main(String[] args) throws Exception {
        RestHighLevelClient client = new RestHighLevelClient(
                RestClient.builder(
                        new HttpHost("localhost", 9200, "http")));

        SearchRequest searchRequest = new SearchRequest("your_index");
        SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
        searchSourceBuilder.query(QueryBuilders.matchQuery("price", "gt:50"));
        // 设置分页参数，假设每页10条，查询第100页
        searchSourceBuilder.from(990).size(10);
        searchRequest.source(searchSourceBuilder);

        SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);
        // 处理查询结果
        System.out.println(searchResponse.getHits());

        client.close();
    }
}

（四）业务层面规避

在很多业务场景下，用户很少会查看几十页甚至更多的数据。因此，可以在业务层面进行限制，比如最多展示几百页数据。以电商平台为例，用户通常不会翻到几万页去浏览商品，展示几百页足以满足需求。这样既避免了深度分页带来的性能问题，又符合用户实际使用习惯。

四、总结

MySQL 深度分页优化是一个综合性的问题，需要从数据库层面、代码层面以及业务层面多管齐下。通过减少回表、游标分页、借助其他技术以及业务限制等方法，可以有效提升深度分页的性能，为用户提供更流畅的体验。在实际开发中，要根据项目的具体情况选择合适的优化方案，确保系统的高效稳定运行。希望本文的内容能帮助大家在 Java 开发中更好地应对 MySQL 深度分页问题，记得关注我，获取更多实用干货知识。