PostgreSQL - LIMIT 子句

PostgreSQL - LIMIT 和 OFFSET 子句

PostgreSQL 的 LIMIT 和 OFFSET 子句对于实现分页至关重要，分页是将大型结果集划分为更小、更易于管理的“页面”数据的实践。LIMIT 限制返回的行数，而 OFFSET 在开始返回行之前跳过指定数量的行。

语法

LIMIT 和 OFFSET 子句位于 SELECT 语句的末尾。

SELECT column1, column2, ...
FROM table_name
ORDER BY sort_expression
LIMIT row_count OFFSET offset_value;

其中：

• row_count：要返回的最大行数。
• offset_value：从结果集开头跳过的行数。
• ORDER BY：对于稳定和可预测的结果至关重要。

ORDER BY 的重要性

关键最佳实践： 始终将 ORDER BY 子句与 LIMIT 和 OFFSET 一起使用。没有 ORDER BY，数据库不保证返回行的顺序。这意味着两次运行相同的查询可能会产生不同的结果，使您的分页不可靠。

示例

考虑以下 products 表：

id | product_name      | price
----+-------------------+-------
 1  | Quantum Keyboard  | 75.00
 2  | Nebula Mouse      | 25.00
 3  | Galaxy Headset    | 50.00
 4  | Stellar Monitor   | 300.00
 5  | Cosmic Webcam     | 60.00
 6  | Supernova Mousepad| 15.00
 7  | Meteor Microphone | 120.00

获取前 N 行

要获取 3 个最昂贵的产品，我们将 ORDER BY 与 LIMIT 结合使用。

SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 3;

这将产生可预测的结果：

 product_name    | price
-------------------+--------
 Stellar Monitor   | 300.00
 Meteor Microphone | 120.00
 Quantum Keyboard  | 75.00
(3 rows)

实现分页

假设我们想在每页显示 3 个产品。为了获取第二页，我们使用 LIMIT 和 OFFSET。

-- 第二页：跳过前 3 个产品 (3 * (2-1))，然后获取接下来的 3 个。
SELECT product_name, price
FROM products
ORDER BY id ASC -- 按 ID 排序以实现稳定的分页
LIMIT 3 OFFSET 3;

这将产生以下结果：

 product_name     | price
------------------+--------
 Stellar Monitor  | 300.00
 Cosmic Webcam    | 60.00
 Supernova Mousepad| 15.00
(3 rows)

性能考量与替代方案

虽然 OFFSET 易于使用，但当偏移量很大（例如 OFFSET 100000）时，它可能会变得低效。这是因为数据库在丢弃这些行之前，仍然必须在内部生成所有 100,000 行。

对于大型数据集上的高性能分页，推荐使用一种称为键集分页（或基于游标的分页）的技术。这种方法使用 WHERE 子句根据前一页最后一行中的值来过滤行，从而避免了使用 OFFSET 的需要。

-- 键集分页示例：获取 id=3 的行之后的页面
SELECT product_name, price
FROM products
WHERE id > 3 -- “键集”条件
ORDER BY id ASC
LIMIT 3;