查找聚合性能差

Question

我有两个收藏品

员额：

{
    "_Id": "1",
    "_PostTypeId": "1",
    "_AcceptedAnswerId": "192",
    "_CreationDate": "2012-02-08T20:02:48.790",
    "_Score": "10",
    ...
    "_OwnerUserId": "6",
    ...
},
...

{
    "_Id": "1",
    "_Reputation": "101",
    "_CreationDate": "2012-02-08T19:45:13.447",
    "_DisplayName": "Geoff Dalgas",
    ...
    "_AccountId": "2"
},
...

db.posts.aggregate([
    {
        $lookup: {
            from: "users", 
            localField: "_OwnerUserId",
            foreignField: "_AccountId", 
            as: "X"
        }
    },  
    {
        $group: {
            _id: "$X._AccountId", 
            posts: { $sum: 1 }
        }
    },   
    {
        $match : {posts: {$gte: 5, $lte: 15}}
    },  
    {
        $sort: {posts: -1 }
    },
    {
        $project : {posts: 1}
    }
])

Answer 1

只要您要按用户_accountID分组，就应该先按_owneruserid进行$group，然后仅在筛选具有10 的帐户后进行查找，这将减少查找：

js prettyprint-override">db.posts.aggregate([{
    $group: {
      _id: "$_OwnerUserId",
      postsCount: {
        $sum: 1
      },
      posts: {
        $push: "$$ROOT"
      } //if you need to keep original posts data
    }
  },
  {
    $match: {
      postsCount: {
        $gte: 5,
        $lte: 15
      }
    }
  },
  {
    $lookup: {
      from: "users",
      localField: "_id",
      foreignField: "_AccountId",
      as: "X"
    }
  },
  {
    $unwind: "$X"
  },
  {
    $sort: {
      postsCount: -1
    }
  },
  {
    $project: {
      postsCount: 1,
      X: 1
    }
  }
])