不需要擔(dān)心數(shù)據(jù)庫性能優(yōu)化問題的日子已經(jīng)一去不復(fù)返了。
隨著時代的進(jìn)步,隨著野心勃勃的企業(yè)想要變成下一個 Facebook,隨著為機(jī)器學(xué)習(xí)預(yù)測收集盡可能多數(shù)據(jù)的想法的出現(xiàn),作為開發(fā)人員,我們要不斷地打磨我們的 API,讓它們提供可靠和有效的端點(diǎn),從而毫不費(fèi)力地瀏覽海量數(shù)據(jù)。
如果你做過后臺開發(fā)或數(shù)據(jù)庫架構(gòu),你可能是這么分頁的:
如果你真的是這么分頁,那么我不得不抱歉地說,你這樣做是錯的。
你不以為然?沒關(guān)系。Slack、Shopify 和 Mixmax 這些公司都在用我們今天將要討論的方式進(jìn)行分頁。
我想你很難找出一個不使用 OFFSET 和 LIMIT 進(jìn)行數(shù)據(jù)庫分頁的人。對于簡單的小型應(yīng)用程序和數(shù)據(jù)量不是很大的場景,這種方式還是能夠“應(yīng)付”的。
如果你想從頭開始構(gòu)建一個可靠且高效的系統(tǒng),在一開始就要把它做好。
今天我們將探討已經(jīng)被廣泛使用的分頁方式存在的問題,以及如何實現(xiàn)高性能分頁。
1、OFFSET 和 LIMIT 有什么問題?
正如前面段落所說的那樣,OFFSET 和 LIMIT 對于數(shù)據(jù)量少的項目來說是沒有問題的。
但是,當(dāng)數(shù)據(jù)庫里的數(shù)據(jù)量超過服務(wù)器內(nèi)存能夠存儲的能力,并且需要對所有數(shù)據(jù)進(jìn)行分頁,問題就會出現(xiàn)。
為了實現(xiàn)分頁,每次收到分頁請求時,數(shù)據(jù)庫都需要進(jìn)行低效的全表掃描。
什么是全表掃描?全表掃描 (又稱順序掃描) 就是在數(shù)據(jù)庫中進(jìn)行逐行掃描,順序讀取表中的每一行記錄,然后檢查各個列是否符合查詢條件。這種掃描是已知最慢的,因為需要進(jìn)行大量的磁盤 I/O,而且從磁盤到內(nèi)存的傳輸開銷也很大。
這意味著,如果你有 1 億個用戶,OFFSET 是 5 千萬,那么它需要獲取所有這些記錄 (包括那么多根本不需要的數(shù)據(jù)),將它們放入內(nèi)存,然后獲取 LIMIT 指定的 20 條結(jié)果。
也就是說,為了獲取一頁的數(shù)據(jù):
10萬行中的第5萬行到第5萬零20行
需要先獲取 5 萬行。這么做是多么低效?
左邊的 Schema SQL 將插入 10 萬行數(shù)據(jù),右邊有一個性能很差的查詢和一個較好的解決方案。只需單擊頂部的 Run,就可以比較它們的執(zhí)行時間。第一個查詢的運(yùn)行時間至少是第二個查詢的 30 倍。
數(shù)據(jù)越多,情況就越糟??纯次覍?10 萬行數(shù)據(jù)進(jìn)行的 PoC。
現(xiàn)在你應(yīng)該知道這背后都發(fā)生了什么:OFFSET 越高,查詢時間就越長。
2、替代方案
你應(yīng)該這樣做:
這是一種基于指針的分頁。
你要在本地保存上一次接收到的主鍵 (通常是一個 ID) 和 LIMIT,而不是 OFFSET 和 LIMIT,那么每一次的查詢可能都與此類似。
為什么?因為通過顯式告知數(shù)據(jù)庫最新行,數(shù)據(jù)庫就確切地知道從哪里開始搜索(基于有效的索引),而不需要考慮目標(biāo)范圍之外的記錄。
比較這個查詢:
和優(yōu)化的版本:
返回同樣的結(jié)果,第一個查詢使用了 12.80 秒,而第二個僅用了 0.01 秒。
要使用這種基于游標(biāo)的分頁,需要有一個惟一的序列字段 (或多個),比如惟一的整數(shù) ID 或時間戳,但在某些特定情況下可能無法滿足這個條件。
我的建議是,不管怎樣都要考慮每種解決方案的優(yōu)缺點(diǎn),以及需要執(zhí)行哪種查詢。
如果我們的表沒有主鍵,比如是具有多對多關(guān)系的表,那么就使用傳統(tǒng)的 OFFSET/LIMIT 方式,只是這樣做存在潛在的慢查詢問題。我建議在需要分頁的表中使用自動遞增的主鍵,即使只是為了分頁。
審核編輯:劉清
-
存儲器
+關(guān)注
關(guān)注
38文章
7408瀏覽量
163431 -
PoC
+關(guān)注
關(guān)注
1文章
67瀏覽量
20461 -
SQL
+關(guān)注
關(guān)注
1文章
751瀏覽量
43996 -
機(jī)器學(xué)習(xí)
+關(guān)注
關(guān)注
66文章
8325瀏覽量
132219
原文標(biāo)題:別再用 offset 和 limit 分頁了,性能太差!
文章出處:【微信號:芋道源碼,微信公眾號:芋道源碼】歡迎添加關(guān)注!文章轉(zhuǎn)載請注明出處。
發(fā)布評論請先 登錄
相關(guān)推薦
評論