Mysql分頁：高效處理海量數據的核心技術

01 引言

在Web應用、移動應用或數據分析場景中，數據庫常常需要處理百萬甚至千萬級的數據記錄。一次性加載所有數據不僅效率低下，還會消耗大量網絡帶寬和內存資源。數據庫分頁技術正是解決這一挑戰的關鍵方案。

在日常開發中，對于列表頁面的查詢、全鏈路操作日志等，數據的結果集可以無限增大或者數據量本身很大的場景，我們常常會增加分頁，以避免一次性全量加載帶來的內存、IO的壓力。

02 分頁方式

為了測試需要，按照ID順序插入了100萬的數據。

下面是分頁插件展示的效果：

2.1 LIMIT-OFFSET

腳本

-- 獲取第3頁（每頁10條）
select * from user_info order by id asc LIMIT 10 OFFSET 20;

LIMIT后面跟的事查詢的數據數量，而OFFSET后面跟的是數據偏移量，也就是要跳過的數據量。

結果

頁數計算：

• 第一頁：1~10
• 第二頁：11~20
• 第三頁：21~30

這種分頁的方式，小編之前是不知道的，在查線上問題的時候偶然看到一段代碼塊，就好奇的點進去看了看：

才發現原來這樣也可以分頁，真的是漲知識了。

2.2 LIMIT X,Y

limit x,y 是小編常用的分頁方式，x指偏移量，同offset。而y則指需要查詢的數量。

腳本

-- 獲取第3頁（每頁10條）
select * from user_info order by id asc LIMIT 20, 10;

結果

這種方式可能是習慣了，用起來感覺更加順手。

03 分頁使用注意事項

查詢的結果的分頁用起來比較簡單，但是使用不當的話就會出現與期望偏差的數據。

3.1 語法

語法很簡單，需要LIMIT關鍵字。

• LIMIT ${偏移量},${要顯示的記錄數}
• LIMIT ${要顯示的記錄數} OFFEST ${偏移量}

兩者任選其一。

3.2 執行順序

分頁一定是最后需要執行或者處理的，無論簡單的腳本還是復雜的腳本都是在語句的結尾。

腳本

-- 聚合查詢
select age, count(*) from user_info GROUP BY age HAVING age > 30 ORDER BY age LIMIT 20, 10;

結果

頁數計算：

• 第一頁：31~40
• 第二頁：41~50
• 第三頁：51~60

3.3 單條數據的查詢

在業務代碼中，我們如何查詢一條數據呢？Mapper的查詢結果中，返回的結果是一個數據集，要查詢一條數據我們一般都是取集合中的第一條。

例如：我們需要再數據庫中找到一個18歲的女孩。

腳本

select * from user_info WHERE age=18 AND sex='女';

結果

偽代碼

List<UserInfo> userList = userInfoService.selectByAgeAndSex(18, "女");
return CollectionUtils.isNotEmpty(userList) ? userList.get(0) : null;

乍一看代碼似乎沒有什么問題，結果是確實返回了一條。但是Mysql結果集有很多數據，需要加載到內存中甚至在微服務之間傳輸，這樣的無疑增加了資源的消耗。

我們可以直接從數據庫中只查一條數據就好了，沒有必要都查出來。我們需要使用LIMIT的另一語法：

• LIMIT 要返回的數據量 【LIMIT 1 ：返回一條數據】

select * from user_info WHERE age=18 AND sex='女' LIMIT 1;

3.4 深分頁問題

深分頁是一個無聊的話題，但是確實面試的一個考察點。

select * from user_info LIMIT 800000,10;

這已經翻了8w頁了，還要繼續翻么？這就是所謂的深分頁。誰會這么干!

當然了，這樣的查詢語句的效率是低下的，測試的數據比較簡單耗時大概1084ms。如何去優化呢？

主要原因是沒有使用到覆蓋索引，此時為了得到完整記錄就需要回表，而回表是隨機磁盤IO，速度慢消耗大。

解決辦法就是減少回表次數：

-- 根據ID自增特性
select * from user_info WHERE id > 800000 limit 10;-- 自關聯，減少回表次數
select * from user_info u INNER JOIN (select id from user_info LIMIT 800000,10) uu ON uu.id=u.id;

從一定程度上可以提高響應速度，測試結果分別可以的達到758ms、915ms

04 分頁公式

4.1 總頁數計算

分頁需要我們來計算。首先我們需要知道總共有多少數據（count），每頁多少數據（length），這樣我們才能知道能分多少頁（total）。

// 總頁數total的計算公式
int total = (int) Math.floor((this.count * 1.0d) / this.length);
if (this.count % this.length != 0) {// 除不盡，需要頁數+1this.total++;
}

注意：這里是Math.floor()向下取整，然后總記錄數除不盡每頁的數量，頁數就是+1。

4.2 偏移量計算

偏移量的計算需要知道當前是多少頁（current）了。

// 計算偏移量
int offset = (this.current - 1) * this.length;

4.3 分頁的使用

• 通過上面的公式自定義分頁
• 使用第三方的分頁，如cn.hutool.db.PageResult

05 小結

我們習慣了三方庫的分頁，可能從來都沒有自己去實現過分頁或者封裝都屬于自己框架的分頁，趕快去試試吧。