MongoDB:与基本查找相比，性能较低的管道查找 [英] MongoDB: slow performance pipeline lookup compared to basic lookup

问题描述

我有两个集合:

匹配:

[{
    date: "2020-02-15T17:00:00Z",    
    players: [
      {_id: "5efd9485aba4e3d01942a2ce"}, 
      {_id: "5efd9485aba4e3d01942a2cf"}
    ]        
},
{...}] 

和玩家:

 [{    
     _id: "5efd9485aba4e3d01942a2ce", 
     name: "Rafa Nadal"   
 },
 {    
     _id: "5efd9485aba4e3d01942a2ce", 
     name: "Roger Federer"   
 },
 {...}]  

我需要使用查找管道，因为我正在构建一个带有递归函数的 graphql 解析器，并且我需要嵌套查找.我遵循了这个例子 https://docs.mongodb.com/datalake/reference/pipeline/lookup-stage#nested-example

我的问题是管道查找需要 11 秒，但基本查找只需要 0.67 秒.而且我的测试数据库很短！大约 1300 名球员和 700 场比赛.

My problem is that with pipeline lookup I need 11 seconds but with basic lookup only 0.67 seconds. And my test database is very short! about 1300 players and 700 matches.

这是我的管道查找(11 秒解决)

This is my pipeline lookup (11 seconds to resolve)

db.collection('matches').aggregate([{
   $lookup: {
      from: 'players',
      let: { ids: '$players' },            
      pipeline: [{ $match: { $expr: { $in: ['$_id', '$$ids' ] } } }],
      as: 'players'
   }
}]);

这是我的基本查找(解析时间为 0.67 秒)

And this my basic lookup (0.67 seconds to resolve)

db.collection('matches').aggregate([{
   $lookup: {
     from: "players",
     localField: "players",
     foreignField: "_id",
     as: "players"
   }
}]);

为什么差别这么大?我可以通过什么方式进行更快的管道查找?

Why so much difference? In what way can I do faster pipeline lookup?

推荐答案

问题是，当您使用 pipeline 和 match stage 执行 lookup 时，索引将仅用于与 $eq operator 匹配的字段，其余索引将不被使用.

The thing is that when you do a lookup using pipeline with a match stage, then the index would be used only for the fields that are matched with $eq operator and for the rest index will not be used.

你用管道指定的例子将像这样工作(这里不会使用索引，因为它不是 $eq )

And the example you specified with pipeline will work like this ( again index will not be used here as it is not $eq )

db.matches.aggregate([
  {
    $lookup: {
      from: "players",
      let: {
        ids: {
          $map: {
            input: "$players",
            in: "$$this._id"
          }
        }
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [
                "$_id",
                "$$ids"
              ]
            }
          }
        }
      ],
      as: "players"
    }
  }
])

由于玩家是一个对象数组，所以需要先映射到 id 数组

As players is an array of object so it need to be mapped to array of ids first

MongoDB 游乐场

这篇关于MongoDB:与基本查找相比，性能较低的管道查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！