为什么$ in比$ all快得多？ [英] Why $in is much faster than $all?

问题描述

db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.053980478460939611 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$all" : [/^soto/, /^nasi/] } }).limit(200);

db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.053980478460939611 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$in" : [/^soto/, /^nasi/] } }).limit(200);

这是结果

/* 88 */
{
  "ts" : ISODate("2012-09-11T06:57:26.801Z"),
  "op" : "query",
  "ns" : "newisikota.tablebusiness",
  "query" : {
    "LongitudeLatitude" : {
      "$nearSphere" : [106.772835, -6.186753],
      "$maxDistance" : 0.053980478460939611
    },
    "Prominent" : {
      "$gte" : 15.0
    },
    "indexContents" : {
      "$all" : [/^soto/, /^nasi/]
    }
  },
  "ntoreturn" : 200,
  "nscanned" : 48,
  "nreturned" : 48,
  "responseLength" : 60002,
  "millis" : 3821,
  "client" : "127.0.0.1",
  "user" : ""
}

/* 89 */
{
  "ts" : ISODate("2012-09-11T06:57:43.147Z"),
  "op" : "query",
  "ns" : "newisikota.tablebusiness",
  "query" : {
    "LongitudeLatitude" : {
      "$nearSphere" : [106.772835, -6.186753],
      "$maxDistance" : 0.053980478460939611
    },
    "Prominent" : {
      "$gte" : 15.0
    },
    "indexContents" : {
      "$in" : [/^soto/, /^nasi/]
    }
  },
  "ntoreturn" : 200,
  "nscanned" : 200,
  "nreturned" : 200,
  "responseLength" : 249598,
  "millis" : 320,
  "client" : "127.0.0.1",
  "user" : ""
}

注意：$ all查询可能偶尔运行26秒。

Note: the $all query can run for 26 seconds once in a while.

解释结果如下：

db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.053980478460939611 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$all" : [/^soto/, /^nasi/] } }).limit(200).explain();




{
        "cursor" : "GeoSearchCursor",
        "nscanned" : 48,
        **"nscannedObjects" : 48,**
        "n" : 48,
        "millis" : 8563,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {
        }
}
>


db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.053980478460939611 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$in" : [/^soto/, /^nasi/] } }).limit(200).explain();
{
        "cursor" : "GeoSearchCursor",
        "nscanned" : 200,
        **"nscannedObjects" : 200,**
        "n" : 200,
        "millis" : 516,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {
        }
}

通知搜索中的$扫描更多对象。

Notice that the $in search scan more object.

最糟糕的是，mongdob可以在搜索中执行$，然后将事情过滤掉。比率不应该很大。

Worst come to worst, mongdob could do $in search and then filter things out. The ratio shouldn't be huge.

推荐答案

我在为什么在mongodb中使用$ all要慢得多？

这次我只用了一个字。因此，$ in和$ all之间应该没有区别。它们都是等价的。

This time I use only one word. Hence, there should be no difference between $in and $all. They both are equivalent.

仍然是$ all慢得多。

Still $all is much slower.

结果，每个答案都是这样， mongodb本身有一个错误。

Turns out, per answer on that, there is a bug in mongodb itself.

https://jira.mongodb.org/browse/SERVER-1748

我想在问题出现之前根本不会使用$ all固定。

I guess I simply won't use $all at all until the problem is fixed.

对于所有其他答案，你试过这个吗？

To all other answers, have you tried this your self?

这篇关于为什么$ in比$ all快得多？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！