在实际操作mongodb的过程中,发现将find与sort结合使用时,mongodb优先使用sort查询条件可以利用的索引,只有当sort查询条件没有索引可用时,才尝试利用find查询条件中的索引。
mongodb版本2.6.3
> db.version()
2.6.3
下面用命令行进行实际操作演示
collection中有如下记录
> db.TestOrder.find()
{ "_id" : ObjectId("56dfb5af9f436cc02505fac0"), "cust_id" : 12, "amount" : 199, "status" : "A", "create_time" : "14512345678" }
{ "_id" : ObjectId("56dfb61a9f436cc02505fac2"), "cust_id" : 121, "amount" : 1999, "status" : "A", "create_time" : "14512345679" }
{ "_id" : ObjectId("56dfb6359f436cc02505fac3"), "cust_id" : 119, "amount" : 23999, "status" : "B", "create_time" : "14512345680" }
{ "_id" : ObjectId("56dfb6419f436cc02505fac4"), "cust_id" : 1192, "amount" : 2999, "status" : "B", "create_time" : "14512345681" }
{ "_id" : ObjectId("56dfb64d9f436cc02505fac5"), "cust_id" : 11921, "amount" : 2992, "status" : "B", "create_time" : "14512345683" }
{ "_id" : ObjectId("56dfb9089f436cc02505fac8"), "cust_id" : 1000, "amount" : 399, "status" : "A", "create_time" : "14312345678" }
{ "_id" : ObjectId("56dfb9089f436cc02505fac9"), "cust_id" : 1100, "amount" : 599, "status" : "C", "create_time" : "14212345680" }
现有索引
创建了cust_id升序索引,create_time降序索引。
> db.TestOrder.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "test.TestOrder"
},
{
"v" : 1,
"key" : {
"cust_id" : 1
},
"name" : "cust_id_1",
"ns" : "test.TestOrder"
},
{
"v" : 1,
"key" : {
"create_time" : -1
},
"name" : "create_time_-1",
"ns" : "test.TestOrder"
}
]
仅使用find查询
利用上了cust_id升序索引
> db.TestOrder.find({cust_id:{$in:[12,121,1000,1100]}}).explain()
{
"cursor" : "BtreeCursor cust_id_1",
"isMultiKey" : false,
"n" : 4,
"nscannedObjects" : 4,
"nscanned" : 5,
"nscannedObjectsAllPlans" : 4,
"nscannedAllPlans" : 5,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"cust_id" : [
[
12,
12
],
[
121,
121
],
[
1000,
1000
],
[
1100,
1100
]
]
},
"server" : "Test_Node003:27020",
"filterSet" : false,
"stats" : {
"type" : "FETCH",
"works" : 5,
"yields" : 0,
"unyields" : 0,
"invalidates" : 0,
"advanced" : 4,
"needTime" : 0,
"needFetch" : 0,
"isEOF" : 1,
"alreadyHasObj" : 0,
"forcedFetches" : 0,
"matchTested" : 0,
"children" : [
{
"type" : "IXSCAN",
"works" : 4,
"yields" : 0,
"unyields" : 0,
"invalidates" : 0,
"advanced" : 4,
"needTime" : 0,
"needFetch" : 0,
"isEOF" : 1,
"keyPattern" : "{ cust_id: 1.0 }",
"boundsVerbose" : "field #0['cust_id']: [12.0, 12.0], [121.0, 121.0], [1000.0, 1000.0], [1100.0, 1100.0]",
"isMultiKey" : 0,
"yieldMovedCursor" : 0,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0,
"matchTested" : 0,
"keysExamined" : 5,
"children" : [ ]
}
]
}
}
同时使用find、sort查询
利用上了create_time索引进行全表排序,未用上cust_id索引。
> db.TestOrder.find({cust_id:{$in:[12,121,1000,1100]}}).sort({create_time:-1}).explain()
{
"cursor" : "BtreeCursor create_time_-1",
"isMultiKey" : false,
"n" : 4,
"nscannedObjects" : 7,
"nscanned" : 7,
"nscannedObjectsAllPlans" : 11,
"nscannedAllPlans" : 12,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"create_time" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
},
"server" : "Test_Node003:27020",
"filterSet" : false,
"stats" : {
"type" : "FETCH",
"works" : 9,
"yields" : 0,
"unyields" : 0,
"invalidates" : 0,
"advanced" : 4,
"needTime" : 3,
"needFetch" : 0,
"isEOF" : 1,
"alreadyHasObj" : 0,
"forcedFetches" : 0,
"matchTested" : 4,
"children" : [
{
"type" : "IXSCAN",
"works" : 7,
"yields" : 0,
"unyields" : 0,
"invalidates" : 0,
"advanced" : 7,
"needTime" : 0,
"needFetch" : 0,
"isEOF" : 1,
"keyPattern" : "{ create_time: -1.0 }",
"boundsVerbose" : "field #0['create_time']: [MaxKey, MinKey]",
"isMultiKey" : 0,
"yieldMovedCursor" : 0,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0,
"matchTested" : 0,
"keysExamined" : 7,
"children" : [ ]
}
]
}
}
删除sort查询条件中的索引
删除create_time降序索引
> db.TestOrder.dropIndex({create_time:-1})
{ "nIndexesWas" : 3, "ok" : 1 }
再次同时使用find、sort查询
因为sort查询无索引可用,mongodb利用上了cust_id索引。
> db.TestOrder.find({cust_id:{$in:[12,121,1000,1100]}}).sort({create_time:-1}).explain()
{
"cursor" : "BtreeCursor cust_id_1",
"isMultiKey" : false,
"n" : 4,
"nscannedObjects" : 4,
"nscanned" : 5,
"nscannedObjectsAllPlans" : 4,
"nscannedAllPlans" : 5,
"scanAndOrder" : true,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"cust_id" : [
[
12,
12
],
[
121,
121
],
[
1000,
1000
],
[
1100,
1100
]
]
},
"server" : "Test_Node003:27020",
"filterSet" : false,
"stats" : {
"type" : "SORT",
"works" : 11,
"yields" : 0,
"unyields" : 0,
"invalidates" : 0,
"advanced" : 4,
"needTime" : 5,
"needFetch" : 0,
"isEOF" : 1,
"forcedFetches" : 0,
"memUsage" : 424,
"memLimit" : 33554432,
"children" : [
{
"type" : "KEEP_MUTATIONS",
"works" : 5,
"yields" : 0,
"unyields" : 0,
"invalidates" : 0,
"advanced" : 4,
"needTime" : 0,
"needFetch" : 0,
"isEOF" : 1,
"children" : [
{
"type" : "FETCH",
"works" : 5,
"yields" : 0,
"unyields" : 0,
"invalidates" : 0,
"advanced" : 4,
"needTime" : 0,
"needFetch" : 0,
"isEOF" : 1,
"alreadyHasObj" : 0,
"forcedFetches" : 0,
"matchTested" : 0,
"children" : [
{
"type" : "IXSCAN",
"works" : 4,
"yields" : 0,
"unyields" : 0,
"invalidates" : 0,
"advanced" : 4,
"needTime" : 0,
"needFetch" : 0,
"isEOF" : 1,
"keyPattern" : "{ cust_id: 1.0 }",
"boundsVerbose" : "field #0['cust_id']: [12.0, 12.0], [121.0, 121.0], [1000.0, 1000.0], [1100.0, 1100.0]",
"isMultiKey" : 0,
"yieldMovedCursor" : 0,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0,
"matchTested" : 0,
"keysExamined" : 5,
"children" : [ ]
}
]
}
]
}
]
}
}