https://www.toutiao.com/article/7542534157415547430

  1. 观点总览
    没有银弹：分页策略取决于是否需要跳页、是否强一致、排序字段是否稳定唯一、是否有复杂筛选等。
    反模式优先禁用：深度 OFFSET … LIMIT 与一次性拉全量。
    工程化组合拳：Keyset（游标）分页 + 覆盖索引 +（可选）Elasticsearch/物化视图 + 前端交互约束（无限滚动/限制页深）。
    以“可观测性”和“容量规划”收尾：限流、缓存、慢查询治理、压测基线。

1. 绝对禁忌：请不要这样做
1.1 深度 OFFSET ... LIMIT
问题：数据库需要扫描并丢弃前 N 行，复杂度 O(N)，页码越深越慢。
示例（错误）：
SELECT * FROM large_table ORDER BY id DESC LIMIT 9999999, 20;
后果：慢查询、IO 飙升、Buffer Pool 污染、连接被占满。
1.2 一次性查询全量数据入内存
问题：JVM OOM、GC 抖动，甚至拖垮整机。
示例（错误）：
List<Entity> all = repository.findAll(); // 千万级，危险！
结论：深度 OFFSET 和 全量加载 都是灾难起点。
  1. 常规方案（简单场景可用）
2.1 Keyset/Seek（基于索引的游标分页）
思想：不记录偏移量，只记录“上一页最后一条记录的游标值”（如自增 id 或时间戳）。
前提：排序字段单调可比较且有索引，最好唯一（避免跳行/重复）。
SQL：
-- 第一页
SELECT * FROM large_table ORDER BY id DESC LIMIT 20;
-- 后续页（上一页末 id = 10020）
SELECT * FROM large_table WHERE id < 10020 ORDER BY id DESC LIMIT 20;
Spring Data JPA：
public interface LargeDataRepository extends JpaRepository<LargeData, Long> {
    List<LargeData> findTop20ByOrderByIdDesc();
    List<LargeData> findTop20ByIdLessThanOrderByIdDesc(Long lastId);
}
MyBatis：
<select id="pageBySeek" parameterType="map" resultType="LargeData">
  SELECT *
  FROM large_table
  <where>
    <if test="lastId != null">
      AND id < #{lastId}
    </if>
  </where>
  ORDER BY id DESC
  LIMIT #{size}
</select>

优点：与页码深度无关；可稳定走索引；性能恒定。
缺点：不支持任意跳页；需要排序字段稳定唯一（可用复合游标解决）。
2.1.1 非唯一排序的复合游标

当按 create_time DESC 排序且时间存在相同值：

-- 复合游标 (create_time, id)
SELECT *
FROM large_table
WHERE (create_time < :lastTime)
   OR (create_time = :lastTime AND id < :lastId)
ORDER BY create_time DESC, id DESC
LIMIT :size;

2.2 覆盖索引 + 回表优化（支持有限跳页）


思想：子查询只扫描轻量索引（如 (create_time, id)），先拿到本页主键，再回表取全列。
SQL：
SELECT t.*
FROM large_table t
JOIN (
  SELECT id
  FROM large_table
  FORCE INDEX(idx_ctime_id) -- 可选：强制使用覆盖索引
  ORDER BY create_time DESC
  LIMIT 9_999_999, 20
) x ON x.id = t.id;
优缺点：比直接 OFFSET 快很多，但本质仍受深度影响；作为必须跳页时的“最后底线”，并应限制最大可跳页数（如 ≤ 500）。
  1. 高级/最优方案（高性能与复杂检索）
    3.1 业务侧优化：限制页深 + 引导精确过滤
    策略：
    限制最大页深（如最多 100 页）。
    提示用户使用时间范围、状态、关键词等过滤，以缩小候选集。
    首页/前几页可短缓存（如 10–30s）缓解热度冲击。
    3.2 引入搜索引擎（Elasticsearch）

