如何实现分页? [英] How do I implement pagination?

问题描述

我有一个People表(Id, first_name, last_name)，其中主键是id.我希望能够在(last_name, first_name, Id)排序的表中查找前N个人.在某些情况下，我需要查找下一个N个人，依此类推.我想有效地做到这一点.最好的方法是什么?

I have a People table (Id, first_name, last_name), where the primary key is id. I want to be able to look up the first N people in the table ordered by (last_name, first_name, Id). In some cases, I need to lookup the next N people, and so on. I want to do this efficiently. What is the best way to do this?

推荐答案

有两种主要方法:

• 使用LIMIT和OFFSET
• 使用LIMIT和上一页密钥

• Use LIMIT and OFFSET
• Use LIMIT and key-of-previous-page

OFFSET策略允许您读取任意页面，但是效率不高，因为每次查询运行时，它必须读取所有先前页面中的行.这是最容易实现的方法，并且可以作为可接受的策略(尤其是如果您只需要前几页的话)，但是通常不建议这样做.上一页键策略确实要求按顺序读取页面，但是效率更高，因为每个页面仅读取所需的行.

The OFFSET strategy lets you read an arbitrary page, but is not efficient since each time the query runs it must read the rows from all previous pages. It is the easiest to implement and can be an acceptable strategy (particularly if you only want the first few pages), but in general it is not recommended. The key-of-previous-page strategy does require pages are read in order, but is more efficient because each page reads only the rows it needs.

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

So let's start with the original query to fetch results from your table ordered by (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

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

You will probably want to ensure that your queries all view a consistent snapshot of your database's data, so you'll want to make sure that your sequence of queries always reads from the same timestamp. The easiest way to accomplish this is for your first query to be a ReadOnly transaction with returnReadTimestamp set to true. Then, your subsequent queries can also be ReadOnly transactions, and they should use the same timestamp returned by the original query as their readTimestamp. Note that -- whatever approach you choose -- the ORDER BY clause is vital to assure consistent results across your sequence of queries. Let's say the last row returned is (1709, "John", "Smith"). Then your first attempt at a query to get the next page of results might look like this:

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值.而且您需要非常小心浮点数和NaN值. Cloud Spanner的Read API在这种情况下也很有用，因为它使在表上进行范围扫描分页更容易.

The middle WHERE clause is new. But writing this predicate is trickier than you might think. You may have to handle NULL values. You have to handle the case where there are multiple people named John Smith with different id values. And you would need to be really careful with floating point numbers and NaN values. Cloud Spanner's Read API can also be useful in cases like this, as it makes it easier to paginate a range scan on a table.

这篇关于如何实现分页?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！