MongoDB索引与排序

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

dbDao 百度贴吧：http://tieba.baidu.com/dbdao

MongoDB技术学习QQ群： 421431253

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。