這是我們后端團(tuán)隊(duì)的一個(gè)故事。

我們當(dāng)時(shí)正在開發(fā)一個(gè)相當(dāng)簡單的 API，它可以分頁獲取用戶的交易歷史記錄。非常標(biāo)準(zhǔn)的功能。最初幾個(gè)月運(yùn)行順暢。但隨著數(shù)據(jù)不斷增長，之前 200 毫秒就能返回結(jié)果的查詢……現(xiàn)在需要 2 到 3秒。

起初，我們以為這只是加載問題，或者可能是缺少索引。但在深入挖掘并檢查數(shù)據(jù)庫執(zhí)行計(jì)劃后，我們發(fā)現(xiàn)了一些非常基本且開銷巨大的問題。

這是 OFFSET。

OFFSET 分頁：沉默的殺手

這是我們使用的查詢：

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

看起來很干凈，對吧？但幕后真正發(fā)生的事情是這樣的：

數(shù)據(jù)庫獲取10,020 行，然后丟棄前 10,000 行，只返回最后 20 行。因此，即使我們只向用戶顯示 20 條記錄，數(shù)據(jù)庫仍然需要做大量工作。

每次滾動(dòng)或分頁，OFFSET 都會(huì)不斷增加……性能也會(huì)持續(xù)下降。數(shù)據(jù)越大，性能越差。

修復(fù)：鍵集分頁

在嘗試了一些沒有太大作用的優(yōu)化之后，我們使用鍵集分頁重寫了查詢，這改變了一切。

新版本的外觀如下：

SELECT * FROM transactions
WHERE user_id = 42
  AND created_at < '2024-05-01 10:00:00'
ORDER BY created_at DESC
LIMIT 20;

因此，我們不會(huì)說“跳過前 10,000 行”，而是告訴數(shù)據(jù)庫：“給我此時(shí)間戳之后的接下來的 20 行”。“給我這個(gè)時(shí)間戳之后的 20 行。”

這稱為搜索分頁或鍵集分頁。

這樣效率更高，因?yàn)閿?shù)據(jù)庫不需要掃描和丟棄任何東西。它只需使用索引直接跳轉(zhuǎn)到正確的位置。

進(jìn)行此更改后，我們的 API 響應(yīng)時(shí)間從2.6 秒下降到 200 毫秒以下，無需基礎(chǔ)設(shè)施更改，無需緩存，只需更智能的 SQL。

我們?nèi)绾翁幚碇貜?fù)的時(shí)間戳

我們面臨的一個(gè)小問題：有時(shí)多個(gè)交易具有完全相同的created_at時(shí)間戳（尤其是當(dāng)用戶批量上傳數(shù)據(jù)時(shí)）。

當(dāng)我們僅根據(jù) 進(jìn)行分頁時(shí)，這會(huì)導(dǎo)致分頁故障，例如重復(fù)或跳過行created_at。

因此，我們這樣修復(fù)它：

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

通過在和子句中添加決勝因素（id），分頁變得穩(wěn)定且可預(yù)測。ORDER BYWHERE

我們考慮的其他一些方法

雖然鍵集分頁解決了我們的大多數(shù)問題，但我們也探索了一些其他想法，每個(gè)想法都根據(jù)用例有用。

1.基于游標(biāo)的分頁

這本質(zhì)上是鍵集分頁，但不是在 API 中傳遞原始時(shí)間戳或 ID，而是將它們包裝在游標(biāo)令牌中，如下所示：

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

Instagram、Twitter 以及大多數(shù)現(xiàn)代 API 正是這樣處理滾動(dòng)的。它保持了簡潔、無狀態(tài)且高效。

2. 僅索引偏移

注意：當(dāng)你必須使用OFFSET時(shí)

在一些內(nèi)部工具（如管理儀表板）中，我們必須允許跳轉(zhuǎn)到任何頁面，如第 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);

這不會(huì)使 OFFSET 變得更快，但確實(shí)比以前更快了。如果你必須使用 OFFSET，這是最省事的方法。

3.物化視圖

注意：對于靜態(tài)儀表板

我們的一個(gè)報(bào)告儀表板不斷地重復(fù)執(zhí)行相同的慢速查詢，每天匯總用戶交易。

我們用物化視圖解決了這個(gè)問題：

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

我們使用 cron 每隔幾分鐘刷新一次視圖。這使得報(bào)告更加快速，并減少了實(shí)時(shí)表格的負(fù)載。使用 cron這使得報(bào)告更加簡潔，并減少了實(shí)時(shí)表的負(fù)載。

修復(fù)后的實(shí)際結(jié)果

以下是每次改進(jìn)后查詢時(shí)間的變化情況：

查詢類型平均響應(yīng)時(shí)間OFFSET 10000~2600 msOFFSET + 索引~1300 ms鍵集/查找方法~180 ms鍵集 + 游標(biāo)標(biāo)記~190 ms物化視圖~50–100 ms

我們并沒有想到僅僅改變 SQL 樣式就會(huì)帶來如此巨大的差異，但事實(shí)是它確實(shí)產(chǎn)生了巨大的影響。

最后的想法

這次經(jīng)歷提醒我們，性能并不總是需要大的改變或昂貴的升級。

有時(shí)，簡單地重寫 SQL 查詢可以比投入資源解決問題節(jié)省更多時(shí)間、成本和精力。

因此，如果你的應(yīng)用程序使用基于 OFFSET 的分頁，并且發(fā)現(xiàn)速度很慢，我強(qiáng)烈建議切換到鍵集分頁。

它簡單、優(yōu)雅、高效。