google-cloud-platform - 如何实现分页？

Question

我有一个People 表(Id, first_name, last_name)，其中主键是id。我希望能够在按 (last_name, first_name, Id) 排序的表中查找前 N 个人。在某些情况下，我需要查找下 N 个人，等等。我想有效地做到这一点。执行此操作的最佳方法是什么？

Answer 1

主要有两种方式:

• 使用LIMIT 和OFFSET
• 使用LIMIT 和key-of-previous-page

OFFSET 策略允许您读取任意页面，但效率不高，因为每次运行查询时，它都必须读取所有先前页面的行。这是最容易实现的，也是可以接受的策略(特别是如果您只想要前几页)，但通常不推荐这样做。 key-of-previous-page 策略确实需要按顺序读取页面，但效率更高，因为每个页面只读取它需要的行。

因此，让我们从原始查询开始，以从按 (LastName, FirstName, Id) 排序的表中获取结果:

SELECT
  t.id, 
  t.first_name, 
  t.last_name
FROM
  People as t
ORDER BY
  t.last_name,
  t.first_name,
  t.id
LIMIT
  @limit_rows

您可能希望确保您的所有查询都查看数据库数据的一致快照，因此您需要确保您的查询序列始终从相同的时间戳读取。完成此操作的最简单方法是将您的第一个查询设为 ReadOnly 事务，并将 returnReadTimestamp 设置为 true。然后，您的后续查询也可以是 ReadOnly 事务，并且它们应该使用原始查询返回的相同时间戳作为它们的 readTimestamp。请注意，无论您选择哪种方法，ORDER BY 子句对于确保整个查询序列的结果一致至关重要。假设返回的最后一行是 (1709, "John", "Smith")。那么您第一次尝试获取下一页结果的查询可能如下所示:

SELECT
  t.id, 
  t.first_name, 
  t.last_name
FROM
  People as t
WHERE
  t.last_name > "Smith"
  OR
  (t.last_name = "Smith" and t.first_name > "John")
  OR
  (t.last_name = "Smith" and t.first_name = "John" AND t.id > 1709)
ORDER BY
  t.last_name,
  t.first_name,
  t.id
LIMIT
  @limit_rows

中间的 WHERE 子句是新的。但是编写这个谓词比您想象的要棘手。您可能必须处理 NULL 值。您必须处理多个名为 John Smith 且具有不同 id 值的人的情况。并且您需要非常小心地处理 float 和 NaN 值。 Cloud Spanner 的读取 API 在这种情况下也很有用，因为它可以更轻松地对表格的范围扫描进行分页。