MySQL深分頁進(jìn)行性能優(yōu)化的常見方法
引言：深分頁，真的只是“翻頁慢”那么簡單嗎？
在面試中，你是否遇到過這樣的問題？
“你了解 MySQL 深分頁的性能問題嗎？如何優(yōu)化？”
又或者在真實項目中，當(dāng)你翻到第 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ù)場景進(jìn)行優(yōu)化？
- 有哪些可落地的代碼實踐？
- 如何用一個高性能的游標(biāo)分頁方案替代傳統(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)，運(yùn)營人員需要查看歷史訂單數(shù)據(jù)，而這些訂單數(shù)據(jù)量非常龐大（千萬級），他們經(jīng)常會翻到第 1000 頁查看訂單（每頁 20 條）。
你發(fā)現(xiàn)系統(tǒng)在翻頁到后面時接口響應(yīng)非常慢，排查后定位到是 MySQL 查詢耗時嚴(yán)重。
這時候我們就需要 優(yōu)化深分頁的查詢方式。
四、優(yōu)化思路
方法一：使用覆蓋索引
SELECT id FROM orders ORDER BY id LIMIT 100000, 20; SELECT * FROM orders WHERE id IN (...);
通過先查主鍵，再回表查詳細(xì)數(shù)據(jù)，減少回表成本。
方法二：記錄上一次的游標(biāo)（推薦）
使用**“基于游標(biāo)的分頁”**，也叫作“Keyset Pagination”或“Seek 方法”。
思路是：不要使用 LIMIT offset，而是通過上一次查詢的最后一條數(shù)據(jù)的主鍵或排序字段作為游標(biāo)，下次查詢直接從該游標(biāo)之后開始。
SELECT * FROM orders WHERE id > ? ORDER BY id ASC LIMIT 20;
優(yōu)勢：
- 避免跳過大量數(shù)據(jù)，性能優(yōu)越
- 更適合實時數(shù)據(jù)流或按時間排序的場景
五、實戰(zhàn)代碼：Java 工具類實現(xiàn)游標(biāo)分頁
我們封裝一個通用的分頁工具類，支持游標(biāo)分頁，適用于 Spring + MyBatis/MyBatis-Plus 項目。
1. 分頁請求參數(shù)類
public class CursorPageRequest {
/**
* 游標(biāo)字段，如 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 {
/**
* 查詢游標(biāo)分頁數(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 作為下次游標(biāo)
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 | 簡單通用 | 深分頁性能差 |
| 游標(biāo)分頁（Keyset） | 性能優(yōu)秀，避免跳過大量數(shù)據(jù) | 不支持跳頁，只支持順序加載 |
建議：在數(shù)據(jù)量大、分頁頁碼深的業(yè)務(wù)場景中，優(yōu)先使用 游標(biāo)分頁，尤其是管理后臺、數(shù)據(jù)分析系統(tǒng)、接口服務(wù)等。
八、建議與擴(kuò)展
- 游標(biāo)字段最好是唯一遞增的，比如主鍵 ID、時間戳等
- 多字段組合游標(biāo)可以支持更復(fù)雜的排序場景
- 可以將分頁封裝為 AOP 或 BaseService 通用組件
以上就是MySQL深分頁進(jìn)行性能優(yōu)化的常見方法的詳細(xì)內(nèi)容，更多關(guān)于MySQL深分頁性能優(yōu)化的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
這篇文章主要為大家詳細(xì)介紹了linux上mysql安裝詳細(xì)教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-01-01
MySQL 有輸入輸出參數(shù)的存儲過程實例2009-08-08
mysql設(shè)置指定ip遠(yuǎn)程訪問連接實例
這篇文章主要介紹了mysql設(shè)置指定ip遠(yuǎn)程訪問連接的方法,分別實例講述了從任意主機(jī)和指定ip訪問遠(yuǎn)程MySQL數(shù)據(jù)庫的方法,代碼簡單功能實用,需要的朋友可以參考下2014-10-10
這篇文章主要介紹了mysql查詢當(dāng)天的數(shù)據(jù),第一種數(shù)量小的時候用,數(shù)據(jù)量稍微起來巨慢,第二種速度快,但是最好配合復(fù)合索引來查,避免全表掃描,需要的朋友可以參考下2023-08-08
Mysql 5.7 忘記root密碼或重置密碼的詳細(xì)方法
在Centos中安裝完MySQL數(shù)據(jù)庫以后，不知道密碼，這可怎么辦，下面給大家說一下怎么重置密碼2016-12-12
MySQL中LIKE子句相關(guān)使用的學(xué)習(xí)教程
這篇文章主要介紹了MySQL中LIKE子句相關(guān)使用的學(xué)習(xí)教程,LIKE子句一般用于WHERE語句中,需要的朋友可以參考下2015-12-12
這篇文章主要介紹了mysql 觸發(fā)器語法與應(yīng)用,結(jié)合實例形式詳細(xì)分析了mysql 觸發(fā)器的基本語法與插入、更細(xì)、刪除等相關(guān)操作技巧,需要的朋友可以參考下2020-05-05
MySQL主從數(shù)據(jù)庫搭建的實現(xiàn)
本文主要介紹了MySQL8.0主從復(fù)制搭建步驟,包括配置文件修改、復(fù)制用戶權(quán)限設(shè)置、主庫狀態(tài)查詢、SSL證書生成及數(shù)據(jù)同步,具有一定的參考價值,感興趣的可以了解一下2025-06-06
MySQL中JSON_CONTAINS函數(shù)詳細(xì)用法示例?及場景
這篇文章主要介紹了MySQL中JSON_CONTAINS函數(shù)詳細(xì)用法示例?及場景的相關(guān)資料,MySQL JSON_CONTAINS檢查目標(biāo)JSON是否包含搜索值,文中將用法介紹的非常詳細(xì),需要的朋友可以參考下2025-06-06
本文系統(tǒng)梳理MySQL常見SQL語句格式,涵蓋數(shù)據(jù)庫與表的創(chuàng)建、刪除、修改、查詢操作,以及記錄增刪改查和多表關(guān)聯(lián)等高級查詢,同時提供索引優(yōu)化、事務(wù)處理、臨時表等實用技巧,感興趣的朋友一起看看吧2025-06-06