Mongo聚合函数使用Sort排序时内存不足导致查询失败,开启allowDiskUse=true

目录

1、问题描述

2、问题解决

1. Java代码方式解决办法

2. Mongo 原生聚合函数解决办法

---

1、问题描述

由于项目原因, 需要向数据存储至mongo，在使用过程中 , 单个document(集合)数据量大概在80万条左右 , 单条数据为16的栏位(属性) ,

现有如下需求 , 根据某些特定条件一次性查出N条数据(大致数量在8万条左右) , 并进行 sort 排序,然后在使用Java代码查询时候, 直接抛出了异常 ,

经过Debug跟踪发现在问题具体抛出点是在 进行Sort时导致的 代码如下:

List<AggregationOperation> aggregationOptions = new ArrayList<>();
        AggregationOptions publicAggregationOptions = AggregationOptions.builder().allowDiskUse(true).build();
        aggregationOptions.add(Aggregation.match(Criteria.where("benchId").is(benchDocument.getBenchId())));
        // 这里就是问题引发处....
        aggregationOptions.add(Aggregation.sort(Sort.Direction.DESC,
                "routeCode", "procedureName", "procedureCode", "paramCode"));

由以上代码抛出的异常信息如下:

org.springframework.data.mongodb.UncategorizedMongoDbException: Command failed with error 16819 (Location16819): 'Sort exceeded memory limit of 104857600 bytes, 
but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.' on server 192.168.207.234:27017. 
The full response is {"ok": 0.0, "errmsg": "Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.", "code": 16819, "codeName": "Location16819"};
 nested exception is com.mongodb.MongoCommandException: Command failed with error 16819 (Location16819): 'Sort exceeded memory limit of 104857600 bytes,
 but did not opt in to external sorting. Aborting operation. 
Pass allowDiskUse:true to opt in.' on server 192.168.207.234:27017. The full response is {"ok": 0.0, "errmsg": "Sort exceeded memory limit of 104857600 bytes,
 but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.", "code": 16819, "codeName": "Location16819"}
 at 



org.springframework.data.mongodb.core.MongoExceptionTranslator.translateExceptionIfPossible(MongoExceptionTranslator.java:138)
     at org.springframework.data.mongodb.core.MongoTemplate.potentiallyConvertRuntimeException(MongoTemplate.java:2902)
     at org.springframework.data.mongodb.core.MongoTemplate.execute(MongoTemplate.java:587)

上面的报错信息是由 spring-data-mongo 封装了原本的mongo报错信息重新打印出来的  , 稍微有点乱  , 意思就是在MongoDB中，内存中排序有100M的限制  要执行大型排序，需要启用allowDiskUse=true选项使用系统缓存将数据写入临时文件以进行排序。 

Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.

2、问题解决

1. Java代码方式解决办法

Spring Data MongoDB api不同版本对allowDiskUse设置的操作写法不同。

对于Spring Data MongoDB 1.5.x以上2.0版本以下版本并没有提供显式的封装 , 故而需要自己创建：

AggregationOptions aggregationOptions = new AggregationOptions(true, false, null);

2.0及以上版本提供内部静态类，以builder的方式设置：

AggregationOptions aggregationOptions = AggregationOptions.builder().allowDiskUse(true).build();

设置完该属性之后 , 需要将 AggregationOptions 通过   Aggregation.newAggregation()..withOptions(AggregationOptions ) 使之生效 , 具体用发如下:

AggregationOptions publicAggregationOptions = AggregationOptions.builder().allowDiskUse(true).build();
        aggregationOptions.add(Aggregation.match(Criteria.where("benchId").is(123)));
        aggregationOptions.add(Aggregation.sort(Sort.Direction.DESC,
                "routeCode", "procedureName", "procedureCode", "paramCode"));
        // .withOptions()
        Aggregation aggregation = Aggregation.newAggregation( aggregationOptions).withOptions(publicAggregationOptions);
        AggregationResults<Document> aggregate = mongoTemplate.aggregate(aggregation,
                COLLECTION_NAME, Document.class);

2. Mongo 原生聚合函数解决办法

Mongo原生聚合函数 , 取十万条数据 , 进行排序 

db.getCollection("document001").aggregate([
    {
        $project: {
            _id: 0
        }
    },
    {
        $skip: 0
    },
    {
        $limit: 100000
    },
    {
        $sort: {
            temperature: 1,
            supplierWorkNo: 1,
            checkTime:  - 1
        }
    }
])

mongo提示报错信息

 

其实解决办法 , 提示信息中也已经写了出来  加上 allowDiskUse 属性即可 , 修改后SQL 如下:

 

db.getCollection("document001").aggregate([
    {
        $project: {
            _id: 0
        }
    },
    {
        $skip: 0
    },
    {
        $limit: 77000
    },
    {
        $sort: {
            temperature: 1,
            supplierWorkNo: 1,
            checkTime: - 1
        }
    }
], {
    allowDiskUse: true
})