跳到主要内容

LIMIT 和 OFFSET

LIMIT 限制最多返回多少行，OFFSET 跳过结果开头的若干行。它们常用于列表分页、预览数据和“取前 N 条”：

SELECT order_id, customer_id, created_at
FROM orders
ORDER BY created_at DESC, order_id DESC
LIMIT 20 OFFSET 40;

这条查询先按创建时间和订单编号倒序排列，再跳过 40 行，最多返回 20 行。可以理解为每页 20 条时读取第 3 页。

LIMIT 和 OFFSET 并不是所有数据库采用的标准拼写。本文先讲通用语义，再说明常见替代语法。

先排序，再限制行数​

没有 ORDER BY 时，数据库可以任意选择符合条件的行：

-- 只保证最多 10 行，不保证是哪 10 行
SELECT order_id, created_at
FROM orders
LIMIT 10;

若需求是“最新 10 张订单”，必须写出排序：

SELECT order_id, created_at
FROM orders
ORDER BY created_at DESC, order_id DESC
LIMIT 10;

逻辑上，查询先形成并排序完整结果，再应用 OFFSET 和 LIMIT。优化器可以使用索引或 Top-N 算法避免真的处理全部数据，但返回语义必须与这个顺序一致。

LIMIT 的基本用法​

SELECT product_id, product_name, price
FROM products
ORDER BY product_id
LIMIT 5;

结果最多 5 行；如果满足条件的行不足 5 行，就返回实际行数。LIMIT 0 通常返回空结果，但仍可能完成语法分析、权限检查和查询规划。

部分数据库允许 LIMIT ALL 表示不限制行数。可维护代码通常直接省略 LIMIT，因为 ALL 不是所有产品都支持。

应用程序应把限制值作为数值参数绑定，并在服务端设置合理上限：

LIMIT :page_size

驱动和数据库是否允许在此位置绑定参数存在差异。不要把未经校验的用户文本直接拼进 SQL。

OFFSET 的基本用法​

OFFSET n 跳过排序结果中的前 n 行：

SELECT product_id, product_name
FROM products
ORDER BY product_id
LIMIT 20 OFFSET 20;

通常：

offset = (page_number - 1) * page_size

页码从 1 开始、每页 20 条时，第 2 页的偏移量是 20。应用层计算时应防止负数、整数溢出和过大的页码。

只使用 OFFSET 而不限制行数，意味着跳过开头后返回所有剩余行，通常不适合面向用户的分页接口。

常见方言​

PostgreSQL、MySQL、SQLite 常见写法​

LIMIT 20 OFFSET 40

MySQL 还支持 LIMIT 40, 20，其中第一个数字是偏移量、第二个才是数量。为了减少参数顺序误读，优先使用含 OFFSET 关键字的形式。

标准 SQL 风格​

OFFSET 40 ROWS
FETCH NEXT 20 ROWS ONLY

只取开头若干行时可以写：

FETCH FIRST 20 ROWS ONLY

数据库对 FIRST、NEXT、ROW、ROWS 的允许组合可能不同。

SQL Server​

现代 SQL Server 常使用：

ORDER BY created_at DESC, order_id DESC
OFFSET 40 ROWS
FETCH NEXT 20 ROWS ONLY;

OFFSET ... FETCH 通常要求存在 ORDER BY。只取前若干行也常使用 TOP：

SELECT TOP (20) order_id, created_at
FROM orders
ORDER BY created_at DESC, order_id DESC;

迁移 SQL 时，应同时检查语法、参数绑定和执行计划，不要只替换关键字。

分页排序必须稳定​

下面的排序可能存在大量并列值：

ORDER BY created_at DESC
LIMIT 20 OFFSET 20

如果第 20 行附近有多条相同时间的记录，数据库可以在并列行之间采用任意顺序，导致某条记录在两页重复或完全漏掉。应增加唯一键作为最后的排序条件：

ORDER BY created_at DESC, order_id DESC
LIMIT 20 OFFSET 20

完整排序键组合需要唯一且在分页期间保持不变。若排序列本身会频繁更新，即使有唯一并列键，记录也可能因位置变化跨页移动。

OFFSET 分页的优点与局限​

基于页码的分页容易理解，也允许用户直接跳到指定页。它适合：

  • 数据量较小；
  • 页数不深；
  • 后台报表或低频查询；
  • 必须支持跳页的界面；
  • 数据在分页期间基本不变。

但它有两个重要局限。

深分页成本​

OFFSET 100000 不表示数据库可以瞬间从第 100001 行开始。数据库通常仍要定位或读取前面的大量行，然后丢弃它们：

SELECT order_id, created_at
FROM orders
ORDER BY created_at DESC, order_id DESC
LIMIT 20 OFFSET 100000;

偏移越深，工作量通常越大。即使索引支持排序，被跳过的索引项也往往仍需扫描。

并发变更导致漂移​

读取第 1 页后，如果前面插入了一条新记录，第 2 页的偏移位置就会整体后移，可能重复看到第 1 页末尾的记录。若前面删除记录，则可能漏掉一条。

