MongoDB聚合Lookup查询过慢问题排查求助

问题背景 #

• banks集合总记录数：9707
• rates集合总记录数：21,399
• 基于rates集合的原聚合管道：

[
  {
    "$match": {
      "isDeleted": 0,
      "rateStatus": 1
    }
  },
  {
    "$lookup": {
      "from": "banks",
      "localField": "bankId",
      "foreignField": "_id",
      "as": "bankId"
    }
  },
  {
    "$unwind": {
      "path": "$bankId",
      "preserveNullAndEmptyArrays": false
    }
  },
  {
    "$match": {
      "bankId.type": "typeOfBank"
    }
  }
]

• 已在rates集合创建isDeleted_1_rateStatus_1复合索引
• 执行计划可视化：
  
• 完整执行计划详情：

{
  "command": "db.runCommand({explain: { 'aggregate' : 'rates', 'pipeline' : [{ '$match' : { 'isDeleted' : 0, 'rateStatus' : 1 } }, { '$lookup' : { 'from' : 'banks', 'localField' : 'bankId', 'foreignField' : '_id', 'as' : 'bankId' } }, { '$unwind' : { 'path' : '$bankId', 'preserveNullAndEmptyArrays' : false } }, { '$match' : { 'bankId.type' : 'bankType' } }], 'allowDiskUse' : true, 'cursor' : { }, 'maxTimeMS' : 60000 }})",
  "stages": [
    {
      "stage": "$query",
      "timeInclusiveMS": 1896.5298,
      "timeExclusiveMS": 1896.5298,
      "in": 21399,
      "out": 21399,
      "dependency": {
        "getNextPageCount": 2,
        "count": 2,
        "time": 0,
        "bytes": 9522389
      },
      "details": {
        "database": "devDB",
        "collection": "rates",
        "query": {
          "$and": [
            {
              "isDeleted": {
                "$eq": 0
              }
            },
            {
              "rateStatus": {
                "$eq": 1
              }
            }
          ]
        },
        "indexUsage": {
          "pathsIndexed": {
            "individualIndexes": [],
            "compoundIndexes": []
          },
          "pathsNotIndexed": {
            "individualIndexes": [
              "isDeleted",
              "rateStatus"
            ],
            "compoundIndexes": []
          }
        },
        "shardInformation": [
          {
            "activityId": "bd6b26fb-5f6b-4557-a2e8-95ca176d533b",
            "shardKeyRangeId": "[,FF) move next",
            "durationMS": 757.6661,
            "preemptions": 0,
            "outputDocumentCount": 20548,
            "retrievedDocumentCount": 20548
          },
          {
            "activityId": "677c38b1-b098-4e22-a690-521fe7fee747",
            "shardKeyRangeId": "[,FF) move next",
            "durationMS": 52.9936,
            "preemptions": 0,
            "outputDocumentCount": 851,
            "retrievedDocumentCount": 851
          }
        ],
        "queryMetrics": {
          "retrievedDocumentCount": 21399,
          "retrievedDocumentSizeBytes": 24652478,
          "outputDocumentCount": 21399,
          "outputDocumentSizeBytes": 10902148,
          "indexHitRatio": 1,
          "totalQueryExecutionTimeMS": 750.71,
          "queryPreparationTimes": {
            "queryCompilationTimeMS": 0.2,
            "logicalPlanBuildTimeMS": 0.09,
            "physicalPlanBuildTimeMS": 0.92,
            "queryOptimizationTimeMS": 0.02
          },
          "indexLookupTimeMS": 0.14,
          "documentLoadTimeMS": 237.47,
          "vmExecutionTimeMS": 723.08,
          "runtimeExecutionTimes": {
            "queryEngineExecutionTimeMS": 59.49,
            "systemFunctionExecutionTimeMS": 34.31,
            "userDefinedFunctionExecutionTimeMS": 0
          },
          "documentWriteTimeMS": 425.98
        }
      }
    },
    {
      "stage": "$lookup",
      "timeInclusiveMS": 2797.0257,
      "timeExclusiveMS": 580.2518,
      "in": 24488,
      "out": 21399,
      "dependency": {
        "getNextPageCount": 2,
        "count": 2,
        "time": 320.2441,
        "bytes": 1869635
      },
      "details": {
        "from": "banks",
        "localField": "bankId",
        "foreignField": "_id",
        "as": "bankId",
        "shardInformation": [
          {
            "activityId": "46e038e6-69b4-442b-a77a-0dce3b858b2f",
            "shardKeyRangeId": "[,FF) move next",
            "durationMS": 119.8859,
            "preemptions": 0,
            "outputDocumentCount": 2750,
            "retrievedDocumentCount": 2750
          },
          {
            "activityId": "00e4e3d2-a4f1-4d76-a682-fc266eaa8f5f",
            "shardKeyRangeId": "[,FF) move next",
            "durationMS": 30.6709,
            "preemptions": 0,
            "outputDocumentCount": 339,
            "retrievedDocumentCount": 339
          }
        ],
        "queryMetrics": {
          "retrievedDocumentCount": 3089,
          "retrievedDocumentSizeBytes": 4073512,
          "outputDocumentCount": 3089,
          "outputDocumentSizeBytes": 1875595,
          "indexHitRatio": 1,
          "totalQueryExecutionTimeMS": 92.62,
          "queryPreparationTimes": {
            "queryCompilationTimeMS": 0.59,
            "logicalPlanBuildTimeMS": 1.06,
            "physicalPlanBuildTimeMS": 2.06,
            "queryOptimizationTimeMS": 0.65
          },
          "indexLookupTimeMS": 29.53,
          "documentLoadTimeMS": 17.51,
          "vmExecutionTimeMS": 85.54,
          "runtimeExecutionTimes": {
            "queryEngineExecutionTimeMS": 3.35,
            "systemFunctionExecutionTimeMS": 2.07,
            "userDefinedFunctionExecutionTimeMS": 0
          },
          "documentWriteTimeMS": 35.15
        }
      }
    },
    {
      "stage": "$unwind",
      "timeInclusiveMS": 3175.3604,
      "timeExclusiveMS": 378.3347,
      "in": 21399,
      "out": 21385,
      "details": {
        "path": "$bankId",
        "preserveNullAndEmptyArrays": false,
        "includeArrayIndex": ""
      }
    },
    {
      "stage": "$match",
      "timeInclusiveMS": 3476.2204,
      "timeExclusiveMS": 300.86,
      "in": 21385,
      "out": 14768,
      "details": {
        "query": {
          "bankId.type": {
            "$eq": "bankType"
          }
        }
      }
    }
  ],
  "estimatedDelayFromRateLimitingInMilliseconds": 0,
  "retriedDueToRateLimiting": false,
  "totalRequestCharge": 1578.03,
  "continuation": {
    "hasMore": false
  },
  "ActivityId": "9b4ccb57-a5c2-4dce-a6bc-c2a3dbd76d6f",
  "ok": 1
}

