別再用 offset 和 limit 分頁(yè)了,性能太差!
來(lái)源：toutiao.com/i6860655404431442444
ChatGPT中文網(wǎng)站：https://aigc.cxyquan.com
- 1、OFFSET 和 LIMIT 有什么問(wèn)題？
- 2、替代方案
旁白君：主要去理解 offset 為什么會(huì)在大數(shù)據(jù)量下的查詢帶來(lái)性能問(wèn)題？
思考完后，可以在思考下，如果分庫(kù)分表，你會(huì)這么去分頁(yè)呢？
不需要擔(dān)心數(shù)據(jù)庫(kù)性能優(yōu)化問(wèn)題的日子已經(jīng)一去不復(fù)返了。
隨著時(shí)代的進(jìn)步，隨著野心勃勃的企業(yè)想要變成下一個(gè) Facebook，隨著為機(jī)器學(xué)習(xí)預(yù)測(cè)收集盡可能多數(shù)據(jù)的想法的出現(xiàn)，作為開(kāi)發(fā)人員，我們要不斷地打磨我們的 API，讓它們提供可靠和有效的端點(diǎn)，從而毫不費(fèi)力地瀏覽海量數(shù)據(jù)。
如果你做過(guò)后臺(tái)開(kāi)發(fā)或數(shù)據(jù)庫(kù)架構(gòu)，你可能是這么分頁(yè)的：
如果你真的是這么分頁(yè)，那么我不得不抱歉地說(shuō)，你這樣做是錯(cuò)的。
你不以為然？沒(méi)關(guān)系。Slack、Shopify 和 Mixmax 這些公司都在用我們今天將要討論的方式進(jìn)行分頁(yè)。
我想你很難找出一個(gè)不使用 OFFSET 和 LIMIT 進(jìn)行數(shù)據(jù)庫(kù)分頁(yè)的人。對(duì)于簡(jiǎn)單的小型應(yīng)用程序和數(shù)據(jù)量不是很大的場(chǎng)景，這種方式還是能夠“應(yīng)付”的。
如果你想從頭開(kāi)始構(gòu)建一個(gè)可靠且高效的系統(tǒng)，在一開(kāi)始就要把它做好。
今天我們將探討已經(jīng)被廣泛使用的分頁(yè)方式存在的問(wèn)題，以及如何實(shí)現(xiàn)高性能分頁(yè)。
1、OFFSET 和 LIMIT 有什么問(wèn)題？
正如前面段落所說(shuō)的那樣，OFFSET 和 LIMIT 對(duì)于數(shù)據(jù)量少的項(xiàng)目來(lái)說(shuō)是沒(méi)有問(wèn)題的。
但是，當(dāng)數(shù)據(jù)庫(kù)里的數(shù)據(jù)量超過(guò)服務(wù)器內(nèi)存能夠存儲(chǔ)的能力，并且需要對(duì)所有數(shù)據(jù)進(jìn)行分頁(yè)，問(wèn)題就會(huì)出現(xiàn)。
為了實(shí)現(xiàn)分頁(yè)，每次收到分頁(yè)請(qǐng)求時(shí)，數(shù)據(jù)庫(kù)都需要進(jìn)行低效的全表掃描。
什么是全表掃描？全表掃描 (又稱順序掃描) 就是在數(shù)據(jù)庫(kù)中進(jìn)行逐行掃描，順序讀取表中的每一行記錄，然后檢查各個(gè)列是否符合查詢條件。這種掃描是已知最慢的，因?yàn)樾枰M(jìn)行大量的磁盤(pán) I/O，而且從磁盤(pán)到內(nèi)存的傳輸開(kāi)銷也很大。
這意味著，如果你有 1 億個(gè)用戶，OFFSET 是 5 千萬(wàn)，那么它需要獲取所有這些記錄 (包括那么多根本不需要的數(shù)據(jù))，將它們放入內(nèi)存，然后獲取 LIMIT 指定的 20 條結(jié)果。
也就是說(shuō)，為了獲取一頁(yè)的數(shù)據(jù)：
10萬(wàn)行中的第5萬(wàn)行到第5萬(wàn)零20行
需要先獲取 5 萬(wàn)行。這么做是多么低效？
如果你不相信，可以看看這個(gè)例子：
https://www.db-fiddle.com/f/3JSpBxVgcqL3W2AzfRNCyq/1?ref=hackernoon.com
左邊的 Schema SQL 將插入 10 萬(wàn)行數(shù)據(jù)，右邊有一個(gè)性能很差的查詢和一個(gè)較好的解決方案。只需單擊頂部的 Run，就可以比較它們的執(zhí)行時(shí)間。第一個(gè)查詢的運(yùn)行時(shí)間至少是第二個(gè)查詢的 30 倍。微信搜索公眾號(hào)：Java后端編程，回復(fù)：java 領(lǐng)取資料 。
數(shù)據(jù)越多，情況就越糟。看看我對(duì) 10 萬(wàn)行數(shù)據(jù)進(jìn)行的 PoC。
https://github.com/IvoPereira/Efficient-Pagination-SQL-PoC?ref=hackernoon.com
現(xiàn)在你應(yīng)該知道這背后都發(fā)生了什么：OFFSET 越高，查詢時(shí)間就越長(zhǎng)。
2、替代方案
你應(yīng)該這樣做：
這是一種基于指針的分頁(yè)。
你要在本地保存上一次接收到的主鍵 (通常是一個(gè) ID) 和 LIMIT，而不是 OFFSET 和 LIMIT，那么每一次的查詢可能都與此類似。
為什么？因?yàn)橥ㄟ^(guò)顯式告知數(shù)據(jù)庫(kù)最新行，數(shù)據(jù)庫(kù)就確切地知道從哪里開(kāi)始搜索（基于有效的索引），而不需要考慮目標(biāo)范圍之外的記錄。
比較這個(gè)查詢：
和優(yōu)化的版本：
返回同樣的結(jié)果，第一個(gè)查詢使用了 12.80 秒，而第二個(gè)僅用了 0.01 秒。
要使用這種基于游標(biāo)的分頁(yè)，需要有一個(gè)惟一的序列字段 (或多個(gè))，比如惟一的整數(shù) ID 或時(shí)間戳，但在某些特定情況下可能無(wú)法滿足這個(gè)條件。
我的建議是，不管怎樣都要考慮每種解決方案的優(yōu)缺點(diǎn)，以及需要執(zhí)行哪種查詢。
如果需要基于大量數(shù)據(jù)做查詢操作，Rick James 的文章提供了更深入的指導(dǎo)。
http://mysql.rjweb.org/doc.php/lists
如果我們的表沒(méi)有主鍵，比如是具有多對(duì)多關(guān)系的表，那么就使用傳統(tǒng)的 OFFSET/LIMIT 方式，只是這樣做存在潛在的慢查詢問(wèn)題。我建議在需要分頁(yè)的表中使用自動(dòng)遞增的主鍵，即使只是為了分頁(yè)。
-End-
讀到這里說(shuō)明你 喜歡 本公眾號(hào)的文章，歡迎 置頂（標(biāo)星） 本公眾號(hào) Linux技術(shù)迷，這樣就可以第一時(shí)間獲取推送了~
在本公眾號(hào) Linux技術(shù)迷，后臺(tái)回復(fù)： Linux ，領(lǐng)取2T學(xué)習(xí)資料 ！ 推 薦 閱 讀 1. ChatGPT 4.0 + Midjourney繪畫(huà) ，國(guó)內(nèi)直接使用 ！ 2. 如何成為更好的軟件架構(gòu)師？ 3. 一款非常強(qiáng)大的 Nginx 可視化管理平臺(tái) 4. 2萬(wàn)字系統(tǒng)總結(jié)，帶你實(shí)現(xiàn)Linux命令自由