上亿数据怎么玩深度分页以及是否兼容MySQL + ES + MongoDB

发布时间：2021-09-29 09:46:12 作者：柒染
来源：亿速云阅读：163

# 上亿数据怎么玩深度分页以及是否兼容MySQL + ES + MongoDB

## 引言

在大数据时代，处理上亿级别数据的分页查询成为系统设计的常见挑战。传统`LIMIT offset, size`分页方式在超大数据量下性能急剧下降，且不同数据库（MySQL、Elasticsearch、MongoDB）的分页实现机制差异显著。本文将深入探讨：
1. 深度分页的核心问题与优化方案
2. 三种数据库的深度分页实现对比
3. 混合架构下的兼容性设计方案

## 一、深度分页为什么是性能杀手？

### 1.1 传统分页的问题
```sql
-- MySQL典型分页查询
SELECT * FROM tb_order ORDER BY id DESC LIMIT 1000000, 20;

执行过程： 1. 读取1000020条完整记录 2. 丢弃前100万条 3. 返回最后20条

性能瓶颈： - 大量无效IO（尤其机械硬盘） - 内存占用飙升（需缓存中间结果） - 越往后翻页性能越差（O(n)复杂度）

1.2 不同数据库的差异表现

数据库类型	分页机制	百万级数据耗时
MySQL(InnoDB)	全表扫描+排序	>5s
Elasticsearch	默认限制10000条	0.5s(但受限制)
MongoDB	游标分页	0.8s

二、MySQL深度分页优化方案

2.1 主键连续场景：ID定位法

-- 上一页最后ID为1000000
SELECT * FROM tb_order 
WHERE id > 1000000 
ORDER BY id ASC 
LIMIT 20;

优势： - 利用聚簇索引特性 - 时间复杂度O(1)

限制： - 要求ID连续无空洞 - 不适用复杂排序条件

2.2 非连续ID场景：延迟关联

SELECT t.* FROM tb_order t
JOIN (
    SELECT id FROM tb_order
    ORDER BY create_time DESC
    LIMIT 1000000, 20
) tmp ON t.id = tmp.id;

原理： 1. 子查询先快速定位ID 2. 通过JOIN回表获取完整数据

2.3 特殊场景：预先计算分片

-- 按日期分片查询
SELECT * FROM tb_order_202301 
UNION ALL
SELECT * FROM tb_order_202302
...
LIMIT 1000000, 20;

三、Elasticsearch深度分页方案

3.1 Scroll API（游标查询）

POST /orders/_search?scroll=1m
{
  "size": 100,
  "query": {"match_all": {}}
}

-- 后续请求
POST _search/scroll
{
  "scroll_id": "DXF1ZXJ5QW5kRmV0Y2gBAAAAAA..."
}

特点： - 适合数据导出场景 - 保持上下文消耗内存 - 非实时数据

3.2 Search After（推荐方案）

{
  "size": 20,
  "query": {"term": {"status": "paid"}},
  "sort": [
    {"create_time": "desc"},
    {"_id": "asc"}
  ],
  "search_after": [1654041600, "654321"]
}

优势： - 无状态分页 - 支持实时数据 - 性能稳定

3.3 业务折衷方案

限制最大翻页深度（如只允许前100页）
结合时间范围过滤缩小数据集

四、MongoDB分页最佳实践

4.1 游标分页（find + skip）

db.orders.find()
       .sort({create_time: -1})
       .skip(1000000)
       .limit(20);

优化建议： - 配合allowDiskUse:true避免内存溢出 - 必须建立排序字段索引

4.2 范围查询分页

// 记录最后一条记录的create_time
let lastTime = ISODate("2023-01-01T00:00:00Z");

db.orders.find({
  create_time: {$lt: lastTime}
})
.sort({create_time: -1})
.limit(20);

4.3 分片集群策略

sh.addShardTag("shard1", "2023Q1");
sh.addShardTag("shard2", "2023Q2");
// 按时间范围定向查询指定分片

五、混合架构兼容方案

5.1 统一分页接口设计

public interface PaginationService {
    PageResult query(PageParam param);
}

// 参数抽象
public class PageParam {
    private String sortField;
    private Object lastValue; // 上一页最后值
    private int pageSize;
    private SortDirection direction;
}

5.2 多数据源适配层

数据源	适配实现
MySQL	延迟关联+ID定位
ES	Search After
MongoDB	范围查询+游标

5.3 性能对比决策树

graph TD
    A[是否需要实时数据] -->|是| B{排序复杂度}
    A -->|否| C[ES Scroll]
    B -->|简单| D[MySQL ID定位]
    B -->|复杂| E[ES Search After]
    D --> F[数据量>1亿?]
    F -->|是| G[MongoDB分片]
    F -->|否| H[MySQL优化]

六、实战案例：电商订单系统

6.1 场景需求

3亿+订单数据
多维度排序（金额/时间/商家）
跨MySQL+ES混合存储

6.2 最终方案

热数据（3个月内）：ES Search After分页
冷数据：MySQL分区表+ID分段查询
统计报表：MongoDB预聚合+游标导出

6.3 性能指标

方案	第100万页响应	内存消耗
原生MySQL	12.8s	4.2GB
ES SearchAfter	0.3s	200MB
混合方案	0.5s	500MB

七、总结建议

避免深度分页：通过业务设计限制翻页深度
选择合适的工具：
- 简单查询：MySQL优化方案
- 复杂搜索：Elasticsearch
- 日志型数据：MongoDB分片
混合架构关键：
- 统一分页参数抽象
- 实现数据源适配器
- 建立决策路由规则

技术选型没有银弹，需根据具体业务场景的数据规模、实时性要求、排序复杂度等因素综合决策。建议在方案实施前用实际数据量进行基准测试。 “`