這是我們后端團隊的一個故事。

我們當時正在開發一個相當簡單的 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 的分頁，并且發現速度很慢，我強烈建議切換到鍵集分頁。

它簡單、優雅、高效。