MySQL分頁，求求你別再用offset和limit了！

點擊關注上方“SQL數(shù)據(jù)庫開發(fā)”，

設為“置頂或星標”，第一時間送達干貨

不需要擔心數(shù)據(jù)庫性能優(yōu)化問題的日子已經(jīng)一去不復返了。

隨著時代的進步，隨著野心勃勃的企業(yè)想要變成下一個 Facebook，隨著為機器學習預測收集盡可能多數(shù)據(jù)的想法的出現(xiàn)，作為開發(fā)人員，我們要不斷地打磨我們的 API，讓

它們提供可靠和有效的端點，從而毫不費力地瀏覽海量數(shù)據(jù)。

如果你做過后臺開發(fā)或數(shù)據(jù)庫架構，你可能是這么分頁的：

如果你真的是這么分頁，那么我不得不抱歉地說，你這樣做是錯的。

你不以為然？沒關系。Slack、Shopify 和 Mixmax 這些公司都在用我們今天將要討論的方式進行分頁。

我想你很難找出一個不使用 OFFSET 和 LIMIT 進行數(shù)據(jù)庫分頁的人。對于簡單的小型應用程序和數(shù)據(jù)量不是很大的場景，這種方式還是能夠“應付”的。

如果你想從頭開始構建一個可靠且高效的系統(tǒng)，在一開始就要把它做好。

今天我們將探討已經(jīng)被廣泛使用的分頁方式存在的問題，以及如何實現(xiàn)高性能分頁。

OFFSET 和 LIMIT 有什么問題？

正如前面段落所說的那樣，OFFSET 和 LIMIT 對于數(shù)據(jù)量少的項目來說是沒有問題的。

但是，當數(shù)據(jù)庫里的數(shù)據(jù)量超過服務器內(nèi)存能夠存儲的能力，并且需要對所有數(shù)據(jù)進行分頁，問題就會出現(xiàn)。

為了實現(xiàn)分頁，每次收到分頁請求時，數(shù)據(jù)庫都需要進行低效的全表掃描。

什么是全表掃描？全表掃描 (又稱順序掃描) 就是在數(shù)據(jù)庫中進行逐行掃描，順序讀取表中的每一行記錄，然后檢查各個列是否符合查詢條件。這種掃描是已知最慢的，因為需要進行大量的磁盤 I/O，而且從磁盤到內(nèi)存的傳輸開銷也很大。

這意味著，如果你有 1 億個用戶，OFFSET 是 5 千萬，那么它需要獲取所有這些記錄 (包括那么多根本不需要的數(shù)據(jù))，將它們放入內(nèi)存，然后獲取 LIMIT 指定的 20 條結果。

也就是說，為了獲取一頁的數(shù)據(jù)：

10萬行中的第5萬行到第5萬零20行

需要先獲取 5 萬行。這么做是多么低效？

如果你不相信，可以看看這個例子：https://www.db-fiddle.com/f/3JSpBxVgcqL3W2AzfRNCyq/1?ref=hackernoon.com

左邊的 Schema SQL 將插入 10 萬行數(shù)據(jù)，右邊有一個性能很差的查詢和一個較好的解決方案。只需單擊頂部的 Run，就可以比較它們的執(zhí)行時間。第一個查詢的運行時間至少是第二個查詢的 30 倍。

數(shù)據(jù)越多，情況就越糟。看看我對 10 萬行數(shù)據(jù)進行的 PoC

https://github.com/IvoPereira/Efficient-Pagination-SQL-PoC?ref=hackernoon.com

現(xiàn)在你應該知道這背后都發(fā)生了什么：OFFSET 越高，查詢時間就越長。

替代方案

你應該這樣做：

這是一種基于指針的分頁。

你要在本地保存上一次接收到的主鍵 (通常是一個 ID) 和 LIMIT，而不是 OFFSET 和 LIMIT，那么每一次的查詢可能都與此類似。

為什么？因為通過顯式告知數(shù)據(jù)庫最新行，數(shù)據(jù)庫就確切地知道從哪里開始搜索（基于有效的索引），而不需要考慮目標范圍之外的記錄。

比較這個查詢：

和優(yōu)化的版本：

返回同樣的結果，第一個查詢使用了 12.80 秒，而第二個僅用了 0.01 秒。

要使用這種基于游標的分頁，需要有一個惟一的序列字段 (或多個)，比如惟一的整數(shù) ID 或時間戳，但在某些特定情況下可能無法滿足這個條件。

我的建議是，不管怎樣都要考慮每種解決方案的優(yōu)缺點，以及需要執(zhí)行哪種查詢。

如果需要基于大量數(shù)據(jù)做查詢操作，Rick James 的文章提供了更深入的指導。

如果我們的表沒有主鍵，比如是具有多對多關系的表，那么就使用傳統(tǒng)的 OFFSET/LIMIT 方式，只是這樣做存在潛在的慢查詢問題。我建議在需要分頁的表中使用自動遞增的主鍵，即使只是為了分頁。

英語原文：

https://hackernoon.com/please-dont-use-offset-and-limit-for-your-pagination-8ux3u4y

——End——
后臺回復關鍵字：1024，獲取一份精心整理的技術干貨
后臺回復關鍵字：進群，帶你進入高手如云的交流群。
推薦閱讀
為什么阿里巴巴禁止使用存儲過程？
一份非常完整的 MySQL 規(guī)范，速速收藏！
數(shù)據(jù)庫中為什么不推薦使用外鍵約束
阿里規(guī)定超過3張表，禁止JOIN，為何？
MySQL 常用命令手冊


這是一個能學到技術的公眾號，歡迎關注

點擊「閱讀原文」了解SQL訓練營