摘要：从一次"翻到第5000页需要等待45秒"的用户投诉出发，深度剖析MySQL深分页的性能陷阱。通过LIMIT offset原理图解、回表代价的精确计算、以及5种优化方案的压测对比，揭秘为什么offset越大查询越慢、MySQL到底做了什么、以及如何将查询时间从45秒优化到0.05秒。配合时序图展示查询流程，对比电商、社交、后台管理等不同场景的最佳方案选型。
💥 翻车现场
周三下午，运营同学在群里发了一张截图。
运营同学：@哈吉米 后台订单导出功能卡死了！
截图内容：订单列表页面，进度条卡在"加载中..."，已经等了3分钟。
哈吉米查看SQL日志：
SELECT * FROM `order`
WHERE status = 1
ORDER BY create_time DESC
LIMIT 500000, 100;
-- 执行时间：45.23秒
-- 扫描行数：500100行
哈吉米："卧槽，第5000页的查询要45秒？第1页才0.01秒啊！"
查看表数据量：
SELECT COUNT(*) FROM `order`;
-- 结果：1200万行
哈吉米："1200万数据，分页查询第5000页就这么慢？"
用EXPLAIN分析：
EXPLAIN SELECT * FROM `order`
WHERE status = 1
ORDER BY create_time DESC
LIMIT 500000, 100\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: order
type: index
key: idx_create_time
key_len: 5
ref: NULL
rows: 500100 ← 需要扫描50万行
Extra: Using where
哈吉米："明明走索引了，为什么还要扫描50万行？"
南北绿豆和阿西噶阿西来了。
南北绿豆："这就是深分页问题！LIMIT 500000, 100 的本质是：扫描50万行，扔掉49.98万行，只返回100行。"
哈吉米："？？？"
阿西噶阿西："来，我给你彻底讲清楚MySQL到底做了什么。"
🤔 MySQL到底做了什么？—— LIMIT的执行原理
LIMIT offset, size的执行流程
阿西噶阿西在白板上画了一个详细的流程图。
SELECT * FROM `order` ORDER BY create_time DESC LIMIT 500000, 100;
MySQL的执行步骤：
步骤1：扫描索引idx_create_time（倒序）
↓
读取第1行的主键：id=1000001
↓
回表：根据主键到聚簇索引查询完整数据
↓
【扔掉】第1行
↓
读取第2行的主键：id=1000002
↓
回表：查询完整数据
↓
【扔掉】第2行
↓
... 循环50万次 ...
↓
读取第500001行的主键：id=500001
↓
回表：查询完整数据
↓
【保留】第1条结果 ✅
↓
... 再查询100次 ...
↓
返回100条数据
总计：
- 扫描索引：500100次
- 回表查询：500100次
- 扔掉的数据：500000行
- 返回的数据：100行
- 浪费比例：99.98%
详细时序图
sequenceDiagram
participant Query as SQL查询
participant Index as 二级索引(create_time)
participant Primary as 主键索引
participant Result as 结果集(只要100行)
Note over Query: LIMIT 500000, 100
loop 扫描前500000行（丢弃）
Query->>Index: 1. 读取第N行的主键
Index->>Primary: 2. 回表（根据主键查完整数据）
Primary->>Query: 3. 返回完整数据
Query->>Query: 4. 丢弃这行（offset内）
end
rect rgb(255, 182, 193)
Note over Query,Primary: 已经回表了500000次<br/>全部丢弃！
end
loop 扫描后100行（保留）
Query->>Index: 5. 读取第500001-500100行的主键
Index->>Primary: 6. 回表
Primary->>Result: 7. 加入结果集
end
Result->>Query: 8. 返回100条数据
Note over Query,Result: 总计回表：500100次<br/>有效数据：100行<br/>浪费比例：99.98%
南北绿豆："看到了吗？关键问题是：扫描了50万行，回表了50万次，但全部扔掉了！"
哈吉米："卧槽，这也太浪费了！"
性能数据实测
测试环境：
- 表：1200万行数据
- 索引：idx_create_time
- 硬件：8核16G，SSD
不同页数的性能对比：
| 页数 | offset | 扫描行数 | 回表次数 | 执行时间 | 数据传输 |
|---|---|---|---|---|---|
| 第1页 | 0 | 100 | 100 | 0.01秒 | 30KB |
| 第10页 | 900 | 1000 | 1000 | 0.05秒 | 300KB |
| 第100页 | 9900 | 10000 | 10000 | 0.8秒 | 3MB |
| 第1000页 | 99900 | 100000 | 100000 | 8秒 | 30MB |
| 第5000页 | 499900 | 500000 | 500000 | 45秒 | 150MB |
| 第10000页 | 999900 | 1000000 | 1000000 | 92秒 | 300MB |
性能曲线：
执行时间（秒）
|
100 | * (第10000页)
90 |
80 |
70 |
60 |
50 | * (第5000页)
40 |
30 |
20 |
10 | * (第1000页)
0 |__*__*___________|_____________|____________页数
1 100 1000 5000 10000
规律：性能随offset线性下降
阿西噶阿西："offset每增加10倍，执行时间也增加约10倍，这是线性退化！"
🚀 优化方案1：子查询优化（延迟关联）
核心思想
问题根源：回表太多次（50万次）
优化思路：先用覆盖索引查出主键ID，再回表查询完整数据
-- ❌ 传统分页（回表50万次）
SELECT * FROM `order`
ORDER BY create_time DESC
LIMIT 500000, 100;
-- ✅ 延迟关联（只回表100次）
SELECT o.*
FROM `order` o
INNER JOIN (
SELECT id FROM `order` -- 子查询：覆盖索引，不回表
ORDER BY create_time DESC
LIMIT 500000, 100
) t ON o.id = t.id;
执行流程对比
传统分页的IO操作：
1. 扫描索引：500100次（读索引树）
2. 回表：500100次（读聚簇索引）
3. 丢弃：500000行
4. 返回：100行
总IO：500100 + 500100 = 1000200次
延迟关联的IO操作：
子查询（覆盖索引）：
1. 扫描索引：500100次（读索引树）
2. 不回表（只读ID，覆盖索引）
3. 丢弃：500000个ID
4. 返回：100个ID
主查询（根据ID回表）：
1. 拿着100个ID回表：100次
总IO：500100（子查询） + 100（主查询） = 500200次
性能提升：1000200 / 500200 ≈ 2倍
性能对比：
| 方案 | 执行时间 | 提升 |
|---|---|---|
| 传统分页 | 45秒 | - |
| 延迟关联 | 18秒 | 2.5倍 |
哈吉米："只优化了一半？还有更好的方案吗？"
南北绿豆："有！标签记录法能优化到0.05秒！"
🚀 优化方案2：标签记录法（游标分页）
核心思想
不用offset，用WHERE id > last_id
-- ❌ 传统分页（扫描50万行）
SELECT * FROM `order` ORDER BY id DESC LIMIT 500000, 100;
-- ✅ 标签记录法（只扫描100行）
SELECT * FROM `order`
WHERE id < #{lastId} -- lastId是上一页最后一条的ID
ORDER BY id DESC
LIMIT 100;
为什么这么快？
查询：WHERE id < 800000 ORDER BY id DESC LIMIT 100
执行流程：
1. 在主键索引找到id < 800000的位置
2. 倒序扫描100行
3. 直接返回
总计：
- 扫描：100行
- 回表：0次（主键索引就是数据）
- 丢弃：0行
总IO：100次（仅扫描100行）
性能对比：
| 方案 | 扫描行数 | 回表次数 | 执行时间 |
|---|---|---|---|
| 传统分页 | 500100 | 500100 | 45秒 |
| 延迟关联 | 500100 | 100 | 18秒 |
| 标签记录法 | 100 | 0 | 0.05秒 |
性能提升：45秒 / 0.05秒 = 900倍！
完整代码实现
/**
* 标签记录法分页
*/
@RestController
public class OrderController {
@GetMapping("/order/page")
public Result pageOrders(@RequestParam(required = false) Long lastId,
@RequestParam(defaultValue = "20") Integer pageSize) {
List<Order> orders;
if (lastId == null) {
// 第1页
orders = orderMapper.selectFirstPage(pageSize);
} else {
// 后续页（WHERE id < lastId）
orders = orderMapper.selectNextPage(lastId, pageSize);
}
return Result.ok(orders);
}
}
// Mapper
@Mapper
public interface OrderMapper {
@Select("SELECT * FROM `order` ORDER BY id DESC LIMIT #{pageSize}")
List<Order> selectFirstPage(@Param("pageSize") Integer pageSize);
@Select("SELECT * FROM `order` WHERE id < #{lastId} ORDER BY id DESC LIMIT #{pageSize}")
List<Order> selectNextPage(@Param("lastId") Long lastId,
@Param("pageSize") Integer pageSize);
}
前端实现（下拉加载）
// Vue示例
export default {
data() {
return {
orders: [],
lastId: null,
loading: false,
noMore: false
}
},
methods: {
async loadMore() {
if (this.loading || this.noMore) return;
this.loading = true;
const resp = await axios.get('/order/page', {
params: {
lastId: this.lastId,
pageSize: 20
}
});
const newOrders = resp.data;
if (newOrders.length === 0) {
this.noMore = true;
} else {
this.orders.push(...newOrders);
// 更新lastId（最后一条的ID）
this.lastId = newOrders[newOrders.length - 1].id;
}
this.loading = false;
}
},
mounted() {
// 监听滚动到底部
window.addEventListener('scroll', () => {
if (window.scrollY + window.innerHeight >= document.body.scrollHeight - 100) {
this.loadMore();
}
});
// 加载第1页
this.loadMore();
}
}
优缺点
优点：
- ✅ 性能极好（不管第几页都是0.05秒）
- ✅ 扫描行数固定（只扫描pageSize行）
- ✅ 适合移动端（下拉加载）
缺点：
- ❌ 只能向后翻页（不能跳页）
- ❌ 不能显示总页数
- ❌ 需要前端配合（传递lastId）
- ❌ 不适合PC端（用户习惯跳页）
🚀 优化方案3：二次查询
核心思想
先查出起始ID，再根据ID范围查询
-- 第1次查询：获取起始ID（覆盖索引，快）
SELECT id FROM `order`
ORDER BY create_time DESC
LIMIT 500000, 1;
-- 假设返回：id=500001
-- 执行时间：3秒
-- 第2次查询：根据ID范围查询（走主键索引，快）
SELECT * FROM `order`
WHERE id <= 500001
ORDER BY id DESC
LIMIT 100;
-- 执行时间：0.02秒
-- 总耗时：3.02秒
为什么第1次查询快？
第1次查询：SELECT id ...
执行流程：
1. 扫描索引idx_create_time：500001次
2. 不回表（覆盖索引，只读ID）
3. 丢弃前500000个ID
4. 返回1个ID
总IO：500001次（只扫描索引，不回表）
传统分页：
总IO：500100次扫描 + 500100次回表 = 1000200次
性能差距：1000200 / 500001 ≈ 2倍
代码实现
@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
public interface OrderMapper {
@Select("SELECT id FROM `order` ORDER BY create_time DESC LIMIT #{offset}, 1")
Long selectIdByOffset(@Param("offset") Integer offset);
@Select("SELECT * FROM `order` WHERE id <= #{startId} ORDER BY id DESC LIMIT #{pageSize}")
List<Order> selectByIdRange(@Param("startId") Long startId,
@Param("pageSize") Integer pageSize);
}
性能对比：
| 方案 | 执行时间 | 提升 |
|---|---|---|
| 传统分页 | 45秒 | - |
| 二次查询 | 3秒 | 15倍 |
🚀 优化方案4：记录上次查询位置（书签法）
核心思想
记住上次查询到哪里了，下次从那里继续
/**
* 书签分页（记录查询位置）
*/
@Service
public class BookmarkPagingService {
@Autowired
private RedisTemplate<String, String> redisTemplate;
public PageResult<Order> pageOrders(String userId, Integer page, Integer pageSize) {
String bookmarkKey = "bookmark:order:" + userId;
if (page == 1) {
// 第1页：清空书签
redisTemplate.delete(bookmarkKey);
List<Order> orders = orderMapper.selectFirstPage(pageSize);
// 保存书签（最后一条的create_time和id）
if (!orders.isEmpty()) {
Order lastOrder = orders.get(orders.size() - 1);
saveBookmark(bookmarkKey, lastOrder);
}
return PageResult.of(orders);
} else {
// 后续页：从书签位置继续
Bookmark bookmark = getBookmark(bookmarkKey);
List<Order> orders = orderMapper.selectFromBookmark(
bookmark.getCreateTime(),
bookmark.getId(),
pageSize
);
// 更新书签
if (!orders.isEmpty()) {
Order lastOrder = orders.get(orders.size() - 1);
saveBookmark(bookmarkKey, lastOrder);
}
return PageResult.of(orders);
}
}
private void saveBookmark(String key, Order order) {
Bookmark bookmark = new Bookmark();
bookmark.setCreateTime(order.getCreateTime());
bookmark.setId(order.getId());
redisTemplate.opsForValue().set(key, JSON.toJSONString(bookmark), 10, TimeUnit.MINUTES);
}
}
@Mapper
public interface OrderMapper {
@Select("SELECT * FROM `order` ORDER BY create_time DESC, id DESC LIMIT #{pageSize}")
List<Order> selectFirstPage(@Param("pageSize") Integer pageSize);
@Select("SELECT * FROM `order` " +
"WHERE (create_time < #{createTime}) " +
" OR (create_time = #{createTime} AND id < #{id}) " +
"ORDER BY create_time DESC, id DESC " +
"LIMIT #{pageSize}")
List<Order> selectFromBookmark(@Param("createTime") Date createTime,
@Param("id") Long id,
@Param("pageSize") Integer pageSize);
}
优点：
- ✅ 性能稳定（每页都是0.05秒）
- ✅ 支持上一页、下一页
缺点：
- ❌ 不能跳页
- ❌ 需要Redis存储书签
🚀 优化方案5：ES + MySQL混合
核心思想
搜索走ES，详情走MySQL
@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<OrderDoc> response = esClient.search(s -> s
.index("order")
.from((pageNum - 1) * pageSize) // ES的深分页性能好
.size(pageSize)
.query(q -> q
.multiMatch(m -> m
.fields("order_no", "user_name")
.query(keyword)
)
)
.sort(sort -> sort.field(f -> f.field("create_time").order(SortOrder.Desc)))
, OrderDoc.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.selectBatchIds(ids);
// 4. 按ES返回的顺序排序
Map<Long, Order> orderMap = orders.stream()
.collect(Collectors.toMap(Order::getId, o -> o));
List<Order> sortedOrders = ids.stream()
.map(orderMap::get)
.filter(Objects::nonNull)
.collect(Collectors.toList());
return PageResult.of(sortedOrders, response.hits().total().value());
}
}
ES的深分页为什么快？
ES的分页机制：
1. ES是分布式搜索引擎
2. 每个分片独立查询
3. 协调节点归并排序
4. 使用scroll或search_after API优化深分页
性能：
- 第1页：0.05秒
- 第5000页：0.08秒（仍然很快）
- 第10000页：0.1秒
性能对比：
| 方案 | 第1页 | 第5000页 | 第10000页 |
|---|---|---|---|
| MySQL LIMIT | 0.01秒 | 45秒 | 92秒 |
| ES + MySQL | 0.05秒 | 0.08秒 | 0.1秒 |
📊 方案选型指南
不同场景的推荐方案
| 场景 | 推荐方案 | 原因 |
|---|---|---|
| 移动端列表（下拉加载） | 标签记录法 | 性能最好，只能向后翻 |
| PC端列表（需要跳页） | 延迟关联 | 支持跳页，性能中等 |
| 后台管理（跳页+导出） | 延迟关联 | 平衡性能和功能 |
| 搜索功能 | ES + MySQL | 深分页性能好，搜索能力强 |
| 数据导出（全量） | 流式查询 | 内存占用低 |
| 实时数据（如监控） | 只展示最近N条 | 限制翻页深度 |
组合使用策略
@Service
public class SmartPagingService {
public PageResult<Order> pageOrders(PagingRequest request) {
// 策略1：前100页用延迟关联
if (request.getPageNum() <= 100) {
return delayJoinPaging(request);
}
// 策略2：100页以后，强制用标签记录法
if (request.getLastId() != null) {
return bookmarkPaging(request);
}
// 策略3：如果既要深分页又要跳页，建议用ES
return Result.error("数据过多，请使用搜索功能");
}
}
🎯 深分页的本质问题
哈吉米："为什么深分页这么慢？有没有通用的优化思路？"
南北绿豆："深分页的本质问题是：无效扫描。"
问题本质
LIMIT offset, size的本质：
1. 扫描offset + size行
2. 扔掉前offset行
3. 返回size行
问题：
- offset越大，无效扫描越多
- 回表次数 = offset + size
- 浪费比例 = offset / (offset + size)
示例：
LIMIT 500000, 100
- 扫描：500100行
- 浪费：500000行
- 浪费比例：99.98%
优化的核心思路
核心思路：减少无效扫描
方法1：不扫描前面的数据
→ 标签记录法（WHERE id > last_id）
→ 只扫描需要的100行
方法2：扫描但不回表
→ 延迟关联（子查询只查ID，覆盖索引）
→ 只回表100次
方法3：换工具
→ ES擅长深分页
→ 分布式搜索引擎
阿西噶阿西："记住：深分页的根源是无效扫描，优化的核心是减少无效IO。"
🎓 面试标准答案
题目：深分页为什么慢？如何优化？
答案：
为什么慢：
LIMIT offset, size 的执行流程：
- 扫描索引：offset + size 行
- 回表：offset + size 次
- 丢弃：offset 行
- 返回：size 行
例如 LIMIT 500000, 100：
- 扫描50万行，回表50万次，全部丢弃
- 再扫描100行，回表100次，返回结果
- 浪费比例：99.98%
性能数据：
- 第1页：0.01秒
- 第100页：0.8秒
- 第1000页：8秒
- 第5000页：45秒
优化方案：
1. 标签记录法（WHERE id > last_id）
- 性能：0.05秒（所有页）
- 缺点：只能向后翻
- 适用：移动端
2. 延迟关联（子查询先查ID）
- 性能：18秒 → 3秒（提升15倍）
- 优点：支持跳页
- 适用：PC端
3. 二次查询
- 性能：45秒 → 3秒（提升15倍）
- 优点：支持跳页
4. ES + MySQL
- 性能：稳定在0.1秒内
- 优点：深分页性能好
- 缺点：架构复杂
5. 限制翻页深度
- 只允许查询前100页
- 超过100页，引导用户搜索
推荐方案：
- 移动端：标签记录法
- PC端：延迟关联 + 限制深度（前100页）
- 搜索：ES + MySQL
🎉 结束语
晚上9点，哈吉米把订单列表改成了标签记录法。
哈吉米："用标签记录法后，不管翻到第几页都是0.05秒，性能提升900倍！"
南北绿豆："对，深分页的核心是减少无效扫描，标签记录法是最优解。"
阿西噶阿西："记住：能用WHERE id > last_id就别用LIMIT offset，性能差900倍。"
哈吉米："还有延迟关联，虽然不如标签记录法，但支持跳页，适合PC端。"
南北绿豆："对，没有银弹，根据场景选方案！"
记忆口诀：
LIMIT offset扫描多，回表次数浪费高
标签记录用WHERE，只扫需要性能好
延迟关联先查ID，回表次数大减少
ES混合终极解，深分页稳定快
移动端标签PC延迟，搜索就用ES
