前言

適用場景：數(shù)據(jù)量大（百萬/千萬+）、分頁翻到很后面（page 很大）、LIMIT offset, size 越來越慢。

1. 為什么LIMIT offset, size會(huì)慢

典型寫法：

SELECT * FROM orders
WHERE mch_no = ?
ORDER BY id DESC
LIMIT 1000000, 20;

問題在于：MySQL 需要先“找到并丟棄”前 offset 條，再取后面的 size 條。
當(dāng) offset 很大時(shí)，掃描行數(shù)巨大，可能觸發(fā)：

• 大量行掃描（CPU/IO 增加）
• 臨時(shí)表 / filesort（尤其排序字段沒索引或索引用不上）
• 回表次數(shù)爆炸（SELECT * 從二級(jí)索引回主鍵再回表）

結(jié)論：深度分頁的本質(zhì)是“跳過大量數(shù)據(jù)”帶來的掃描成本。

2. 總原則（你只要記住這三條）

1. 能不用 offset 就不用：優(yōu)先用“游標(biāo)/seek”分頁（Keyset Pagination）。
2. 必須 offset 時(shí)，讓 offset 掃描盡量走索引且少回表：覆蓋索引 + 延遲關(guān)聯(lián)（Delayed Join）。
3. 分頁必須穩(wěn)定：排序字段要唯一或加唯一補(bǔ)充鍵（例如 create_time DESC, id DESC）。

3. 方案一：游標(biāo)分頁（Keyset / Seek Method）最推薦

3.1 思路

不用“第 N 頁”這種隨機(jī)跳轉(zhuǎn)思維，而是“給我下一頁”，用上一頁最后一條記錄的排序鍵作為游標(biāo)：

• ORDER BY id DESC：用 lastId 做游標(biāo)
• ORDER BY create_time DESC, id DESC：用 (lastTime, lastId) 做復(fù)合游標(biāo)

這樣每頁只掃 size 附近的數(shù)據(jù)，復(fù)雜度接近 O(size)。

3.2 單字段排序：按自增/雪花 id

SELECT *
FROM orders
WHERE mch_no = ?
  AND id < ?
ORDER BY id DESC
LIMIT ?;

• 第一頁：不傳 lastId（或傳一個(gè)超大值）
• 下一頁：把上一頁最后一條的 id 作為 lastId

索引建議：

CREATE INDEX idx_orders_mch_id ON orders(mch_no, id);

3.3 復(fù)合排序：按時(shí)間 + id（更通用）

時(shí)間排序常見，但 create_time 不唯一，所以要加 id 做 tie-breaker。

SELECT *
FROM orders
WHERE mch_no = ?
  AND (
      create_time < ?
      OR (create_time = ? AND id < ?)
  )
ORDER BY create_time DESC, id DESC
LIMIT ?;

索引建議：

CREATE INDEX idx_orders_mch_time_id ON orders(mch_no, create_time, id);

注意：where 條件和 order by 的字段順序盡量和索引一致，減少 filesort。

3.4 Spring Boot + MyBatis 示例

DTO：分頁請(qǐng)求/響應(yīng)

@Data
public class SeekPageReq {
    private String mchNo;
    private Integer pageSize = 20;

    // 單字段游標(biāo)
    private Long lastId;

    // 復(fù)合游標(biāo)（時(shí)間 + id）
    private LocalDateTime lastCreateTime;
    private Long lastTieId;
}

Mapper（XML 方式示例：復(fù)合游標(biāo)）

