高性能分页的常见解决方案是使用索引字段,从前一页的最后一个值开始每个新的"页面"。例如,对于这样的数据集(假设类别和 ID 是主键):
Category | ID | Name
Red | 10 | Bob Jones
Red | 14 | Sam Smith
Red | 16 | Jill White
Blue | 10 | Mike Green
Blue | 16 | Mary Brown
假设页面大小(相当小)为 1,如果我们想返回所有Red
类别记录(假设 ORDER BY Category, ID):
SELECT * FROM table WHERE Category='Red' AND ID>'00' (1st page, returns Bob Jones)
SELECT * FROM table WHERE Category='Red' AND ID>'10' (2nd page, returns Sam Smith)
SELECT * FROM table WHERE Category='Red' AND ID>'14' (3rd page, returns Jill White)
这是有效的,因为通过分页,"键集"仅使用 ID 字段(如果 ID 是全局唯一的,它也可以在多个字段上使用,但事实并非如此)。
但是,如果我想返回所有红色和蓝色记录(假设该表还包含其他类别),则一次仍然一页(假设按类别排序,ID):
SELECT * FROM table WHERE Category IN ['Red', 'Blue'] AND Category>'' AND ID>'00' (1st page, returns Bob Jones)
SELECT * FROM table WHERE Category IN ['Red', 'Blue'] AND Category>'Red' AND ID>'10' (2nd page, returns Sam Smith, but skips Mike Green)
在PostgreSQL和其他一些文章中,有一个"行值"谓词语法支持这一点(假设ORDER BY Category,ID):
SELECT * FROM table WHERE (Category, ID) > ('', '00') (1st page, returns Bob Jones)
SELECT * FROM table WHERE (Category, ID) > ('Red', '10') (2nd page, returns Sam Smith)
它之所以有效,是因为出于测试目的,类别和 ID 都被视为单个复合值。但我没有使用PostgreSQL或支持"行值"的数据库。所以问题是是否有替代解决方案可以解决这个问题(是否有 2 个或 n 个字段)?为了使它适用于多个变量字段的分页,我需要设备一个谓词,该谓词将始终在多字段排序顺序中找到"下一条记录"。
PS:偏移/限制或跳过/限制分页当然有效,但在大型数据集上两者都没有效率,这就是我尝试使用"键集"分页的原因。
扩展 The Impaler 的答案,使用组合键进行键集分页的通用语法如下:
WHERE
(x > a) OR
(x = a AND y > b) OR
(x = a AND y = b AND z > c) OR
...
这不如(x, y, z) > (a, b, c)
,但你可以用你选择的语言生成SQL。循环访问组合字段集并展开每个连续字段以包含前面{field} = {value} AND
字段。
你总是可以表达谓词:
(x, y) > (a, b)
如:
x >= a and (x = a and y > b or x > a)
请注意,第一个预x >= a
促进(它不能确保)在该列上使用索引。也就是说,它成为"访问谓词"。第二个x = a and y > b or x > a
过滤掉多余的行,有效地成为"过滤谓词"。
这种措辞"元组不等式"谓词的方式促进了索引的使用。但是,如果要比较 3、4 或更多列,它们会变得越来越复杂。