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.00OFFSET 的计算方式
假设每页显示 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 分页的页边界可能变化。后台管理列表可接受这种变化;订单流水、消息流等连续浏览场景更适合游标分页。
按自己的节奏,确认真正理解。