为什么这里要扫描任何物体? [英] Why are any objects being scanned here?

问题描述

我有一个索引:

{indices.textLc:1, group:1, lc:1, wordCount:1, pattern:1, clExists:1} 

并且 Morphia 生成如下查询:

and Morphia generates queries like:

{
    $and: [{
        lc: "eng"
    },
    {
        $or: [{
            group: "cn"
        },
        {
            group: "all"
        }]
    },
    {
        "indices.textLc": {
            $in: ["media strengthening", "strengthening", "media"]
        }
    },
    {
        wordCount: {
            $gte: 1
        }
    },
    {
        wordCount: {
            $lte: 2
        }
    }]
}

并解释给出:

{
    "cursor" : "BtreeCursor indices.textLc_1_group_1_lc_1_wordCount_1_pattern_1_clExists_1 multi",
    "nscanned" : 20287,
    "nscannedObjects" : 20272,
    "n" : 22,
    "millis" : 677,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "isMultiKey" : true,
    "indexOnly" : false,
    "indexBounds" : {
        "indices.textLc" : [
            [
                "media",
                "media"
            ],
            [
                "media strengthening",
                "media strengthening"
            ],
            [
                "strengthening",
                "strengthening"
            ]
        ],
        "group" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ],
        "lc" : [
            [
                "eng",
                "eng"
            ]
        ],
        "wordCount" : [
            [
                1,
                1.7976931348623157e+308
            ]
        ],
        "pattern" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ],
        "clExists" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ]
    }

首先，我不明白为什么需要任何扫描，因为索引中的所有内容都可用.更具体地说，为什么 indexBounds 的 wordCount 部分看起来不像:

Firstly, I don't understand why any scanning is required since everything is available in the index. More specifically, why does the wordCount part of the indexBounds not look like:

 "wordCount" : [
            [
                1,
                2
            ]
        ],

更新 2012-03-20:如果它有助于解释任何事情，我正在运行 MongoDB 2.0.3

Update 2012-03-20: If it helps explain anything, I'm running MongoDB 2.0.3

推荐答案

查询中在复合索引中可用的每个字段很少说明是否可以为查询中的每个子句使用一个索引.有几点需要考虑:

Every field in your query being available in your compound index says very little about whether or not it can use your one index for every clause in your query. There are a few things to consider :

• 除了顶级 $or 子句可以为每个子句使用一个索引外，每个 MongoDB 查询最多只能使用一个索引.
• 复合索引仅在复合中的每个后续字段都可以按顺序使用时才有效，这意味着您的查询允许首先过滤第一个索引字段，然后是第二个，依此类推.因此，如果您有一个索引 {a:1, b:1}，即使该字段在复合索引中，查询 {b:"Hi!"} 也不会使用该索引.

现在，您的查询需要扫描的原因是您的索引只能优化indices.textLc"字段(您的第一个索引字段)的查询执行计划，在这种特殊情况下是lc"，因为它是一个单独的子句在你的 $and 中.

Now, the reason your query requires a scan is because your index can only optimize the query execution plan for the "indices.textLc" field (your first index field) and in this particular case "lc" because it's a seperate clause in your $and.

解释的wordCount"部分实际上应该是:

The "wordCount" part of the explain should actually read :

 "wordCount" : [
         [
                 1,
                 2
         ]
 ]

我刚刚在我的机器上测试过它，所以我认为你的 Morphia/mapping 解决方案出了问题.

I just tested it and it does on my machine so I think something's going wrong with your Morphia/mapping solution there.

像您这样的复合索引和复杂查询是一个棘手的主题.我现在没有时间查看您的查询和索引，看看它是否可以优化.如果可以的话，我今晚会重新访问并帮助您.

Compound indexes and complicated queries such as yours are a tricky subject. I don't have time now to look at your query and index and see if it can be optimized. I'll revisit tonight and help you out if I can.

这篇关于为什么这里要扫描任何物体?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！