一、深分页问题背景
当分页偏移量（OFFSET）过大时（如LIMIT 100000, 20），MySQL需要扫描并丢弃前100,000条记录，导致：
- 查询性能急剧下降
- 服务器资源浪费
- 响应时间变长
二、解决方案详解
1. 游标查询（Cursor-based Pagination）
原理：通过记录上一页的最后一条数据的唯一标识（如自增ID），作为下一页的起始条件。
示例：
-- 第一页 SELECT * FROM products ORDER BY id LIMIT 20;-- 第二页（假设第一页最后一条ID=123） SELECT * FROM products WHERE id > 123 ORDER BY id LIMIT 20;
优点：
- 无需计算OFFSET，性能稳定
- 适合有序数据（如按ID、时间排序）
缺点：
- 仅支持单向分页（无法直接跳转到第N页）
- 需确保排序字段唯一且连续
2. 子查询优化
原理：先通过子查询定位目标数据范围，再关联查询完整数据。
示例：
SELECT * FROM products WHERE id >= (SELECT id FROM products ORDER BY id LIMIT 100000, 1 ) LIMIT 20;
变体（覆盖索引优化）：
SELECT p.* FROM products p JOIN (SELECT id FROM products ORDER BY id LIMIT 100000, 20 ) AS tmp ON p.id = tmp.id;
适用场景：
- 需保留
ORDER BY + LIMIT的复杂查询 - 排序字段有索引
3. 延迟关联（Inner Join + 覆盖索引）
原理：先通过索引查询主键ID列表，再通过ID关联查询完整数据。
步骤：
- 创建覆盖索引：
ALTER TABLE products ADD INDEX idx_order (price, id); - 执行延迟关联查询：
SELECT p.* FROM products p INNER JOIN (SELECT id FROM products ORDER BY price DESC, id LIMIT 100000, 20 ) AS tmp ON p.id = tmp.id;
优点：
- 索引扫描替代全表扫描
- 减少IO压力
4. 数据分片（Sharding）
策略：
- 水平分片：按范围/哈希将数据分散到不同表或库
-- 按ID范围分片示例 SELECT * FROM products_part3 WHERE id > 123 ORDER BY id LIMIT 20;
- 垂直分片：将热点字段拆分到独立表
工具支持：
- Vitess、MyCat等中间件
- 应用层路由（如按用户ID哈希分片）
5. 使用数据库中间件
推荐方案：
- ProxySQL：缓存查询结果，支持结果集复用
- ShardingSphere：透明化分片逻辑
- Redis缓存层：缓存热门分页数据
示例架构：
应用层 → 中间件（分片/缓存） → MySQL集群
6. 控制分页深度
实施方法：
- 前端限制：禁用"跳转到第N页"，改用"下一页"按钮
- 后端限制：
// Java示例：限制最大偏移量 int maxOffset = 2000; // 相当于100页（每页20条） if (pageNum > 100) {throw new IllegalArgumentException("分页过深"); }
- 混合策略：前100页用常规分页，超页数改用关键词搜索
三、方案对比
| 方案 | 性能 | 实现复杂度 | 适用场景 |
|---|---|---|---|
| 游标查询 | ★★★★ | ★ | 有序数据，移动端分页 |
| 子查询 | ★★★ | ★★ | 需保留复杂排序 |
| 延迟关联 | ★★★★ | ★★ | 大数据量，有合适索引 |
| 数据分片 | ★★★★★ | ★★★★ | 超大规模数据（亿级以上） |
| 中间件 | ★★★★ | ★★★ | 分布式架构，需要统一入口 |
| 控制分页深度 | ★ | ★ | 快速见效的临时方案 |
四、最佳实践建议
- 优先使用游标分页：适用于90%的深分页场景
- 结合索引优化：确保
ORDER BY字段有索引 - 监控告警：对超过阈值的分页请求进行日志记录
- 渐进式改造：从限制分页深度开始，逐步引入游标查询
五、扩展思考
- ES集成：对全文检索场景，可考虑Elasticsearch分页
- 预计算：定时生成热门数据快照
- CDN缓存：静态内容分页结果可缓存至CDN