Mongo大集合Skip+Limit分页性能问题及优化方案咨询

使用React+Spring Boot架构搭配MongoDB数据库，history集合包含475,876,939条记录。当跳转至末页或靠后分页时，数据查询耗时长达10分钟，性能问题严重。

当前实现代码如下：

Controller类 #

package com.demo.controller;

import java.util.*;


import com.demo.model.*;
import com.demo.repository.HistoryDao;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.web.bind.annotation.*;



@CrossOrigin(origins = "http://localhost:3000")
@RestController
@RequestMapping("/user/")
public class HistoryController {
    private static final Logger log = LogManager.getLogger(HistoryController.class);


    private HistoryDao dao;


    @Autowired
    public HistoryController(HistoryDao dao) {
        this.dao = dao;
    }

    public HistoryController() {
    }

    @GetMapping("/history")
    public List<HistoryRecord> getHistory(@RequestParam(defaultValue = "0") int pageNumber,
                                                  @RequestParam(defaultValue = "10") int pageSize){
        try {
            Pageable page = PageRequest.of(pageNumber, pageSize, Sort.by(Sort.Order.desc("timestamp")));
            Query query = new Query();
            query.with(page);
            List<HistoryRecord> recordsPage = dao.getHistory(query);
            return recordsPage;
        } catch (Exception de) {
            log.error("Exception ",de);
        }
        return null;
    }

}

HistoryDao.java #

package com.demo.repository;

import com.demo.model.*;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.Query;

import org.springframework.stereotype.Repository;

import java.util.*;


@Repository
public class HistoryDao{

    @Autowired
    private  MongoTemplate mongoTemplate;
    private static final Logger log = LogManager.getLogger(HistoryDao.class);
    public List<HistoryRecord> getHistory(Query query){
        return mongoTemplate.find(query, HistoryRecord.class);
    }
    
}

模型类HistoryRecord.java #

package com.demo.model;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.data.annotation.Transient;
import org.springframework.data.mongodb.core.index.CompoundIndex;
import org.springframework.data.mongodb.core.index.CompoundIndexes;
import org.springframework.data.mongodb.core.mapping.Document;
import java.util.Date;


@Document(collection = "history")
@CompoundIndexes({
        @CompoundIndex(name = "timestamp_index", def = "{'timestamp': -1}", unique = false)
})
public class HistoryRecord {
    private static final Logger log = LogManager.getLogger(HistoryRecord.class);

    private String fromUser;

    private String toUser;

    private Date timestamp;



    public String getFromUser() {
        return fromUser;
    }

    public void setFromUser(String fromUser) {
        this.fromUser = fromUser;
    }

    public String getToUser() {
        return toUser;
    }

    public void setToUser(String toUser) {
        this.toUser = toUser;
    }

    public Date getTimestamp() {
        return timestamp;
    }

    public void setTimestamp() {
        try {
            this.timestamp = new Date();
        } catch (Exception e) {
            log.error(e);
        }
    }
}

直接在MongoDB CLI执行查询同样耗时约10分钟：

db.history.find({}).sort({ timestamp: -1 }).skip(47587693).limit(10);

注：页码=47587693，页大小=10；

查询执行计划：

db.history.find({}).sort({ timestamp: -1 }).skip(47587693).limit(10).explain();

{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "demo_db.history",
                "indexFilterSet" : false,
                "parsedQuery" : {

                },
                "queryHash" : "17A361F7",
                "planCacheKey" : "17A361F7",
                "winningPlan" : {
                        "stage" : "LIMIT",
                        "limitAmount" : 10,
                        "inputStage" : {
                                "stage" : "FETCH",
                                "inputStage" : {
                                        "stage" : "SKIP",
                                        "skipAmount" : 47587693,
                                        "inputStage" : {
                                                "stage" : "IXSCAN",
                                                "keyPattern" : {
                                                        "timestamp" : -1
                                                },
                                                "indexName" : "timestamp_index",
                                                "isMultiKey" : false,
                                                "multiKeyPaths" : {
                                                        "timestamp" : [ ]
                                                },
                                                "isUnique" : false,
                                                "isSparse" : false,
                                                "isPartial" : false,
                                                "indexVersion" : 2,
                                                "direction" : "forward",
                                                "indexBounds" : {
                                                        "timestamp" : [
                                                                "[MaxKey, MinKey]"
                                                        ]
                                                }
                                        }
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "user-db",
                "port" : 27017,
                "version" : "4.4.29",
                "gitVersion" : "f4dda329a99811c707eb06d05ad023599f9be263"
        },
        "ok" : 1
}

1. 替换skip/limit为基于游标（Keyset Pagination）的分页 #

MongoDB的skip在处理大偏移量时性能极差，因为它需要遍历所有跳过的文档。改用**基于最后一条记录的timestamp+_id**来定位下一页数据（_id全局唯一，用来处理timestamp重复的情况）：

修改后端接口 #

@GetMapping("/history")
public List<HistoryRecord> getHistory(
        @RequestParam(required = false) Date lastTimestamp,
        @RequestParam(required = false) String lastId,
        @RequestParam(defaultValue = "10") int pageSize){
    try {
        Query query = new Query();
        // 按timestamp倒序，再按_id倒序，避免timestamp重复导致分页混乱
        Sort sort = Sort.by(Sort.Order.desc("timestamp"), Sort.Order.desc("_id"));
        query.with(sort);
        
        // 非首页时，通过上一页最后一条记录的信息过滤数据
        if (lastTimestamp != null && lastId != null) {
            query.addCriteria(Criteria.where("timestamp").lt(lastTimestamp)
                    .orOperator(
                            Criteria.where("timestamp").is(lastTimestamp).and("_id").lt(lastId)
                    ));
        }
        
        query.limit(pageSize);
        List<HistoryRecord> recordsPage = dao.getHistory(query);
        return recordsPage;
    } catch (Exception de) {
        log.error("Exception ",de);
    }
    return null;
}

前端调整 #

• 每次获取数据后，保存当前页最后一条记录的timestamp和_id
• 下一页请求时，将这两个参数传给后端
• 末页跳转：直接查询最早的pageSize条数据（sort({timestamp: 1}).limit(pageSize)），再倒序返回给前端展示

2. 优化索引 #

创建支持Keyset分页的复合索引，避免额外的文档扫描：

db.history.createIndex({timestamp: -1, _id: -1})

这个索引能直接匹配我们的查询条件和排序规则，提升查询效率。

3. 优化末页跳转逻辑 #

如果必须支持直接跳转到末页，不要用大偏移量的skip，而是：

1. 先获取集合中最早的一条记录的timestamp和_id：

   db.history.find().sort({timestamp: 1, _id: 1}).limit(1)
   

2. 基于这个结果，查询该时间点附近的pageSize条数据，再调整为倒序返回。

4. 数据分片（长期优化） #

对于近5亿条记录的集合，建议搭建MongoDB分片集群，按timestamp字段分片，将数据分散到多个节点，大幅提升查询和写入性能。

5. 前端交互与数据归档 #

• 隐藏大页码的直接输入框，只保留首页、上一页、下一页、末页按钮，减少大偏移量查询的场景
• 如果业务允许，限制用户只能查看最近N个月的历史记录，将旧数据归档到低成本冷存储（如MongoDB Archive Storage）

内容的提问来源于stack exchange，提问作者Harish Mahi