MongoDB、MySQL、PostgreSQL 百万级分页最优方案总结

核心共性：所有数据库百万级分页，均禁止使用大 offset/skip，此类操作会导致数据库扫描大量无效数据后丢弃，越往后翻页速度越慢，甚至卡死；最优方案统一为「游标分页」，配合合理索引，实现百万/千万级数据毫秒级查询。

一、核心禁忌（三大数据库通用）

❌ 绝对禁止的分页写法（大 offset/skip 致命缺陷）

MongoDB：db.col.find().skip(1000000).limit(20)（skip 大数值，扫描前100万条再丢弃）
MySQL/PostgreSQL：SELECT * FROM table LIMIT 1000000, 20（offset 100万，扫描前100万条再丢弃）

✅ 核心逻辑：用「唯一有序字段定位」替代「跳过数据」，避免无效扫描。

二、通用最优方案：游标分页（Seek Pagination）

适用场景：APP下拉加载、小程序列表、后台普通列表（无需显示总页数/指定页码跳转），三大数据库通用，速度均为 O(1)，翻页无性能衰减。

2.1 核心前提

需有一个「有序、唯一、不可变」的字段，优先选择：

MongoDB：默认 _id（自带唯一有序，ObjectId 天然递增）
MySQL/PostgreSQL：自增主键 id（INT/BIGINT 自增）
特殊场景：可用「时间字段（createTime）+ 唯一主键」组合（如 createTime 可能重复，需搭配主键保证唯一）

2.2 分数据库实现代码（可直接复制使用）

1. MongoDB

// 第1页
db.col.find()
  .sort({_id: 1})  // 按 _id 升序（保证顺序一致）
  .limit(20);      // 每页条数

// 第2页及以后（关键：带上一页最后一条的 _id）
db.col.find({_id: {$gt: ObjectId("上一页最后一条数据的_id")}})
  .sort({_id: 1})
  .limit(20);

// 带查询条件（如 status=1、userId=123）
db.col.find({
  status: 1,
  userId: 123,
  _id: {$gt: ObjectId("上一页最后一条数据的_id")}
})
.sort({_id: 1})
.limit(20);

2. MySQL / PostgreSQL（完全通用）

-- 第1页
SELECT * FROM table 
ORDER BY id ASC  -- 按自增主键升序
LIMIT 20;        -- 每页条数

-- 第2页及以后（关键：带上一页最后一条的 id）
SELECT * FROM table 
WHERE id > 123456  -- 上一页最后一条数据的 id
ORDER BY id ASC 
LIMIT 20;

-- 带查询条件（如 user_id=1001、status=1）
SELECT * FROM table 
WHERE user_id = 1001 AND status = 1 AND id > 123456
ORDER BY id ASC 
LIMIT 20;

三、通用索引设计（决定分页速度，必建）

核心规则：查询条件字段 + 唯一有序字段（_id/id），实现索引覆盖查询，无需回表，速度最快。

分数据库索引代码

MongoDB（带查询条件示例）： db.col.createIndex({ userId: 1, status: 1, _id: 1 })
MySQL / PostgreSQL（带查询条件示例）： CREATE INDEX idx_user_status_id ON table (user_id, status, id);

说明：前面是查询过滤字段，最后必须加唯一有序字段（_id/id），避免回表查询，提升性能。

四、特殊场景：需要显示页码（1、2、3…）+ 总页数（后台管理系统）

适用场景：后台管理系统，需支持“跳转到指定页”“显示总页数/总条数”，三大数据库通用解决方案：二次查询法（兼顾性能与功能）。

实现步骤（分数据库示例）

1. MongoDB

// 步骤1：查询总数（只查1次，缓存起来，无需每次翻页查询）
const total = db.col.countDocuments({ status: 1, userId: 123 });

// 步骤2：跳页时，只查前 N-1 页最后一条 _id（仅查1条，极快）
const page = 5000;    // 目标页码
const pageSize = 20;  // 每页条数
const lastId = db.col.find({ status: 1, userId: 123 })
  .sort({_id: 1})
  .skip((page - 1) * pageSize)  // 只跳1条，不是跳 (page-1)*pageSize 条
  .limit(1)
  .toArray()[0]?._id;

// 步骤3：用游标查询当前页（毫秒级）
const list = db.col.find({
  status: 1,
  userId: 123,
  _id: {$gt: lastId}
})
.sort({_id: 1})
.limit(pageSize);

2. MySQL / PostgreSQL

-- 步骤1：查询总数（只查1次，缓存）
SELECT COUNT(*) FROM table WHERE user_id = 1001 AND status = 1;

-- 步骤2：跳页时，只查前 N-1 页最后一条 id（仅查1条，极快）
SELECT id FROM table 
WHERE user_id = 1001 AND status = 1
ORDER BY id ASC 
LIMIT 10000, 1;  -- 跳10000条，但只查1条，无性能压力

-- 步骤3：用游标查询当前页（毫秒级）
SELECT * FROM table 
WHERE user_id = 1001 AND status = 1 AND id > (步骤2查到的id)
ORDER BY id ASC 
LIMIT 20;

五、三大数据库分页核心差异（极简对比）

数据库	唯一有序字段（默认）	分页核心语法差异	索引语法差异
MongoDB	_id（ObjectId）	用 $gt 匹配 _id，find() + sort() + limit()	db.col.createIndex({...})
MySQL	id（自增主键）	用 WHERE id > lastId，SELECT + ORDER BY + LIMIT	CREATE INDEX ... ON table(...)
PostgreSQL	id（自增主键）	与MySQL完全一致（语法通用）	与MySQL完全一致（语法通用）

六、终极总结（必背）

共性：百万级分页的核心是「避免大 offset/skip」，用「游标分页」（唯一有序字段定位）替代，性能无衰减。
索引：必建「查询条件 + 唯一有序字段」的联合索引，实现索引覆盖，避免回表。
差异：仅语法细节不同（MongoDB用_id和$gt，MySQL/PG用id和>），核心逻辑完全一致。
特殊场景：需要页码和总页数时，用「总数查询 + 单条ID定位 + 游标分页」，兼顾性能与功能。

本文由萧兮的博客原创发布，欢迎转载，转载务必保留原文链接。

萧兮的博客：https://www.20010515.xyz · 原文：https://www.20010515.xyz/posts/019d8606-6b6d-7030-a2a5-33796f9faa30