MongoDB查詢優化

我的 MongoDB 中有很多集合

• 產品
• 尺寸
• 顏色
• 商店
• 稅收
• 供應商
• 客戶等...

我的查詢是作業的，但需要很多時間。當我得到20 條記錄時約 15 秒。

在我的查詢中，我有 ~11 次查找

打開集合中的索引（_id，appId）（<-- appId 在每個集合中使用）是否有可能幫助我加快查詢速度？

還是我的查詢有問題？謝謝！

我的查詢是：

const invoices = await Invoice.aggregate([
  { $match: query },
  { $unwind: "$items" },
  //products
  {
    $lookup: {
      from: "products",
      localField: "items.itemId",
      foreignField: "_id",
      as: "itemId",
    },
  },
  //colors
  {
    $lookup: {
      from: "colors",
      localField: "items.itemColor",
      foreignField: "_id",
      as: "itemColor",
    },
  },
  //sizes
  {
    $lookup: {
      from: "sizes",
      localField: "items.itemSize",
      foreignField: "_id",
      as: "itemSize",
    },
  },
  //taxes
  {
    $lookup: {
      from: "taxes",
      localField: "items.itemTax",
      foreignField: "_id",
      as: "itemTax",
    },
  },
  //shops
  {
    $lookup: {
      from: "shops",
      localField: "shop",
      foreignField: "_id",
      as: "shop",
    },
  },
  {
    $unwind: { path: "$shop", preserveNullAndEmptyArrays: true },
  },
  //shopTo
  {
    $lookup: {
      from: "shops",
      localField: "shopTo",
      foreignField: "_id",
      as: "shopTo",
    },
  },
  {
    $unwind: { path: "$shopTo", preserveNullAndEmptyArrays: true },
  },
  //moves_sup
  {
    $lookup: {
      from: "suppliers",
      localField: "supplier",
      foreignField: "_id",
      as: "moves_sup",
    },
  },
  {
    $unwind: { path: "$moves_sup", preserveNullAndEmptyArrays: true },
  },
  //moves_client
  {
    $lookup: {
      from: "clients",
      localField: "supplier",
      foreignField: "_id",
      as: "moves_client",
    },
  },
  {
    $unwind: { path: "$moves_client", preserveNullAndEmptyArrays: true },
  },
  //user
  {
    $lookup: {
      from: "users",
      localField: "user",
      foreignField: "_id",
      as: "user",
    },
  },
  {
    $unwind: { path: "$user", preserveNullAndEmptyArrays: true },
  },
  //editedBy
  {
    $lookup: {
      from: "users",
      localField: "editedBy",
      foreignField: "_id",
      as: "editedBy",
    },
  },
  {
    $unwind: { path: "$editedBy", preserveNullAndEmptyArrays: true },
  },
  //cat
  {
    $lookup: {
      from: "moneycategories",
      localField: "category",
      foreignField: "_id",
      as: "cat",
    },
  },
  {
    $unwind: { path: "$cat", preserveNullAndEmptyArrays: true },
  },
  {
    $addFields: {
      agentName: {
        $cond: [
          { $eq: [{ $type: "$moves_sup" }, "object"] },
          "$moves_sup.name",
          {
            $cond: [
              { $eq: [{ $type: "$moves_client" }, "object"] },
              "$moves_client.name",
              null,
            ],
          },
        ],
      },
      moves_client: "$$REMOVE",
      moves_sup: "$$REMOVE",
      "items.itemColorName": { $arrayElemAt: ["$itemColor.colorName", 0] },
      "items.itemSizeName": { $arrayElemAt: ["$itemSize.sizeName", 0] },
      "items.itemTaxName": { $arrayElemAt: ["$itemTax.name", 0] },
    },
  },
  {
    $group: {
      _id: "$_id",
      invoiceName: { $first: "$invoiceName" },
      invoiceNumber: { $first: "$invoiceNumber" },
      purchaseNumber: { $first: "$purchaseNumber" },
      date: { $first: "$date" },
      type: { $first: "$type" },
      // prMove: { $push: "$prMove" },
      agentName: { $first: "$agentName" },
      shopName: { $first: "$shop.shopName" },
      shopTo: {
        $first: "$shopTo.shopName",
      },
      supplier: { $first: "$supplier" },
      user: { $first: "$user.name" },
      category: { $first: "$cat" },
      categoryName: { $first: "$cat.categoryName" },
      editedBy: { $first: "$editedDate" },
      editedDate: { $first: "$editedDate" },
      comment: { $first: "comment" },
      addDate: { $first: "addDate" },
      items: { $push: "$items" },
    },
  },
])
  .sort({ date: -1 })
  .skip( req.query.offset)
  .limit( req.query.limit);

uj5u.com熱心網友回復：

如果查找集合不是太大，您可以嘗試使用變數來替換它們。例如階段

{
   $lookup: {
      from: "colors",
      localField: "items.itemColor",
      foreignField: "_id",
      as: "itemColor",
   }
}

可以轉換為

const colors = await  db.colors.find({<maybe some filters if applicable>}).toArray();

{
   $set: {
      itemColor: {
         $filter: {
            input: colors,
            as: "color",
            cond: { $eq: ["$$color._id", "$items.itemColor"] }
         }
      }
   }
}

您也可以嘗試跳過某些$unwind階段。那么上面的階段會是這樣的：

{
   $set: {
      items: {
         $map: {
            input: "$items",
            as: "item",
            in: {
               $mergeObjects: [
                  "$$item",
                  {
                     itemColor: {
                        $filter: {
                           input: colors,
                           as: "color",
                           cond: { $eq: ["$$color._id", "$$item.itemColor"] }
                        }
                     }
                  }
               ]
            }
         }
      }
   }
}

標籤：

上一篇：使用貓鼬在物件陣列中查找嵌套物件

下一篇：Mongodb：使用Aggregate獲取嵌套陣列的樣本