SQL深度分页问题案例实战

概述

对比

工作原理

-- 先查询总数SELECTCOUNT(*)FROMtrade_orderWHEREuser_id='xxx';-- 第一页（page=1, size=10）SELECT*FROMtrade_orderWHEREuser_id='xxx'ORDERBYcreate_timeDESCLIMIT10OFFSET0;-- 第二页（page=2, size=10）SELECT*FROMtrade_orderWHEREuser_id='xxx'ORDERBYcreate_timeDESCLIMIT10OFFSET10;

执行流程：

1. 执行COUNT(*)查询获取总记录数
2. 根据页码计算OFFSET = (page - 1) * size
3. 执行主查询，跳过OFFSET条记录
4. 返回当前页数据和总数

-- 第一页（无游标）SELECT*FROMtrade_orderWHEREuser_id='xxx'ORDERBYcreate_timeDESC,idDESCLIMIT11;-- 查询11条，用于判断是否有更多数据-- 第二页（使用游标）SELECT*FROMtrade_orderWHEREuser_id='xxx'AND(create_time<'2025-12-16 10:00:00'OR(create_time='2025-12-16 10:00:00'ANDid<'xxx-uuid'))ORDERBYcreate_timeDESC,idDESCLIMIT11;

执行流程：

1. 如果有游标，解码游标获取createTime和id
2. 添加游标条件：create_time < cursor.createTime OR (create_time = cursor.createTime AND id < cursor.id)
3. 查询size + 1条数据（多查1条用于判断是否有更多数据）
4. 如果返回size + 1条，说明还有更多数据，返回前size条并生成下一个游标
5. 如果返回 ≤size条，说明没有更多数据

性能对比

查询性能对比

说明：

• 传统分页的COUNT(*)查询时间随数据量线性增长
• 传统分页的OFFSET越大，查询越慢
• 游标分页性能稳定，不受数据量和页码影响

数据库负载对比

代码示例

传统分页示例

请求

POST /api-portal/trade/order/page { "page": 2, "size": 10, "status": 10, "createTime": ["2025-11-16 00:00:00", "2025-12-16 00:00:00"] }

响应

{ "code": 0, "data": { "list": [...], "total": 1000, "page": 2, "size": 10, "pages": 100 } }

SQL执行

-- 1. 查询总数 SELECT COUNT(*) FROM trade_order WHERE user_id = 'xxx' AND status = 10 AND create_time BETWEEN '2025-11-16' AND '2025-12-16'; -- 2. 查询数据 SELECT * FROM trade_order WHERE user_id = 'xxx' AND status = 10 AND create_time BETWEEN '2025-11-16' AND '2025-12-16' ORDER BY create_time DESC LIMIT 10 OFFSET 10;

游标分页示例

首次请求（无游标）

POST /api-portal/trade/order/cursor-page { "size": 10, "status": 10, "createTime": ["2025-11-16 00:00:00", "2025-12-16 00:00:00"] }

响应

{ "code": 0, "data": { "list": [...], "nextCursor": "MjAyNS0xMi0xNlQxMDowMDowMHw2ZTdhNTVlYi0zMzc0LTRjMDYtYmEzZi1mZGUwMmU5MGU5MWU=", "hasMore": true } }

后续请求（使用游标）

POST /api-portal/trade/order/cursor-page { "cursor": "MjAyNS0xMi0xNlQxMDowMDowMHw2ZTdhNTVlYi0zMzc0LTRjMDYtYmEzZi1mZGUwMmU5MGU5MWU=", "size": 10, "status": 10, "createTime": ["2025-11-16 00:00:00", "2025-12-16 00:00:00"] }

SQL执行

-- 查询 size + 1 条数据 SELECT * FROM trade_order WHERE user_id = 'xxx' AND status = 10 AND create_time BETWEEN '2025-11-16' AND '2025-12-16' AND (create_time < '2025-12-16 10:00:00' OR (create_time = '2025-12-16 10:00:00' AND id < 'xxx-uuid')) ORDER BY create_time DESC, id DESC LIMIT 11;

⚠️需要注意的问题：

1. 游标设计

• 游标必须唯一且稳定（使用createTime + id组合）
• 游标字段必须有索引
• 使用 Base64 编码保护游标

2. 排序字段

• 必须使用唯一字段作为排序依据（如id）
• 避免使用可能重复的字段（如createTime单独排序）

3. 游标失效

• 如果数据被删除，游标可能失效
• 需要处理游标解析失败的情况

4. 关键字查询

• JOIN 查询时需要注意性能
• 使用DISTINCT去重

游标分页最佳实践

✅推荐做法：

1. 游标格式

// 使用 createTime|id 格式，Base64编码 cursor = Base64.encode("2025-12-16T10:00:00|uuid-string")

2. 排序规则

ORDER BY create_time DESC, id DESC -- 确保排序的唯一性和稳定性

3. 游标条件

WHERE (create_time < cursor.createTime OR (create_time = cursor.createTime AND id < cursor.id))

4. 判断是否有更多数据

// 查询 size + 1 条 List<Order> orders = query(size + 1); boolean hasMore = orders.size() > size; if (hasMore) { orders = orders.subList(0, size); nextCursor = createCursor(orders.get(size - 1)); }

总结

选择建议