MongoDB - 将 $ne 和 $in 与复合多键索引一起使用时不使用索引

Question

        db.test.find().pretty();
    {
        "_id" : ObjectId("5537f2cfba0bf10870747d7e"),
        "a" : 1,
        "b" : [
            "abcd",
            "xyz"
        ]
    }


    db.test.getIndexes();
    [
        {
            "v" : 1,
            "key" : {
                "_id" : 1
            },
            "ns" : "test.test",
            "name" : "_id_"
        },
        {
            "v" : 1,
            "key" : {
                "a" : 1,
                "b" : 1
            },
            "ns" : "test.test",
            "name" : "a_1_b_1"
        }
    ]

db.test.find({a: { $ne : 2}, b : { $in : ["abcd", "xyz"]}}).explain();
{
    **"cursor" : "BasicCursor", - doesn't hits index**.
    "isMultiKey" : false,
    "n" : 1,
    "nscannedObjects" : 1,
    "nscanned" : 1,
    "nscannedObjectsAllPlans" : 3,
    "nscannedAllPlans" : 3,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {

    },
    "server" : "vishals-Mac-mini.local:27017"
}

其他查询，

db.test.find({a: 1, b : { $in : ["abcd", "xyza"]}}) 
db.test.find({a: { $ne : 2}, b : { $in : ["abcd", "xyza"]}})

使用索引。命中索引似乎取决于 $in 数组中指定的值。如果它包含现有文档的所有值，则不使用索引。

Mongo 版本 - 2.4.6

谢谢。

Answer 1

在 docs ，它指出:

The $ne and $nin operators are not selective. See CreateQueries that Ensure Selectivity. If you need to use these, it isoften best to make sure that an additional, more selective criterionis part of the query.