MongoDB索引与排序

对于下面的例子而言,mongoDB会如何处理这个查询?

 

ongos> db.users.insert({user: { login:"maclean", desc:"maclean liu" , date : new Date("2015-01-01") }});
mongos> 
mongos> db.users.findOne();
{
"_id" : ObjectId("555179f2315a40007db97d3c"),
"user" : {
"login" : "maclean",
"desc" : "maclean liu",
"date" : ISODate("2015-01-01T00:00:00Z")
}
}
mongos> db.users.ensureIndex( { "user.login" :1 , "user.date" :-1} , "myIndex" );
{
"raw" : {
"shard0.dbdao.com:35001" : {
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
},
"ok" : 1
}
mongos> db.users.find ( { "user.login": /^mac.*/ }  , {"user":1,"_id":0}).sort({"user.date":1}).explain();
{
"queryPlanner" : {
"mongosPlannerVersion" : 1,
"winningPlan" : {
"stage" : "SINGLE_SHARD",
"shards" : [
{
"shardName" : "shard0000",
"connectionString" : "shard0.dbdao.com:35001",
"serverInfo" : {
"host" : "shard0.dbdao.com",
"port" : 35001,
"version" : "3.0.0",
"gitVersion" : "a841fd6394365954886924a35076691b4d149168"
},
"plannerVersion" : 1,
"namespace" : "test_db.users",
"indexFilterSet" : false,
"parsedQuery" : {
"user.login" : /^mac.*/
},
"winningPlan" : {
"stage" : "PROJECTION",
"transformBy" : {
"user" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "SORT",
"sortPattern" : {
"user.date" : 1
},
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"filter" : {
"user.login" : /^mac.*/
},
"keyPattern" : {
"user.login" : 1,
"user.date" : -1
},
"indexName" : "myIndex",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"user.login" : [
"[\"mac\", \"mad\")",
"[/^mac.*/, /^mac.*/]"
],
"user.date" : [
"[MaxKey, MinKey]"
]
}
}
}
}
},
"rejectedPlans" : [ ]
}
]
}
},
"ok" : 1
}
mongos> db.users.find ( { "user.login": /^mac.*/ }  , {"user":1,"_id":0}).sort({"user.login":1}).explain();
{
"queryPlanner" : {
"mongosPlannerVersion" : 1,
"winningPlan" : {
"stage" : "SINGLE_SHARD",
"shards" : [
{
"shardName" : "shard0000",
"connectionString" : "shard0.dbdao.com:35001",
"serverInfo" : {
"host" : "shard0.dbdao.com",
"port" : 35001,
"version" : "3.0.0",
"gitVersion" : "a841fd6394365954886924a35076691b4d149168"
},
"plannerVersion" : 1,
"namespace" : "test_db.users",
"indexFilterSet" : false,
"parsedQuery" : {
"user.login" : /^mac.*/
},
"winningPlan" : {
"stage" : "PROJECTION",
"transformBy" : {
"user" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"filter" : {
"user.login" : /^mac.*/
},
"keyPattern" : {
"user.login" : 1,
"user.date" : -1
},
"indexName" : "myIndex",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"user.login" : [
"[\"mac\", \"mad\")",
"[/^mac.*/, /^mac.*/]"
],
"user.date" : [
"[MaxKey, MinKey]"
]
}
}
}
},
"rejectedPlans" : [ ]
}
]
}
},
"ok" : 1
}
mongos> db.users.find ( { "user.login": /^mac.*/ }  , {"user":1,"_id":0}).sort({"user.date":-1}).explain();
{
"queryPlanner" : {
"mongosPlannerVersion" : 1,
"winningPlan" : {
"stage" : "SINGLE_SHARD",
"shards" : [
{
"shardName" : "shard0000",
"connectionString" : "shard0.dbdao.com:35001",
"serverInfo" : {
"host" : "shard0.dbdao.com",
"port" : 35001,
"version" : "3.0.0",
"gitVersion" : "a841fd6394365954886924a35076691b4d149168"
},
"plannerVersion" : 1,
"namespace" : "test_db.users",
"indexFilterSet" : false,
"parsedQuery" : {
"user.login" : /^mac.*/
},
"winningPlan" : {
"stage" : "PROJECTION",
"transformBy" : {
"user" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "SORT",
"sortPattern" : {
"user.date" : -1
},
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"filter" : {
"user.login" : /^mac.*/
},
"keyPattern" : {
"user.login" : 1,
"user.date" : -1
},
"indexName" : "myIndex",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"user.login" : [
"[\"mac\", \"mad\")",
"[/^mac.*/, /^mac.*/]"
],
"user.date" : [
"[MaxKey, MinKey]"
]
}
}
}
}
},
"rejectedPlans" : [ ]
}
]
}
},
"ok" : 1
}
> db.users.find ( { "user.login": /^mac.*/ }  , {"user":1,"_id":0}).sort({"user.login":-1}).explain();
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"user.login" : /^mac.*/
},
"winningPlan" : {
"stage" : "PROJECTION",
"transformBy" : {
"user" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"filter" : {
"user.login" : /^mac.*/
},
"keyPattern" : {
"user.login" : 1,
"user.date" : -1
},
"indexName" : "myIndex",
"isMultiKey" : false,
"direction" : "backward",
"indexBounds" : {
"user.login" : [
"[/^mac.*/, /^mac.*/]",
"(\"mad\", \"mac\"]"
],
"user.date" : [
"[MinKey, MaxKey]"
]
}
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "ac.local",
"port" : 27017,
"version" : "3.0.2",
"gitVersion" : "nogitversion"
},
"ok" : 1
}

从上述演示中可以看到:

对于索引 myIndex db.users.ensureIndex( { “user.login” :1 , “user.date” :-1} , “myIndex” );

db.users.find ( { “user.login”: /^mac.*/ } , {“user”:1,”_id”:0}).sort({“user.date”:1}).explain(); ==> 使用了索引myIndex ,但 出现 “stage” : “SORT”, 说明使用索引未避免排序,

 

db.users.find ( { “user.login”: /^mac.*/ }  , {“user”:1,”_id”:0}).sort({“user.login”:1}).explain(); ==》使用了索引myIndex,且未出现 “stage” : “SORT” ; 说明对于index prefix的排序可以利用到索引来避免排序

db.users.find ( { “user.login”: /^mac.*/ }  , {“user”:1,”_id”:0}).sort({“user.login”:-1}).explain();使用了索引myIndex,且未出现 “stage” : “SORT” ; 说明对于index prefix的排序可以利用到索引来避免排序

db.users.find ( { “user.login”: /^mac.*/ }  , {“user”:1,”_id”:0}).sort({“user.date”:-1}).explain();  使用了索引myIndex ,但 出现 “stage” : “SORT”, 说明使用索引未避免排序,

 

以上说明了:

  • 对于索引prefix的字段而言,不管是索引是正序还是倒序,排序是正序需求还是倒序需求,都可以使用到Index索引来避免排序
  • 对于非索引prefix的字段,无法利用其来避免排序,IXSCAN完还需要SORT。

 

Comment

*

沪ICP备14014813号

沪公网安备 31010802001379号