问题分析 #

1. 索引未生效：执行计划显示indexUsage中复合索引列表为空，说明创建的isDeleted_1_rateStatus_1复合索引未被命中，导致rates集合全表扫描21399条数据，这是核心性能瓶颈。
2. 过滤顺序不合理：当前逻辑先全量关联banks数据，再过滤bankId.type，相当于做了大量无效关联后再筛选，浪费计算资源。
3. 阶段耗时集中：$query阶段耗时1896ms，占总耗时一半以上，后续$lookup、$unwind阶段也因前置无效操作增加了额外开销。

优化建议 #

1. 确保复合索引被正确使用 #

先确认索引是否存在：

db.rates.getIndexes()

若索引存在但未被使用，可能是字段类型不匹配（如isDeleted存数字但查询用字符串），可强制指定索引：

db.rates.aggregate([
  { "$hint": "isDeleted_1_rateStatus_1" },
  { "$match": { "isDeleted": 0, "rateStatus": 1 } },
  // 后续管道阶段
])

2. 调整$lookup逻辑，提前过滤banks数据 #

将bankId.type的过滤嵌入$lookup内部，减少关联数据量：

[
  {
    "$match": {
      "isDeleted": 0,
      "rateStatus": 1
    }
  },
  {
    "$lookup": {
      "from": "banks",
      "let": { "rateBankId": "$bankId" },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$and": [
                { "$eq": ["$_id", "$$rateBankId"] },
                { "$eq": ["$type", "typeOfBank"] }
              ]
            }
          }
        }
      ],
      "as": "bankId"
    }
  },
  {
    "$unwind": {
      "path": "$bankId",
      "preserveNullAndEmptyArrays": false
    }
  }
]

此方式会先在banks集合筛选出符合类型条件的文档，再与rates关联，大幅减少关联数据量。

3. 给banks集合添加针对性索引 #

创建_id_1_type_1复合索引，加速$lookup中的关联与过滤：

db.banks.createIndex({ "_id": 1, "type": 1 })

4. 验证优化效果 #

优化后重新查看执行计划，确认索引命中、各阶段数据量减少：

db.runCommand({
  explain: {
    aggregate: 'rates',
    pipeline: [/* 优化后的管道 */],
    allowDiskUse: true
  }
})

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