別再用offset和limit分頁了

終于要對MySQL優化下手了，本文將對分頁進行優化說明，希望可以得到一個合適你的方案

分頁優化

前言

一、表結構

二、初探查詢效率

三、分析查詢耗時的原因

四、優化

五、方案落地

六、總結

前言

分頁這個話題已經是老生常談了，但是有多少小伙伴一邊是既希望優化的自己的系統，另一邊在項目上還是保持自己獨有的個性。

優化這件事是需要自己主動行動起來的，自己搞測試數據，只有在測試的路上才會發現更多你未知的事情。

本文咔咔也會針對分頁優化這個話題進行解讀。

一、表結構

這個數據庫結構就是咔咔目前線上項目的表，只不過咔咔將字段名改了而已，還有將時間字段取消了。

數據庫結構如下

CREATE TABLE `tp_statistics` ( `ss_id` int(11) NOT NULL AUTO_INCREMENT, `ss_field1` decimal(11,2) NOT NULL DEFAULT '0.00', `ss_field2` decimal(11,2) NOT NULL DEFAULT '0.00', `ss_field3` decimal(11,2) NOT NULL DEFAULT '0.00', PRIMARY KEY (`ss_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3499994 DEFAULT CHARSET=utf8 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPACT;

1

2

3

4

5

6

7

8

根據以上信息可以看到目前表里邊的數據有350萬記錄，接下來就針對這350W條記錄進行查詢優化。

二、初探查詢效率

先來寫一個查詢的SQL語句，先看一下查詢耗費的時間。

根據下圖可以看到查詢時間基本忽略不計，但是要注意的是limit的偏移量值。

于是我們要一步一步的加大這個偏移量然后進行測試，先將偏移量改為10000

可以看到查詢時間還是非常理想的。

為了節省時間咔咔將這個偏移量的值直接調整到340W。

這個時候就可以看到非常明顯的變化了，查詢時間猛增到了0.79s。

出現了這樣的情況，那肯定就需要進行優化了，拿起鍵盤就是干。

三、分析查詢耗時的原因

提到分析SQL語句，必備的知識點就是explain，如果對這個工具不會使用的可以去看看MySQL的基礎部分。

根據下圖可以看到三條查詢語句都進行了表掃描。

都知道只要有關于分頁就必存在排序，那么加一個排序再來看一下查詢效率。

然后在進行對排序的語句進行分析查看。

通過這里看到當使用了排序時數據庫掃描的行數就是偏移量加上需要查詢的數量。

此時就可以知道的是，在偏移量非常大的時候，就像上圖案例中的limit 3400000,12這樣的查詢。

此時MySQL就需要查詢3400012行數據，然后在返回最后12條數據。

前邊查詢的340W數據都將被拋棄，這樣的執行結果可不是我們想要的。

咔咔之前看到相關文章說是解決這個問題的方案，要么直接限制分頁的數量，要么就優化當偏移量非常大的時候的性能。

如果你都把本文看到了這里，那怎么會讓你失望，肯定是優化大偏移量的性能問題。

四、優化

既然提到了優化，無非就那么倆點，加索引，使用其它的方案來代替這個方案。

咔咔提供的這條數據表結構信息，完全可以理解為就是圖書館的借閱記錄，字段的什么都不要去關心就可以了。

對于排序來說，在這種場景下是不會給時間加排序的，而是給主鍵加排序，并且由于添加測試數據的原因將時間字段給取消了。

接下來使用覆蓋索引加inner join的方式來進行優化。

select ss_id,ss_field1,ss_field2,ss_field3 from tp_statistics inner join ( select ss_id from tp_statistics order by ss_id limit 3000000,10) b using (ss_id);

1

從上圖可以看到查詢時間從0.8s優化到了0.4s，但是這樣的效果還是不盡人意。

于是只能更換一下思路再進行優化。

既然優化最大偏移量這條路有點坎坷，能不能從其它方面進行入手。

估計有很多同學已經知道咔咔將要拋出什么話題了。

沒錯，就是使用where > id 然后使用limit。

先來測試一波結果，在寫具體實現方案。

根據上圖可以看到這種方式是十分可行的，分頁在300W條數據以后的查詢時間也基本忽略不計。

那么這種方案要怎么實現呢！

五、方案落地

其實這個方案真的很簡單，只需要簡單的轉換一下思路即可。

當客戶端第一次獲取數據的時候就正常傳遞offset、limit倆個參數。

首次返回的數據就使用客戶端傳遞過來的offset、limit進行獲取。

當第一次的數據返回成功后。

客戶端第二次拉取數據時這個時候參數就發生改變了，就不能再是offset、limit了。

此時應該傳遞的參數就是第一次獲取的數據最后一條數據的id。

此時的參數就為last_id、limit。

后臺獲取到last_id后就可以在sql語句中使用where條件 < last_id

咔咔這里給的情況是數據在倒敘的情況下，如果正序就是大于last_id即可。

接下來咔咔使用一個案例給大家直接明了的說明。

實戰案例

如下就是將要實戰演示的案例，例如首次使用page、limit獲取到了數據。

返回結果的最后一條數據的id就是3499984

此時如果在獲取第二條記錄就不是使用offset、limit了，就是傳遞last_id和limit了。

如下圖

此時就是使用的where條件來進行直接過濾數據，條件就是id小于上次數據的最后一條id即可。

時間對比

假設現在要獲取最后一條數據

沒有優化之前

優化之后可以明顯的看到查詢時間的變化

六、總結

關于limit優化簡單幾句話概述一下。

數據量大的時候不能使用offset、limit來進行分頁，因為offset越大，查詢時間越久。

當然不能說所有的分頁都不可以，如果你的數據就那么幾千、幾萬條，那就很無所謂，隨便使用。

落地方案就是咔咔上邊的方案，首次使用offset、limit獲取數據，第二次獲取數據使用where條件到第一次數據最后一條id即可。

堅持學習、堅持寫博、堅持分享是咔咔從業以來一直所秉持的信念。希望在偌大互聯網中咔咔的文章能帶給你一絲絲幫助。我是咔咔，下期見。

MySQL SQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。