如何在SQL中实现分页查询？OFFSET与FETCH的正确用法-SQL-PHP中文网

如何在SQL中实现分页查询？OFFSET与FETCH的正确用法

雪夜

发布： 2025-09-04 19:19:01

原创

377人浏览过

答案是使用OFFSET和FETCH NEXT结合ORDER BY实现分页，核心在于通过ORDER BY确保排序确定性，OFFSET跳过指定行数，FETCH NEXT获取所需行数；例如SELECT column FROM table ORDER BY col OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY可获取第2页数据（每页10条）；相比LIMIT/OFFSET，OFFSET/FETCH符合SQL标准且适用于SQL Server等数据库；但大偏移量时性能差，因需扫描跳过行；解决方法包括键集分页（利用上一页最后ID作为下一页起点）、为ORDER BY列建立索引、根据访问模式选择分页策略，以及使用缓存或物化视图优化频繁查询。

如何在sql中实现分页查询？offset与fetch的正确用法

在SQL中实现分页查询，现代且标准的方法是利用

OFFSET

登录后复制

和

FETCH NEXT

登录后复制

子句。它允许你跳过指定数量的行，然后获取接下来的若干行，这对于构建用户界面中的数据列表或API分页非常有效。

解决方案

实现分页查询，核心在于结合

ORDER BY

登录后复制

、

OFFSET

登录后复制

和

FETCH NEXT

登录后复制

。

ORDER BY

登录后复制

是不可或缺的，因为它决定了行的顺序，没有它，分页结果将是不可预测的。

一个基本的分页查询通常看起来像这样：

SELECT column1, column2, ...
FROM YourTable
ORDER BY YourSortColumn ASC/DESC
OFFSET @PageNumber * @PageSize ROWS -- 跳过多少行
FETCH NEXT @PageSize ROWS ONLY;      -- 获取多少行

登录后复制

这里，

@PageNumber

登录后复制

通常是从0开始的页码（或者从1开始，但计算时需要调整），

@PageSize

登录后复制

是每页显示的记录数。例如，要获取第二页（假设页码从0开始，每页10条），

@PageNumber

登录后复制

就是1，

@PageSize

登录后复制

就是10，那么

OFFSET

登录后复制

就是10，

FETCH NEXT

登录后复制

就是10。

为什么传统的LIMIT/OFFSET在某些场景下不再是最佳选择？

说实话，当我们谈到分页，很多开发者脑子里第一反应可能是

LIMIT

登录后复制

和

OFFSET

登录后复制

。这在MySQL和PostgreSQL中确实很常见，也很好用。但如果你在SQL Server或者Oracle这样的环境中工作，或者希望遵循更广泛的SQL标准，

OFFSET

登录后复制

和

FETCH NEXT

登录后复制

就显得更“正统”一些。我个人觉得，

LIMIT/OFFSET

登录后复制

虽然简洁，但在不同数据库间的语法差异确实是个小麻烦，比如SQL Server就没有直接的

LIMIT

登录后复制

。

更重要的是，

LIMIT/OFFSET

登录后复制

（以及

OFFSET/FETCH

登录后复制

）在处理非常大的偏移量时，性能可能会遇到瓶颈。数据库需要先处理（甚至排序）所有被跳过的行，即使我们最终并不需要它们。这就像你要从一本很厚的书里找第1000页的第10行，你还是得先翻过前面999页。对于小数据集或者浅层分页，这问题不大，但对于百万级甚至千万级数据，而且用户可能想跳到很后面的页面时，性能下降会非常明显。

另一个我常强调的点是，无论是哪种分页方式，

ORDER BY

登录后复制

都必须有。没有它，数据库返回的行顺序是不确定的。今天你看到的“第一页”数据，明天可能就完全不同了，这在实际应用中是绝对不能接受的。我见过不少新手开发者忽略了这一点，导致用户界面数据跳来跳去，非常困扰。

OFFSET FETCH NEXT的实际应用与常见陷阱

OFFSET FETCH NEXT

登录后复制

在实际开发中简直是标配。无论是Web应用的数据表格、移动端App的列表，还是后台管理系统的报表，只要需要分批加载数据，它都能派上用场。我经常用它来实现“无限滚动”效果，用户滚动到底部时，就加载下一页数据。

蓝心千询

蓝心千询是vivo推出的一个多功能AI智能助手

查看详情

然而，在使用过程中，有一些坑是我们需要特别留意的：

首先，我必须再次强调，缺少

ORDER BY

登录后复制

是最大的陷阱。没有它，你的分页结果将是混乱且不可预测的。数据库可能根据内部存储顺序、查询计划或其他非确定性因素返回数据，这会导致用户看到重复数据或漏掉某些数据。比如，你查询“商品列表”，第一页是A、B、C，第二页是D、E、F。如果没

ORDER BY

登录后复制

，下次刷新，第一页可能就变成D、A、C了，完全乱套。

其次，大偏移量下的性能问题。正如前面提到的，即使是

OFFSET FETCH NEXT

登录后复制

，如果

OFFSET

登录后复制

值非常大，数据库仍然需要扫描或排序大量的行才能到达你想要的起点。这会消耗大量的CPU和I/O资源。我曾经在一个项目中遇到过，用户尝试直接跳转到几千页之后，整个查询直接超时，导致用户体验极差。这时候，我们可能需要考虑更高级的优化手段。

再者，并发修改导致的数据不一致。设想一下，你在获取第一页数据后，有新的数据被插入或旧数据被删除。当你请求第二页时，由于数据行的增减，你可能会看到第一页的最后几条数据再次出现在第二页，或者直接跳过了一些数据。这种情况在数据更新频繁的系统中尤为常见。这通常需要应用层的一些策略来缓解，比如使用快照隔离级别，或者基于时间戳/ID的“游标分页”方式。

-- 一个带有ORDER BY的正确分页示例
SELECT ProductID, ProductName, Price
FROM Products
WHERE CategoryID = 101
ORDER BY Price DESC, ProductID ASC -- 先按价格降序，再按ID升序，确保唯一排序
OFFSET 20 ROWS                      -- 跳过前20条记录
FETCH NEXT 10 ROWS ONLY;            -- 获取接下来的10条记录

登录后复制

优化大型数据集分页查询的策略与思考

当

OFFSET FETCH NEXT

登录后复制

在大数据集下开始显得力不从心时，我们不能坐以待毙。这里有一些我个人实践中觉得非常有效的策略：

1. 键集分页（Keyset Pagination），也被称为“游标分页”或“Seek Method”： 这是我处理深层分页的首选方案。它不依赖于行数偏移，而是依赖于“上一页最后一条记录的某个唯一标识符”。例如，如果你的数据是按

ID

登录后复制

排序的，那么下一页的查询条件就是

WHERE ID > [上一页最后一条记录的ID] ORDER BY ID ASC LIMIT N

登录后复制

。这种方式的优点是数据库不需要扫描所有被跳过的行，它直接“跳”到你上次停止的地方。性能提升是巨大的，因为它能充分利用索引。缺点是它通常只能“下一页”，不能直接跳到任意页，或者“上一页”的逻辑会稍微复杂一点。

-- 键集分页示例 (假设ProductID是唯一的，且按此排序)
SELECT ProductID, ProductName, Price
FROM Products
WHERE ProductID > @LastProductIDOnPreviousPage -- @LastProductIDOnPreviousPage 是上一页最后一条记录的ProductID
ORDER BY ProductID ASC
FETCH NEXT 10 ROWS ONLY;

登录后复制

2. 确保