MongoDB Atlas无服务器聚合查询内存超限问题求助

我开发了一个使用Atlas MongoDB无服务器数据库的Java应用，执行包含$match、$project、$addFields、$sort、$facet、$project步骤的聚合查询。当返回大量结果时，出现QueryExceededMemoryLimitNoDiskUseAllowed异常。尝试添加allowDiskUse: true但未解决问题。在Atlas控制台复现发现，执行到$facet步骤前均正常，该步骤返回错误：Sort exceeded memory limit of 33554432 bytes, but did not opt in to external sorting。$facet用于分页结果，考虑过拆分查询但不确定是否最优。

聚合查询语句：

db.vendor_search.aggregate(
{$match: {
    $or: [
        {'searchKeys.value': {$regex: "vendor"}},
        {'searchKeys.value': {$regex: "test"}},
        {'searchKeys.valueClean': {$regex: "vendor"}},
        {'searchKeys.valueClean': {$regex: "test"}},
    ],
    buyerId: 7
}},
{$project: {
   companyId: 1,
   buyerId: 1,
   companyName: 1,
   legalForm: 1,
   country: 1,
   supplhiCompanyCode: 1,
   vat: 1,
   erpCode: 1,
   visibility: 1,
   businessStatus: 1,
   city: 1,
   logo: 1,
   location: {$concat : ["$country.value",'$city']},
   searchKeys: {
    "$filter": {
      "input": "$searchKeys",
      "cond": {
        "$or": [
          {$regexMatch: {input: "$$this.value",regex: "vendor"}},
          {$regexMatch: {input: "$$this.value",regex: "test"}},
          {$regexMatch: {input: "$$this.valueClean",regex: "vendor"}},
          {$regexMatch: {input: "$$this.valueClean",regex: "test"}}
        ]
      }
    }
  }
}},
{$addFields: {
  searchMatching: {
        $reduce: {
            input: "$searchKeys.type",
            initialValue: [],
            in: {
                $concatArrays: [
                    "$$value", 
                    {$cond: [{$in: ["$$this", "$$value"]},[], ["$$this"]]}
                ]
            }
        }
    },
    'sort.supplhiId': { $toLower: "$supplhiCompanyCode" },
    'sort.companyName': { $toLower: "$companyName" },
    'sort.location': { $toLower: {$concat : ["$country.value"," ","$city"]}},
    'sort.vat': { $toLower: "$vat" },
    'sort.companyStatus': { $toLower: "$businessStatus" },
    'sort.erpCode': { $toLower: "$erpCode" }
}},
{$sort: {"sort.companyName": 1}},
{$facet: {
      paginatedResults: [{ $skip: 0 }, { $limit: 50 }],
      totalCount: [
        {
          $count: 'count'
        }
      ]
    }
},
{$project: {paginatedResults:1, 'totalCount': {$first : '$totalCount.count'}}}
)

数据模型：

{
"buyerId": 1,
"companyId": 869048,
"address": "FP8R+52H",
"businessStatus": "AC",
"city": "Chiffa",
"companyName": "Test Algeria 25 agosto",
"country": {
    "lookupId": 78,
    "code": "DZA",
    "value": "Algeria"
},
"erpCode": null,
"legalForm": "Ltd.",
"logo": "fc4d821a-e814-49e4-96d1-f32421fdaa6d_1.jpg",
"searchKeys": [
    {
        "type": "contact",
        "value": "pebiw81522@xitudy.com",
        "valueClean": "pebiw81522xitudycom"
    },
    {
        "type": "company_registration_number",
        "value": "112211331144",
        "valueClean": "112211331144"
    },
    {
        "type": "vendor_name",
        "value": "test algeria 25 agosto ltd.",
        "valueClean": "test algeria 25 agosto ltd"
    },
    {
        "type": "contact",
        "value": "tredicisf2@ottobre2022.com",
        "valueClean": "tredicisf2ottobre2022com"
    },
    {
        "type": "contact",
        "value": "ty@s.com",
        "valueClean": "tyscom"
    },
    {
        "type": "contact",
        "value": "info@x.com",
        "valueClean": "infoxcom"
    },
    {
        "type": "tin",
        "value": "00112341675",
        "valueClean": "00112341675"
    },
    {
        "type": "contact",
        "value": "hatikog381@rxcay.com",
        "valueClean": "hatikog381rxcaycom"
    },
    {
        "type": "supplhi_id",
        "value": "100059410",
        "valueClean": "100059410"
    },
    {
        "type": "contact",
        "value": "tredici@ottobre2022.com",
        "valueClean": "trediciottobre2022com"
    },
    {
        "type": "country_key",
        "value": "00112341675",
        "valueClean": "00112341675"
    },
    {
        "type": "vat",
        "value": "00112341675",
        "valueClean": "00112341675"
    },
    {
        "type": "address",
        "value": "fp8r+52h",
        "valueClean": "fp8r52h"
    },
    {
        "type": "city",
        "value": "chiffa",
        "valueClean": "chiffa"
    },
    {
        "type": "contact",
        "value": "prova@supplhi.com",
        "valueClean": "provasupplhicom"
    },
    {
        "type": "contact",
        "value": "saraxo2669@dmonies.com",
        "valueClean": "saraxo2669dmoniescom"
    }
],
"supplhiCompanyCode": "100059410",
"vat": "00112341675",
"visibility": true
}

