引言

在后端開發(fā)或者面試中，常常會(huì)遇到深分頁的問題。在處理電商平臺(tái)海量商品數(shù)據(jù)、社交媒體時(shí)間線等場(chǎng)景時(shí)，深分頁問題會(huì)導(dǎo)致用戶體驗(yàn)急劇下降，甚至造成服務(wù)器崩潰。接下來讓我們解析一下深分頁問題。

什么是深分頁問題??

例如前端要求返回指定第1000000行到第1000000 + 10行數(shù)據(jù)，我們常常會(huì)寫出這樣的SQL語句。

select * from user order by id limit 1000000, 10;

這種limit的offset，size中的offset如果很小則沒有問題，一旦變得很大，那么查詢的時(shí)間就會(huì)很長，接口響應(yīng)時(shí)間也會(huì)變得很慢，嚴(yán)重影響用戶使用體驗(yàn)。

為什么offset一旦變得很大，sql執(zhí)行速度就會(huì)變得很慢呢？這是和MySQL的執(zhí)行流程有關(guān)的，以上面的SQL語句為例子，當(dāng)你執(zhí)行這條SQL語句時(shí)，MySQL的server層會(huì)調(diào)用存儲(chǔ)引擎獲取到從0到1000000 + 10條數(shù)據(jù)，然后將前1000000數(shù)據(jù)拋棄，留下剩下的10條數(shù)據(jù)包裝成結(jié)果集返回。獲取這些多余數(shù)據(jù)耗費(fèi)的時(shí)間非常大，所以SQL查詢速度相應(yīng)的也會(huì)變慢了，這就是深分頁帶來的問題。

解決深分頁問題??

1.優(yōu)化SQL語句（必做）

select * from user order by id limit 1000000, 10;

還是以這條SQL語句為例子，去除深分頁問題不談，這樣SQL語句本身執(zhí)行效率就不夠高，至少要進(jìn)行以下的優(yōu)化：

1. 避免使用select *語句，前端需要什么內(nèi)容就查詢什么字段，使用select *語句執(zhí)行效率過慢。
2. 確保查詢的字段帶有合適的索引，結(jié)合實(shí)際情況，為查詢字段建立合適的索引，例如唯一索引，聯(lián)合索引等
3. 使用合適的where條件進(jìn)行過濾

2.子查詢優(yōu)化

在第一個(gè)優(yōu)化的基礎(chǔ)上，我們可以選用子查詢來進(jìn)行優(yōu)化，見以下SQL語句為例：

select (字段) from user where id >= (select id from user order by id limit 1000000, 1) order by id limit 10

通過子查詢查詢單id字段，查詢到前1000000 + 1條數(shù)據(jù)，然后丟棄前1000000數(shù)據(jù)，留下最后一條數(shù)據(jù)。因?yàn)檫@里只查詢單個(gè)id字段數(shù)據(jù)，時(shí)間效率可以提升。如果有合適的where條件進(jìn)行過濾，則可以起到覆蓋索引的效果，可以進(jìn)一步提高時(shí)間查詢效率。

查到了一條id之后，存儲(chǔ)引擎根據(jù)條件，就再走一遍主鍵索引（在為id加上了主鍵索引的前提下），然后向后取后十條字段數(shù)據(jù)就完成了該SQL語句執(zhí)行。這種優(yōu)化可以提高時(shí)間效率1.5倍左右。

3.游標(biāo)分頁法優(yōu)化

• 實(shí)現(xiàn)步驟：

  1. 首次查詢時(shí)，獲取第一頁數(shù)據(jù)，并返回一個(gè)游標(biāo)。
  2. 后續(xù)查詢使用該游標(biāo)來獲取下一頁數(shù)據(jù)。

• 優(yōu)點(diǎn)：避免了偏移量帶來的性能問題。

• 缺點(diǎn)：依賴穩(wěn)定的排序，不適合隨機(jī)訪問頁碼。

SELECT id,name FROM user WHERE sex = 'male' AND id > 10 LIMIT 10;

在上面的SQL語句中id > 10,這個(gè)10就是上一頁的查詢內(nèi)容id，可以借助它再查詢下一頁，這樣就可以避免一時(shí)間要查詢大量數(shù)據(jù)而導(dǎo)致的時(shí)間效率低下的弊端，查詢速度非常穩(wěn)定，但適用場(chǎng)景有限，可以用作瀑布流，例如抖音，頭條新聞等。

閑談??

其實(shí)深分頁問題只能緩解，沒有根治之法。如果你去瀏覽器進(jìn)行查詢，最多給你展示的也就幾百頁，不會(huì)到萬的這個(gè)級(jí)別，用戶一般也不會(huì)去翻到最后一頁去查看感興趣的內(nèi)容，所以如果有人給你提出了這個(gè)需求，你需要和他反饋一下這個(gè)需求是否合理，可不可以改成其他相同效果的需求，例如不用直接跳轉(zhuǎn)的，只有上一頁，下一頁這樣的效果，這樣用戶體驗(yàn)也不錯(cuò)，服務(wù)器的壓力也會(huì)變小。

在實(shí)際應(yīng)用中，需要根據(jù)具體場(chǎng)景選擇合適的解決方案，并與前端和產(chǎn)品團(tuán)隊(duì)充分溝通，找到最佳實(shí)踐。

總結(jié)??

到此這篇關(guān)于一站式解決mysql深分頁問題的文章就介紹到這了,更多相關(guān)mysql深分頁內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論