为什么MongoDB无法使用与查询非常相似(不精确)的复合索引? [英] Why MongoDB cannot use a compound index that is much similar(not exact) to the query?

问题描述

考虑下面的Mongo索引策略和查询，

Consider the below Mongo index strategy and the query,

索引:

db.collec.ensureIndex({a:1,b:1,c:1});

查询:

db.collec.find({"a":"valueA"},{"_id":0,"a":1,"c":1}).sort({"c":-1}).limit(150)

上述查询的说明返回:

/* 0 */
{
    "cursor" : "BtreeCursor a_1_b_1_c_1",
    "isMultiKey" : false,
    "n" : 150,
    "nscannedObjects" : 178,
    "nscanned" : 178,
    "nscannedObjectsAllPlans" : 279,
    "nscannedAllPlans" : 279,
    "scanAndOrder" : true,
    "indexOnly" : true,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 1,
    "indexBounds" : {
        "a" : [ 
            [ 
                "valueA", 
                "valueA"
            ]
        ],
        "b" : [ 
            [ 
                {
                    "$minElement" : 1
                }, 
                {
                    "$maxElement" : 1
                }
            ]
        ],
        "c" : [ 
            [ 
                {
                    "$minElement" : 1
                }, 
                {
                    "$maxElement" : 1
                }
            ]
        ]
    }
}

这里的问题是 它清楚地表明查询完全在Index(as "indexOnly" : true)上运行. 但是为什么"scanAndOrder" : true
根据Btree索引模型，c位于索引的尾部，因此可以用于排序.不?

The question here is Its clearly indicated that the query runs completely on Index(as "indexOnly" : true). But why the "scanAndOrder" : true
According to Btree index model, c is at the tail of the index so it can be utilized to sort. No?

为什么不使用它?

推荐答案

这是正确的，而且

This is correct and also documented.

原因:索引看起来本质上像这棵树:

As to why: The index looks essentially like this tree:

• A:值A"
  • B:"ABC"
    • C:435
    • C:678
    • A: "value A"
      • B : "ABC"
        • C: 435
        • C: 678
        • C:123
        • C:993

        如您所见，排序是正确的并且升序的，但是如果您将c的值按顺序进行而不限制为固定的b子集，则会得到[435, 678, 123, 993]，该值是不正确的，因此必须scanAndOrder.

        As you can see, the ordering is correct and ascending, but if you'd take the values of c in-order without limiting to a subset of fixed b, you'd get [435, 678, 123, 993], which is not correct, so scanAndOrder is required.

        不幸的是，没有索引相交的索引非常不灵活.

        Unfortunately, indexes without index intersectioning are very inflexible.

        这篇关于为什么MongoDB无法使用与查询非常相似(不精确)的复合索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！