Mongo选择错误的索引

Question

我正在使用MongoDB 2.6.9版本。我在一个集合上创建了两个索引，但是查询计划器总是选择错误的索引，我不明白其中的原因。

我希望能够理解我所缺少的知识。

第一个索引：

{
   "TimeStamp":1,
   "A":1,
   "B":1,
}

第二个索引：

{
   "TimeStamp":1,
   "A":1,
   "C":1,
}

当我使用以下查询时，计划程序会选择第一个索引：

db.collection.find({"TimeStamp":{ "$gte" : ISODate("2015-04-14T00:00:00Z"), "$lt" : ISODate("2015-04-15T00:00:00Z") },"C":2137,"A":1}).explain()

解释({verbose:1}) 的结果：

{
    "cursor" : "BtreeCursor IX_First",
    "isMultiKey" : false,
    "n" : 0,
    "nscannedObjects" : 0,
    "nscanned" : 0,
    "nscannedObjectsAllPlans" : 0,
    "nscannedAllPlans" : 0,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
            "TimeStamp" : [
                    [
                            ISODate("2015-04-14T00:00:00Z"),
                            ISODate("2015-04-15T00:00:00Z")
                    ]
            ],
            "A" : [
                    [
                            1,
                            1
                    ]
            ],
            "B" : [
                    [
                            {
                                    "$minElement" : 1
                            },
                            {
                                    "$maxElement" : 1
                            }
                    ]
            ],
    },
    "allPlans" : [
            {
                    "cursor" : "BtreeCursor IX_First",
                    "isMultiKey" : false,
                    "n" : 0,
                    "nscannedObjects" : 0,
                    "nscanned" : 0,
                    "scanAndOrder" : false,
                    "indexOnly" : false,
                    "nChunkSkips" : 0,
                    "indexBounds" : {
            "TimeStamp" : [
                    [
                            ISODate("2015-04-14T00:00:00Z"),
                            ISODate("2015-04-15T00:00:00Z")
                    ]
            ],
            "A" : [
                    [
                            1,
                            1
                    ]
            ],
            "B" : [
                    [
                            {
                                    "$minElement" : 1
                            },
                            {
                                    "$maxElement" : 1
                            }
                    ]
            ],
    },
            {
                    "cursor" : "BtreeCursor IIX_Second",
                    "isMultiKey" : false,
                    "n" : 0,
                    "nscannedObjects" : 0,
                    "nscanned" : 0,
                    "scanAndOrder" : false,
                    "indexOnly" : false,
                    "nChunkSkips" : 0,
                    "indexBounds" : {
                            "TimeStamp" : [
                                    [
                                            ISODate("2015-04-14T00:00:00Z"),
                                            ISODate("2015-04-15T00:00:00Z")
                                    ]
                            ],
                            "A" : [
                                    [
                                            1,
                                            1
                                    ]
                            ],
                            "C" : [
                                    [
                                            2137,
                                            2137
                                    ]
                            ]
                    }
            }
    ],
    "server" : "mongo2:27017",
    "filterSet" : false,
    "stats" : {
            "type" : "KEEP_MUTATIONS",
            "works" : 2,
            "yields" : 0,
            "unyields" : 0,
            "invalidates" : 0,
            "advanced" : 0,
            "needTime" : 0,
            "needFetch" : 0,
            "isEOF" : 1,
            "children" : [
                    {
                            "type" : "FETCH",
                            "works" : 1,
                            "yields" : 0,
                            "unyields" : 0,
                            "invalidates" : 0,
                            "advanced" : 0,
                            "needTime" : 0,
                            "needFetch" : 0,
                            "isEOF" : 1,
                            "alreadyHasObj" : 0,
                            "forcedFetches" : 0,
                            "matchTested" : 0,
                            "children" : [
                                    {
                                            "type" : "IXSCAN",
                                            "works" : 1,
                                            "yields" : 0,
                                            "unyields" : 0,
                                            "invalidates" : 0,
                                            "advanced" : 0,
                                            "needTime" : 0,
                                            "needFetch" : 0,
                                            "isEOF" : 1,
                                            "keyPattern" : "{ TimeStamp: 1, A: 1, B: 1}",
                                            "isMultiKey" : 0,
                                            "boundsVerbose" : "field #0['TimeStamp']: [new Date(1428969600000), new Date(1429056000000)), field #1['A']: [1.0, 1.0], field #2['B']: [MinKey, MaxKey]",
                                            "yieldMovedCursor" : 0,
                                            "dupsTested" : 0,
                                            "dupsDropped" : 0,
                                            "seenInvalidated" : 0,
                                            "matchTested" : 0,
                                            "keysExamined" : 0,
                                            "children" : [ ]
                                    }
                            ]
                    }
            ]
    }

}

- Matan Shidlov

---

也许你可以发布 explain 的输出...或者更好的方式是 (explain({verbose:1}));-) - Fox

也许可以看一下 hint() 函数？ - ʰᵈˑ

已更新帖子并添加了explain({verbose:1})。 - Matan Shidlov

MongoDB会根据经验选择索引 - 简单来说，它会将索引相互竞争，以查看谁能最先达到100个文档（或所有结果）。多个索引可以通过扫描零个文档来满足查询，因此经验测试和索引选择毫无意义。请尝试使用大量数据进行测试。 - wdberkeley

这个问题与其他问题几乎相似，这个答案可能会有所帮助 https://stackoverflow.com/a/72159004/8730051 - Hamed Lohi

Answer 1

这是因为在这种情况下索引字段的顺序很重要。尝试交换A和C：

db.collection.find({"TimeStamp":{ "$gte" : ISODate("2015-04-14T00:00:00Z"), "$lt" : ISODate("2015-04-15T00:00:00Z") },"A":1,"C":2137}).explain()

- panagdu