事务隔离级别较高或数据库快照可以让多次查询看到同一版本，但长事务也会带来资源与并发成本，不能作为所有分页接口的默认方案。

基于游标的分页​

游标分页也称键集分页。客户端不提交页码，而是提交上一页最后一行的完整排序键：

SELECT order_id, customer_id, created_at
FROM orders
WHERE
  created_at < :last_created_at
  OR (created_at = :last_created_at AND order_id < :last_order_id)
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

排序是“时间倒序、编号倒序”，因此下一页读取比上一页末行更小的键。部分数据库支持等价的行值比较：

WHERE (created_at, order_id) < (:last_created_at, :last_order_id)
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

行值比较及其空值行为存在方言差异，展开条件通常更容易跨数据库使用。

游标分页的优势是：

  • 可以从索引边界继续查找，不必丢弃大量前置行；
  • 前面新增或删除记录时，后续页面通常不易漂移；
  • 性能不随页码线性恶化。

它的限制是：

  • 不适合直接跳到任意页；
  • 游标必须包含完整排序键；
  • 向前翻页需要反转比较方向和显示顺序；
  • 排序键更新后仍可能发生移动；
  • 混合升降序、NULL 和复杂表达式会让边界条件更复杂。

实际 API 常把排序键编码成不透明游标，客户端只负责原样传回。服务端仍应校验游标结构，并使用绑定参数。

为什么游标必须包含唯一键​

如果游标只有 created_at：

WHERE created_at < :last_created_at

上一页末尾可能还有其他相同时间的记录，它们会因严格小于条件而被遗漏。加入 order_id 后，可以准确描述上一页终点：

(created_at, order_id)

排序项、比较项、顺序方向和空值规则必须完全一致。任何一个缺失，都可能造成重复或遗漏。

WITH TIES：保留边界并列行​

部分数据库支持标准风格的 WITH TIES：

SELECT product_id, score
FROM products
ORDER BY score DESC
FETCH FIRST 10 ROWS WITH TIES;

如果第 10 名的分数有并列，结果会包含所有同分记录，因此实际行数可能超过 10。它适合排行榜的公平边界，不适合必须固定页面大小的接口。

是否支持 WITH TIES 及具体语法取决于数据库版本。有些产品通过 TOP ... WITH TIES 提供类似能力。

分页与总记录数​

分页列表经常还要展示总数。最直接的方法是单独统计：

SELECT COUNT(*)
FROM orders
WHERE status = 'paid';

列表查询与计数查询必须使用相同筛选条件，否则页数会不一致。复杂连接中还要确认统计的是连接行数还是业务实体数，必要时使用 COUNT(DISTINCT business_key)。

总数查询可能和列表查询一样昂贵，而且在并发修改下，两条语句的结果不一定来自同一数据快照。很多产品可以只返回“是否还有下一页”：请求 page_size + 1 行，若多出一行就设置 has_more = true，实际响应只返回前 page_size 行。

窗口函数也能在每一行附带总数：

SELECT
  order_id,
  created_at,
  COUNT(*) OVER () AS total_count
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, order_id DESC
LIMIT 20 OFFSET 0;

这种写法方便，但数据库通常仍需处理全部匹配行才能得出总数；应根据执行计划和接口需求选择。

限制修改行数要特别谨慎​

某些数据库允许在 UPDATE 或 DELETE 中使用 LIMIT、TOP 或其他行数限制。若没有确定排序和唯一条件，“任意修改 N 行”通常不可预测，而且各产品语法差异很大。

需要分批修改时，更稳妥的思路是先在受控查询中按唯一键选出目标，再按键执行修改，并考虑锁、并发和重试。不能把查询分页语法直接假定为修改语句的通用能力。

常见错误​

没有排序就取前 N 行​

只能保证数量，不能保证具体记录。先定义“前”的业务含义，再写 ORDER BY。

只按非唯一列分页​

并列行会在页面之间不稳定。使用完整且唯一的排序键。

对深分页性能抱有错误预期​

OFFSET 跳过的行并非没有成本。连续滚动、时间线和大数据列表优先评估游标分页。

游标条件与排序方向不一致​

倒序列表通常使用“小于上一页末键”读取下一页；升序则通常使用“大于”。混合方向必须逐项推导，不能机械套用。

无限制接受客户端页大小​

过大的 LIMIT 会增加数据库、网络和应用内存压力。服务端应设置默认值和最大值。

检查清单​

  1. 是否有明确、稳定且唯一的 ORDER BY；
  2. 页大小、页码和偏移量是否经过范围校验；
  3. 是否需要跳页，还是更适合游标分页；
  4. 深分页是否检查过执行计划与索引；
  5. 游标是否包含完整排序键并正确处理方向；
  6. 并发插入、删除或更新时允许怎样的页面变化；
  7. 是否真的需要精确总数；
  8. 目标数据库使用 LIMIT、FETCH 还是 TOP。

分页不是简单地截取数组。先定义稳定顺序，再根据跳页需求、数据规模和并发变化选择 OFFSET 或游标，才能同时保证正确性与性能。