如果mongodb聚合未将索引用于$ lookup，为什么使用索引时性能会提高? [英] If mongodb aggregate does not use indexes for $lookup why does my performance increase when using indexes?

问题描述

我有以下代码段来运行聚合命令.

I have the following code snippet to run the aggregation command.

      console.time("something");
      const cursor = await db.collection("main").aggregate([
        {
          $match: {
            mainField: mainField,
          },
        },
        {
          $lookup: {
            from: "reference",
            localField: "referenceId",
            foreignField: "referenceField",
            as: "something",
          },
        },
      ]);
      const results = await cursor.toArray();
      console.timeEnd("something");

我有一个用于测试目的的廉价云服务器(2GB内存，1个CPU等)，用于存储mongodb.

I have a cheap cloud server for testing purposes (2gb ram, 1 cpu etc.) where mongodb is stored.

我将1万个文档插入到主集合和参考集合中(因此，总共插入了2万个文档).

I insert 10k documents into main and reference collections (so combined 20k documents inserted).

不使用索引并运行上面的聚合查询，需要30秒钟以上才能返回结果.

Without using indexes and running the above aggregation query it takes more than 30 seconds to return the result.

如果我在参考集合上具有以下索引并运行上述聚合查询，则结果大约需要1.2秒.

If I have the following index on the reference collection and run the above aggregation query the results take around 1.2 seconds.

await db.collection("reference").createIndex({ referenceField: 1 });

推荐答案

不幸的是，MongoDB手册当前并未提及$lookup的潜在索引用法，但确实是这种情况.

Unfortunately the MongoDB manual doesn't currently mention potential index usage for $lookup, but this is definitely the case.

与示例类似的简单$lookup查询对另一个集合中的foreignField执行相等匹配，因此您添加了正确的索引以提高性能(假设此字段也具有选择性).

A simple $lookup query similar to your example performs an equality match on the foreignField in another collection, so you've added the correct index to improve performance (assuming this field also is reasonably selective).

在MongoDB 4.0上，$lookup的索引用法未在 SERVER-22622:改进$ lookup解释以指示有关来自"集合的查询计划.

As at MongoDB 4.0 the index usage for $lookup is not reported in aggregation explain output. There is a relevant issue to watch/upvote in the MongoDB issue tracker: SERVER-22622: Improve $lookup explain to indicate query plan on the "from" collection.

这篇关于如果mongodb聚合未将索引用于$ lookup，为什么使用索引时性能会提高?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！