offset分页在大数据量下变慢是因为数据库必须扫描并跳过前offset行，即使只取1条记录也要读取并丢弃前面所有行；索引无法绕过“跳过”动作，尤其order by字段有重复值时优化器更保守，常退化为全索引扫描。

OFFSET分页在大数据量下为什么越来越慢

因为数据库必须扫描并跳过前 OFFSET 行，哪怕你只要第1000001条记录，它也得把前面100万行全读出来、计数、丢弃。索引能加速定位起始位置，但无法绕过这个“跳过”动作——尤其是当 ORDER BY 字段存在重复值时，排序稳定性会让优化器更保守，实际执行计划常退化为全索引扫描。

常见错误现象：SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000 执行时间从几毫秒飙升到2秒以上，且 OFFSET 每翻一倍，耗时几乎线性增长。

• 使用场景：仅适用于总记录数稳定在万级以内、且分页深度可控（如前100页）的后台管理页
• 参数差异：LIMIT 越小越快，OFFSET 越大越慢；加复合索引 (created_at, id) 可缓解但不根治
• 兼容性影响：所有主流SQL方言都支持，但PostgreSQL 13+ 对 OFFSET 做了部分优化，MySQL 8.0 仍无实质性改进

键集分页（Keyset Pagination）怎么写才不翻车

核心是用上一页最后一条记录的排序字段值作为下一页的查询条件，彻底避开 OFFSET。它本质是“游标式”分页，要求排序字段组合具备唯一性或强确定性。

典型错误写法：WHERE created_at —— 如果多条记录 <code>created_at 相同，结果会漏数据或重复。

• 正确做法：强制用主键补全排序键，例如 ORDER BY created_at DESC, id DESC，然后下一页条件写成 WHERE (created_at, id)
• 使用场景：高并发列表页（如电商商品流、消息时间线）、数据持续写入且需深分页的系统
• 性能影响：响应时间恒定，与总数据量无关；但必须有覆盖排序字段的联合索引，否则走全表扫描
• 注意点：前端必须保留上一页末尾的完整排序键值（不止一个字段），不能只传 created_at

MySQL和PostgreSQL在键集分页上的关键差异

两者都支持行构造器（row constructor）语法，但对 NULL 和类型隐式转换的处理不同，直接导致分页断层或越界。

常见错误现象：PostgreSQL中 WHERE (a, b) > (1, NULL) 返回空结果；MySQL 5.7 则可能报错或行为不一致。

• MySQL建议用显式拆解：WHERE created_at > '2023-01-01' OR (created_at = '2023-01-01' AND id > 12345)
• PostgreSQL可安全使用行构造器：WHERE (created_at, id) > ('2023-01-01', 12345)，但需确保字段非空或提前过滤
• 索引要求一致：都必须建 INDEX (created_at, id)，顺序与 ORDER BY 完全一致，否则无法利用索引下推

什么时候该放弃分页，改用搜索或加载更多

当用户真实需求不是“看第N页”，而是“找某类内容”时，硬做深分页只是掩盖问题。比如用户滚动到第50页后还在刷，大概率是在找特定信息，而非浏览。

容易被忽略的信号：OFFSET 超过10万、接口平均响应>500ms、前端反复请求同一“页码”但数据不变。

• 替代方案：提供搜索框 + 筛选条件，后端用 WHERE 过滤后再分页，把100万行变成几千行再 OFFSET
• 或改用无限滚动（infinite scroll），每次加载基于上一批末尾的 cursor，本质还是键集分页，但用户感知更自然
• 警惕“伪分页”：某些ORM自动生成 OFFSET 语句却不暴露底层逻辑，查文档确认是否支持手动指定游标条件

键集分页真正难的不是写SQL，而是让前后端对齐游标格式、处理边界空值、以及在数据变更频繁时保证一致性——这些细节一旦漏掉，分页就会悄悄跳行或卡死。