对于下面的例子而言,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。
Leave a Reply