这是我们使用的查询：

SELECT * FROM transactionsWHERE user_id = 42ORDER BY created_at DESCLIMIT 20 OFFSET 10000;

看起来很干净，对吧？但幕后真正发生的事情是这样的：

数据库获取10,020 行，然后丢弃前 10,000 行，只返回最后 20 行。因此，即使我们只向用户显示 20 条记录，数据库仍然需要做大量工作。

每次滚动或分页，OFFSET 都会不断增加……性能也会持续下降。数据越大，性能越差。

在尝试了一些没有太大作用的优化之后，我们使用键集分页重写了查询，这改变了一切。

新版本的外观如下：

SELECT * FROM transactionsWHERE user_id = 42  AND created_at < '2024-05-01 10:00:00'ORDER BY created_at DESCLIMIT 20;

因此，我们不会说“跳过前 10,000 行”，而是告诉数据库：“给我此时间戳之后的接下来的 20 行”。“给我这个时间戳之后的 20 行。”

这称为搜索分页或键集分页。

这样效率更高，因为数据库不需要扫描和丢弃任何东西。它只需使用索引直接跳转到正确的位置。

进行此更改后，我们的 API 响应时间从2.6 秒下降到 200 毫秒以下，无需基础设施更改，无需缓存，只需更智能的 SQL。

我们面临的一个小问题：有时多个交易具有完全相同的created_at时间戳（尤其是当用户批量上传数据时）。

当我们仅根据 进行分页时，这会导致分页故障，例如重复或跳过行created_at。

因此，我们这样修复它：

WHERE (created_at, id) < ('2024-05-01 10:00:00', 98765)ORDER BY created_at DESC, id DESC

通过在和子句中添加决胜因素（id），分页变得稳定且可预测。ORDER BYWHERE

虽然键集分页解决了我们的大多数问题，但我们也探索了一些其他想法，每个想法都根据用例有用。

这本质上是键集分页，但不是在 API 中传递原始时间戳或 ID，而是将它们包装在游标令牌中，如下所示：

"next_cursor": "2024-05-01T10:00:00Z_98765"

Instagram、Twitter 以及大多数现代 API 正是这样处理滚动的。它保持了简洁、无状态且高效。

在一些内部工具（如管理仪表板）中，我们必须允许跳转到任何页面，如第 7 页或第 10 页。在这种情况下，键集不起作用。

因此我们所做的是：

• 在查询中使用的字段上添加覆盖索引

• 仅从索引本身中选择列

CREATE INDEX idx_user_created_id_amountON transactions(user_id, created_at DESC, id, amount);transactions(user_id, created_at DESC, id, amount);

这不会使 OFFSET 变得更快，但确实比以前更快了。如果你必须使用 OFFSET，这是最省事的方法。

我们的一个报告仪表板不断地重复执行相同的慢速查询，每天汇总用户交易。

我们用物化视图解决了这个问题：

CREATE MATERIALIZED VIEW user_summary ASSELECT user_id, DATE(created_at), SUM(amount)FROM transactionsGROUP BY user_id, DATE(created_at);

我们使用 cron 每隔几分钟刷新一次视图。这使得报告更加快速，并减少了实时表格的负载。使用 cron这使得报告更加简洁，并减少了实时表的负载。

以下是每次改进后查询时间的变化情况：

查询类型平均响应时间OFFSET 10000~2600 msOFFSET + 索引~1300 ms键集/查找方法~180 ms键集 + 游标标记~190 ms物化视图~50–100 ms

我们并没有想到仅仅改变 SQL 样式就会带来如此巨大的差异，但事实是它确实产生了巨大的影响。

这次经历提醒我们，性能并不总是需要大的改变或昂贵的升级。

有时，简单地重写 SQL 查询可以比投入资源解决问题节省更多时间、成本和精力。

因此，如果你的应用程序使用基于 OFFSET 的分页，并且发现速度很慢，我强烈建议切换到键集分页。

它简单、优雅、高效。