如何在API中实现游标以进行分页 [英] How to implement cursors for pagination in an api

问题描述

这类似于此问题，没有任何答案.我已经阅读了有关如何在 twitter ，

This is similar to to this question which doesn't have any answers. I've read all about how to use cursors with the twitter, facebook, and disqus api's and also this article about how disqus generally built their cursors, but I still cannot seem to grok the concept of how they work and how to implement a similar solution in my own projects. Can someone explain specifically the different techniques and concepts behind them?

推荐答案

让我们首先了解为什么大型数据集的偏移分页失败的原因.

Lets first understand why offset pagination fails for large data sets with an example.

客户提供两个参数 limit 用于结果数量， 偏移 和页面偏移. 例如，在offset = 40，limit = 20的情况下，我们可以告诉数据库返回前20个项目，而跳过前40个项目.

Clients provide two parameters limit for number of results and offset and for page offset. For example, with offset = 40, limit = 20, we can tell the database to return the next 20 items, skipping the first 40.

缺点:

• 使用LIMIT OFFSET 不能很好地扩展 数据集.随着偏移量的增加，您在 数据集，数据库仍必须读取直到偏移量+计数行 磁盘中，在放弃偏移量且仅返回计数之前 行.
• 如果将项目频繁地写入数据集中，则 页面窗口变得不可靠，可能会跳过或返回 重复结果.

• Using LIMIT OFFSET doesn’t scale well for large datasets. As the offset increases the farther you go within the dataset, the database still has to read up to offset + count rows from disk, before discarding the offset and only returning count rows.
• If items are being written to the dataset at a high frequency, the page window becomes unreliable, potentially skipping or returning duplicate results.

游标如何解决?

基于光标的分页通过将指针返回到数据集中的特定项目来实现.在后续请求中，服务器在给定指针之后返回结果.

Cursor-based pagination works by returning a pointer to a specific item in the dataset. On subsequent requests, the server returns results after the given pointer.

在这种情况下，我们将使用参数 next_cursor 和 limit 作为客户端提供的参数.

We will use parameters next_cursor along with limit as the parameters provided by client in this case.

假设我们要从最新用户到最早的用户进行分页.当客户第一次请求时，假设我们通过查询选择了第一页:

Let’s assume we want to paginate from the most recent user to the oldest user.When client request for the first time , suppose we select the first page through query:

SELECT * FROM users
WHERE team_id = %team_id
ORDER BY id DESC
LIMIT %limit

其中 limit 等于 limit加1 ，以获取比客户指定的计数多一的结果.额外的结果不会返回到结果集中，但是我们使用值的ID作为 next_cursor .

Where limit is equal to limit plus one, to fetch one more result than the count specified by the client. The extra result isn’t returned in the result set, but we use the ID of the value as the next_cursor.

服务器的响应将是:

{
   "users": [...],
   "next_cursor": "1234",  # the user id of the extra result
}

然后，客户端将在第二个请求中提供 next_cursor 作为光标.

The client would then provide next_cursor as cursor in the second request.

SELECT * FROM users
WHERE team_id = %team_id
AND id <= %cursor
ORDER BY id DESC
LIMIT %limit

以此，我们解决了基于偏移的分页的缺点:

With this, we’ve addressed the drawbacks of offset based pagination:

• 我们总是在特定参考点之后获取下一个计数行，而不是根据项目总数从每个请求的头开始计算窗口.如果将项目高频次写入数据集中，则光标在集合中的整体位置可能会发生变化，但分页窗口会相应调整.
• 对于大型数据集，这将很好地扩展.我们正在使用WHERE子句来获取ID值小于上一页中最后一个ID的行.这样，我们就可以利用列上的索引，并且数据库不必读取我们已经看到的任何行.

• Instead of the window being calculated from scratch on each request based on the total number of items, we’re always fetching the next count rows after a specific reference point. If items are being written to the dataset at a high frequency, the overall position of the cursor in the set might change, but the pagination window adjusts accordingly.
• This will scale well for large datasets. We’re using a WHERE clause to fetch rows with id values less than the last id from the previous page. This lets us leverage the index on the column and the database doesn’t have to read any rows that we’ve already seen.

有关详细说明，请访问松弛！

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