1.索引介绍 mongodb的索引和我们遇到的rdbms的索引含义一样,原理也基本一样首先我们先在一个没有索引的集合上做一个查询,具体的查询计划可以通过explain()函数获取: db . t1 . find ({ username : user101 }). explain () { queryPlanner : { plannerVersion

1.索引介绍

mongodb的索引和我们遇到的rdbms的索引含义一样,原理也基本一样 首先我们先在一个没有索引的集合上做一个查询,具体的查询计划可以通过explain()函数获取:

> db.t1.find({"username":"user101"}).explain(){	"queryPlanner" : {		"plannerVersion" : 1,		"namespace" : "suq.t1",		"indexFilterSet" : false,		"parsedQuery" : {			"username" : {				"$eq" : "user101"			}		},		"winningPlan" : {			"stage" : "COLLSCAN",			"filter" : {				"username" : {					"$eq" : "user101"				}			},			"direction" : "forward"		},		"rejectedPlans" : [ ]	},	"serverInfo" : {		"host" : "mongodb1",		"port" : 27017,		"version" : "3.2.6",		"gitVersion" : "05552b562c7a0b3143a729aaa0838e558dc49b25"	},	"ok" : 1}

如果要看详细的执行计划可以在explain添加executionStats或者allPlansExecution

> db.t1.find({"username":"user101"}).explain("allPlansExecution"){	"queryPlanner" : {		"plannerVersion" : 1,		"namespace" : "suq.t1",		"indexFilterSet" : false,		"parsedQuery" : {			"username" : {				"$eq" : "user101"			}		},		"winningPlan" : {			"stage" : "FETCH",			"inputStage" : {				"stage" : "IXSCAN",				"keyPattern" : {					"username" : 1				},				"indexName" : "username_1",				"isMultiKey" : false,				"isUnique" : false,				"isSparse" : false,				"isPartial" : false,				"indexVersion" : 1,				"direction" : "forward",				"indexBounds" : {					"username" : [						"[\"user101\", \"user101\"]"					]				}			}		},		"rejectedPlans" : [ ]	},	"executionStats" : {		"executionSuccess" : true,		"nReturned" : 1,		"executionTimeMillis" : 0,		"totalKeysExamined" : 1,		"totalDocsExamined" : 1,		"executionStages" : {			"stage" : "FETCH",			"nReturned" : 1,			"executionTimeMillisEstimate" : 0,			"works" : 2,			"advanced" : 1,			"needTime" : 0,			"needYield" : 0,			"saveState" : 0,			"restoreState" : 0,			"isEOF" : 1,			"invalidates" : 0,			"docsExamined" : 1,			"alreadyHasObj" : 0,			"inputStage" : {				"stage" : "IXSCAN",				"nReturned" : 1,				"executionTimeMillisEstimate" : 0,				"works" : 2,				"advanced" : 1,				"needTime" : 0,				"needYield" : 0,				"saveState" : 0,				"restoreState" : 0,				"isEOF" : 1,				"invalidates" : 0,				"keyPattern" : {					"username" : 1				},				"indexName" : "username_1",				"isMultiKey" : false,				"isUnique" : false,				"isSparse" : false,				"isPartial" : false,				"indexVersion" : 1,				"direction" : "forward",				"indexBounds" : {					"username" : [						"[\"user101\", \"user101\"]"					]				},				"keysExamined" : 1,				"dupsTested" : 0,				"dupsDropped" : 0,				"seenInvalidated" : 0			}		},		"allPlansExecution" : [ ]	},	"serverInfo" : {		"host" : "mongodb1",		"port" : 27017,		"version" : "3.2.6",		"gitVersion" : "05552b562c7a0b3143a729aaa0838e558dc49b25"	},	"ok" : 1}

这里的执行计划是collscan表示集合扫描,我们给文件username创建一个索引,使用db.collectionname.ensureIndex({"colname":[1/-1]})来创建 --新版本中使用createIndex来替换ensureIndex 其中1表示列的排序是从小大到排序,-1表示从大到小排序.

> db.t1.ensureIndex({"username":1}){	"createdCollectionAutomatically" : false,	"numIndexesBefore" : 1,
                        

                            

                                
< 上一篇
Mysql学习总结（17）MySQL数据库表设计优化
                            
                            

                                 
下一篇 >
【sql查询与优化】5.使用字符串