摘要：从一次"翻到第10000页需要30秒"的线上故障出发，深度剖析深分页的性能问题。通过LIMIT offset机制的原理图解、回表代价的计算、以及5种优化方案的完整代码和性能对比，揭秘为什么LIMIT 10000000, 20会扫描1000万行、以及如何用标签记录法、延迟关联、子查询优化等方案将查询时间从30秒降到0.1秒。配合时序图展示查询流程，给出不同场景下的最佳选型。
💥 翻车现场
周五下午，运营同学在群里@了哈吉米。
运营同学：@哈吉米 订单导出功能又卡死了！
哈吉米：什么情况？
运营同学：想导出最近的订单，翻到第10000页就一直转圈圈，等了5分钟都没反应！
哈吉米查看代码：
// 订单分页查询
@GetMapping("/order/page")
public Result pageOrders(Integer pageNum, Integer pageSize) {
// pageNum=10000, pageSize=20
int offset = (pageNum - 1) * pageSize; // offset = 199980
List<Order> orders = orderMapper.selectByPage(offset, pageSize);
return Result.ok(orders);
}
Mapper：
<select id="selectByPage" resultType="Order">
SELECT * FROM `order`
ORDER BY create_time DESC
LIMIT #{offset}, #{pageSize}
</select>
实际SQL：
SELECT * FROM `order`
ORDER BY create_time DESC
LIMIT 199980, 20;
-- 执行时间：32秒 😱
哈吉米："卧槽，怎么这么慢？表才1000万数据啊！"
EXPLAIN分析：
EXPLAIN SELECT * FROM `order` ORDER BY create_time DESC LIMIT 199980, 20\G
*************************** 1. row ***************************
type: index
key: idx_create_time
rows: 200000 ← 预估扫描20万行
Extra: Using index
哈吉米："明明走索引了，为什么还这么慢？"
下午，南北绿豆和阿西噶阿西来了。
南北绿豆："这是深分页问题！LIMIT 199980, 20 需要扫描199980+20=20万行，然后扔掉前面的199980行！"
哈吉米："？？？"
阿西噶阿西："来，我画个图给你看。"
🤔 深分页的本质问题
LIMIT offset, size 的执行流程
sequenceDiagram
participant Query as SQL查询
participant Index as 索引idx_create_time
participant PrimaryIndex as 主键索引
participant Result as 结果集
Note over Query: LIMIT 199980, 20
Query->>Index: 1. 按create_time倒序扫描索引
loop 扫描200000行
Index->>Index: 读取第1行主键
Index->>PrimaryIndex: 回表查询完整数据
PrimaryIndex->>Index: 返回数据
Note over Index: 第1行（扔掉）
Index->>Index: 读取第2行主键
Index->>PrimaryIndex: 回表查询完整数据
Note over Index: 第2行（扔掉）
Note over Index: ...扔掉199980行...
Index->>Index: 读取第199981行主键
Index->>PrimaryIndex: 回表查询完整数据
PrimaryIndex->>Result: 第1条结果 ✅
Note over Index: ...查询20行...
end
Result->>Query: 返回20条数据
Note over Query,Result: 扫描了200000行<br/>回表了200000次<br/>只返回了20行
关键问题：
LIMIT 199980, 20 的执行过程：
1. 按create_time倒序扫描索引
2. 回表199980次（查前面的数据）
3. 扔掉前面的199980行 ← 白白浪费
4. 继续回表20次（查真正需要的数据）
5. 返回这20行
总回表次数：200000次
有效数据：20行
浪费比例：99.99%
南北绿豆："看到了吗？扫描了20万行，但只要20行，前面的199980行全部浪费！"
哈吉米："卧槽，怪不得这么慢！"
为什么第1页很快，第10000页很慢？
测试对比：
| 页数 | offset | 扫描行数 | 执行时间 |
|---|---|---|---|
| 第1页 | 0 | 20 | 0.01秒 |
| 第100页 | 1980 | 2000 | 0.05秒 |
| 第1000页 | 19980 | 20000 | 0.5秒 |
| 第10000页 | 199980 | 200000 | 32秒 |
性能图：
执行时间（秒）
|
35 | * (第10000页)
30 |
25 |
20 |
15 |
10 |
5 | * (第1000页)
0 |___*_____*________________|____________页数
1 100 1000 10000
阿西噶阿西："这就是深分页问题！offset越大，扫描的行数越多，性能越差！"
🚀 优化方案1：标签记录法（推荐⭐⭐⭐⭐⭐）
原理
核心思路：不用offset，而是记录上一页的最后一条记录ID，用WHERE id > last_id。
-- ❌ 传统分页
SELECT * FROM `order` ORDER BY id DESC LIMIT 199980, 20;
-- ✅ 标签记录法
SELECT * FROM `order` WHERE id < 上一页最后一条的id ORDER BY id DESC LIMIT 20;
代码实现
// 前端传参
@Data
public class PageRequest {
private Long lastId; // 上一页最后一条记录的ID
private Integer pageSize; // 每页条数
}
// 后端查询
@GetMapping("/order/page")
public Result pageOrders(PageRequest request) {
List<Order> orders;
if (request.getLastId() == null) {
// 第1页
orders = orderMapper.selectFirstPage(request.getPageSize());
} else {
// 后续页
orders = orderMapper.selectNextPage(request.getLastId(), request.getPageSize());
}
return Result.ok(orders);
}
Mapper：
<!-- 第1页 -->
<select id="selectFirstPage" resultType="Order">
SELECT * FROM `order`
ORDER BY id DESC
LIMIT #{pageSize}
</select>
<!-- 后续页 -->
<select id="selectNextPage" resultType="Order">
SELECT * FROM `order`
WHERE id < #{lastId} <!-- 关键：WHERE id < last_id -->
ORDER BY id DESC
LIMIT #{pageSize}
</select>
性能对比
-- 传统分页（第10000页）
SELECT * FROM `order` ORDER BY id DESC LIMIT 199980, 20;
-- 执行时间：32秒
-- 扫描行数：200000
-- 标签记录法
SELECT * FROM `order` WHERE id < 800020 ORDER BY id DESC LIMIT 20;
-- 执行时间：0.02秒
-- 扫描行数：20
-- 性能提升：1600倍！
EXPLAIN对比：
EXPLAIN SELECT * FROM `order` WHERE id < 800020 ORDER BY id DESC LIMIT 20\G
type: range ← 范围查询
key: PRIMARY
rows: 20 ← 只扫描20行
Extra: Using where
优缺点
优点：
- ✅ 性能极好（不管翻到第几页，都是0.02秒）
- ✅ 扫描行数固定（只扫描pageSize行）
缺点：
- ❌ 只能向后翻页（不能跳页）
- ❌ 不能显示总页数
- ❌ 前端需要传递lastId
适用场景：
- 移动端列表（下拉加载更多）
- 时间线（微博、朋友圈）
- 消息列表
🚀 优化方案2：延迟关联（推荐⭐⭐⭐⭐）
原理
核心思路：先用覆盖索引查出ID，再回表查询完整数据。
-- ❌ 传统分页（回表20万次）
SELECT * FROM `order` ORDER BY create_time DESC LIMIT 199980, 20;
-- ✅ 延迟关联（只回表20次）
SELECT o.* FROM `order` o
INNER JOIN (
SELECT id FROM `order` ORDER BY create_time DESC LIMIT 199980, 20
) t ON o.id = t.id;
原理图解
传统分页：
1. 扫描索引idx_create_time，读取200000个主键
2. 回表200000次
3. 扔掉前面199980行
4. 返回20行
总IO：200000次回表
延迟关联：
1. 子查询：扫描索引idx_create_time，读取200000个主键（覆盖索引，不回表）
2. 扔掉前面199980行
3. 返回20个主键ID
4. 主查询：拿着20个主键回表20次
5. 返回20行
总IO：20次回表
性能提升：200000 / 20 = 10000倍！
性能对比
-- 传统分页
SELECT * FROM `order` ORDER BY create_time DESC LIMIT 199980, 20;
-- 执行时间：32秒
-- 延迟关联
SELECT o.* FROM `order` o
INNER JOIN (
SELECT id FROM `order` ORDER BY create_time DESC LIMIT 199980, 20
) t ON o.id = t.id;
-- 执行时间：2.1秒
-- 性能提升：15倍
优缺点
优点：
- ✅ 性能提升显著（10-20倍）
- ✅ 支持跳页
- ✅ 可以显示总页数
缺点：
- ⚠️ SQL稍复杂
- ⚠️ 仍然需要扫描offset+size行（比方案1慢）
适用场景：
- PC端列表（需要跳页）
- 后台管理系统
🚀 优化方案3：二次查询（推荐⭐⭐⭐）
原理
核心思路：先查ID范围，再查数据。
-- 第1次查询：只查ID（覆盖索引，快）
SELECT id FROM `order` ORDER BY create_time DESC LIMIT 199980, 1;
-- 假设返回：id = 800020
-- 第2次查询：根据ID范围查询
SELECT * FROM `order`
WHERE id <= 800020
ORDER BY id DESC
LIMIT 20;
代码实现
@Service
public class OrderService {
public List<Order> pageOrders(Integer pageNum, Integer pageSize) {
int offset = (pageNum - 1) * pageSize;
// 第1次查询：获取起始ID（覆盖索引）
Long startId = orderMapper.selectIdByOffset(offset);
if (startId == null) {
return Collections.emptyList();
}
// 第2次查询：根据ID范围查询
return orderMapper.selectByIdRange(startId, pageSize);
}
}
Mapper：
<!-- 第1次查询：只查ID -->
<select id="selectIdByOffset" resultType="Long">
SELECT id FROM `order`
ORDER BY create_time DESC
LIMIT #{offset}, 1
</select>
<!-- 第2次查询：范围查询 -->
<select id="selectByIdRange" resultType="Order">
SELECT * FROM `order`
WHERE id <= #{startId}
ORDER BY id DESC
LIMIT #{pageSize}
</select>
性能对比
-- 传统分页
SELECT * FROM `order` ORDER BY create_time DESC LIMIT 199980, 20;
-- 执行时间：32秒
-- 二次查询
-- 第1次：0.8秒
-- 第2次：0.02秒
-- 总计：0.82秒
-- 性能提升：39倍
🚀 优化方案4：游标分页（适合数据导出）
原理
核心思路：用流式查询，不一次性加载所有数据。
@Mapper
public interface OrderMapper {
// 流式查询（fetchSize控制每次取多少行）
@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000)
@Select("SELECT * FROM `order` ORDER BY id")
void streamOrders(ResultHandler<Order> handler);
}
@Service
public class OrderExportService {
public void exportOrders(OutputStream outputStream) {
// 流式处理（不会一次性加载到内存）
orderMapper.streamOrders(resultContext -> {
Order order = resultContext.getResultObject();
// 写入Excel或CSV
writeToExcel(outputStream, order);
});
}
}
性能对比
| 方案 | 内存占用 | 执行时间 | 适用场景 |
|---|---|---|---|
| 传统分页 | 20万条 × 3KB = 600MB | 32秒 | ❌ 不适合 |
| 游标分页 | 1000条 × 3KB = 3MB | 8秒 | ✅ 数据导出 |
优点：
- ✅ 内存占用低
- ✅ 适合大数据量导出
缺点：
- ❌ 只能向前遍历（不能跳页）
- ❌ 不适合用户翻页
🚀 优化方案5：ES + MySQL混合（终极方案⭐⭐⭐⭐⭐）
原理
核心思路：搜索走ES，详情走MySQL。
架构：
用户搜索 → ES（返回ID列表） → MySQL（根据ID批量查询详情）
代码实现
@Service
public class OrderSearchService {
@Autowired
private ElasticsearchClient esClient;
@Autowired
private OrderMapper orderMapper;
public PageResult<Order> searchOrders(String keyword, Integer pageNum, Integer pageSize) {
// 1. ES搜索（快）
SearchResponse<OrderES> response = esClient.search(s -> s
.index("order")
.from((pageNum - 1) * pageSize)
.size(pageSize)
.query(q -> q
.match(m -> m
.field("order_no")
.query(keyword)
)
)
.sort(sort -> sort
.field(f -> f.field("create_time").order(SortOrder.Desc))
), OrderES.class
);
// 2. 获取ID列表
List<Long> ids = response.hits().hits().stream()
.map(hit -> hit.source().getId())
.collect(Collectors.toList());
if (ids.isEmpty()) {
return PageResult.empty();
}
// 3. MySQL批量查询详情（IN查询，走主键索引）
List<Order> orders = orderMapper.selectByIds(ids);
// 4. 按ES返回的顺序排序
Map<Long, Order> orderMap = orders.stream()
.collect(Collectors.toMap(Order::getId, o -> o));
List<Order> sortedOrders = ids.stream()
.map(orderMap::get)
.collect(Collectors.toList());
return PageResult.of(sortedOrders, response.hits().total().value());
}
}
性能对比
| 方案 | 第1页 | 第10000页 | 搜索能力 |
|---|---|---|---|
| MySQL LIMIT | 0.01秒 | 32秒 | 弱 |
| ES + MySQL | 0.05秒 | 0.08秒 | 强 |
优点：
- ✅ 深分页性能稳定（不管第几页都是0.1秒内）
- ✅ 搜索能力强（全文搜索、模糊搜索）
- ✅ 支持跳页
缺点：
- ❌ 架构复杂（需要ES）
- ❌ 数据同步（MySQL → ES）
适用场景：
- 电商商品搜索
- 订单搜索
- 日志查询
📊 五种方案完整对比
| 方案 | 第1页 | 第10000页 | 支持跳页 | 显示总数 | 复杂度 | 推荐指数 |
|---|---|---|---|---|---|---|
| 传统LIMIT | 0.01秒 | 32秒 | ✅ | ✅ | ⭐ | ⭐ |
| 标签记录法 | 0.01秒 | 0.02秒 | ❌ | ❌ | ⭐⭐ | ⭐⭐⭐⭐⭐ |
| 延迟关联 | 0.01秒 | 2.1秒 | ✅ | ✅ | ⭐⭐ | ⭐⭐⭐⭐ |
| 二次查询 | 0.01秒 | 0.82秒 | ✅ | ✅ | ⭐⭐ | ⭐⭐⭐ |
| ES + MySQL | 0.05秒 | 0.08秒 | ✅ | ✅ | ⭐⭐⭐⭐ | ⭐⭐⭐⭐⭐ |
🎯 方案选型指南
场景1：移动端列表（下拉加载） → 标签记录法
// 前端
let lastId = null;
function loadMore() {
axios.get('/order/page', {
params: { lastId: lastId, pageSize: 20 }
}).then(resp => {
// 追加数据
orders.push(...resp.data);
// 更新lastId
lastId = resp.data[resp.data.length - 1].id;
});
}
场景2：PC端列表（需要跳页） → 延迟关联
-- 适合后台管理系统
SELECT o.* FROM `order` o
INNER JOIN (
SELECT id FROM `order` ORDER BY create_time DESC LIMIT #{offset}, #{pageSize}
) t ON o.id = t.id;
场景3：数据导出 → 游标分页
// 流式导出，不占内存
orderMapper.streamOrders(resultContext -> {
Order order = resultContext.getResultObject();
writeToExcel(order);
});
场景4：搜索功能 → ES + MySQL
ES搜索 → 返回ID → MySQL查详情
🎓 面试标准答案
题目：深分页为什么慢？如何优化？
答案：
为什么慢：
LIMIT offset, size 需要扫描 offset + size 行，然后扔掉前面的 offset 行。
例如：LIMIT 199980, 20
- 扫描：200000行
- 回表：200000次
- 返回：20行
- 浪费：99.99%
优化方案：
-
标签记录法（WHERE id > last_id）
- 性能最好（0.02秒）
- 只能向后翻页
-
延迟关联（子查询先查ID）
- 减少回表次数
- 支持跳页
-
二次查询（先查起始ID）
- 性能中等
- 支持跳页
-
ES + MySQL（搜索+详情）
- 性能稳定
- 架构复杂
推荐方案：
- 移动端：标签记录法
- PC端：延迟关联
- 搜索：ES + MySQL
🎉 结束语
晚上9点，哈吉米把订单列表改成了标签记录法。
哈吉米："从32秒优化到0.02秒，性能提升1600倍！"
南北绿豆："对，深分页问题很常见，标签记录法是最优解。"
阿西噶阿西："记住：不用offset，用WHERE id > last_id，性能提升几千倍！"
哈吉米："还有延迟关联，先查ID再回表，也能大幅减少回表次数。"
南北绿豆："对，根据场景选方案，没有银弹！"
记忆口诀：
深分页回表多，offset越大越慢
标签记录用WHERE，性能提升上千倍
延迟关联先查ID，回表次数大减少
ES混合终极解，搜索分页都不愁
希望这篇文章能帮你彻底搞定深分页问题！记住：能用WHERE id > last_id就别用LIMIT offset！💪
