PostgreSQL分页查询频频“见鬼”？排序“踩坑”，数据“隐身”！

张家辉的DB演武场 2025-08-06

341

分页查询频频出错，部分记录反复出现，部分记录又莫名消失。这“神秘”的现象，究竟是数据库引擎的BUG，还是程序员的失误？今天我们就来拆解 PostgreSQL 分页查询中这个令人头疼的 “坑”，通过实际案例带你看清问题本质，找到解决方案。

案例重现：数据为何 “时隐时现”？

先来看一个典型场景。我们创建一张 students 表，包含 id（主键）、name、age 三个字段，并插入 13 条测试数据：

-- 创建表结构
CREATE TABLE students(
  id int primary key,
  name varchar,
  age int
);


-- 插入测试数据
INSERT INTO students VALUES 
(1,'a',22), (2,'b',22), (3,'c',22),
(4,'d',22), (5,'e',23), (6,'f',22),
(7,'g',22), (8,'h',23), (9,'i',22),
(10,'j',22), (11,'k',24), (12,'l',24),
(13,'m',22);

现在，我们按 age 字段排序，每页查询 3 条数据，结果却出现了诡异现象：

第一页（LIMIT 3 OFFSET 0）：

testdb=# SELECT * FROM students ORDER BY age LIMIT 3;
 id | name | age
----+------+-----
  2 | b    |  22
  3 | c    |  22
  1 | a    |  22
(3 rows)

第二页（LIMIT 3 OFFSET 3）：

testdb=# SELECT * FROM students ORDER BY age LIMIT 3 OFFSET 3;
 id | name | age
----+------+-----
  2 | b    |  22  -- 重复出现
  6 | f    |  22
  7 | g    |  22
(3 rows)

第三页（LIMIT 3 OFFSET 6）：

testdb=# SELECT * FROM students ORDER BY age LIMIT 3 OFFSET 6;
 id | name | age
----+------+-----
 10 | j    |  22
  1 | a    |  22  -- 重复出现
  6 | f    |  22  -- 重复出现
(3 rows)

更奇怪的是，当我们查询全量数据时，所有记录都完好存在，“m”、“d”、“i” 等 “失踪” 的记录明明都在：

testdb=# SELECT * FROM students ORDER BY age ;
 id | name | age
----+------+-----
 13 | m    |  22
  2 | b    |  22
  3 | c    |  22
  4 | d    |  22
  7 | g    |  22
  9 | i    |  22
 10 | j    |  22
  1 | a    |  22
  6 | f    |  22
  5 | e    |  23
  8 | h    |  23
 11 | k    |  24
 12 | l    |  24
(13 rows)

结果显示，13 条记录完整，其中 age=22 的有 9 条（包含 d、i、m），明明数据没丢，分页查询却像 “筛子” 一样漏数据，还出现重复的数据。

根源解析：非确定性排序的 “隐形陷阱”

答案就藏在 ORDER BY age 这个排序逻辑里。当排序字段（这里的 age）存在大量重复值时，PostgreSQL 对这些 “等值记录” 的相对顺序没有明确规则，这种排序被称为 “非确定性排序”。数据库会根据一些 “隐藏因素” 临时决定这些记录顺序：

记录在磁盘上的物理存储位置（比如数据页内的偏移量）；
索引扫描时的随机访问顺序（若使用非唯一索引）；
并发操作（如插入、更新）导致的数据页变化；
执行计划差异（比如两次查询用了不同的扫描方式）。

这些 “隐藏因素” 极不稳定，可能在两次查询之间发生变化。而分页查询依赖 OFFSET 来 “切割” 数据（比如 OFFSET 3 表示 “跳过前 3 条”），一旦记录顺序改变，“前 3 条” 的范围就会偏移，最终导致：

前一页的记录 “跑到” 下一页（重复）；
本该出现的记录被 “跳过”（缺失）。

比如案例中 age=22 的 9 条记录，第一次查询时顺序可能是 2→3→1→4→6→7→...，第二次查询可能变成 3→1→2→6→4→7→...，OFFSET 切割的位置变了，结果自然混乱。

解决方案：让排序 “绝对唯一”

要避免分页异常，核心是让排序规则具有唯一性：即使排序字段的值相同，也能通过其他字段的值来确定唯一顺序。

最可靠的做法是：在 ORDER BY 中补充一个唯一字段（通常是主键，因为主键天然唯一且不变）。

案例中的查询，我们可以增加 id 作为第二排序字段，即按 age 排序，age 相同时则再按 id 排序，以下是优化后的结果：

testdb=# SELECT * FROM students ORDER BY age,id LIMIT 3;
 id | name | age
----+------+-----
  1 | a    |  22
  2 | b    |  22
  3 | c    |  22
(3 rows)


testdb=# SELECT * FROM students ORDER BY age,id LIMIT 3 OFFSET 3;
 id | name | age
----+------+-----
  4 | d    |  22
  6 | f    |  22
  7 | g    |  22
(3 rows)


testdb=# SELECT * FROM students ORDER BY age,id LIMIT 3 OFFSET 6;
 id | name | age
----+------+-----
  9 | i    |  22
 10 | j    |  22
 13 | m    |  22
(3 rows)

postgresql 分页

文章转载自张家辉的DB演武场，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL分页查询频频“见鬼”？排序“踩坑”，数据“隐身”！

评论