MySQL 深度分页（Deep Pagination）指在数据量巨大时（如百万、千万级），使用 LIMIT offset, count 查询靠后的页码（如第 10000 页），性能会急剧下降甚至出现查询超时。
一、问题本质：为什么深度分页慢？
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 1000000, 20;
MySQL 执行流程：
- 全表扫描/索引扫描：按
created_at排序，扫描 1,000,020 行 - 回表操作：通过主键回表取完整数据（如果是二级索引）
- 丢弃数据：丢弃前 1,000,000 行，只保留最后 20 行
随着 offset 增大，扫描的数据量线性增长，IO 和 CPU 开销飙升。
二、解决方案对比
| 方案 | 时间复杂度 | 适用场景 | 缺点 |
|---|---|---|---|
| 游标分页 | O(limit) | 无限滚动、下一页/上一页 | 无法跳页、无法显示总页数 |
| 延迟关联 | O(offset+limit) | 必须跳页但 offset < 10万 | 仍有性能开销、实现稍复杂 |
| 覆盖索引 | O(offset+limit) | 仅需索引字段 | 数据不全，需二次查询 |
| 业务限制 | - | 所有列表页 | 牺牲用户体验 |
三、具体实现方案
方案 1：游标分页（Cursor / Keyset Pagination）⭐ 推荐
利用上一页的最后一条记录作为下一页的查询条件，避免扫描 offset 行。
SQL 实现：
-- 第一页
SELECT * FROM orders
ORDER BY id DESC
LIMIT 20;
-- 获取最后一条 id 为 9980，查询下一页
SELECT * FROM orders
WHERE id < 9980 -- 利用索引直接定位
ORDER BY id DESC
LIMIT 20;
Django ORM 实现：
# views.py
def order_list(request):
cursor = request.GET.get('cursor') # 上一页最后一条的 ID
queryset = Order.objects.all().order_by('-id')
if cursor:
queryset = queryset.filter(id__lt=cursor)
# 多字段游标（如按时间+ID）
# if cursor:
# last_time, last_id = cursor.split(',')
# queryset = queryset.filter(
# Q(created_at__lt=last_time) |
# Q(created_at=last_time, id__lt=last_id)
# )
orders = queryset[:21] # 多查1条用于判断是否还有下一页
has_next = len(orders) > 20
orders = list(orders[:20])
next_cursor = orders[-1].id if orders and has_next else None
return JsonResponse({
'data': [{'id': o.id, ...} for o in orders],
'next_cursor': next_cursor,
'has_next': has_next
})
优点： 性能恒定，无论查第 1 页还是第 100 万页，都只需扫描 20 行。 缺点： 不支持跳转到指定页码（如直接跳到第 50 页）。
方案 2：延迟关联（Deferred Join）
先通过覆盖索引查询主键 ID，再通过 ID 关联取完整数据，减少回表次数。
SQL 实现：
-- 传统方式（慢）：需要回表 1000020 次
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 1000000, 20;
-- 优化方式：先查 ID（覆盖索引，不回表），再 JOIN
SELECT o.*
FROM orders o
INNER JOIN (
SELECT id FROM orders
ORDER BY created_at DESC
LIMIT 1000000, 20
) tmp ON o.id = tmp.id;
执行计划对比：
- 传统：扫描 1000020 行，回表 1000020 次
- 优化：子查询扫描 1000020 行（仅用索引），回表 仅 20 次
Django ORM 实现：
# 方式 1：使用子查询（Django 1.11+）
from django.db.models import Subquery
ids = Order.objects.order_by('-created_at').values('id')[1000000:1000020]
orders = Order.objects.filter(id__in=Subquery(ids)) # 注意：这里无序，需再排序
# 方式 2：两次查询（更推荐，避免 IN 性能问题）
ids = list(Order.objects.order_by('-created_at').values_list('id', flat=True)[1000000:1000020])
orders = Order.objects.filter(id__in=ids).order_by('-created_at')
方案 3：覆盖索引优化
如果列表页只需要展示索引中的字段（如只显示订单号和金额），根本不需要回表。
-- 假设索引为 idx_created_at_id(created_at, id, order_no, amount)
SELECT id, order_no, amount FROM orders
ORDER BY created_at DESC
LIMIT 1000000, 20;
性能： 扫描 1000020 行，但全部在内存中完成，无需回表，速度提升 10-100 倍。
方案 4：业务层限制（最实用）
绝大多数业务场景中，用户极少翻到 100 页以后。
策略：
- 限制最大页码：只允许查看前 100 页（Google 搜索也是如此）
if page > 100: raise ValidationError("只能查看前 100 页，请使用筛选条件缩小范围") - 提供筛选条件：让用户通过时间范围、状态等条件过滤，而非翻页
方案 5：数据归档（终极方案）
对于超大数据量（如日志、历史订单）：
- 冷热分离：热数据（近 3 个月）在 MySQL，冷数据归档到 ClickHouse 或 Elasticsearch
- 分表：按时间分表，查询时路由到对应表
四、性能测试数据对比
假设表 orders 有 1000 万条数据：
| 页码 | 传统 LIMIT | 延迟关联 | 游标分页 |
|---|---|---|---|
| 第 1 页 | 10ms | 10ms | 1ms |
| 第 1000 页 | 50ms | 30ms | 1ms |
| 第 10000 页 | 500ms | 200ms | 1ms |
| 第 100000 页 | 5s+ | 2s | 1ms |
五、Django 完整示例：游标分页封装
from django.db import models
from django.http import JsonResponse
from functools import wraps
class CursorPaginator:
"""游标分页装饰器"""
def __init__(self, ordering='-id', page_size=20):
self.ordering = ordering
self.page_size = page_size
def __call__(self, view_func):
@wraps(view_func)
def wrapper(request, *args, **kwargs):
# 获取游标（上一页最后一条记录的排序字段值）
cursor = request.GET.get('cursor')
# 获取基础 QuerySet（由 view_func 提供）
queryset = view_func(request, *args, **kwargs)
# 应用排序
if self.ordering.startswith('-'):
field = self.ordering[1:]
order_by = '-' + field
filter_cond = {f'{field}__lt': cursor} if cursor else {}
else:
order_by = self.ordering
filter_cond = {f'{self.ordering}__gt': cursor} if cursor else {}
queryset = queryset.order_by(order_by)
if cursor:
queryset = queryset.filter(**filter_cond)
# 多查一条判断是否有下一页
items = list(queryset[:self.page_size + 1])
has_next = len(items) > self.page_size
items = items[:self.page_size]
return {
'items': items,
'next_cursor': getattr(items[-1], field if self.ordering.startswith('-') else self.ordering) if items and has_next else None,
'has_next': has_next,
'has_prev': bool(cursor)
}
return wrapper
# 使用示例
@CursorPaginator(ordering='-created_at', page_size=20)
def get_orders(request):
return Order.objects.filter(status=request.GET.get('status', 'paid'))
六、总结建议
- 首选项：游标分页（无限滚动场景），性能最优，实现简单
- 次选项：如果必须支持跳页，使用延迟关联 + 限制最大页码（如最多 100 页）
- 架构层面：大数据量列表考虑引入 Elasticsearch 或 ClickHouse，MySQL 专注事务处理
- 索引检查：确保
ORDER BY字段有索引，最好是覆盖索引
对于 Django 项目，推荐使用 django-cursor-pagination 库，它封装了游标分页的复杂逻辑（处理多字段排序、边界情况等）。
