Java项目MySQL深度分页优化方案详解：从延迟关联到Elasticsearch的完整指南

Java项目中MySQL深度分页解决方案大全

在日常的Java项目开发中，随着业务数据的不断增长，我们经常会遇到MySQL深度分页的性能瓶颈。通常，我们的分页语句可能长这样：

SELECT * FROM order_table ORDER BY id LIMIT 1000000, 10;

这条语句看起来只是查询了10条数据，但MySQL的执行逻辑是：先扫描出前1000010行记录，然后丢弃前1000000行，最后返回剩下的10行。当偏移量（OFFSET）非常大时，大量的扫描和丢弃操作会导致严重的I/O和CPU消耗，甚至导致数据库服务不可用。

本文将全面梳理在Java项目中解决MySQL深度分页的常见及高级方案，帮助开发者在不同业务场景下选择最优解。

一、 延迟关联（推荐）

延迟关联的核心思想是：先利用覆盖索引快速查出主键ID，然后再用主键ID去关联原表获取完整数据。覆盖索引的查询不需要回表，因此扫描大量数据时速度极快。

1. 原理与SQL改写

-- 原始慢查询
SELECT * FROM order_table ORDER BY create_time LIMIT 1000000, 10;

-- 优化后：延迟关联
SELECT t1.* FROM order_table t1
INNER JOIN (
    SELECT id FROM order_table ORDER BY create_time LIMIT 1000000, 10
) t2 ON t1.id = t2.id;

在子查询中，SELECT id FROM order_table ORDER BY create_time 可以完全走索引 idx_create_time（覆盖索引），不需要回表查整行数据，极大提升了分页效率。

2. MyBatis实现示例

// Mapper接口
List<Order> queryOrderByDelayAssociate(@Param("offset") long offset, @Param("limit") int limit);

// XML映射文件
<select id="queryOrderByDelayAssociate" resultType="com.ipipp.demo.entity.Order">
    SELECT t1.* FROM order_table t1
    INNER JOIN (
        SELECT id FROM order_table ORDER BY create_time LIMIT #{offset}, #{limit}
    ) t2 ON t1.id = t2.id
</select>

二、 标签记录法（游标分页/Seek Method）

标签记录法摒弃了传统的 OFFSET，而是记住上一页最后一条记录的某个特定值（通常是主键或排序字段），下次查询时直接从该值之后开始查询。

1. 适用场景

适合App端下拉加载、滚动翻页等不需要精确跳转到指定页码的场景（如朋友圈、订单流水）。

2. SQL改写

-- 假设上一页最后一条记录的 id 为 1000000
SELECT * FROM order_table WHERE id > 1000000 ORDER BY id ASC LIMIT 10;

这种方式无论翻到第几页，查询效率都和第一页一样高，因为MySQL只需要扫描10行数据即可。

3. Spring Boot Controller 示例

@RestController
@RequestMapping("/api/orders")
public class OrderController {

    @Autowired
    private OrderService orderService;

    // 前端请求例如：http://www.ipipp.com/api/orders/next?lastId=1000000&size=10
    @GetMapping("/next")
    public List<Order> getNextPage(@RequestParam(required = false) Long lastId,
                                   @RequestParam(defaultValue = "10") Integer size) {
        if (lastId == null) {
            // 第一页
            return orderService.getFirstPage(size);
        }
        // 后续页
        return orderService.getNextPageByLastId(lastId, size);
    }
}

三、 BETWEEN ... AND ... 寻址法

如果主键是自增且连续的（没有大量删除导致空洞），可以通过计算直接估算出当前页对应的主键范围。

-- 假设每页10条，要查第100001页
-- 计算起始ID：1000000，结束ID：1000010
SELECT * FROM order_table WHERE id BETWEEN 1000000 AND 1000010;

注意：这种方案局限性很大，一旦数据存在物理删除导致ID不连续，或者排序字段不是主键，就会出现漏数据或数据不对的情况，生产环境需谨慎使用。更稳妥的做法是先用子查询查出起始ID：

SELECT * FROM order_table 
WHERE id >= (SELECT id FROM order_table ORDER BY id LIMIT 1000000, 1) 
LIMIT 10;

四、 借助搜索引擎（Elasticsearch）

当业务复杂、数据量极大（千万级/亿级），且涉及多维度条件筛选和深度分页时，直接查MySQL已经力不从心。此时应该将数据同步到Elasticsearch，利用ES的分布式检索能力来处理。

1. ES深度分页优化

ES自身也存在深度分页（from + size）问题，默认限制 from + size 不能超过10000。针对深度分页，ES提供了 search_after 和 scroll 两种方案。

2. Java中使用 search_after 示例

import org.elasticsearch.action.search.SearchRequest;
import org.elasticsearch.action.search.SearchResponse;
import org.elasticsearch.client.RequestOptions;
import org.elasticsearch.client.RestHighLevelClient;
import org.elasticsearch.index.query.QueryBuilders;
import org.elasticsearch.search.SearchHit;
import org.elasticsearch.search.builder.SearchSourceBuilder;
import org.elasticsearch.search.sort.SortOrder;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class EsDeepPagination {

    private final RestHighLevelClient client;

    public List<Map<String, Object>> searchAfterPage(Object[] lastSortValues, int size) throws IOException {
        SearchRequest searchRequest = new SearchRequest("order_index");
        SearchSourceBuilder sourceBuilder = new SearchSourceBuilder();
        
        sourceBuilder.query(QueryBuilders.matchAllQuery());
        sourceBuilder.size(size);
        // 按照时间倒排，必须有唯一键（如id）作为二级排序保证唯一性
        sourceBuilder.sort("create_time", SortOrder.DESC);
        sourceBuilder.sort("id", SortOrder.ASC);
        
        // 如果有上一页的排序值，则加入 search_after
        if (lastSortValues != null) {
            sourceBuilder.searchAfter(lastSortValues);
        }
        
        searchRequest.source(sourceBuilder);
        SearchResponse response = client.search(searchRequest, RequestOptions.DEFAULT);
        
        List<Map<String, Object>> resultList = new ArrayList<>();
        for (SearchHit hit : response.getHits()) {
            resultList.add(hit.getSourceAsMap());
        }
        return resultList;
    }
}

五、 业务层面的妥协与优化

有时候技术上的难题可以通过业务设计的调整来规避：

1. 限制最大翻页数

参考百度、Google等搜索引擎，它们通常只显示前几十页的搜索结果。如果用户真的需要100万条以后的数据，说明他的搜索条件太宽泛了，应该引导用户增加筛选条件。

public PageResult<Order> queryOrders(int pageNum, int pageSize) {
    // 强制限制最大分页数
    int maxPageNum = 0;
    if (pageNum > maxPageNum) {
        throw new BusinessException("数据量过大，请增加查询条件缩小范围！");
    }
    // 正常查询...
}

2. 数据冷热分离

历史数据查询频率低，可以将3个月前的数据归档到历史表或数据仓库中。在线业务表只保留近期活跃数据，从而减小单表体积，降低深度分页的概率。

六、 总结与选型建议

在实际开发中，推荐优先考虑延迟关联解决大部分B端系统的深度分页问题；在C端无限加载场景中，优先使用标签记录法；当数据量达到瓶颈且检索复杂时，毫不犹豫地引入Elasticsearch。

MySQL深度分页延迟关联标签记录法Java分页优化Elasticsearch