MongoDB多关联集合查询性能优化方案咨询

场景概述 #

• 集合规模：
  • users：2万条数据，username字段频繁变更
  • providers：1万条数据，name字段频繁变更
  • data：当前320万条，后续将增长至1亿条，通过user（关联users._id）和provider（关联providers._id）字段与另外两个集合关联
• 现有问题：使用原聚合查询匹配username以"leo"开头、provider.name以"prov3"开头的data数据，耗时15秒以上，已创建data.user、data.provider、users.username、providers.name索引，但性能未达标

现有数据示例 #

users集合 #

db.users.insert({ _id: 1, username: 'mark' });
db.users.insert({ _id: 2, username: 'leon' });
db.users.insert({ _id: 3, username: 'leontin'});

providers集合 #

db.providers.insert({ _id: 1, name: 'prov1' });
db.providers.insert({ _id: 2, name: 'prov2' });
db.providers.insert({ _id: 3, name: 'prov3' });
db.providers.insert({ _id: 39, name: 'prov39' });

data集合 #

db.data.insert({
      _id: ObjectId('6675309f82bdfbe3b00febfa'),
      user: 1,
      provider: 1,
      message: '94451 Laos Sweden Iran',
    });
db.data.insert({
      _id: ObjectId('6675309f82bdfbe3b00febfb'),
      user: 2,
      provider: 3,
      message: 'message  22222',
    });
db.data.insert({
      _id: ObjectId('6675309f82bdfbe3b00febfc'),
      user: 3,
      provider: 39,
      message: 'message  3333',
    });

原慢查询代码 #

db.data.aggregate([
  {
    "$lookup": {
      "from": "users",
      "localField": "user",
      "foreignField": "_id",
      "as": "user_info"
    }
  },
  {
    "$lookup": {
      "from": "providers",
      "localField": "provider",
      "foreignField": "_id",
      "as": "provider_info"
    }
  },
  {
    $match: {
      $expr: {
        $and: [
          {
            $regexMatch: {
              input: {
                $first: "$user_info.username"
              },
              regex: "^leo",
              options: "i"
            }
          },
          {
            $regexMatch: {
              input: {
                $first: "$provider_info.name"
              },
              regex: "^prov3",
              options: "i"
            }
          }
        ]
      }
    }
  }
])

优化方案 #

1. 反向查询：先过滤小集合，再关联大集合 #

核心思路：利用users和providers数据量小的特点，先筛选出符合条件的ID列表，再去data集合中做索引匹配，避免对全量data数据做关联后再过滤。

优化后查询代码：

db.data.aggregate([
  // 第一步：从小集合筛选符合条件的ID
  {
    $facet: {
      valid_users: [
        { $match: { username: { $regex: "^leo" } }, collation: { locale: "en", strength: 2 } },
        { $project: { _id: 1 } }
      ],
      valid_providers: [
        { $match: { name: { $regex: "^prov3" } }, collation: { locale: "en", strength: 2 } },
        { $project: { _id: 1 } }
      ]
    }
  },
  // 提取ID数组
  {
    $addFields: {
      user_ids: { $map: { input: "$valid_users", as: "u", in: "$$u._id" } },
      provider_ids: { $map: { input: "$valid_providers", as: "p", in: "$$p._id" } }
    }
  },
  // 关联data集合，仅匹配符合条件的ID（利用data.user和data.provider的索引）
  {
    $lookup: {
      from: "data",
      let: { u_ids: "$user_ids", p_ids: "$provider_ids" },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $in: ["$user", "$$u_ids"] },
                { $in: ["$provider", "$$p_ids"] }
              ]
            }
          }
        },
        // 按需关联用户/服务商信息（如果需要返回这些字段）
        {
          $lookup: {
            from: "users",
            localField: "user",
            foreignField: "_id",
            as: "user_info"
          }
        },
        {
          $lookup: {
            from: "providers",
            localField: "provider",
            foreignField: "_id",
            as: "provider_info"
          }
        },
        { $unwind: "$user_info" },
        { $unwind: "$provider_info" }
      ],
      as: "matched_data"
    }
  },
  // 展开并格式化结果
  { $unwind: "$matched_data" },
  { $replaceRoot: { newRoot: "$matched_data" } }
])

2. 优化正则索引，确保前缀匹配走索引 #

针对前缀正则（^xxx）和不区分大小写的需求，创建区分大小写强度为2的排序规则索引，让正则查询直接命中索引：

// 创建users的username索引
db.users.createIndex({ username: 1 }, { collation: { locale: "en", strength: 2 } })
// 创建providers的name索引
db.providers.createIndex({ name: 1 }, { collation: { locale: "en", strength: 2 } })

查询时指定相同的collation，避免$regexMatch的options:"i"无法利用索引的问题。

3. 避免冗余关联，按需返回字段 #

如果不需要user_info和provider_info的全部字段，在lookup的pipeline中添加$project仅保留需要的字段，减少内存占用和数据传输：

// 示例：仅返回username和name字段
{
  $lookup: {
    from: "users",
    localField: "user",
    foreignField: "_id",
    as: "user_info",
    pipeline: [{ $project: { username: 1, _id: 0 } }]
  }
}

4. 验证索引利用 #

通过explain("executionStats")查看查询计划，确认data.user和data.provider的索引被用于$in匹配，users.username和providers.name的索引被用于正则过滤。

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