Java项目中mysql深度分页解决方案大全

阿宝爷 發表於 2026-1-4 10:52:42

Java项目中mysql深度分页解决方案大全

<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">前言</a></li><li><a href="#_label1">1. 为什么LIMIT offset, size会慢</a></li><li><a href="#_label2">2. 总原则（你只要记住这三条）</a></li><li><a href="#_label3">3. 方案一：游标分页（Keyset / Seek Method）最推荐</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_0">3.1 思路</a></li><li><a href="#_lab2_3_1">3.2 单字段排序：按自增/雪花 id</a></li><li><a href="#_lab2_3_2">3.3 复合排序：按时间 + id（更通用）</a></li><li><a href="#_lab2_3_3">3.4 Spring Boot + MyBatis 示例</a></li><ul class="third_class_ul"><li><a href="#_label3_3_3_0">DTO：分页请求/响应</a></li><li><a href="#_label3_3_3_1">Mapper（XML 方式示例：复合游标）</a></li><li><a href="#_label3_3_3_2">Service：返回下一页游标</a></li></ul></ul><li><a href="#_label4">4. 方案二：覆盖索引 + 延迟关联（Delayed Join）适合“必须跳页”的场景</a></li><ul class="second_class_ul"><li><a href="#_lab2_4_4">4.1 思路</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_5">4.2 索引建议</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_6">4.3 为什么有效</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label5">5. 方案三：分段/范围分页（适合按时间分区或业务天然分桶）</a></li><ul class="second_class_ul"><li><a href="#_lab2_5_7">5.1 强制加时间范围（让查询天然变小）</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_5_8">5.2 物理分区（Partition）或按月分表</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label6">6. 方案四：先给用户“可用的跳页”，再用游标实现（产品层折中）</a></li><ul class="second_class_ul"></ul><li><a href="#_label7">7. 统计总数（COUNT）怎么做更靠谱</a></li><ul class="second_class_ul"><li><a href="#_lab2_7_9">7.1 你真的需要精确总数吗？</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_7_10">7.2 精确 COUNT 的索引建议</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label8">8. 关键细节（别踩坑）</a></li><ul class="second_class_ul"><li><a href="#_lab2_8_11">8.1 排序必须稳定</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_8_12">8.2 避免SELECT *</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_8_13">8.3 用 EXPLAIN 看有没有 filesort / 临时表</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_8_14">8.4 InnoDB 二级索引回表成本</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label9">9. 推荐组合（直接抄）</a></li><ul class="second_class_ul"><li><a href="#_lab2_9_15">9.1 默认列表分页（APP/后台）</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_9_16">9.2 管理后台“跳到第 N 页”</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_9_17">9.3 超大历史数据</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label10">10. 快速检查清单（上线前 2 分钟自检）</a></li><ul class="second_class_ul"></ul><li><a href="#_label11">11. 附：单字段 seek 的 MyBatis-Plus 写法示例</a></li><ul class="second_class_ul"></ul><li><a href="#_label12">12. 结论（一句话）</a></li><ul class="second_class_ul"></ul><li><a href="#_label13">总结</a></li><ul class="second_class_ul"></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>前言</h2>
<blockquote><p>适用场景：数据量大（百万/千万+）、分页翻到很后面（page 很大）、<code>LIMIT offset, size</code> 越来越慢。</p></blockquote>
<p class="maodian"><a name="_label1"></a></p><h2>1. 为什么LIMIT offset, size会慢</h2>
<p>典型写法：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT * FROM orders
WHERE mch_no = ?
ORDER BY id DESC
LIMIT 1000000, 20;
</pre></div>
<p>问题在于：MySQL 需要先“找到并丢弃”前 <code>offset</code> 条，再取后面的 <code>size</code> 条。<br />当 offset 很大时，扫描行数巨大，可能触发：</p>
<ul><li>大量行扫描（CPU/IO 增加）</li><li>临时表 / filesort（尤其排序字段没索引或索引用不上）</li><li>回表次数爆炸（<code>SELECT *</code> 从二级索引回主键再回表）</li></ul>
<p>结论：<strong>深度分页的本质是“跳过大量数据”带来的扫描成本。</strong></p>
<p class="maodian"><a name="_label2"></a></p><h2>2. 总原则（你只要记住这三条）</h2>
<ol><li><strong>能不用 offset 就不用</strong>：优先用“游标/seek”分页（Keyset Pagination）。</li><li><strong>必须 offset 时，让 offset 扫描尽量走索引且少回表</strong>：覆盖索引 + 延迟关联（Delayed Join）。</li><li><strong>分页必须稳定</strong>：排序字段要唯一或加唯一补充键（例如 <code>create_time DESC, id DESC</code>）。</li></ol>
<p class="maodian"><a name="_label3"></a></p><h2>3. 方案一：游标分页（Keyset / Seek Method）最推荐</h2>
<p class="maodian"><a name="_lab2_3_0"></a></p><h3>3.1 思路</h3>
<p>不用“第 N 页”这种随机跳转思维，而是“给我下一页”，用上一页最后一条记录的排序键作为游标：</p>
<ul><li><code>ORDER BY id DESC</code>：用 <code>lastId</code> 做游标</li><li><code>ORDER BY create_time DESC, id DESC</code>：用 <code>(lastTime, lastId)</code> 做复合游标</li></ul>
<p>这样每页只扫 <code>size</code> 附近的数据，复杂度接近 O(size)。</p>
<p class="maodian"><a name="_lab2_3_1"></a></p><h3>3.2 单字段排序：按自增/雪花 id</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT *
FROM orders
WHERE mch_no = ?
AND id < ?
ORDER BY id DESC
LIMIT ?;
</pre></div>
<ul><li>第一页：不传 lastId（或传一个超大值）</li><li>下一页：把上一页最后一条的 <code>id</code> 作为 <code>lastId</code></li></ul>
<p><strong>索引建议：</strong></p>
<div class="jb51code"><pre class="brush:sql;">CREATE INDEX idx_orders_mch_id ON orders(mch_no, id);
</pre></div>
<p class="maodian"><a name="_lab2_3_2"></a></p><h3>3.3 复合排序：按时间 + id（更通用）</h3>
<p>时间排序常见，但 <code>create_time</code> 不唯一，所以要加 <code>id</code> 做 tie-breaker。</p>
<div class="jb51code"><pre class="brush:sql;">SELECT *
FROM orders
WHERE mch_no = ?
AND (
   create_time < ?
   OR (create_time = ? AND id < ?)
)
ORDER BY create_time DESC, id DESC
LIMIT ?;
</pre></div>
<p><strong>索引建议：</strong></p>
<div class="jb51code"><pre class="brush:sql;">CREATE INDEX idx_orders_mch_time_id ON orders(mch_no, create_time, id);
</pre></div>
<blockquote><p>注意：where 条件和 order by 的字段顺序尽量和索引一致，减少 filesort。</p></blockquote>
<p class="maodian"><a name="_lab2_3_3"></a></p><h3>3.4 Spring Boot + MyBatis 示例</h3>
<p class="maodian"><a name="_label3_3_3_0"></a></p><h4>DTO：分页请求/响应</h4>
<div class="jb51code"><pre class="brush:java;">@Data
public class SeekPageReq {
private String mchNo;
private Integer pageSize = 20;

// 单字段游标
private Long lastId;

// 复合游标（时间 + id）
private LocalDateTime lastCreateTime;
private Long lastTieId;
}
</pre></div>
<p class="maodian"><a name="_label3_3_3_1"></a></p><h4>Mapper（XML 方式示例：复合游标）</h4>
<div class="jb51code"><pre class="brush:xml;"><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 <!]> #{lastCreateTime}
   OR (create_time = #{lastCreateTime} AND id <!]> #{lastTieId})
)
</if>
ORDER BY create_time DESC, id DESC
LIMIT #{pageSize}
</select>
</pre></div>
<p class="maodian"><a name="_label3_3_3_2"></a></p><h4>Service：返回下一页游标</h4>
<div class="jb51code"><pre class="brush:java;">public class SeekPageResp<T> {
private List<T> list;
private boolean hasMore;
private LocalDateTime nextCreateTime;
private Long nextTieId;
private Long nextId;
}
</pre></div>
<div class="jb51code"><pre class="brush:java;">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;
}
</pre></div>
<p class="maodian"><a name="_label4"></a></p><h2>4. 方案二：覆盖索引 + 延迟关联（Delayed Join）适合“必须跳页”的场景</h2>
<p>有些产品硬要“跳到第 50000 页”。这时 offset 不可避免，但你可以把“丢弃 offset 行”的成本降到最低。</p>
<p class="maodian"><a name="_lab2_4_4"></a></p><h3>4.1 思路</h3>
<p>先只查主键（走覆盖索引，避免回表），拿到一小段 id，再回表查详情。</p>
<div class="jb51code"><pre class="brush:sql;">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;
</pre></div>
<p class="maodian"><a name="_lab2_4_5"></a></p><h3>4.2 索引建议</h3>
<div class="jb51code"><pre class="brush:sql;">CREATE INDEX idx_orders_mch_id ON orders(mch_no, id);
</pre></div>
<p class="maodian"><a name="_lab2_4_6"></a></p><h3>4.3 为什么有效</h3>
<ul><li>子查询阶段只扫描索引叶子节点（更轻）</li><li>回表只回 20 行，而不是回表 offset+size 行</li></ul>
<blockquote><p>仍然会扫描 offset 行的索引，但比 <code>SELECT * LIMIT offset</code> 好很多，尤其列多、行宽时收益明显。</p></blockquote>
<p class="maodian"><a name="_label5"></a></p><h2>5. 方案三：分段/范围分页（适合按时间分区或业务天然分桶）</h2>
<p>如果你的查询大多按时间，比如订单只看近 3 个月：</p>
<p class="maodian"><a name="_lab2_5_7"></a></p><h3>5.1 强制加时间范围（让查询天然变小）</h3>
<div class="jb51code"><pre class="brush:sql;">WHERE create_time >= NOW() - INTERVAL 90 DAY
</pre></div>
<p class="maodian"><a name="_lab2_5_8"></a></p><h3>5.2 物理分区（Partition）或按月分表</h3>
<ul><li>MySQL Partition（按 range 分区）</li><li>业务分表：<code>orders_202501</code>, <code>orders_202502</code> …</li></ul>
<p>这样深度分页变成“在更小的数据集上分页”。</p>
<blockquote><p>这不是“分页技巧”，而是“数据治理”，效果通常是最猛的。</p></blockquote>
<p class="maodian"><a name="_label6"></a></p><h2>6. 方案四：先给用户“可用的跳页”，再用游标实现（产品层折中）</h2>
<p>现实里用户想要：</p>
<ul><li>“快速跳到某个位置”</li><li>“看到总页数”</li><li>“页码随便点”</li></ul>
<p>你可以这样折中：</p>
<ol><li>UI 上保留页码，但后端用游标分页（每次翻页携带 token）</li><li>“跳到第 N 页”变成：先定位锚点（anchor）再 seek</li></ol>
<p>定位锚点的方法：</p>
<ul><li>用延迟关联查出该页第一条 id（只查 id）</li><li>或用缓存的“页锚点表”（每 1000 页存一次 anchor id）</li></ul>
<p class="maodian"><a name="_label7"></a></p><h2>7. 统计总数（COUNT）怎么做更靠谱</h2>
<p>深度分页通常伴随 <code>SELECT COUNT(*)</code> 慢的问题。</p>
<p class="maodian"><a name="_lab2_7_9"></a></p><h3>7.1 你真的需要精确总数吗？</h3>
<p>很多列表：用户只想“有多少大概”，或只要“是否还有更多”。</p>
<p>替代方案：</p>
<ul><li>只返回 <code>hasMore</code></li><li>返回 <code>estimatedTotal</code>（估算）</li><li>或异步计算 total（缓存）</li></ul>
<p class="maodian"><a name="_lab2_7_10"></a></p><h3>7.2 精确 COUNT 的索引建议</h3>
<ul><li><code>COUNT(*)</code> 会尽量走覆盖索引，但仍可能很慢（范围大）</li><li>优化方式：让 WHERE 条件尽量命中高选择性索引，缩小范围</li></ul>
<p class="maodian"><a name="_label8"></a></p><h2>8. 关键细节（别踩坑）</h2>
<p class="maodian"><a name="_lab2_8_11"></a></p><h3>8.1 排序必须稳定</h3>
<p>不要只按 <code>create_time</code> 排序，否则同一秒插入多条会导致翻页重复/漏数据。</p>
<p>✅ 正确：</p>
<div class="jb51code"><pre class="brush:sql;">ORDER BY create_time DESC, id DESC
</pre></div>
<p class="maodian"><a name="_lab2_8_12"></a></p><h3>8.2 避免SELECT *</h3>
<p>列表页只查需要的列，能减少 IO 和回表成本。</p>
<p class="maodian"><a name="_lab2_8_13"></a></p><h3>8.3 用 EXPLAIN 看有没有 filesort / 临时表</h3>
<ul><li><code>Using filesort</code>：排序没走索引</li><li><code>Using temporary</code>：临时表开销大</li></ul>
<p class="maodian"><a name="_lab2_8_14"></a></p><h3>8.4 InnoDB 二级索引回表成本</h3>
<p>二级索引叶子存的是主键，需要回表拿其他列。<br />所以覆盖索引、延迟关联就是在对抗回表。</p>
<p class="maodian"><a name="_label9"></a></p><h2>9. 推荐组合（直接抄）</h2>
<p class="maodian"><a name="_lab2_9_15"></a></p><h3>9.1 默认列表分页（APP/后台）</h3>
<ul><li>✅ <strong>Keyset/Seek 分页</strong></li><li>排序：<code>create_time DESC, id DESC</code></li><li>索引：<code>(mch_no, create_time, id)</code></li></ul>
<p class="maodian"><a name="_lab2_9_16"></a></p><h3>9.2 管理后台“跳到第 N 页”</h3>
<ul><li>✅ <strong>延迟关联分页</strong>（子查询只查 id + 回表）</li><li>或 “页锚点缓存” + seek</li></ul>
<p class="maodian"><a name="_lab2_9_17"></a></p><h3>9.3 超大历史数据</h3>
<ul><li>✅ 时间分区 / 分表 + seek</li><li>COUNT 用异步/缓存/估算</li></ul>
<p class="maodian"><a name="_label10"></a></p><h2>10. 快速检查清单（上线前 2 分钟自检）</h2>
<ul><li>你是不是还在用 <code>LIMIT offset, size</code> 翻到很后面？</li><li>排序字段是不是唯一/稳定？（加 id 了吗）</li><li>where + order by 的字段是不是能走同一个索引？</li><li>列表是不是还在 <code>SELECT *</code>？</li><li>EXPLAIN 有没有 <code>Using filesort</code>？</li><li>是否能用 seek 分页替代“页码”？</li></ul>
<p class="maodian"><a name="_label11"></a></p><h2>11. 附：单字段 seek 的 MyBatis-Plus 写法示例</h2>
<div class="jb51code"><pre class="brush:sql;">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);
</pre></div>
<p class="maodian"><a name="_label12"></a></p><h2>12. 结论（一句话）</h2>
<p><strong>深度分页最强解：Keyset/Seek。</strong></p>
<p>如果产品硬要跳页：<strong>覆盖索引 + 延迟关联</strong> 来兜底。</p>
<p>数据特别大：<strong>分区/分表</strong> 才是长期方案。</p>
<p class="maodian"><a name="_label13"></a></p><h2>总结</h2>

頁: [1]

琼殿技术论坛's Archiver

Java项目中mysql深度分页解决方案大全