MongoDB查询异常缓慢 [英] Mongodb query exceptionally slow

问题描述

我的mongodb非常简单:一个数据集/条目在3层上具有大约30个属性.其中一个这样的条目最多可包含约5000个字符.我有50万. 当我执行以下查询时...

My mongodb is rather simple: a dataset/entry has around 30 properties on 3 layers. One such entry has up to around 5000 characters. I have 500k of them. When I execute the following query...

db.images.find({ "featureData.cedd": { $exists: false}}).count()

...这非常慢.它没有被索引，但是根据我的MySQL经验，仍然不需要 20分钟来执行一个这样的查询.

...it is extremely slow. It's not indexed, but still.. from my MySQL experience it shouldn't take 20 minutes to execute one such query.

(直接在mongo终端上)执行时，CPU使用率为3％，可用内存仍超过2 Gig.

While being executed (directly on the mongo terminal) there's 3% CPU usage and still over 2 Gigs of free memory.

感谢您给我一些提示！

查询的explain()(无计数)给出:

An explain() of the query (without count) gives:

db.images.find({ "featureData.cedd": { $exists: false }}).explain()
{
    "cursor" : "BasicCursor",
    "nscanned" : 532537,
    "nscannedObjects" : 532537,
    "n" : 438,
    "millis" : 1170403,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "isMultiKey" : false,
    "indexOnly" : false,
    "indexBounds" : {

    }
}

iostat的输出:

Output of iostat:

Linux 3.2.0-58-generic (campartex)      03/25/2014      _x86_64_        (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          34.93    0.01    0.25    0.48    0.00   64.33

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sda               2.08       103.79        11.26  172805914   18749067
fd0               0.00         0.00         0.00        148          0

explain()的输出添加索引后:

Output of explain() after adding an index:

db.images.find({ "featureData.cedd": { $exists: false }}).explain()
{
    "cursor" : "BtreeCursor featureData.cedd_1",
    "nscanned" : 438,
    "nscannedObjects" : 438,
    "n" : 438,
    "millis" : 2,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "isMultiKey" : true,
    "indexOnly" : false,
    "indexBounds" : {
            "featureData.cedd" : [
                    [
                            null,
                            null
                    ]
            ]
    }
}

推荐答案

在我的案例中，添加索引使查询速度提高了600'000. $exists:false寻找空值-仅在其他对象不经常使用cedd:null(作为有效值)时才有效.就是这种情况.此外，没有让步值的对象要小得多.

In my case adding an index improved the query speed by a factor of 600'000. $exists:false looks for null values - that only works efficiently if the other objects don't have cedd:null very often (as a valid value). That is the case here. Further, the objects that have no cedd value are a lot smaller.

这篇关于MongoDB查询异常缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！