SQL 中 ORDER BY 和 LIMIT/OFFSET 分页有哪些注意点？

简化版

分页必须使用稳定排序，ORDER BY 最好带唯一键兜底；深分页时 OFFSET 会扫描并丢弃大量行，常用基于游标或最后一条记录的 keyset pagination 优化。

详细版

LIMIT/OFFSET 可以实现简单分页，例如 LIMIT 20 OFFSET 40 表示跳过前 40 行再取 20 行。但如果没有 ORDER BY，数据库不保证返回顺序，分页可能出现重复或漏数据。

即使有 ORDER BY created_at DESC，如果多条数据的 created_at 相同，顺序仍可能不稳定。工程上通常写成 ORDER BY created_at DESC, id DESC，用唯一字段作为第二排序键。

深分页是另一个高频问题。OFFSET 100000 LIMIT 20 往往需要先找到并跳过 100000 行，代价很高。对于按时间流、列表流翻页的场景，可以改成 WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20，从上一页最后一条继续取。

完整版教学

一、分页为什么必须先谈排序

SQL 表本质上是无序集合。没有 ORDER BY 时，数据库可以按主键、索引、物理存储、并行执行结果等任意方式返回行。

下面的写法看起来像第一页，实际没有稳定含义：

SELECT id, title
FROM posts
LIMIT 10 OFFSET 0;

今天它可能返回 1~10，明天换了执行计划可能返回另一组。分页依赖“第 1 页、第 2 页”的顺序概念，所以必须先定义顺序。

SELECT id, title
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 10 OFFSET 0;

记忆钩子：没有稳定 ORDER BY 的分页，不是分页，只是随机切片。

二、为什么排序键要能唯一兜底

很多业务只写 ORDER BY created_at DESC，但同一秒可能插入 100 条记录。只按时间排序时，这 100 条记录内部的顺序没有被完全定义。

示例数据：

id | created_at
10 | 2026-07-29 10:00:00
11 | 2026-07-29 10:00:00
12 | 2026-07-29 10:00:00
13 | 2026-07-29 09:59:59

如果第一页取 2 条，第二页再取 2 条，数据库两次查询中对相同时间行的排列发生变化，就可能重复看到 id=11 或漏掉 id=12。

排序方式	是否完全稳定	风险
无 `ORDER BY`	否	顺序不确定
`ORDER BY created_at DESC`	不一定	时间相同的行内部不确定
`ORDER BY created_at DESC, id DESC`	是	`id` 唯一兜底

三、OFFSET 的执行代价在哪里

OFFSET 的语义是“跳过 N 行”。数据库不能凭空知道第 N+1 行在哪里，它通常要按排序条件找到前 N+M 行，再丢弃前 N 行。

SELECT id, title
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

如果每页 20 条，第 5001 页需要越过 100000 条记录。即使有索引，数据库仍要沿索引走过大量位置；如果还要回表取字段，成本会更高。

粗略流程：

按索引顺序读取 100020 条 -> 丢弃 100000 条 -> 返回 20 条

这就是深分页慢的根源：不是返回的 20 条慢，而是“跳过”的 100000 条贵。

四、keyset pagination 怎么工作

keyset pagination 也叫 seek pagination，核心是不再传“页码”，而是传上一页最后一条记录的排序键。

第一页：

SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;

假设最后一条是：

created_at = '2026-07-29 09:30:00'
id = 881

SELECT id, title, created_at
FROM posts
WHERE (created_at < '2026-07-29 09:30:00')
   OR (created_at = '2026-07-29 09:30:00' AND id < 881)
ORDER BY created_at DESC, id DESC
LIMIT 20;

它直接从“上一页之后”继续找，不需要跳过 100000 行。适合信息流、订单列表、日志列表这类“下一页”场景。

五、索引要服务于过滤和排序

分页性能不只看 SQL 语法，还要看索引是否匹配 WHERE 与 ORDER BY。

例如列表按状态过滤、按时间倒序：

SELECT id, title, created_at
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 20;

常见索引设计可以是：

CREATE INDEX idx_posts_status_created_id
ON posts(status, created_at, id);

这样数据库可以先定位 status='published' 的范围，再按时间和 id 的顺序读取。若索引不匹配，可能出现额外排序，数据量大时就会明显变慢。

六、分页中的并发变化问题

分页常遇到一个现实问题：用户看第一页后，期间有人插入或删除数据，第二页内容会发生位移。

假设每页 3 条：

初始顺序：A B C | D E F
用户看第一页：A B C
新插入 X 到最前：X A B | C D E
用户用 OFFSET 3 看第二页：C D E

此时 C 被重复看到。keyset pagination 用上一页最后一条 C 作为边界时，下一页会取 D E F，更适合持续变化的数据流。

当然，后台管理系统需要“跳到第 50 页”时，OFFSET 仍然简单可用；核心是知道它的适用边界。

七、常见误区与追问

误区：LIMIT 不配 ORDER BY 也能稳定分页。 SQL 不保证无排序结果的顺序，分页可能重复或漏数据。
误区：只按时间排序就一定稳定。 时间字段可能重复，最好追加唯一键作为兜底排序。
误区：深分页慢是因为返回行太多。 深分页通常只返回少量行，真正贵的是跳过大量行。
误区：keyset pagination 可以完全替代页码分页。 它适合连续翻页，不适合任意跳页和精确总页数场景。
追问：为什么要写 created_at DESC, id DESC？ created_at 表达业务顺序，id 解决同一时间下的确定性。
追问：怎么验证分页索引是否生效？ 使用 EXPLAIN 看是否利用索引过滤和排序，避免大范围扫描和额外排序。

八、加强记忆

分页题抓三层：先问有没有稳定 ORDER BY，再问排序键是否唯一，最后问页码是否过深。浅分页可以 LIMIT/OFFSET，深分页优先考虑 keyset；列表排序字段和过滤字段要一起进入索引设计。

简化版

详细版