javascript - PostgreSQL 数据库 - 如何按顺序查询表(无限滚动)

Question

与数据库打交道，我并不迷茫，但也不是专家。

我想在我的网站上实现无限滚动，这意味着数据需要按 date_created 或 id 降序排列。我最初的想法是在这样的查询中使用 LIMIT 和 OFFSET(使用 SQLalchemy):

session.query(Posts).filter(Posts.owner_id == _userid_).filter(Posts.id < post_id).orderBy(desc(Posts.id)).limit(5).all()

翻译成这样:

SELECT * from posts WHERE owner_id = _userid_ AND id < _post_id_ ORDER BY id DESC LIMIT 10 OFFSET _somevalue_;

在我的 js 中:

var minimum_post_id = 0;
var posts_list = [];
var post_ids = [];

function infinite_load(_userid_, _post_id_) {
  fetch('/users/' + _userid_ + '/posts/' + _post_id_)
  .then(r => r.json())
  .then(data => { 
     console.log(data); 
     data.posts.forEach(post => { posts_list.push(post); post_ids.push(post.id) });
     minimum_post_id = Math.min(...post_ids);
   })
}

infinite_load(1, minimum_post_id) // random user id

但是，我正在研究这是否有效并遇到了这个:https://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/

基本上它是说有限制和偏移量是不好的，因为它仍然必须计算所有要偏移的记录，只是为了丢弃它们。

所以我的问题是，我的实现不充分吗？我如何有效地按顺序查询数据库？

Answer 1

分页 - 正确完成 - 比简单的“我们显示上一页的 ID 范围是什么？加 10 来限制和偏移”有更多的倒钩。一些快速的问题来激发你的胃口，然后是一个建议:

• 当用户查看从 11 到 20 的项目时，在位置 15 处插入了一条记录。单击“下一步”分页按钮后返回给用户的是什么？

• 相反，当用户查看从 101 到 110 的记录时，位置 100 以下的任意 10 个记录将被删除。单击“下一步”分页后，用户会得到什么？还是“上一个”分页点击？

根据您的数据模型、架构和 UI 要求，这些问题可能很简单，也可能真的很难回答。

现在，为什么 LIMIT/OFFSET 是错误的做法……实际上，只要您有足够小的数据集，这就不是了——而且对于大多数网站来说，这可能足够大了。换句话说，选择适合您的设置的内容。

与此同时，对于在“非常大”的数据集假设下具有教学头脑的人来说:OFFSET 是该查询的 killer 级部分(因为它需要对结果进行统计、排序、计数，然后在 LIMIT 可以之前跳过起作用)。那么，我们怎样才能删除 OFFSET 呢？将其合并到查询的 CONSTRAINT 部分。

您的查询按 ID 排序，然后偏移某个数字。通过确保 ID 大于(或小于)当前屏幕为用户显示的内容来删除偏移量:

SELECT * FROM posts
WHERE
    owner_id = _userid_
AND id < _last_displayed_id
ORDER BY id DESC
LIMIT 10;

同样，如果您按时间排序，则让您的分页按钮(或滚动处理程序)在已呈现给用户的最后一项之后/之前请求新记录。