<select id="selectOrdersSeek" resultType="com.demo.Order">
  SELECT id, mch_no, create_time, amount, status
  FROM orders
  WHERE mch_no = #{mchNo}
  <if test="lastCreateTime != null and lastTieId != null">
    AND (
      create_time <![CDATA[ < ]]> #{lastCreateTime}
      OR (create_time = #{lastCreateTime} AND id <![CDATA[ < ]]> #{lastTieId})
    )
  </if>
  ORDER BY create_time DESC, id DESC
  LIMIT #{pageSize}
</select>

Service：返回下一頁游標(biāo)

public class SeekPageResp<T> {
    private List<T> list;
    private boolean hasMore;
    private LocalDateTime nextCreateTime;
    private Long nextTieId;
    private Long nextId;
}

public SeekPageResp<Order> pageOrders(SeekPageReq req) {
    List<Order> list = orderMapper.selectOrdersSeek(req);
    SeekPageResp<Order> resp = new SeekPageResp<>();
    resp.setList(list);
    resp.setHasMore(list.size() == req.getPageSize());

    if (!list.isEmpty()) {
        Order last = list.get(list.size() - 1);
        resp.setNextCreateTime(last.getCreateTime());
        resp.setNextTieId(last.getId());
        resp.setNextId(last.getId());
    }
    return resp;
}

4. 方案二：覆蓋索引 + 延遲關(guān)聯(lián)（Delayed Join）適合“必須跳頁”的場景

有些產(chǎn)品硬要“跳到第 50000 頁”。這時(shí) offset 不可避免，但你可以把“丟棄 offset 行”的成本降到最低。

4.1 思路

先只查主鍵（走覆蓋索引，避免回表），拿到一小段 id，再回表查詳情。

SELECT o.*
FROM orders o
JOIN (
    SELECT id
    FROM orders
    WHERE mch_no = ?
    ORDER BY id DESC
    LIMIT 1000000, 20
) t ON o.id = t.id
ORDER BY o.id DESC;

4.2 索引建議

CREATE INDEX idx_orders_mch_id ON orders(mch_no, id);

4.3 為什么有效

• 子查詢階段只掃描索引葉子節(jié)點(diǎn)（更輕）
• 回表只回 20 行，而不是回表 offset+size 行

仍然會(huì)掃描 offset 行的索引，但比 SELECT * LIMIT offset 好很多，尤其列多、行寬時(shí)收益明顯。

5. 方案三：分段/范圍分頁（適合按時(shí)間分區(qū)或業(yè)務(wù)天然分桶）

如果你的查詢大多按時(shí)間，比如訂單只看近 3 個(gè)月：

5.1 強(qiáng)制加時(shí)間范圍（讓查詢天然變?。?/h3>

WHERE create_time >= NOW() - INTERVAL 90 DAY

5.2 物理分區(qū)（Partition）或按月分表

• MySQL Partition（按 range 分區(qū)）
• 業(yè)務(wù)分表：orders_202501, orders_202502 …

這樣深度分頁變成“在更小的數(shù)據(jù)集上分頁”。

這不是“分頁技巧”，而是“數(shù)據(jù)治理”，效果通常是最猛的。

6. 方案四：先給用戶“可用的跳頁”，再用游標(biāo)實(shí)現(xiàn)（產(chǎn)品層折中）

現(xiàn)實(shí)里用戶想要：

• “快速跳到某個(gè)位置”
• “看到總頁數(shù)”
• “頁碼隨便點(diǎn)”

你可以這樣折中：

1. UI 上保留頁碼，但后端用游標(biāo)分頁（每次翻頁攜帶 token）
2. “跳到第 N 頁”變成：先定位錨點(diǎn)（anchor）再 seek

定位錨點(diǎn)的方法：

• 用延遲關(guān)聯(lián)查出該頁第一條 id（只查 id）
• 或用緩存的“頁錨點(diǎn)表”（每 1000 頁存一次 anchor id）

7. 統(tǒng)計(jì)總數(shù)（COUNT）怎么做更靠譜

深度分頁通常伴隨 SELECT COUNT(*) 慢的問題。

7.1 你真的需要精確總數(shù)嗎？

很多列表：用戶只想“有多少大概”，或只要“是否還有更多”。

替代方案：

• 只返回 hasMore
• 返回 estimatedTotal（估算）
• 或異步計(jì)算 total（緩存）

7.2 精確 COUNT 的索引建議

• COUNT(*) 會(huì)盡量走覆蓋索引，但仍可能很慢（范圍大）
• 優(yōu)化方式：讓 WHERE 條件盡量命中高選擇性索引，縮小范圍

8. 關(guān)鍵細(xì)節(jié)（別踩坑）

8.1 排序必須穩(wěn)定

不要只按 create_time 排序，否則同一秒插入多條會(huì)導(dǎo)致翻頁重復(fù)/漏數(shù)據(jù)。

? 正確：

ORDER BY create_time DESC, id DESC

8.2 避免SELECT *

列表頁只查需要的列，能減少 IO 和回表成本。

8.3 用 EXPLAIN 看有沒有 filesort / 臨時(shí)表

• Using filesort：排序沒走索引
• Using temporary：臨時(shí)表開銷大

8.4 InnoDB 二級(jí)索引回表成本

二級(jí)索引葉子存的是主鍵，需要回表拿其他列。
所以覆蓋索引、延遲關(guān)聯(lián)就是在對(duì)抗回表。

9. 推薦組合（直接抄）

9.1 默認(rèn)列表分頁（APP/后臺(tái)）

• ? Keyset/Seek 分頁
• 排序：create_time DESC, id DESC
• 索引：(mch_no, create_time, id)

9.2 管理后臺(tái)“跳到第 N 頁”

• ? 延遲關(guān)聯(lián)分頁（子查詢只查 id + 回表）
• 或 “頁錨點(diǎn)緩存” + seek

9.3 超大歷史數(shù)據(jù)

• ? 時(shí)間分區(qū) / 分表 + seek
• COUNT 用異步/緩存/估算

10. 快速檢查清單（上線前 2 分鐘自檢）

• 你是不是還在用 LIMIT offset, size 翻到很后面？
• 排序字段是不是唯一/穩(wěn)定？（加 id 了嗎）
• where + order by 的字段是不是能走同一個(gè)索引？
• 列表是不是還在 SELECT *？
• EXPLAIN 有沒有 Using filesort？
• 是否能用 seek 分頁替代“頁碼”？

11. 附：單字段 seek 的 MyBatis-Plus 寫法示例

LambdaQueryWrapper<Order> qw = Wrappers.<Order>lambdaQuery()
    .eq(Order::getMchNo, mchNo)
    .lt(lastId != null, Order::getId, lastId)
    .orderByDesc(Order::getId)
    .last("LIMIT " + pageSize);

List<Order> list = orderMapper.selectList(qw);

12. 結(jié)論（一句話）

深度分頁最強(qiáng)解：Keyset/Seek。

如果產(chǎn)品硬要跳頁：覆蓋索引 + 延遲關(guān)聯(lián) 來兜底。

數(shù)據(jù)特別大：分區(qū)/分表 才是長期方案。

總結(jié)

到此這篇關(guān)于Java項(xiàng)目中mysql深度分頁解決方案大全的文章就介紹到這了,更多相關(guān)mysql深度分頁內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！