MySQL深分頁進行性能優(yōu)化的常見方法
引言：深分頁，真的只是“翻頁慢”那么簡單嗎？
在面試中，你是否遇到過這樣的問題？
“你了解 MySQL 深分頁的性能問題嗎？如何優(yōu)化？”
又或者在真實項目中，當你翻到第 1000 頁的數(shù)據(jù)時，接口突然變得異常緩慢，甚至超時崩潰。你打開慢 SQL 日志，看到那條熟悉的 LIMIT 100000, 20，心里默默嘆了口氣：“啊，又是深分頁惹的禍。”
作為一名有 8 年開發(fā)經(jīng)驗的 Java 工程師，我在多個后臺系統(tǒng)和數(shù)據(jù)中心項目中都遇到過深分頁帶來的性能瓶頸。起初我們只是加索引、調(diào) SQL，但最終發(fā)現(xiàn)：分頁的本質(zhì)，其實是數(shù)據(jù)訪問策略的設(shè)計問題。
這篇文章將帶你深入理解：
- 為什么深分頁會拖垮數(shù)據(jù)庫？
- 如何結(jié)合實際業(yè)務(wù)場景進行優(yōu)化？
- 有哪些可落地的代碼實踐？
- 如何用一個高性能的游標分頁方案替代傳統(tǒng)分頁？
一、背景介紹
在日常業(yè)務(wù)開發(fā)中，分頁查詢是非常常見的需求。例如在管理后臺系統(tǒng)中，展示訂單列表、用戶列表、日志記錄等都需要分頁加載。
通常我們會使用類似下面的 SQL：
SELECT * FROM orders ORDER BY create_time DESC LIMIT 100000, 20;
上述查詢語句的含義是：跳過 100000 條記錄，取第 100001 到 100020 條數(shù)據(jù)。這種分頁方式我們稱為“深分頁” 。
二、深分頁的性能問題
MySQL 在執(zhí)行 LIMIT offset, size 的時候，會先掃描 offset + size 條記錄，然后拋棄前 offset 條，只返回 size 條。
也就是說，LIMIT 100000, 20 實際上掃描了 100020 行，僅返回 20 行。若表數(shù)據(jù)量和 offset 很大，性能將急劇下降，甚至拖垮數(shù)據(jù)庫。
三、業(yè)務(wù)場景分析
假設(shè)你在做一個訂單系統(tǒng)，運營人員需要查看歷史訂單數(shù)據(jù)，而這些訂單數(shù)據(jù)量非常龐大（千萬級），他們經(jīng)常會翻到第 1000 頁查看訂單（每頁 20 條）。
你發(fā)現(xiàn)系統(tǒng)在翻頁到后面時接口響應(yīng)非常慢，排查后定位到是 MySQL 查詢耗時嚴重。
這時候我們就需要 優(yōu)化深分頁的查詢方式。
四、優(yōu)化思路
方法一：使用覆蓋索引
SELECT id FROM orders ORDER BY id LIMIT 100000, 20; SELECT * FROM orders WHERE id IN (...);
通過先查主鍵，再回表查詳細數(shù)據(jù)，減少回表成本。
方法二：記錄上一次的游標（推薦）
使用**“基于游標的分頁”**，也叫作“Keyset Pagination”或“Seek 方法”。
思路是：不要使用 LIMIT offset，而是通過上一次查詢的最后一條數(shù)據(jù)的主鍵或排序字段作為游標，下次查詢直接從該游標之后開始。
SELECT * FROM orders WHERE id > ? ORDER BY id ASC LIMIT 20;
優(yōu)勢：
- 避免跳過大量數(shù)據(jù)，性能優(yōu)越
- 更適合實時數(shù)據(jù)流或按時間排序的場景
五、實戰(zhàn)代碼：Java 工具類實現(xiàn)游標分頁
我們封裝一個通用的分頁工具類，支持游標分頁，適用于 Spring + MyBatis/MyBatis-Plus 項目。
1. 分頁請求參數(shù)類
public class CursorPageRequest {
/**
* 游標字段，如 id、時間戳等
*/
private Long cursor;
/**
* 每頁數(shù)量
*/
private int pageSize = 20;
public CursorPageRequest() {}
public CursorPageRequest(Long cursor, int pageSize) {
this.cursor = cursor;
this.pageSize = pageSize;
}
public Long getCursor() {
return cursor;
}
public void setCursor(Long cursor) {
this.cursor = cursor;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
}
2. 通用分頁返回類
import java.util.List;
public class CursorPageResponse<T> {
private List<T> data;
private Long nextCursor;
private boolean hasMore;
public CursorPageResponse(List<T> data, Long nextCursor, boolean hasMore) {
this.data = data;
this.nextCursor = nextCursor;
this.hasMore = hasMore;
}
public List<T> getData() {
return data;
}
public Long getNextCursor() {
return nextCursor;
}
public boolean isHasMore() {
return hasMore;
}
}
3. MyBatis 示例 Mapper 接口（以訂單為例）
@Mapper
public interface OrderMapper {
/**
* 查詢游標分頁數(shù)據(jù)
* @param cursor 上次最后一條記錄的 ID
* @param pageSize 每頁數(shù)量
*/
@Select("SELECT * FROM orders " +
"WHERE (:cursor IS NULL OR id > #{cursor}) " +
"ORDER BY id ASC LIMIT #{pageSize}")
List<OrderDO> listByCursor(@Param("cursor") Long cursor, @Param("pageSize") Integer pageSize);
}
4. Service 層封裝分頁邏輯
@Service
public class OrderService {
@Autowired
private OrderMapper orderMapper;
public CursorPageResponse<OrderDO> listOrdersByCursor(CursorPageRequest request) {
List<OrderDO> list = orderMapper.listByCursor(request.getCursor(), request.getPageSize());
Long nextCursor = null;
boolean hasMore = false;
if (!list.isEmpty()) {
// 獲取最后一條記錄的 ID 作為下次游標
nextCursor = list.get(list.size() - 1).getId();
hasMore = list.size() == request.getPageSize();
}
return new CursorPageResponse<>(list, nextCursor, hasMore);
}
}
六、接口調(diào)用示例
前端每次請求：
GET /orders?cursor=10021&pageSize=20
返回：
{
"data": [...],
"nextCursor": 10041,
"hasMore": true
}
前端將 nextCursor 作為下次請求的 cursor 參數(shù)實現(xiàn)“加載更多”效果。
七、總結(jié)
| 分頁方式 | 優(yōu)點 | 缺點 |
|---|---|---|
| LIMIT offset, size | 簡單通用 | 深分頁性能差 |
| 游標分頁（Keyset） | 性能優(yōu)秀，避免跳過大量數(shù)據(jù) | 不支持跳頁，只支持順序加載 |
建議：在數(shù)據(jù)量大、分頁頁碼深的業(yè)務(wù)場景中，優(yōu)先使用 游標分頁，尤其是管理后臺、數(shù)據(jù)分析系統(tǒng)、接口服務(wù)等。
八、建議與擴展
- 游標字段最好是唯一遞增的，比如主鍵 ID、時間戳等
- 多字段組合游標可以支持更復(fù)雜的排序場景
- 可以將分頁封裝為 AOP 或 BaseService 通用組件
以上就是MySQL深分頁進行性能優(yōu)化的常見方法的詳細內(nèi)容，更多關(guān)于MySQL深分頁性能優(yōu)化的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
這篇文章主要介紹了mysql如何將一個字段賦值給另一個字段，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-09-09
這篇文章主要介紹了Mysql DDL常見操作匯總，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-09-09
MySQL數(shù)據(jù)庫索引與事務(wù)從基礎(chǔ)到實踐指南
本文全面介紹了MySQL索引與事務(wù)的關(guān)鍵知識,索引通過提升查詢效率,降低寫操作開銷,確保數(shù)據(jù)一致性,合理創(chuàng)建和維護索引,選擇合適的隔離級別,是優(yōu)化數(shù)據(jù)庫性能和保障數(shù)據(jù)一致性的關(guān)鍵,感興趣的朋友跟隨小編一起看看吧2025-12-12
本書從初學(xué)者的角度出發(fā),由淺入深,循序漸進地介紹了SQL通用語法的相關(guān)知識,下面這篇文章主要給大家介紹了關(guān)于SQL通用語法以及分類的相關(guān)資料,需要的朋友可以參考下2022-12-12
本文介紹MySQL主從復(fù)制部署步驟及學(xué)校管理數(shù)據(jù)庫創(chuàng)建腳本,包含表結(jié)構(gòu)設(shè)計、示例數(shù)據(jù)插入和查詢語句,用于驗證主從同步功能,感興趣的朋友一起看看吧2025-07-07
MySQL Union合并查詢數(shù)據(jù)及表別名、字段別名用法分析
這篇文章主要介紹了MySQL Union合并查詢數(shù)據(jù)及表別名、字段別名用法,結(jié)合實例形式較為詳細的分析了mysql使用Union合并連接查詢數(shù)據(jù)以及使用as實現(xiàn)表別名與字段別名操作,需要的朋友可以參考下2018-06-06
mysql中 datatime與timestamp的區(qū)別說明
這篇文章主要介紹了mysql中 datatime與timestamp的區(qū)別說明，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-02-02
MySQL索引優(yōu)化之不適合構(gòu)建索引及索引失效的幾種情況詳解
索引是有雙面性的，合理的建立索引可以提高數(shù)據(jù)庫的效率。但是如果沒有合理的構(gòu)建索引和使用索引，可能會導(dǎo)致索引失效或者影響數(shù)據(jù)庫性能，本文主要討論的是索引失效以及不適合建立索引的場景2022-07-07
本文介紹了MySQL臨時表的概念、使用方法以及注意事項,臨時表是一種僅在當前會話中存在的表,適用于存儲臨時數(shù)據(jù)、優(yōu)化查詢性能和簡化數(shù)據(jù)操作,通過創(chuàng)建、插入數(shù)據(jù)、查詢數(shù)據(jù)以及更新和刪除數(shù)據(jù)等操作,可以實現(xiàn)臨時表的使用,感興趣的朋友一起看看吧2025-03-03
本文主要介紹了MYSQL統(tǒng)計逗號分隔字段元素的個數(shù)，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-01-01