适用：复杂筛选、高并发检索、排序多样。
做法：
通过 Canal/CDC、Binlog、Debezium 或业务事件将 MySQL 数据近实时写入 ES。
查询走 ES，分页用 search_after（游标式）而非深度 from+size。
search_after 示例：
POST index/_search
{
  "size": 20,
  "sort": [{"create_time":"desc"}, {"id":"desc"}],
  "search_after": ["2025-08-15T10:00:00Z", 10020],
  "query": { "bool": { "must": [ {"term": {"status": "OK"}} ] } }
}

注意：保持 ES 与源库的一致性策略（允许秒级延迟or事务内双写+补偿）；避免无限深度翻页；热字段建 keyword/数值类型。
3.3 物化视图 / 汇总表
适用：报表、榜单、聚合视图。
策略：离线任务定时聚合（每日/每小时）写入更小的宽表。
3.4 前端交互：无限滚动 + 预取
组合：Keyset 分页 + 前端无限下拉；在靠近底部时预取下一页，提升体验。

  1. 数据库与索引设计
    4.1 必备索引
覆盖索引：
CREATE INDEX idx_ctime_id ON large_table(create_time DESC, id DESC);
组合排序场景，保证排序字段在索引前缀。
查询谓词字段（status/category 等）在复合索引中紧跟排序列，或考虑分区。

4.2 分库分表与分区
时间分区（按月/按周），结合 Keyset 在活跃分区内分页。
热点写入：自增 id 容易集中热点，混入时间戳/随机段、或用雪花算法。
4.3 一致性与快照


读与写并发时，Keyset 的“下一页游标”受更新影响：
业务容忍弱一致：直接使用当前读。
业务要求快照一致：记录“查询窗口”的上界（如 max(id) 或 max(create_time)）作为上限。
-- 取得本次会话上界
SELECT @max_id := MAX(id) FROM large_table;
-- 每页都限定在该快照内
SELECT * FROM large_table WHERE id <= @max_id AND id < :lastId ORDER BY id DESC LIMIT :size;
  1. 应用层落地（Spring Boot）
    5.1 统一分页响应模型
    `asp

@Data
public class PageResp {
private List list;
private String nextCursor; // Base64(“ctime:id”) 或纯数字 id
private boolean hasMore;
}

5.2 Keyset 分页的 Service 示例

```asp
@Service
@RequiredArgsConstructor
public class LargeDataService {
  private final LargeDataMapper mapper; // MyBatis 示例

  public PageResp<LargeData> page(String cursor, int size) {
    size = Math.min(Math.max(size, 1), 100); // 防御：1..100
    Long lastId = cursor == null ? null : Long.parseLong(cursor);
    List<LargeData> rows = mapper.pageBySeek(lastId, size + 1); // 取 size+1 判断 hasMore
    boolean hasMore = rows.size() > size;
    if (hasMore) rows = rows.subList(0, size);
    String next = rows.isEmpty() ? null : String.valueOf(rows.get(rows.size()-1).getId());
    PageResp<LargeData> resp = new PageResp<>();
    resp.setList(rows); resp.setHasMore(hasMore); resp.setNextCursor(next);
    return resp;
  }
}

5.3 复合游标编码/解码

public final class CursorCodec {
  public static String encode(Instant ctime, long id){
    return Base64.getUrlEncoder().withoutPadding()
      .encodeToString((ctime.toEpochMilli()+":"+id).getBytes(StandardCharsets.UTF_8));
  }
  public static Pair<Instant,Long> decode(String cursor){
    if (cursor==null) return Pair.of(null,null);
    String s=new String(Base64.getUrlDecoder().decode(cursor), StandardCharsets.UTF_8);
    String[] a=s.split(":");
    return Pair.of(Instant.ofEpochMilli(Long.parseLong(a[0])), Long.parseLong(a[1]));
  }
}
5.4 API 设计（REST）
GET /api/items?size=20&cursor=ey0...
200 OK
{
  "list": [ ... ],
  "nextCursor": "ey0...",
  "hasMore": true
}

