MongoDB索引交集 [英] MongoDB index intersection

问题描述

嘿我想评估指数交叉的表现，但我无法得到两个指数之间的交集。
我在本手册中已经将一些虚拟记录插入到我的数据库中。
http://docs.mongodb.org/manual/core/index-交叉路口/

Hey I want to evaluate the performance of index intersection but I'm not able to get an intersection between two indices. I've inserted some dummy records into my DB along this manual. http://docs.mongodb.org/manual/core/index-intersection/

插入代码：

for(var i=0;i<1000;i++){
    for(var j=0;j<100;j++){
        db.t.insert({item:"abc"+i,qty:j})
    }
}

指数：

[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "db.t"
    },
    {
        "v" : 1,
        "key" : {
            "qty" : 1
        },
        "name" : "qty_1",
        "ns" : "db.t"
    },
    {
        "v" : 1,
        "key" : {
            "item" : 1
        },
        "name" : "item_1",
        "ns" : "db.t"
    }
]

查询：

db.t.find({item:"abc123",qty:{$gt:15}}).explain()

解释结果：

{
    "cursor" : "BtreeCursor item_1",
    "isMultiKey" : false,
    "n" : 84,
    "nscannedObjects" : 100,
    "nscanned" : 100,
    "nscannedObjectsAllPlans" : 201,
    "nscannedAllPlans" : 305,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 2,
    "nChunkSkips" : 0,
    "millis" : 1,
    "indexBounds" : {
        "item" : [
            [
                "abc123",
                "abc123"
            ]
        ]
    },
    "server" : "brews18:27017",
    "filterSet" : false
}

我的问题是为什么mongo只使用item作为index an不使用a n交叉。

My question is why mongo is only using item as an index an does not use an intersection.

提前致谢

推荐答案

即使在这种情况下它也没有。要真正了解正在发生的事情，您需要查看详细解释形式，添加 true ：

Well it actually does even though it does not in this case. To really see what is happening you need to look at the "verbose" form of explain, by adding true:

db.t.find({item:"abc123",qty:{$gt:15}}).explain(true)
{
    "cursor" : "BtreeCursor item_1",
    "isMultiKey" : false,
    "n" : 84,
    "nscannedObjects" : 100,
    "nscanned" : 100,
    "nscannedObjectsAllPlans" : 201,
    "nscannedAllPlans" : 304,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 2,
    "nChunkSkips" : 0,
    "millis" : 2,
    "indexBounds" : {
            "item" : [
                    [
                            "abc123",
                            "abc123"
                    ]
            ]
    },
    "allPlans" : [
            {
                    "cursor" : "BtreeCursor item_1",
                    "isMultiKey" : false,
                    "n" : 84,
                    "nscannedObjects" : 100,
                    "nscanned" : 100,
                    "scanAndOrder" : false,
                    "indexOnly" : false,
                    "nChunkSkips" : 0,
                    "indexBounds" : {
                            "item" : [
                                    [
                                            "abc123",
                                            "abc123"
                                    ]
                            ]
                    }
            },
            {
                    "cursor" : "BtreeCursor qty_1",
                    "isMultiKey" : false,
                    "n" : 0,
                    "nscannedObjects" : 101,
                    "nscanned" : 102,
                    "scanAndOrder" : false,
                    "indexOnly" : false,
                    "nChunkSkips" : 0,
                    "indexBounds" : {
                            "qty" : [
                                    [
                                            15,
                                            Infinity
                                    ]
                            ]
                    }
            },
            {
                    "cursor" : "Complex Plan",
                    "n" : 0,
                    "nscannedObjects" : 0,
                    "nscanned" : 102,
                    "nChunkSkips" : 0
            }
    ],

缩短，但最后一部分是你正在寻找的。正如手册中所述，复杂计划的外观意味着交叉点正在使用。

Cut short, but the last part is what you are looking for. As explained in the manual, the appearance of "Complex Plan" means an intersection is being used.

            {
                    "cursor" : "Complex Plan",
                    "n" : 0,
                    "nscannedObjects" : 0,
                    "nscanned" : 102,
                    "nChunkSkips" : 0
            }

这里唯一的例子是，当它被查看时，优化器在这种情况下并没有选择它作为最多最佳查询。所以优化器说实际上只使用一个选定索引的计划是以最敏感的方式完成的计划。

The only case here is that while it is being "looked at" it is not being chosen by the optimizer in this case as the most "optimal" query. So the optimizer is saying that in fact the plan using just the one selected index, is the one that will complete in the most responsive fashion.

所以虽然交集被认为是，它不是最合适，而是选择了单一指数。

So while the "intersection" was considered, it was not the "best fit" and the single index was chosen.

这篇关于MongoDB索引交集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！