如何在mongodb中选择此查询？

Question

以下是完整的查询：

  async getHistoryByTag(context: StandardContext) {
    const { mongo } = context.state;
    const tag = context.params.tag.replace(/-+/g, " ");
    const today = new Date();
    const prevSunday = getOneWeekAgo(today);

    console.log("today: ", today, "prev sunday: ", prevSunday);

    const all = await mongo
      .collection("search_history")
      .aggregate<SearchHistoryModel & { username: string; word: string }>([
        {
          $match: {
            created_at: { $gt: prevSunday.toISOString() },
          },
        },
        {
          $lookup: {
            from: "users",
            localField: "user_id",
            foreignField: "id",
            as: "user",
            pipeline: [
              {
                $project: {
                  username: 1,
                },
              },
            ],
          },
        },

        // { search_id: 1, created_at: 1, url: 1 }
        {
          $lookup: {
            from: "positive",
            localField: "search_id",
            foreignField: "search_id",
            as: "positive",
            pipeline: [
              {
                $project: {
                  word: 1,
                },
              },
            ],
          },
        },
        {
          $unwind: {
            path: "$user",
            preserveNullAndEmptyArrays: true,
          },
        },
        {
          $unwind: {
            path: "$positive",
            preserveNullAndEmptyArrays: true,
          },
        },
        {
          $replaceRoot: {
            newRoot: {
              $mergeObjects: ["$$ROOT", "$user", "$positive"],
            },
          },
        },
        {
          $match: {
            word: tag,
          },
        },
        {
          $project: {
            user: 0,
            positive: 0,
          },
        },
        {
          $sort: {
            created_at: -1,
          },
        },
      ])
      .toArray();

    context.response.body = all.filter((value, index, self) => {
      return self.findIndex((v) => v.url === value.url) === index;
    });
  }

我尝试过添加索引，但仍然需要5-10秒才能做出响应。

Answer 1

你只需要重新订购蒙戈管道一点。

匹配和排序应该首先使用索引，也不要在根管道中使用填充属性的匹配，而是在填充管道中使用匹配。

所以，你的管道应该是这样的。

    const all = await mongo
      .collection("search_history")
      .aggregate<SearchHistoryModel & { username: string; word: string }>([
        // match should be first
        {
          $match: {
            created_at: { $gt: prevSunday.toISOString() },
          },
        },
        // sort should be second
        {
          $sort: {
            created_at: -1,
          },
        },
        {
          $lookup: {
            from: "users",
            localField: "user_id",
            foreignField: "id",
            as: "user",
            pipeline: [
              {
                $project: {
                  username: 1,
                },
              },
            ],
          },
        },
        {
          $lookup: {
            from: "positive",
            localField: "search_id",
            foreignField: "search_id",
            as: "positive",
            pipeline: [
              {    
                $match: {word: tag},   // move the populated match inside the populate pipeline to use the index
              }
              {
                $project: {
                  word: 1,
                },
              },
            ],
          },
        },
        {
          $unwind: {
            path: "$user",
            preserveNullAndEmptyArrays: true,
          },
        },
        {
          $unwind: {
            path: "$positive",
            preserveNullAndEmptyArrays: true,
          },
        },
        {
          $replaceRoot: {
            newRoot: {
              $mergeObjects: ["$$ROOT", "$user", "$positive"],
            },
          },
        },
        {
          $project: {
            user: 0,
            positive: 0,
          },
        },
      ])
      .toArray();

注意:如果您的收集是巨大的，使用分页更快的查询。