MongoDB数组查询性能优化求助：用户所属群组查询变慢问题

我们正在优化社交媒体类应用的MongoDB集群读取性能，用户可加入1个或多个群组。此前将用户群组归属及管理员身份存储在独立的groupMember集合，但查询用户所属群组时需要关联查询，速度较慢。于是我们把所有群组成员迁移到groups集合的数组字段中，当前groups集合的Schema结构大致如下：

• _id: ObjectId（群组ID）
• name: String（群组名称）
• members: Array（成员数组，每个元素包含userId（用户ID）、isAdmin（是否管理员）等字段）
• 其他群组相关业务字段

当前执行的查询语句为：

this.model.find({
  members: {
    $elemMatch: {
      userId: new ObjectId(userId),
    },
  },
})

原本预期去掉关联查询能提升性能，但部署后性能反而下降。集群约有40k个群组文档，最大群组约3k成员，多数群组规模较小。已为groups集合创建members.userId_1索引，查询也已命中该索引，执行计划如下：

{
  "explainVersion": "1",
  "queryPlanner": {
    "namespace": "***.groups",
    "indexFilterSet": false,
    "parsedQuery": {
      "members": {
        "$elemMatch": {
          "userId": {
            "$eq": "61b091ee9b50220e75208eb6"
          }
        }
      }
    },
    "queryHash": "DCF50157",
    "planCacheKey": "DCF50157",
    "maxIndexedOrSolutionsReached": false,
    "maxIndexedAndSolutionsReached": false,
    "maxScansToExplodeReached": false,
    "winningPlan": {
      "stage": "FETCH",
      "filter": {
        "members": {
          "$elemMatch": {
            "userId": {
              "$eq": "61b091ee9b50220e75208eb6"
            }
          }
        }
      },
      "inputStage": {
        "stage": "IXSCAN",
        "keyPattern": {
          "members.userId": 1
        },
        "indexName": "members.userId_1",
        "isMultiKey": true,
        "multiKeyPaths": {
          "members.userId": [
            "members"
          ]
        },
        "isUnique": false,
        "isSparse": false,
        "isPartial": false,
        "indexVersion": 2,
        "direction": "forward",
        "indexBounds": {
          "members.userId": [
            "[ObjectId('61b091ee9b50220e75208eb6'), ObjectId('61b091ee9b50220e75208eb6')]"
          ]
        }
      }
    },
    "rejectedPlans": []
  },
  "executionStats": {
    "executionSuccess": true,
    "nReturned": 17,
    "executionTimeMillis": 0,
    "totalKeysExamined": 17,
    "totalDocsExamined": 17,
    "executionStages": {
      "stage": "FETCH",
      "filter": {
        "members": {
          "$elemMatch": {
            "userId": {
              "$eq": "61b091ee9b50220e75208eb6"
            }
          }
        }
      },
      "nReturned": 17,
      "executionTimeMillisEstimate": 0,
      "works": 18,
      "advanced": 17,
      "needTime": 0,
      "needYield": 0,
      "saveState": 0,
      "restoreState": 0,
      "isEOF": 1,
      "docsExamined": 17,
      "alreadyHasObj": 0,
      "inputStage": {
        "stage": "IXSCAN",
        "nReturned": 17,
        "executionTimeMillisEstimate": 0,
        "works": 18,
        "advanced": 17,
        "needTime": 0,
        "needYield": 0,
        "saveState": 0,
        "restoreState": 0,
        "isEOF": 1,
        "keyPattern": {
          "members.userId": 1
        },
        "indexName": "members.userId_1",
        "isMultiKey": true,
        "multiKeyPaths": {
          "members.userId": [
            "members"
          ]
        },
        "isUnique": false,
        "isSparse": false,
        "isPartial": false,
        "indexVersion": 2,
        "direction": "forward",
        "indexBounds": {
          "members.userId": [
            "[ObjectId('61b091ee9b50220e75208eb6'), ObjectId('61b091ee9b50220e75208eb6')]"
          ]
        },
        "keysExamined": 17,
        "seeks": 1,
        "dupsTested": 17,
        "dupsDropped": 0
      }
    },
    "allPlansExecution": []
  },
  "command": {
    "find": "groups",
    "filter": {
      "members": {
        "$elemMatch": {
          "userId": "61b091ee9b50220e75208eb6"
        }
      }
    },
    "projection": {},
    "readConcern": {
      "level": "majority"
    },
    "$db": "***"
  },
  "serverInfo": {
    "host": "***",
    "port": 27017,
    "version": "6.0.3",
    "gitVersion": "f803681c3ae19817d31958965850193de067c516"
  },
  "serverParameters": {
    "internalQueryFacetBufferSizeBytes": 104857600,
    "internalQueryFacetMaxOutputDocSizeBytes": 104857600,
    "internalLookupStageIntermediateDocumentMaxSizeBytes": 104857600,
    "internalDocumentSourceGroupMaxMemoryBytes": 104857600,
    "internalQueryMaxBlockingSortMemoryUsageBytes": 104857600,
    "internalQueryProhibitBlockingMergeOnMongoS": 0,
    "internalQueryMaxAddToSetBytes": 104857600,
    "internalDocumentSourceSetWindowFieldsMaxMemoryBytes": 104857600
  },
  "ok": 1,
  "operationTime": {
    "$timestamp": "7168789227251957761"
  }
}

在负载情况下，该查询耗时300-400ms，无法满足性能需求。目前不清楚下一步优化方向，MongoDB未建议额外索引或Schema调整，寻求高效优化方案。

优化方案建议 #

1. 简化查询语句 #

当前使用的$elemMatch可以简化为直接匹配数组嵌套属性，逻辑完全一致但能减少查询解析开销：

this.model.find({
  "members.userId": new ObjectId(userId)
})

2. 构建覆盖索引避免文档FETCH #

如果查询仅需返回群组核心字段（如_id、name），创建覆盖索引让MongoDB直接从索引返回结果，跳过磁盘读取完整文档的步骤：

db.groups.createIndex(
  {"members.userId": 1},
  {"include": ["name"]} // 根据业务需求添加需要返回的字段
)

3. 反向Schema设计：用户-群组映射集合 #

切换为以用户为中心的存储结构，创建userGroups集合，每个文档存储单个用户的所有群组关联信息：

{
  "_id": ObjectId("用户ID"),
  "groups": [
    {"groupId": ObjectId("群组ID"), "isAdmin": true},
    {"groupId": ObjectId("群组ID"), "isAdmin": false}
  ]
}

为_id创建唯一索引，查询时直接根据用户ID读取单文档，性能稳定。维护成本可控：

• 用户加入/退出群组：对groups数组执行push/pull操作
• 群组信息同步：可通过变更流(Change Streams)自动更新，或查询时关联少量groups文档

4. 调整读取Concern级别 #

当前查询使用readConcern: "majority"，高负载下会增加等待时间。若业务允许读取非最新已提交数据，可调整为"local"或"available"：

this.model.find({"members.userId": new ObjectId(userId)}).readConcern("local")

5. 分片集群优化（若已分片） #

如果groups集合已分片，检查分片键合理性：

• 若原分片键为群组ID，查询会跨分片执行，可考虑将分片键调整为members.userId（需评估基数和读写分布）
• 或使用复合分片键{members.userId: 1, _id: 1}，让同一用户的群组数据尽量落在同一分片

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