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

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

如果您已經設計伺服器端應用程式或資料庫一段時間了，那麼您可能已經編寫了執行分頁查詢的程式碼。例如這個：

SELECT * FROM table_name LIMIT 10 OFFSET 40

是這樣嗎？

但是如果您是這樣進行分頁的，那麼很遺憾，您並沒有以最有效的方式進行操作。

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

說出一位從未使用過 OFFSET и LIMIT 執行分頁查詢。在MVP（最小可行產品）和使用少量資料的專案中，這種方法非常適用。可以這麼說，它只是「有效」。

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

今天我們將討論與廣泛使用的（不幸的是）分頁查詢引擎的實現相關的問題，以及如何在執行此類查詢時實現高效能。

OFFSET 和 LIMIT 有什麼問題？

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

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

要解決這個問題，必須出現這樣的情況：DBMS 對每個分頁查詢都採取低效的全表掃描操作（同時，可能正在進行插入和刪除操作，而我們不需要過時的資料！）。

什麼是「全表掃描」（或「順序表掃描」）？這是一個操作，在此操作期間，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 毫秒）。

如果有更多的數據，那麼一切看起來會更糟（要了解這一點，請看我的 例子 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