MongoDB 复合索引在单独查询字段的时候不生效。

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

推荐工具

› RoboMongo

Related Blogs

› Snail in a Turtleneck

这是一个创建于 3036 天前的主题，其中的信息可能已经有所发展或是发生改变。

数据如下：

> db.testcoll.find().limit(2)
{ "_id" : ObjectId("567ac8e9498f43a9cea8dd08"), "Name" : "User1", "Age" : 1, "Gender" : "M", "PreferBooks" : [ "frist book", "Second book" ] }
{ "_id" : ObjectId("567ac8e9498f43a9cea8dd09"), "Name" : "User2", "Age" : 2, "Gender" : "M", "PreferBooks" : [ "frist book", "Second book" ] }

索引如下：

> db.testcoll.getIndexes()
[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "testdb.testcoll"
    },
    {
        "v" : 1,
        "unique" : true,
        "key" : {
            "Name" : 1
        },
        "name" : "Name_1",
        "ns" : "testdb.testcoll"
    },
    {
        "v" : 1,
        "key" : {
            "Name" : 1,
            "Age" : 1
        },
        "name" : "Name_1_Age_1",
        "ns" : "testdb.testcoll"
    }
]
>

从索引里面看到有 Name:1,Age:1 的复合索引。查询单独 Age 数据：

> db.testcoll.find({Age:{$gt:80}}).hint("Name_1_Age_1").explain()
{
    "cursor" : "BtreeCursor Name_1_Age_1",
    "isMultiKey" : false,
    "n" : 21,
    "nscannedObjects" : 99,
    "nscanned" : 99,
    "nscannedObjectsAllPlans" : 99,
    "nscannedAllPlans" : 99,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "Name" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ],
        "Age" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ]
    },
    "server" : "junmao.local:27019",
    "filterSet" : false
}
>

上面结果显示的为什么 age 字段没有使用复合索引呢？麻烦请教下大家，复合索引创建在 Name:1 和 Age:1 的时候不是相当于 Name 和 Age 的都创建了么，查询 Age 数据最起码的应该是对 Age 进行索引啊，我查询 user 的时候是可以索引的，如下

> db.testcoll.find({Name:"User233"}).hint("Name_1_Age_1").explain()
{
    "cursor" : "BtreeCursor Name_1_Age_1",
    "isMultiKey" : false,
    "n" : 0,
    "nscannedObjects" : 0,
    "nscanned" : 0,
    "nscannedObjectsAllPlans" : 0,
    "nscannedAllPlans" : 0,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "Name" : [
            [
                "User233",
                "User233"
            ]
        ],
        "Age" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ]
    },
    "server" : "junmao.local:27019",
    "filterSet" : false
}
>

同时查询 Name 和 Age ：

> db.testcoll.find({Name:"User233",Age:233}).hint("Name_1_Age_1").explain()
{
    "cursor" : "BtreeCursor Name_1_Age_1",
    "isMultiKey" : false,
    "n" : 0,
    "nscannedObjects" : 0,
    "nscanned" : 0,
    "nscannedObjectsAllPlans" : 0,
    "nscannedAllPlans" : 0,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "Name" : [
            [
                "User233",
                "User233"
            ]
        ],
        "Age" : [
            [
                233,
                233
            ]
        ]
    },
    "server" : "junmao.local:27019",
    "filterSet" : false
}
>

复合索引也使用最左匹配法则了？

mongo version