MongoDB計數相關檔案（3級）

我需要快速統計相關檔案。

所以，我有四個集合

團體

{ "_id" : "g1", "name" : "group1" }
{ "_id" : "g2", "name" : "group2" }

培訓班

{ "_id" : "c1", "name" : "course1", "group_id" : "g1" }
{ "_id" : "c2", "name" : "course2", "group_id" : "g2" }

話題

{ "_id" : "t1", "name" : "top1c11", "course_id" : "c1" }
{ "_id" : "t2", "name" : "top1c12", "course_id" : "c1" }
{ "_id" : "t3", "name" : "top1c21", "course_id" : "c2" }

教訓

{ "_id" : "l1", "name" : "lesson111", "topic_id" : "t1" }
{ "_id" : "l2", "name" : "lesson112", "topic_id" : "t1" }
{ "_id" : "l3", "name" : "lesson121", "topic_id" : "t2" }
{ "_id" : "l4", "name" : "lesson211", "topic_id" : "t3" }

我需要計算特定組的所有課程。

我嘗試運行以下聚合，但沒有等待回應。（但它適用于少量資料）

db.getCollection('lessons').aggregate([
{
    "$lookup": {
        "from": "topics",
        "let": { "topicId": "$topic_id" },
        "pipeline": [
            { 
                "$match": { "$expr": { "$eq": [ "$_id", "$$topicId" ] } } 
            },
            {
                "$lookup": {
                    "from": "courses",
                    "let": { "courseId": "$topic_id" },
                    "pipeline": [
                        { "$match": { "$expr": { "$eq": [ "$course_id", "$$courseId" ] } } },
                    ],
                    "as": "course"
                },
            },
            {
                "$unwind": "$course"
            }

        ],
        "as": "topic"
    },
},
{
    "$unwind" : "$topic"
},
{
    "$match": {
        "topic.course.group_id" : "g1"
    }
},
{
    $group: {
        _id: "$course",
        "amount": {$sum:1},
    }
}
])

我相信可以優化這種聚合。但我不確定這是將聚合框架用于此類目的的好方法。如果是這樣，我如何優化聚合。

集合大小（測驗資料）：

• 課程：30000
• 話題：200000
• 課時：30000000

現在我在我的代碼中使用簡單的嵌套回圈來計算課程。這需要 10 秒（對于某個組的 3000 個主題）。

uj5u.com熱心網友回復：

查詢1

• 非嵌套查找（查找和展開）
• 匹配組
• 查找和展開 3 次，最后一次查找只計算課程，并使用管道查找
• 一組一組_id，得到總課程

您需要的索引（所有foreignField）

• courses.group_id
• topics.course_id
• lessons.topic_id

測驗代碼在這里

groups.aggregate(
[{"$match":{"_id":"g1"}},
 {"$lookup":
  {"from":"courses",
   "localField":"_id",
   "foreignField":"group_id",
   "as":"courses"}},
 {"$unwind":"$courses"},
 {"$lookup":
  {"from":"topics",
   "localField":"courses._id",
   "foreignField":"course_id",
   "as":"topics"}},
 {"$unwind":"$topics"},
 {"$lookup":
  {"from":"lessons",
   "localField":"topics._id",
   "foreignField":"topic_id",
   "pipeline":
   [{"$group":{"_id":null, "lessons":{"$sum":1}}},
    {"$set":{"id":"$_id", "_id":"$$REMOVE"}}],
   "as":"lessons"}},
 {"$set":
  {"lessons":
   {"$cond":
    [{"$eq":["$lessons", []]}, 0,
     {"$arrayElemAt":["$lessons.lessons", 0]}]}}},
 {"$group":{"_id":"$_id", "totalLessons":{"$sum":"$lessons"}}}])

查詢2

• 嵌套查找（沒有展開）
• 代碼相同，只是嵌套

測驗代碼在這里

groups.aggregate(
[{"$match":{"_id":"g1"}},
 {"$lookup":
  {"from":"courses",
   "localField":"_id",
   "foreignField":"group_id",
   "pipeline":
   [{"$lookup": 
     {"from":"topics",
      "localField":"_id",
      "foreignField":"course_id",
      "pipeline":
      [{"$lookup":
        {"from":"lessons",
         "localField":"_id",
         "foreignField":"topic_id",
         "pipeline":
         [{"$group":{"_id":null, "lessons":{"$sum":1}}},
          {"$set":{"id":"$_id", "_id":"$$REMOVE"}}],
         "as":"lessons"}},
       {"$set":
        {"lessons":
         {"$cond":
          [{"$eq":["$lessons", []]}, 0,
           {"$arrayElemAt":["$lessons.lessons", 0]}]}}}],
      "as":"topics"}},
    {"$project":
     {"_id":0, "totalLessons":{"$sum":"$topics.lessons"}}}],
   "as":"courses"}},
 {"$set":
  {"courses":"$$REMOVE",
   "totalLessons":
   {"$cond":
    [{"$eq":["$courses", []]}, 0,
     {"$arrayElemAt":["$courses.totalLessons", 0]}]}}}])

如果您可以發送一些關于哪個更快的反饋。
如果對于 1 個組它非常快，可能會洗掉匹配項，為所有組執行此操作，或者允許匹配項通過更多組。

uj5u.com熱心網友回復：

來自Takis 評論的解決方案。Query1，4.2采用

groups.aggregate(
[{"$match":{"_id":"g1"}},
 {"$lookup":
  {"from":"courses",
   "localField":"_id",
   "foreignField":"group_id",
   "as":"courses"}},
 {"$unwind":"$courses"},
 {"$lookup":
  {"from":"topics",
   "localField":"courses._id",
   "foreignField":"course_id",
   "as":"topics"}},
 {"$unwind":"$topics"},
 {"$lookup":
  {"from":"lessons",
   "pipeline":
   [{"$match":{"$expr":{"$eq":["$$ptopic", "$topic_id"]}}},
    {"$group":{"_id":null, "lessons":{"$sum":1}}},
    {"$set":{"id":"$_id", "_id":"$$REMOVE"}}],
   "as":"lessons",
   "let":{"ptopic":"$topics._id"}}},
 {"$set":
  {"lessons":
   {"$cond":
    [{"$eq":["$lessons", []]}, 0,
     {"$arrayElemAt":["$lessons.lessons", 0]}]}}},
 {"$group":{"_id":"$_id", "totalLessons":{"$sum":"$lessons"}}}])

標籤：MongoDB 聚合框架

上一篇：MongodbAsQueryable()性能

下一篇：EFCore在編輯時不更新某些欄位