避免在分頁查詢中使用 OFFSET 和 LIMIT

您不必擔心優化資料庫效能的日子已經一去不復返了。 時間不會靜止。 每個新科技創業家都想創建下一個 Facebook，同時嘗試收集他們所能掌握的所有數據。 企業需要這些數據來更好地訓練模型，幫助他們賺錢。 在這種情況下，程式設計師需要建立 API，使他們能夠快速、可靠地處理大量資訊。

如果您設計應用程式或資料庫後端已有一段時間，您可能已經編寫過執行分頁查詢的程式碼。 例如，像這樣：

SELECT * FROM table_name LIMIT 10 OFFSET 40

事情是這樣的？

但如果這就是你的分頁方式，我很遺憾地說你沒有以最有效的方式做到這一點。

你想反對我嗎？ 你是否可以 沒有 花 時間. 鬆弛, Shopify и 混合最大 他們已經在使用我今天要討論的技術。

至少列出一位從未使用過的後端開發人員 OFFSET и LIMIT 執行分頁查詢。 在MVP（最小可行產品）以及使用少量資料的專案中，這種方法非常適用。 可以這麼說，它「確實有效」。

但是，如果您需要從頭開始建立可靠且高效的系統，則應該提前註意查詢此類系統中使用的資料庫的效率。

今天我們將討論分頁查詢引擎的常用（太糟糕）實現的問題，以及如何在執行此類查詢時實現高效能。

OFFSET 和 LIMIT 有什麼問題？

正如已經說過的， OFFSET и LIMIT 它們在不需要處理大量數據的項目中表現良好。

當資料庫的大小增長到伺服器記憶體無法容納時，就會出現問題。 但是，在使用此資料庫時，您需要使用分頁查詢。

要使這個問題顯現出來，必須存在一種情況，即DBMS對每個分頁查詢都採取低效的全表掃描操作（雖然可能會發生插入和刪除操作，但我們不需要過時的資料！）。

什麼是“全表掃描”（或“順序表掃描”，Sequential Scan）？ 在該操作中，DBMS 順序讀取表的每一行（即表中包含的資料），並檢查它們是否符合給定條件。 眾所周知，這種類型的表格掃描是最慢的。 事實上，當它執行時，會執行許多涉及伺服器磁碟子系統的輸入/輸出操作。 與處理儲存在磁碟上的資料相關的延遲以及將資料從磁碟傳輸到記憶體是一項資源密集型操作的事實使情況變得更糟。

例如，您有 100000000 個使用者的記錄，並且使用以下結構執行查詢 OFFSET 50000000。 這意味著 DBMS 必須載入所有這些記錄（我們甚至不需要它們！），將它們放入記憶體中，然後在其中報告 20 個結果 LIMIT.

假設它可能看起來像這樣：「從 50000 中選擇從 50020 到 100000 的行」。 也就是說，系統首先需要載入50000行才能完成查詢。 你知道她還要做多少不必要的工作嗎？

如果您不相信我，請看一下我使用這些功能創建的範例 db-fiddle.com. 

db-fiddle.com 上的範例

那裡，在左邊，在田野裡 Schema SQL，有程式碼將 100000 行插入資料庫，在右側的欄位中 Query SQL，顯示兩個查詢。 第一個，緩慢的，看起來像這樣：

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

第二個是同一問題的有效解決方案，如下所示：

SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;

為了滿足這些請求，只需點擊按鈕 Run 在頁面頂部。 完成此操作後，我們比較查詢執行時間的資訊。 事實證明，執行一個低效查詢比執行第二個查詢至少花費30 倍的時間（這個時間因運行而異；例如，系統可能會報告第一個查詢花了37 毫秒才能完成，但執行第二個查詢需要1 毫秒）。第二個 - XNUMX 毫秒）。

如果有更多數據，那麼一切看起來都會更糟（要確信這一點，請看看我的 例子 有 10 萬行）。

我們剛剛討論的內容應該可以讓您深入了解資料庫查詢的實際處理方式。

請注意，數值越高 OFFSET — 完成請求所需的時間越長。

我應該使用什麼來代替 OFFSET 和 LIMIT 的組合？

而不是組合 OFFSET и LIMIT 值得使用根據以下方案建構的結構：

SELECT * FROM table_name WHERE id > 10 LIMIT 20

這是使用基於遊標的分頁的查詢執行。

而不是在本地儲存當前的 OFFSET и LIMIT 並在每個請求中傳輸它們，您需要儲存最後收到的主鍵（通常是 ID）和 LIMIT，結果會得到類似上面的查詢。

為什麼？ 重點是，透過明確指定最後讀取的行的標識符，您可以告訴 DBMS 需要從哪裡開始搜尋必要的資料。 此外，由於使用了金鑰，搜尋將有效地進行；系統不必因指定範圍之外的行而分心。

下面我們來看看各種查詢的效能比較。 這是一個無效的查詢。

請求緩慢

這是此請求的最佳化版本。

快速請求

兩個查詢傳回完全相同的資料量。 但第一個需要 12,80 秒才能完成，第二個需要 0,01 秒。 你感覺到有什麼不同嗎？

可能的問題

為了使所提出的查詢方法有效地工作，表必須有一列（或多列），其中包含唯一的順序索引，例如整數標識符。 在某些特定情況下，這可能決定使用此類查詢是否成功提高使用資料庫的速度。

當然，在建立查詢時，您需要考慮表的特定架構，並選擇最適合現有表的機制。 例如，如果您需要處理大量相關資料的查詢，您可能會發現它很有趣 此 文章。

如果我們面臨缺少主鍵的問題，例如，如果我們有一個多對多關係的表，那麼使用傳統的方法 OFFSET и LIMIT，保證適合我們。 但它的使用可能會導致查詢速度變慢。 在這種情況下，我建議使用自動遞增主鍵，即使它只需要處理分頁查詢。

如果您對這個主題感興趣 - 這裡, 這裡 и 這裡 - 一些有用的材料。

結果

我們可以得出的主要結論是，無論我們談論的資料庫大小如何，始終需要分析查詢執行的速度。 如今，解決方案的可擴展性非常重要，如果從在某個系統上工作的一開始就設計正確，那麼將來可以使開發人員免於許多問題。

如何分析和最佳化資料庫查詢？

來源： www.habr.com