你应该停止使用的 SQL 查询

我们来谈谈页面吧。你知道，像这样

或者像这样的无限滚动页面

因为我们从不想将所有数据提供给网站访问者，所以我们将这些数据以页面形式呈现，并让用户随意加载更多信息。

SQL 中的一种分页方法（又称为分页）很简单，就是将查询限制为一定数量，当您想要下一页时，设置一个偏移量。

例如，这是对博客第二页的查询

SELECT * from posts
ORDER BY created_at
LIMIT 10
OFFSET 10

然而，对于较大的数据库来说，这不是一个好的解决方案。

为了演示，我创建了一个数据库，并存入了 2,000,000 条推文。嗯，不是真正的推文，只是一行行的数据。

数据库在我的笔记本电脑上，大小只有 500mb，所以不必太担心结果中的具体数字，只需担心它们代表什么。

首先，我将解释为什么使用偏移量进行分页不是一个好主意，然后提出几种更好的分页方法。

偏移分页

这张图展示了获取每个页面所需的时间。请注意，随着页数的增加，获取该页面所需的时间也呈线性增长。

Results:
200,000 rows returned
~17.7s
~11,300 rows per second
** unable to paginate all 2 million rows under 5 minutes

这是因为偏移量的工作原理是先计算应该跳过多少行，然后给出结果。换句话说，为了得到第 1,000 行到第 1,100 行的结果，它需要扫描前 1,000 行，然后将其丢弃。这看起来是不是有点浪费？

这并不是偏移量不好的唯一原因。如果在分页过程中，又添加或删除了一行数据，该怎么办？由于偏移量会手动统计每页的行数，因此可能会因为删除的行而导致计数不足，或者因为新增的行而导致计数过高。如果数据经常变化，通过偏移量查询将导致重复或缺失结果。

不过，还有更好的分页方法！这里有一个

基于顺序的分页

您可以根据几乎任何可以对数据进行排序的内容进行分页。

例如，如果你有一个递增的 ID，你可以用它作为游标来追踪你当前所在的页面。首先，获取结果，然后使用上一个结果的 ID 来查找下一页。

SELECT * FROM tweet
WHERE id <= $last_id
ORDER BY id DESC
LIMIT 100

2,000,000 rows returned
~4.2s
~476,000 rows per second

这种方法不仅速度更快，而且对数据变化也更具弹性！即使删除了一行或添加了一行新行，分页也会从上次中断的地方开始。

这是另一张图表，显示了每次翻阅 200 万行数据（每次 100 行）所需的时间。请注意，它保持一致！

缺点是它无法跳转到任意页面，因为我们需要通过 id 来找到页面。对于像 Reddit 和 Twitter 这样的无限滚动网站来说，这是一个很好的权衡。

时间分页

下面是一个更实际的基于created_at字段的分页示例。

它与 ID 分页具有相同的优点和缺点。但是，为了获得最佳性能，您需要添加索引(created_at, id)。我还添加了 ID，以避免同时创建的推文被重复。

CREATE INDEX on tweet (created_at, id)

SELECT * from tweet
WHERE (created_at, id) <= ($prev_create_at, $prev_id)
ORDER BY created_at DESC, id DESC
LIMIT 100

2,000,000 rows returned
~4.70s
~425,000 rows per second

结论

你真的应该停止OFFSET在 SQL 查询中使用吗？可能吧。

但实际上，由于用户不会快速浏览你的页面，所以你完全可以接受稍微慢一点的结果。这完全取决于你的系统以及你为了完成任务愿意做出哪些权衡。

我认为偏移量有其用武之地，但不适合用作分页工具。它比起其他简单的替代方案，速度慢得多，内存效率也低得多。感谢阅读！

✌🏾