PostgreSQL / 系统课时

限制与分页查询

限制与分页查询

本课解决查询结果过多时如何分批返回的问题。你将使用 LIMIT 和 OFFSET 完成基础分页,并理解为什么数据量增大后应改用基于排序键的游标分页。

学习目标

  • 使用 LIMIT 限制返回行数
  • 使用 OFFSET 跳过指定数量的记录
  • 识别深分页问题并选择更稳定的分页方式

使用 LIMIT 截取结果

LIMIT n 表示最多返回 n 行。分页查询必须配合稳定的 ORDER BY,否则数据库可以按不同执行计划返回不同顺序,导致相邻页面出现重复或遗漏。下面的示例创建商品数据,并分别查询前两页:

sql
DROP TABLE IF EXISTS products;

CREATE TABLE products (
    id integer PRIMARY KEY,
    name varchar(50) NOT NULL,
    price numeric(10, 2) NOT NULL
);

INSERT INTO products (id, name, price) VALUES
    (1, '键盘', 199.00),
    (2, '鼠标', 99.00),
    (3, '显示器', 1299.00),
    (4, '耳机', 299.00),
    (5, '摄像头', 399.00);

SELECT id, name, price
FROM products
ORDER BY id
LIMIT 2;

SELECT id, name, price
FROM products
ORDER BY id
LIMIT 2 OFFSET 2;

预期结果

text
第一页:
 id | name | price
----+------+--------
  1 | 键盘 | 199.00
  2 | 鼠标 |  99.00

第二页:
 id | name   | price
----+--------+---------
  3 | 显示器 | 1299.00
  4 | 耳机   |  299.00

OFFSET 的计算方式

假设每页显示 pageSize 条,第 pageNo 页的偏移量为:

OFFSET = (pageNo - 1) × pageSize

例如每页 20 条,第 3 页需要跳过前 40 条,即使用 LIMIT 20 OFFSET 40。页码通常从 1 开始,应用层应校验页码和每页数量,避免负数或过大的请求。

深分页为什么变慢

OFFSET 不会让数据库直接跳到目标位置。偏移量很大时,PostgreSQL 仍需读取、排序并丢弃前面的记录,再返回当前页,因此越往后查询成本越高。

当表中有大量数据时,可以记住上一页最后一条记录的排序键,再查询“比它更大”的记录。以下片段依赖前面创建的 products 表:

sql
SELECT id, name, price
FROM products
WHERE id > 2
ORDER BY id
LIMIT 2;

预期结果

text
 id | name   | price
----+--------+---------
  3 | 显示器 | 1299.00
  4 | 耳机   |  299.00

这种方式称为游标分页或键集分页。只要 id 有索引,数据库就能从目标位置继续扫描,适合连续翻页和大数据量列表。

分页查询的关键边界

排序字段存在重复值时,应追加唯一字段保证顺序稳定,例如 ORDER BY created_at DESC, id DESC。如果只按时间排序,同一时间产生的记录可能在翻页时交换位置。

页面数据频繁新增或删除时,OFFSET 分页的页边界可能变化。后台管理列表可接受这种变化;订单流水、消息流等连续浏览场景更适合游标分页。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

限制与分页查询

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通