MongoDB带Lookup的简单聚合查询性能优化问题排查

问题背景 #

在20万条数据的场景下，以下MongoDB聚合查询性能极差。已为lookup关联的geographic_location集合的id字段创建索引，但查询仍采用COLSCAN策略，且$match阶段速度缓慢。

原始聚合查询语句 #

[
  {
    $project: {
      _id: 0,
      id: 1,
      uniqueId: 1,
      shortLabel: 1,
      "geographicLocation.id": 1,
      "economicConcept.id": 1,
      "frequency.id": 1,
      "scale.id": 1,
      "unit.id": 1,
      source: { $arrayElemAt: ["$source", 0] },
    },
  },
  {
    $lookup: {
      from: "geographic_location",
      localField: "geographicLocation.id",
      foreignField: "id",
      as: "region",
    },
  },
  {
    $match: {
      $and: [
        {
          $or: [
            { "region.name": "United States" },
          ],
        },
      ],
    },
  },
  {
    $facet: {
      metadata: [
        { $count: "total" },
        { $addFields: { page: 1 } },
      ],
      data: [{ $skip: 0 }, { $limit: 10 }],
    },
  },
]

Explain执行结果 #

{
  "stages": [
    {
      "$cursor": {
        "queryPlanner": {
          "plannerVersion": 1,
          "namespace": "ihs_markit_data.unique_ids_in_each_bank",
          "indexFilterSet": false,
          "parsedQuery": {},
          "queryHash": "57975262",
          "planCacheKey": "57975262",
          "winningPlan": {
            "stage": "PROJECTION_DEFAULT",
            "transformBy": {
              "uniqueId": true,
              "shortLabel": true,
              "id": true,
              "geographicLocation": {
                "id": true
              },
              "_id": false
            },
            "inputStage": {
              "stage": "COLLSCAN",
              "direction": "forward"
            }
          },
          "rejectedPlans": []
        },
        "executionStats": {
          "executionSuccess": true,
          "nReturned": 230045,
          "executionTimeMillis": 12134,
          "totalKeysExamined": 0,
          "totalDocsExamined": 230045,
          "executionStages": {
            "stage": "PROJECTION_DEFAULT",
            "nReturned": 230045,
            "executionTimeMillisEstimate": 331,
            "works": 230047,
            "advanced": 230045,
            "needTime": 1,
            "needYield": 0,
            "saveState": 254,
            "restoreState": 254,
            "isEOF": 1,
            "transformBy": {
              "uniqueId": true,
              "shortLabel": true,
              "id": true,
              "geographicLocation": {
                "id": true
              },
              "_id": false
            },
            "inputStage": {
              "stage": "COLLSCAN",
              "nReturned": 230045,
              "executionTimeMillisEstimate": 70,
              "works": 230047,
              "advanced": 230045,
              "needTime": 1,
              "needYield": 0,
              "saveState": 254,
              "restoreState": 254,
              "isEOF": 1,
              "direction": "forward",
              "docsExamined": 230045
            }
          },
          "allPlansExecution": []
        }
      },
      "nReturned": 230045,
      "executionTimeMillisEstimate": 360
    },
    {
      "$lookup": {
        "from": "geographic_location",
        "as": "region",
        "localField": "geographicLocation.id",
        "foreignField": "id"
      },
      "nReturned": 230045,
      "executionTimeMillisEstimate": 11840
    },
    {
      "$match": {
        "region.name": {
          "$eq": "United States"
        }
      },
      "nReturned": 114639,
      "executionTimeMillisEstimate": 12004
    },
    {
      "$facet": {
        "metadata": [
          {
            "$teeConsumer": {},
            "nReturned": 114639,
            "executionTimeMillisEstimate": 12123
          },
          {
            "$group": {
              "_id": {
                "$const": null
              },
              "total": {
                "$sum": {
                  "$const": 1
                }
              }
            },
            "nReturned": 1,
            "executionTimeMillisEstimate": 12134
          },
          {
            "$project": {
              "total": true,
              "_id": false
            },
            "nReturned": 1,
            "executionTimeMillisEstimate": 12134
          },
          {
            "$addFields": {
              "page": {
                "$const": 1
              }
            },
            "nReturned": 1,
            "executionTimeMillisEstimate": 12134
          }
        ],
        "data": [
          {
            "$teeConsumer": {},
            "nReturned": 10,
            "executionTimeMillisEstimate": 0
          },
          {
            "$limit": 10,
            "nReturned": 10,
            "executionTimeMillisEstimate": 0
          }
        ]
      },
      "nReturned": 1,
      "executionTimeMillisEstimate": 12134
    }
  ],
  "serverInfo": {
    "host": "",
    "port": ,
    "version": "4.4.0",
    "gitVersion": ""
  },
  "ok": 1
}

核心问题分析 #

1. 聚合阶段顺序错误，触发全表扫描
   原始查询先执行$project，再做$lookup和$match，导致MongoDB先扫描全部23万条数据，之后才过滤符合条件的记录。$match后置无法提前缩减数据量，后续所有阶段都要处理全量数据，这是性能瓶颈的核心原因。

2. 过滤条件依赖关联后字段，无法利用索引
   当前$match基于关联后的region.name字段筛选，关联操作在全量数据处理后才执行，无法提前通过索引定位主表中仅关联美国地区的记录，进一步放大了性能损耗。

3. 冗余逻辑增加计算开销
   $match中的$and和$or均为单条件嵌套，属于冗余写法，会额外增加MongoDB的逻辑解析成本。

优化方案 #

方案1：调整聚合阶段顺序，提前过滤数据 #

先反向查询geographic_location获取美国地区的id，再用该ID过滤主表数据，将$match前置以缩减后续处理的数据量：

[
  // 先获取美国地区的geographic_location id
  {
    $lookup: {
      from: "geographic_location",
      let: {},
      pipeline: [
        { $match: { name: "United States" } },
        { $project: { _id: 0, id: 1 } }
      ],
      as: "targetRegionIds"
    }
  },
  { $unwind: "$targetRegionIds" },
  // 用目标id过滤主表数据
  {
    $match: {
      "geographicLocation.id": "$targetRegionIds.id"
    }
  },
  // 执行字段投影
  {
    $project: {
      _id: 0,
      id: 1,
      uniqueId: 1,
      shortLabel: 1,
      "geographicLocation.id": 1,
      "economicConcept.id": 1,
      "frequency.id": 1,
      "scale.id": 1,
      "unit.id": 1,
      source: { $arrayElemAt: ["$source", 0] }
    }
  },
  // 关联完整地区信息（若需要）
  {
    $lookup: {
      from: "geographic_location",
      localField: "geographicLocation.id",
      foreignField: "id",
      as: "region"
    }
  },
  // 分页与统计
  {
    $facet: {
      metadata: [
        { $count: "total" },
        { $addFields: { page: 1 } }
      ],
      data: [{ $skip: 0 }, { $limit: 10 }]
    }
  }
]

方案2：为主表创建针对性索引 #

给主表的geographicLocation.id字段创建索引，让前置的$match可以直接通过索引定位数据，避免全表扫描：

db.unique_ids_in_each_bank.createIndex({"geographicLocation.id": 1})

方案3：简化冗余逻辑 #

去掉$match中多余的嵌套逻辑，直接写成：

{ $match: { "region.name": "United States" } }

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