SQL OFFSET 使用场景与注意事项

offset分页易出错因仅按行数偏移，无法应对数据增删导致的重复或跳过；应优先采用基于created_at和id的游标分页（如where created_at

OFFSET 在分页查询中为什么经常出错

因为 OFFSET 本身不解决“数据变动导致的重复或跳过”问题，只按行数偏移。当上游数据在两次查询间被插入或删除，OFFSET 20 下次可能指向新插入的记录，也可能跳过刚删掉的那条。

• 真实分页应优先用游标（cursor-based）方案，比如基于 created_at 和 id 的范围查询：WHERE created_at
• OFFSET 适合后台管理类场景（数据静默、用户容忍跳变），不适合高并发读写列表（如朋友圈、订单流）
• mysql 8.0+、postgresql 支持 FETCH FIRST n ROWS ONLY，但和 OFFSET 是绑定使用的，不能替代它

OFFSET 性能崩坏的典型表现

执行 select * FROM orders ORDER BY id DESC OFFSET 100000 LIMIT 20 时，数据库仍需扫描前 100000 行再丢弃——不是跳过索引位置，而是跳过结果集行数。

• PostgreSQL 中，如果排序字段有索引，OFFSET 越大，I/O 和 CPU 消耗越接近线性增长
• MySQL 在 ORDER BY 字段无索引时，会触发 filesort + 临时表，OFFSET 加剧内存溢出风险
• 替代思路：用延迟关联（deferred join）减少回表，例如先 SELECT id FROM orders ORDER BY id DESC LIMIT 20 OFFSET 100000，再用 IN 查详情

不同数据库对 OFFSET 的语法容忍度

标准 SQL 要求 OFFSET 必须跟在 ORDER BY 后面，但各数据库实现松紧不一。

• PostgreSQL 强制要求 ORDER BY 存在，否则报错：Error: OFFSET clause is not supported unless ORDER BY is also specified
• MySQL 5.7 允许无 ORDER BY 使用 OFFSET，但结果不可预测（引擎决定顺序）
• sqlite 接受 OFFSET 单独使用，但文档明确警告：“without ORDER BY, the order is arbitrary”
• SQL Server 不支持 OFFSET 原生语法，得用 OFFSET-FETCH（且必须搭配 ORDER BY）

OFFSET 和 LIMIT 组合时的常见误用

很多人以为 LIMIT 20 OFFSET 20 就是“第 2 页”，但没意识到这和业务语义脱节：页码从 1 开始，而偏移量从 0 开始，换算稍有不慎就错位。

• 计算公式必须是：OFFSET = (page_number - 1) * page_size，不是 page_number * page_size
• 前端传参为 page=1&size=20 时，后端若写成 OFFSET #{page} * #{size}，第一页就跳过了前 20 行
• 某些 ORM（如 django ORM）封装了 .offset().limit()，但底层仍是字符串拼接，参数未校验会导致 SQL 注入，务必用参数化查询

OFFSET 看似简单，但它把“物理行序”和“逻辑页序”的耦合暴露得特别彻底。真正难的不是写对语法，而是判断此刻该不该用它。