对于下面的例子而言，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