约束：size 上限、cursor 过期处理（返回 400 + 指南）、幂等性。
5.5 首页短缓存 & 限流
缓存：热点列表页缓存 10–30s（Caffeine/Redis），键含筛选条件。
限流：令牌桶/滑动窗口；异常时降级为“近 1 分钟快照”。

  1. 压测与可观测性
    6.1 压测基线
    数据规模：≥ 实际规模的 1.5x。
    场景：
    高频第一页 + 高频下一页（Keyset）。
    大量条件组合（ES/覆盖索引）。
    极端页深（验证保护是否生效）。
    6.2 监控指标
    DB：慢查询数、扫描行数（Rows_examined）、命中率、锁等待、活跃连接。
    应用：P95/P99 延迟、错误率、限流触发计数、GC 暂停。
    ES：查询耗时、拒绝率（circuit breaker）、刷新/合并开销。
    6.3 常见瓶颈与对策
    扫描行数大 → 调整索引顺序/覆盖索引。
    排序无法走索引 → 改 Keyset 或改排序字段。
    热键/热点页 → 分片缓存、随机化打散、边缘缓存（CDN for API 不常见但可用）。

  2. 决策树（实践指引）
    必须任意跳页吗？
    否 → Keyset 分页。
    是 → 覆盖索引 + 限制最大页深；尽量引导筛选。
    存在复杂筛选/搜索吗？
    是 → 引入 Elasticsearch（search_after）。
    对一致性要求强吗？
    强 → 快照上界（max(id)/max(ctime)）+ 事务隔离策略。
    读多写少的报表/榜单？
    是 → 物化视图/汇总表 + 定时刷新。
    推荐默认架构：MySQL（Keyset）处理简单排序的流式分页 + Elasticsearch 处理复杂搜索。

  3. FAQ 与细节陷阱


删除造成的 id 空洞会影响 Keyset 吗？ 不会，WHERE id < lastId 仍然正确。
排序非唯一导致重复/漏数？ 使用复合游标（如 ctime,id）。
并发更新会“跳页”吗？ 允许弱一致即可；强一致用“快照上界”。
需要导出全量 CSV？ 后台任务 + 游标流式拉取（fetchSize/ResultSet.TYPE_FORWARD_ONLY），绝不走分页接口循环 OFFSET。
ES 与 DB 排序对不齐？ 同步相同排序字段并保持相同归一化规则（时区/精度）。
  1. DDL/示例清单

-- 表与索引
CREATE TABLE large_table (
  id BIGINT PRIMARY KEY,
  create_time DATETIME NOT NULL,
  status TINYINT NOT NULL,
  payload JSON,
  KEY idx_ctime_id (create_time DESC, id DESC),
  KEY idx_status_ctime (status, create_time DESC, id DESC)
) ENGINE=InnoDB;
# application.yaml 关键参数（示例）
spring:
  datasource:
    hikari:
      maximum-pool-size: 50
      minimum-idle: 10
  jpa:
    properties:
      hibernate.jdbc.fetch_size: 100
      hibernate.jdbc.batch_size: 100
      hibernate.order_inserts: true
      hibernate.order_updates: true
// JDBC 流式读取（用于导出）
PreparedStatement ps = conn.prepareStatement(sql,
    ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ps.setFetchSize(Integer.MIN_VALUE); // MySQL 开启流式
  1. 收尾建议（落地清单）
    禁用深度 OFFSET，必要时加最大页深保护（例如 500）。
    默认采用 Keyset 分页，为非唯一排序提供复合游标实现。
    为复杂搜索引入 Elasticsearch，用 search_after，并建立数据同步链路。
    设计覆盖索引，保证排序字段在索引前缀；评估分区/分表。
    提供统一分页响应模型（nextCursor/hasMore）。
    首页/热门查询短缓存 + 限流 + 退化策略。
    建立压测基线与可观测指标，持续优化慢查询与扫描行。
    一言以蔽之：在互联网常见业务里，用 Keyset（用于简单、可顺序浏览的场景） + Elasticsearch（用于复杂、需筛选排序的场景） 是最均衡且可扩展的方案。
文档更新时间: 2025-09-12 08:27   作者：admin