sql - 通过更改排序顺序处理分页

Question

我正在创建一个 RESTful 网络服务(在 Golang 中)，它从数据库中提取一组行并将其返回给客户端(智能手机应用程序或网络应用程序)。该服务需要能够提供寻呼。唯一的问题是此数据是按定期更改的“计算”列排序的(例如，网站上某内容的“赞成”或“反对”的数量)，因此行可以跳来跳去客户请求之间的页码。

我研究了一些 PostgreSQL 功能，我可能会使用这些功能来帮助我解决这个问题，但似乎没有什么是非常好的解决方案。

• 物化 View :保存仅每隔一段时间更新一次的“陈旧”数据。这并没有真正解决问题，因为如果用户恰好在更新实体化 View 时翻阅数据，数据仍然会跳来跳去。
• 游标:为每个客户端 session 创建并在请求之间保存。如果一次有很多并发 session (将会有)，这似乎将是一场噩梦。

有人对如何在客户端或数据库端处理此问题有任何建议吗？有什么我真的可以做的吗，或者像这样的问题通常只能由使用数据的客户端来解决吗？

编辑:我应该提到智能手机应用程序允许用户通过“无限滚动”查看更多数据，因此它会跟踪自己的客户端数据列表。

Answer 1

这是一个没有完全令人满意的解决方案的问题，因为您试图组合本质上不兼容的要求:

• 仅按需向客户端发送所需数量的数据，即您不能下载整个数据集然后在客户端对其进行分页。

• 最大限度地减少服务器必须跟踪的每个客户端状态的数量，以实现大量客户端的可扩展性。

• 为每个客户端维护不同的状态

这是一种“任选两个”的情况。你必须妥协；接受您不能让每个客户端的分页状态完全正确，接受您必须将大数据集下载到客户端，或者接受您必须使用大量服务器资源来维护客户端状态。

在混合了各种妥协的情况下存在变体，但这就是一切的归结。

例如，有些人会向客户端发送一些额外的数据，足以满足大多数客户端的要求。如果客户端超出了这个范围，那么它的分页就会被破坏。

有些系统会在短时间内缓存客户端状态(使用短暂的未记录表、临时文件或其他任何内容)，但会很快过期，因此如果客户端不经常请求新数据，它的分页就会被破坏。

等等

另见:

• How to provide an API client with 1,000,000 database results?
• Using "Cursors" for paging in PostgreSQL
• Iterate over large external postgres db, manipulate rows, write output to rails postgres db
• offset/limit performance optimization
• If PostgreSQL count(*) is always slow how to paginate complex queries?
• How to return sample row from database one by one

我可能会实现某种形式的混合解决方案，例如:

• 使用游标，读取并立即将数据的第一部分发送给客户端。

• 立即从游标中获取足够的额外数据，以满足 99% 的客户需求。将它存储到一个快速、不安全的缓存中，例如 memcached、Redis、BigMemory、EHCache，以及任何可以让我检索它以供同一客户端稍后请求的 key 下的缓存。然后关闭游标以释放数据库资源。

• 在最近最少使用的基础上使缓存过期，因此如果客户端没有保持足够快的读取速度，他们必须从数据库中获取一组新数据，并且分页会发生变化。

• 如果客户端比绝大多数同行想要更多的结果，当您切换到直接从数据库而不是缓存读取或生成一个新的更大的缓存数据集时，分页会在某个时候发生变化。

这样一来，大多数客户端就不会注意到分页问题，​​您也不必向大多数客户端发送大量数据，但您的数据库服务器也不会崩溃。但是，您需要一个大的 boofy 缓存才能解决这个问题。它的实用性取决于您的客户是否可以应对分页中断——如果中断分页根本 Not Acceptable ，那么您将不得不在 DB 端使用游标、临时表、在第一次请求时处理整个结果集等。它还取决于数据集大小以及每个客户端通常需要多少数据。