引言：深分頁(yè)，真的只是“翻頁(yè)慢”那么簡(jiǎn)單嗎？

在面試中，你是否遇到過(guò)這樣的問(wèn)題？

“你了解 MySQL 深分頁(yè)的性能問(wèn)題嗎？如何優(yōu)化？”

又或者在真實(shí)項(xiàng)目中，當(dāng)你翻到第 1000 頁(yè)的數(shù)據(jù)時(shí)，接口突然變得異常緩慢，甚至超時(shí)崩潰。你打開(kāi)慢 SQL 日志，看到那條熟悉的 LIMIT 100000, 20，心里默默嘆了口氣：“啊，又是深分頁(yè)惹的禍。”

作為一名有 8 年開(kāi)發(fā)經(jīng)驗(yàn)的 Java 工程師，我在多個(gè)后臺(tái)系統(tǒng)和數(shù)據(jù)中心項(xiàng)目中都遇到過(guò)深分頁(yè)帶來(lái)的性能瓶頸。起初我們只是加索引、調(diào) SQL，但最終發(fā)現(xiàn)：分頁(yè)的本質(zhì)，其實(shí)是數(shù)據(jù)訪(fǎng)問(wèn)策略的設(shè)計(jì)問(wèn)題。

這篇文章將帶你深入理解：

• 為什么深分頁(yè)會(huì)拖垮數(shù)據(jù)庫(kù)？
• 如何結(jié)合實(shí)際業(yè)務(wù)場(chǎng)景進(jìn)行優(yōu)化？
• 有哪些可落地的代碼實(shí)踐？
• 如何用一個(gè)高性能的游標(biāo)分頁(yè)方案替代傳統(tǒng)分頁(yè)？

一、背景介紹

在日常業(yè)務(wù)開(kāi)發(fā)中，分頁(yè)查詢(xún)是非常常見(jiàn)的需求。例如在管理后臺(tái)系統(tǒng)中，展示訂單列表、用戶(hù)列表、日志記錄等都需要分頁(yè)加載。

通常我們會(huì)使用類(lèi)似下面的 SQL：

SELECT * FROM orders ORDER BY create_time DESC LIMIT 100000, 20;

上述查詢(xún)語(yǔ)句的含義是：跳過(guò) 100000 條記錄，取第 100001 到 100020 條數(shù)據(jù)。這種分頁(yè)方式我們稱(chēng)為“深分頁(yè)” 。

二、深分頁(yè)的性能問(wèn)題

MySQL 在執(zhí)行 LIMIT offset, size 的時(shí)候，會(huì)先掃描 offset + size 條記錄，然后拋棄前 offset 條，只返回 size 條。

也就是說(shuō)，LIMIT 100000, 20 實(shí)際上掃描了 100020 行，僅返回 20 行。若表數(shù)據(jù)量和 offset 很大，性能將急劇下降，甚至拖垮數(shù)據(jù)庫(kù)。

三、業(yè)務(wù)場(chǎng)景分析

假設(shè)你在做一個(gè)訂單系統(tǒng)，運(yùn)營(yíng)人員需要查看歷史訂單數(shù)據(jù)，而這些訂單數(shù)據(jù)量非常龐大（千萬(wàn)級(jí)），他們經(jīng)常會(huì)翻到第 1000 頁(yè)查看訂單（每頁(yè) 20 條）。

你發(fā)現(xiàn)系統(tǒng)在翻頁(yè)到后面時(shí)接口響應(yīng)非常慢，排查后定位到是 MySQL 查詢(xún)耗時(shí)嚴(yán)重。

這時(shí)候我們就需要 優(yōu)化深分頁(yè)的查詢(xún)方式。

四、優(yōu)化思路

方法一：使用覆蓋索引

SELECT id FROM orders ORDER BY id LIMIT 100000, 20;
SELECT * FROM orders WHERE id IN (...);

通過(guò)先查主鍵，再回表查詳細(xì)數(shù)據(jù)，減少回表成本。

方法二：記錄上一次的游標(biāo)（推薦）

使用**“基于游標(biāo)的分頁(yè)”**，也叫作“Keyset Pagination”或“Seek 方法”。

思路是：不要使用 LIMIT offset，而是通過(guò)上一次查詢(xún)的最后一條數(shù)據(jù)的主鍵或排序字段作為游標(biāo)，下次查詢(xún)直接從該游標(biāo)之后開(kāi)始。

SELECT * FROM orders WHERE id > ? ORDER BY id ASC LIMIT 20;

優(yōu)勢(shì)：

• 避免跳過(guò)大量數(shù)據(jù)，性能優(yōu)越
• 更適合實(shí)時(shí)數(shù)據(jù)流或按時(shí)間排序的場(chǎng)景

五、實(shí)戰(zhàn)代碼：Java 工具類(lèi)實(shí)現(xiàn)游標(biāo)分頁(yè)

我們封裝一個(gè)通用的分頁(yè)工具類(lèi)，支持游標(biāo)分頁(yè)，適用于 Spring + MyBatis/MyBatis-Plus 項(xiàng)目。

1. 分頁(yè)請(qǐng)求參數(shù)類(lèi)

public class CursorPageRequest {
    /**
     * 游標(biāo)字段，如 id、時(shí)間戳等
     */
    private Long cursor;

    /**
     * 每頁(yè)數(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. 通用分頁(yè)返回類(lèi)

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 {

    /**
     * 查詢(xún)游標(biāo)分頁(yè)數(shù)據(jù)
     * @param cursor 上次最后一條記錄的 ID
     * @param pageSize 每頁(yè)數(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 層封裝分頁(yè)邏輯

@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)用示例

前端每次請(qǐng)求：

GET /orders?cursor=10021&pageSize=20

返回：

{
  "data": [...],
  "nextCursor": 10041,
  "hasMore": true
}

前端將 nextCursor 作為下次請(qǐng)求的 cursor 參數(shù)實(shí)現(xiàn)“加載更多”效果。

七、總結(jié)

建議：在數(shù)據(jù)量大、分頁(yè)頁(yè)碼深的業(yè)務(wù)場(chǎng)景中，優(yōu)先使用 游標(biāo)分頁(yè)，尤其是管理后臺(tái)、數(shù)據(jù)分析系統(tǒng)、接口服務(wù)等。

八、建議與擴(kuò)展

• 游標(biāo)字段最好是唯一遞增的，比如主鍵 ID、時(shí)間戳等
• 多字段組合游標(biāo)可以支持更復(fù)雜的排序場(chǎng)景
• 可以將分頁(yè)封裝為 AOP 或 BaseService 通用組件

以上就是MySQL深分頁(yè)進(jìn)行性能優(yōu)化的常見(jiàn)方法的詳細(xì)內(nèi)容，更多關(guān)于MySQL深分頁(yè)性能優(yōu)化的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論