逆天改命!仅一行SQL，查询时间提速10倍!

这是我们后端团队的一个故事。

我们当时正在开发一个相当简单的 API，它可以分页获取用户的交易历史记录。非常标准的功能。最初几个月运行顺畅。但随着数据不断增长，之前 200 毫秒就能返回结果的查询……现在需要 2 到 3秒。

起初，我们以为这只是加载问题，或者可能是缺少索引。但在深入挖掘并检查数据库执行计划后，我们发现了一些非常基本且开销巨大的问题。

这是 OFFSET。

OFFSET 分页：沉默的杀手

这是我们使用的查询：

SELECT * FROM transactions
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

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

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

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

修复：键集分页

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

新版本的外观如下：

SELECT * FROM transactions
WHERE user_id = 42
  AND created_at < '2024-05-01 10:00:00'
ORDER BY created_at DESC
LIMIT 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

我们考虑的其他一些方法

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

1.基于游标的分页

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

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

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

2. 仅索引偏移

注意：当你必须使用OFFSET时

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

因此我们所做的是：

• 在查询中使用的字段上添加覆盖索引
• 仅从索引本身中选择列

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

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

3.物化视图

注意：对于静态仪表板

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

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

CREATE MATERIALIZED VIEW user_summary AS
SELECT user_id, DATE(created_at), SUM(amount)
FROM transactions
GROUP BY user_id, DATE(created_at);

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

修复后的实际结果

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

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

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

最后的想法

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

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

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

它简单、优雅、高效。

作者丨Himanshu Singour 编译丨Rio

来源丨网址：https://medium.com/@himanshusingour7/not-kidding-one-line-of-sql-brought-down-our-query-time-from-3s-to-300ms-6b1ed80c94ac

dbaplus社群欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn