传统分页查询（Offset/Limit 分页）
这是最直观、最常见的分页方式。 实现原理：
-- 获取第N页数据，每页20条
SELECT * FROM products
ORDER BY created_at DESC
LIMIT 20 OFFSET 40; -- 第3页：跳过前40条，取20条
工作机制：
数据库需要先扫描并排序所有匹配的行，然后跳过前 OFFSET 条记录，最后返回 LIMIT 条记录。
优点：
-
简单直观：实现简单，理解容易
-
随机跳页：支持直接跳转到任意页码
-
前后端解耦：前端只需传递页码和每页大小
-
总数可知：可以方便地计算总页数
缺点：
-
深度分页性能差：OFFSET 越大性能越差，因为数据库需要先扫描并跳过大量行
-
数据不一致：在分页过程中如果数据有增删，可能导致重复或丢失数据
-
资源消耗大：需要处理整个结果集或大范围的数据
适用场景：
-
数据量不大（如小于100万）
-
需要跳转到任意页码的后台管理系统
-
用户需要知道总页数的场景
-
对性能要求不高的内部系统
游标查询/键集分页（Cursor/Keyset Pagination）
基于值的分页，使用上一页的最后一条记录作为起点。
实现原理：
-- 第一页
SELECT * FROM products
WHERE status = 'active'
ORDER BY id DESC
LIMIT 20;
-- 下一页（使用上一页最后一条的id）
SELECT * FROM products
WHERE status = 'active' AND id < [上一页最后一条的id]
ORDER BY id DESC
LIMIT 20;
关键要求：
-
必须有一个唯一且有序的列（或列组合）作为游标
-
通常使用自增ID、时间戳、或其他具有业务意义的唯一字段
优点：
-
性能稳定：无论翻到第几页，性能都基本一致
-
v数据一致性好：在分页过程中新增数据不影响已查看的分页
-
适合无限滚动：非常适合移动端的无限下拉加载，即增量数据加载，大数据量的高性能查询和处理。
缺点：
-
不支持随机跳页：只能顺序翻页（上一页/下一页）
-
需要业务逻辑支持：前后端需要配合传递游标值
-
无法直接获取总数：难以计算总页数
适用场景：
-
社交媒体时间线（Twitter、Facebook）
-
消息列表
-
实时数据流
-
移动端无限滚动
-
数据量大且需要高性能分页的场景
滚动查询（Scroll/Seek）
主要用于一次性处理大量数据，特别是需要稳定快照的场景。
实现原理：
-- Elasticsearch风格的滚动
POST /products/_search?scroll=1m
{
"size": 100,
"query": { "match_all": {} }
}
-- 后续使用返回的scroll_id获取下一批
在SQL数据库中，通常通过游标（数据库游标，非游标分页）或特定的窗口函数实现。
优点：
-
数据快照：在滚动期间提供一致的数据视图
-
适合批处理：非常适合数据导出、批量处理、ETL等
-
服务端状态：服务器维护分页状态，客户端简单
缺点：
-
资源占用：服务器需要维护滚动上下文，消耗资源
-
有超时限制：滚动通常有时间限制
-
不适合Web分页：更多用于后端处理
适用场景：
-
全量数据导出
-
批量数据处理任务
-
数据迁移
-
搜索引擎的大结果集遍历
其他分页策略
覆盖索引分页
-- 先通过覆盖索引获取ID，再获取完整数据
SELECT * FROM products
WHERE id IN (
SELECT id FROM products
WHERE category_id = 5
ORDER BY created_at DESC
LIMIT 20 OFFSET 40
);
分区/分片分页
将数据物理分区，分别查询后合并。
近似分页
使用估算值代替精确计数，牺牲准确性换取性能。
库表设计时的关键考虑
在设计表结构时，要为高效分页做好准备：
1. 游标字段设计
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY, -- 主键作为基础游标
order_number VARCHAR(32) UNIQUE, -- 业务唯一键
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
-- 添加复合索引支持多维度分页
INDEX idx_orders_pagination (status, created_at, id)
);
2. 索引策略
为ORDER BY和WHERE条件创建复合索引
确保索引包含游标字段
考虑使用覆盖索引减少回表
3. 分区设计
对于超大数据集，考虑按时间或业务键分区：
-- 按月分区
CREATE TABLE events_2024_01 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
