在 MySQL 中，深度分页（如 LIMIT 100000, 10）会因跳过大量数据导致性能急剧下降。以下是解决深度分页问题的常见方案及其对比分析：
一、核心问题根源
- 偏移量过大：
LIMIT offset, size需要扫描并跳过前offset条记录，导致性能随offset线性下降。 - 全表扫描：未使用索引时，MySQL 需要遍历整个表或索引树。
- 回表操作：使用二级索引时，需通过主键回查数据页，增加 I/O 开销。
二、解决方案详解
1. 游标分页（Cursor-based Pagination）
原理：通过记录上一页最后一条记录的唯一标识（如自增主键 id），避免使用 OFFSET，直接定位下一页起始位置。
示例：
-- 第一页
SELECT * FROM orders ORDER BY id ASC LIMIT 10;
-- 第二页（假设上一页最后id=10）
SELECT * FROM orders WHERE id > 10 ORDER BY id ASC LIMIT 10;优点：
- 性能稳定：时间复杂度为
O(1)，不受分页深度影响。 - 无需跳过数据：直接定位到起始位置。
缺点：
- 不支持随机跳页（如直接跳转到第 1000 页）。
- 要求排序字段唯一且有序（如自增主键）。
适用场景：
- 无限滚动加载（如社交动态流）。
- 数据按唯一字段排序且无需随机跳页。
2. 延迟关联（Deferred Join）
原理：先通过覆盖索引获取主键范围，再关联主表获取完整数据，减少回表操作。
示例：
-- 先通过子查询获取主键
SELECT t1.*
FROM orders t1
INNER JOIN (
SELECT id
FROM orders
WHERE user_id = 'Chaya'
ORDER BY create_time DESC
LIMIT 1000000, 20
) t2 ON t1.id = t2.id;优点：
- 子查询仅扫描索引树，避免回表开销。
- 性能提升可达 10 倍以上。
缺点：
- 需要创建合适的联合索引。
- 无法直接支持非主键字段的排序。
适用场景：
- 查询条件涉及二级索引字段，且需排序和分页。
3. 覆盖索引优化
原理：创建包含查询字段的联合索引，避免回表操作。
示例：
-- 创建联合索引
CREATE INDEX idx_cover ON orders (user_id, create_time, amount);
-- 使用覆盖索引查询
SELECT user_id, create_time, amount
FROM orders
WHERE user_id = 'Chaya'
ORDER BY create_time DESC
LIMIT 1000000, 20;优点：
- 查询仅从索引中获取数据，避免回表。
- 性能显著提升，尤其适用于高频查询。
缺点：
- 索引占用存储空间，写操作开销增加。
- 需根据查询条件设计联合索引。
适用场景：
- 查询字段较少且可被索引覆盖。
- 排序字段与索引顺序一致。
4. 基于排序字段的“跳跃式”查询（Seek Method）
原理：记录上一页最后一条记录的排序字段值，直接定位下一页起始位置。
示例：
-- 第一页
SELECT * FROM articles ORDER BY publish_time DESC LIMIT 10;
-- 第二页（假设上一页最后一条publish_time='2024-05-07 10:00:00'）
SELECT * FROM articles
WHERE publish_time < '2024-05-07 10:00:00'
ORDER BY publish_time DESC LIMIT 10;优点：
- 无需
OFFSET，性能稳定。 - 支持非唯一排序字段（需结合主键去重）。
缺点：
- 需要处理非唯一排序字段的“次级排序条件”（如主键）。
- 无法直接跳页。
适用场景：
- 按时间倒序的分页（如新闻列表）。
5. 分段加载（Lazy Loading）
原理：按需加载数据，减少单次查询的数据量。
示例：
-- 初始加载第一页
SELECT * FROM products LIMIT 20;
-- 用户滚动加载更多时
SELECT * FROM products WHERE id > last_seen_id LIMIT 20;优点：
- 降低单次查询压力。
- 适合前端交互式加载（如无限滚动）。
缺点：
- 无法直接跳页。
- 需要前端维护
last_seen_id。
适用场景：
- 大型数据集的前端分页（如电商平台商品列表）。
三、方案对比表
| 方案 | 性能 | 支持随机跳页 | 依赖索引 | 适用场景 |
|---|---|---|---|---|
| 游标分页 | ✅ 高 | ❌ 否 | ✅ 是 | 无限滚动、自增主键排序 |
| 延迟关联 | ✅ 高 | ❌ 否 | ✅ 是 | 二级索引查询 + 排序分页 |
| 覆盖索引优化 | ✅ 高 | ❌ 否 | ✅ 是 | 高频查询字段可被索引覆盖 |
| 跳跃式查询（Seek Method） | ✅ 高 | ❌ 否 | ✅ 是 | 时间倒序分页（如新闻列表） |
| 分段加载 | ✅ 中 | ❌ 否 | ✅ 是 | 前端动态加载（如商品列表） |
四、其他优化策略
- 缓存策略：
- 对高频访问的分页结果使用 Redis 缓存。
- 缓存需设置合理的过期时间，避免数据不一致。
- 数据分片：
- 将大表按时间或业务逻辑拆分为小表（如按年分表）。
- 分散查询压力，减少单表数据量。
- 业务层规避：
- 限制最大分页深度（如不允许跳过超过 1000 条记录）。
- 提供“搜索”替代深度分页（如按关键词过滤）。
五、总结
- 首选方案：根据业务场景选择游标分页或延迟关联，避免
OFFSET。 - 索引设计：为排序字段和查询条件创建联合索引，减少回表。
- 结合缓存：对高频查询结果进行缓存，降低数据库压力。
- 权衡取舍：深度分页本质是设计缺陷，优先通过业务逻辑减少分页需求（如搜索代替分页）。
通过合理选择方案，可以显著提升 MySQL 深度分页的性能，适应大数据量场景的需求。
THE END
