怎么解决深分页问题

解决深分页问题（即大数据量场景下访问靠后页面的性能瓶颈）的核心是避免全表扫描和大量无效数据加载。以下是专业解决方案：

1. 游标分页（Cursor-based Pagination）

原理：基于有序字段（如自增ID/时间戳）记录上一页最后一条数据的位置
优势：时间复杂度稳定为 $O(1)$
实现：

-- 获取第一页（按created_at和id升序排序）
SELECT * FROM records
ORDER BY created_at, id
LIMIT 10; -- 假设每页10条

-- 假设上一页最后一条记录的created_at为'2023-10-01 12:00:00'，id为100
-- 查询下一页
SELECT * FROM records
WHERE 
  (created_at > '2023-10-01 12:00:00') OR 
  (created_at = '2023-10-01 12:00:00' AND id > 100)
ORDER BY created_at, id
LIMIT 10;
 

2. 延迟关联（Deferred Join）

原理：先通过子查询获取主键，再关联原表
性能提升：减少回表操作
示例：

SELECT * FROM orders 
JOIN (
  SELECT id FROM orders 
  ORDER BY created_at DESC 
  LIMIT 1000000, 10  -- 深分页位置
) AS tmp USING(id);

3. 覆盖索引优化

原理：创建包含所有查询字段的联合索引
效果：避免回表操作
索引设计：

CREATE INDEX idx_covering ON orders (created_at, id, user_id, amount);

4. 业务层优化

• 禁止跳页：只允许"下一页/上一页"操作（如Twitter）
• 分区查询：按时间范围分区（如按月分表）
• 异步加载：前端滚动加载 + 后端缓存预取

5. 特殊场景方案

方案对比表：

最佳实践建议

1. 优先选择游标分页：99%场景的最优解
2. 索引设计原则：
   • 排序字段必须包含在索引中
   • 满足最左前缀原则
3. 深度限制：当 offset > 10000 时强制转为游标模式
4. 监控工具：使用EXPLAIN ANALYZE验证执行计划

示例：电商订单分页优化后，查询耗时从 1200ms 降至 15ms（数据量 1 亿+）