1. 明确核心限制：无服务器集群不支持allowDiskUse #

Atlas无服务器MongoDB出于架构设计限制，完全不允许使用磁盘进行外部排序，因此allowDiskUse: true参数无效，必须通过优化查询逻辑或索引来降低内存占用。

2. 优化排序逻辑，减少内存负载 #

当前管道中$sort在$facet之前，需要对所有匹配$match的文档做全局排序，数据量大时必然触发内存超限。可通过以下方式优化：

• 提前缩小结果集：给searchKeys相关的正则查询添加索引（见下文），减少进入排序阶段的文档数量。
• 简化排序字段：直接在$sort中处理原始字段的小写转换，避免生成额外的sort对象占用内存：

  // 替代原$addFields中的sort.companyName与$sort步骤
  {$sort: {companyName: {$toLower: "$companyName"}}}
  

3. 添加复合索引，跳过内存排序 #

针对$match+$sort的组合条件创建复合索引，让MongoDB直接利用索引返回排序后的结果，无需在内存中做全量排序：

// 基础复合索引（按原始companyName排序）
db.vendor_search.createIndex({buyerId: 1, companyName: 1})

// 支持小写排序的表达式索引（MongoDB 4.2+）
db.vendor_search.createIndex({
  buyerId: 1,
  lowercaseCompanyName: {$toLower: "$companyName"}
})

创建索引后，修改$sort步骤直接使用索引字段，彻底规避内存排序的内存压力。

4. 拆分查询替代$facet实现分页 #

$facet需要同时处理分页和计数两个分支，会额外占用内存。可拆分为两个独立查询：

1. 计数查询：单独获取总条数

   db.vendor_search.countDocuments({
       $or: [
           {'searchKeys.value': {$regex: "vendor"}},
           {'searchKeys.value': {$regex: "test"}},
           {'searchKeys.valueClean': {$regex: "vendor"}},
           {'searchKeys.valueClean': {$regex: "test"}},
       ],
       buyerId: 7
   })
   

2. 分页查询：单独获取分页数据

   db.vendor_search.aggregate([
   {$match: {
       $or: [
           {'searchKeys.value': {$regex: "vendor"}},
           {'searchKeys.value': {$regex: "test"}},
           {'searchKeys.valueClean': {$regex: "vendor"}},
           {'searchKeys.valueClean': {$regex: "test"}},
       ],
       buyerId: 7
   }},
   {$project: {
      // 保留原project字段
   }},
   {$addFields: {
      // 保留原addFields字段
   }},
   {$sort: {"sort.companyName": 1}},
   {$skip: 0},
   {$limit: 50}
   ])
   

拆分后每个查询的内存负载更低，更适配无服务器集群的内存限制。

5. 精简管道数据传递 #

• 移除冗余过滤：$match阶段已过滤出包含指定关键词的文档，$project中对searchKeys的二次过滤可考虑移除，减少数组数据处理量。
• 裁剪不必要字段：在$project阶段仅保留后续步骤必需的字段，例如searchMatching若仅用于前端展示，可移至客户端生成，降低管道内的数据体积。

内容的提问来源于stack exchange，提问作者Luca Riccitelli