作者 | Ivo Pereira 譯者 | 無名 策劃 | 小智
不需要擔心資料庫性能優化問題的日子已經一去不複返了。
随着時代的進步,随着野心勃勃的企業想要變成下一個 Facebook,随着為機器學習預測收集盡可能多資料的想法的出現,作為開發人員,我們要不斷地打磨我們的 API,讓它們提供可靠和有效的端點,進而毫不費力地浏覽海量資料。
如果你做過背景開發或資料庫架構,你可能是這麼分頁的:
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsISPrdEZwZ1Rh5WNXp1bwNjW1ZUba9VZwlHdsATOfd3bkFGazxCMx8VesATMfhHLlN3XnxCMwEzX0xiRGZkRGZ0Xy9GbvNGLpZTY1EmMZVDUSFTU4VFRR9Fd4VGdsYTMfVmepNHLrJXYtJXZ0F2dvwVZnFWbp1zczV2YvJHctM3cv1Ce-cmbw5iYjJDMlJjN0MmZ0MmZ1gDZ3Y2MxEWO4ImZzIDZ4gjZ28CXyEzLcdDMxIDMy8CXn9Gbi9CXzV2Zh1WavwVbvNmLvR3YxUjL3M3Lc9CX6MHc0RHaiojIsJye.png)
如果你真的是這麼分頁,那麼我不得不抱歉地說,你這樣做是錯的。
你不以為然?沒關系。Slack、Shopify 和 Mixmax 這些公司都在用我們今天将要讨論的方式進行分頁。
我想你很難找出一個不使用 OFFSET 和 LIMIT 進行資料庫分頁的人。對于簡單的小型應用程式和資料量不是很大的場景,這種方式還是能夠“應付”的。
如果你想從頭開始建構一個可靠且高效的系統,在一開始就要把它做好。
今天我們将探讨已經被廣泛使用的分頁方式存在的問題,以及如何實作高性能分頁。
1OFFSET 和 LIMIT 有什麼問題?
正如前面段落所說的那樣,OFFSET 和 LIMIT 對于資料量少的項目來說是沒有問題的。
但是,當資料庫裡的資料量超過伺服器記憶體能夠存儲的能力,并且需要對所有資料進行分頁,問題就會出現。
為了實作分頁,每次收到分頁請求時,資料庫都需要進行低效的全表掃描。
什麼是全表掃描?全表掃描 (又稱順序掃描) 就是在資料庫中進行逐行掃描,順序讀取表中的每一行記錄,然後檢查各個列是否符合查詢條件。這種掃描是已知最慢的,因為需要進行大量的磁盤 I/O,而且從磁盤到記憶體的傳輸開銷也很大。
這意味着,如果你有 1 億個使用者,OFFSET 是 5 千萬,那麼它需要擷取所有這些記錄 (包括那麼多根本不需要的資料),将它們放入記憶體,然後擷取 LIMIT 指定的 20 條結果。
也就是說,為了擷取一頁的資料:
需要先擷取 5 萬行。這麼做是多麼低效?
如果你不相信,可以看看這個例子:
https://www.db-fiddle.com/f/3JSpBxVgcqL3W2AzfRNCyq/1?ref=hackernoon.com
左邊的 Schema SQL 将插入 10 萬行資料,右邊有一個性能很差的查詢和一個較好的解決方案。隻需單擊頂部的 Run,就可以比較它們的執行時間。第一個查詢的運作時間至少是第二個查詢的 30 倍。
資料越多,情況就越糟。看看我對 10 萬行資料進行的 PoC。
https://github.com/IvoPereira/Efficient-Pagination-SQL-PoC?ref=hackernoon.com
現在你應該知道這背後都發生了什麼:OFFSET 越高,查詢時間就越長。
2替代方案
你應該這樣做:
這是一種基于指針的分頁。
你要在本地儲存上一次接收到的主鍵 (通常是一個 ID) 和 LIMIT,而不是 OFFSET 和 LIMIT,那麼每一次的查詢可能都與此類似。
為什麼?因為通過顯式告知資料庫最新行,資料庫就确切地知道從哪裡開始搜尋(基于有效的索引),而不需要考慮目标範圍之外的記錄。
比較這個查詢:
和優化的版本:
傳回同樣的結果,第一個查詢使用了 12.80 秒,而第二個僅用了 0.01 秒。
要使用這種基于遊标的分頁,需要有一個惟一的序列字段 (或多個),比如惟一的整數 ID 或時間戳,但在某些特定情況下可能無法滿足這個條件。
我的建議是,不管怎樣都要考慮每種解決方案的優缺點,以及需要執行哪種查詢。
如果需要基于大量資料做查詢操作,Rick James 的文章提供了更深入的指導。
http://mysql.rjweb.org/doc.php/lists
如果我們的表沒有主鍵,比如是具有多對多關系的表,那麼就使用傳統的 OFFSET/LIMIT 方式,隻是這樣做存在潛在的慢查詢問題。我建議在需要分頁的表中使用自動遞增的主鍵,即使隻是為了分頁。
英文原文
https://hackernoon.com/please-dont-use-offset-and-limit-for-your-pagination-8ux3u4y