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

V2EX = way to explore

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

现在注册

已注册用户请登录

推荐工具

RoboMongo

Related Blogs

Snail in a Turtleneck

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

数据如下：

> 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