索引被用于为频繁使用的查询提供高性能读取操作;
默认情况下,当一个集合被创建并且文档被添加到其中时,会在_id字段上创建一个索引;
使用for循环在testindx的新集合中插入100W个文档:
> for(i = 0; i < 1000000; i++) { db.testindx.insert({"Name":"user"+i,"Age":Math.floor(Math.random()*120)})}
WriteResult({ "nInserted" : 1 })
查询Name值为user101的文档:
> db.testindx.find({"Name":"user101"}).explain("allPlansExecution")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "mydb.testindx",
"indexFilterSet" : false,
"parsedQuery" : {
"Name" : {
"$eq" : "user101"
}
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"Name" : {
"$eq" : "user101"
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 232,
"totalKeysExamined" : 0,
"totalDocsExamined" : 1000000,
"executionStages" : {
"stage" : "COLLSCAN",
"filter" : {
"Name" : {
"$eq" : "user101"
}
},
"nReturned" : 1,
"executionTimeMillisEstimate" : 216,
"works" : 1000002,
"advanced" : 1,
"needTime" : 1000000,
"needYield" : 0,
"saveState" : 7817,
"restoreState" : 7817,
"isEOF" : 1,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 1000000
},
"allPlansExecution" : [ ]
},
"serverInfo" : {
"host" : "localhost.localdomain",
"port" : 27017,
"version" : "3.4.10",
"gitVersion" : "078f28920cb24de0dd479b5ea6c66c644f6326e9"
},
"ok" : 1
}
1、单键索引
> db.testindx.ensureIndex({"Name":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
> db.testindx.find({"Name":"user101"}).explain("allPlansExecution")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "mydb.testindx",
"indexFilterSet" : false,
"parsedQuery" : {
"Name" : {
"$eq" : "user101"
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"Name" : 1
},
"indexName" : "Name_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"Name" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"Name" : [
"[\"user101\", \"user101\"]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 223,
"totalKeysExamined" : 1,
"totalDocsExamined" : 1,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 1,
"executionTimeMillisEstimate" : 136,
"works" : 2,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 1,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1,
"executionTimeMillisEstimate" : 136,
"works" : 2,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"Name" : 1
},
"indexName" : "Name_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"Name" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"Name" : [
"[\"user101\", \"user101\"]"
]
},
"keysExamined" : 1,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
},
"allPlansExecution" : [ ]
},
"serverInfo" : {
"host" : "localhost.localdomain",
"port" : 27017,
"version" : "3.4.10",
"gitVersion" : "078f28920cb24de0dd479b5ea6c66c644f6326e9"
},
"ok" : 1
}
2、复合索引
复合索引更有效的执行带有多个子句的查询;
注意:创建复合索引时,牢记字段将被用于第一个出现的精确匹配,其后是要用在范围中的字段;
> db.testindx.ensureIndex({"Name":1, "Age":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
> db.testindx.find({"Name":"user5", "Age":{"$gt":25}}).explain("allPlansExecution")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "mydb.testindx",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"Name" : {
"$eq" : "user5"
}
},
{
"Age" : {
"$gt" : 25
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"Name" : 1,
"Age" : 1
},
"indexName" : "Name_1_Age_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"Name" : [ ],
"Age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"Name" : [
"[\"user5\", \"user5\"]"
],
"Age" : [
"(25.0, inf.0]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"Age" : {
"$gt" : 25
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"Name" : 1
},
"indexName" : "Name_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"Name" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"Name" : [
"[\"user5\", \"user5\"]"
]
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 0,
"totalKeysExamined" : 1,
"totalDocsExamined" : 1,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"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" : {
"Name" : 1,
"Age" : 1
},
"indexName" : "Name_1_Age_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"Name" : [ ],
"Age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"Name" : [
"[\"user5\", \"user5\"]"
],
"Age" : [
"(25.0, inf.0]"
]
},
"keysExamined" : 1,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
},
"allPlansExecution" : [
{
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"totalKeysExamined" : 1,
"totalDocsExamined" : 1,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"Age" : {
"$gt" : 25
}
},
"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" : {
"Name" : 1
},
"indexName" : "Name_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"Name" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"Name" : [
"[\"user5\", \"user5\"]"
]
},
"keysExamined" : 1,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
{
"nReturned" : 1,
"executionTimeMillisEstimate" : 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" : {
"Name" : 1,
"Age" : 1
},
"indexName" : "Name_1_Age_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"Name" : [ ],
"Age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"Name" : [
"[\"user5\", \"user5\"]"
],
"Age" : [
"(25.0, inf.0]"
]
},
"keysExamined" : 1,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
]
},
"serverInfo" : {
"host" : "localhost.localdomain",
"port" : 27017,
"version" : "3.4.10",
"gitVersion" : "078f28920cb24de0dd479b5ea6c66c644f6326e9"
},
"ok" : 1
}
3、唯一索引
删除已有的索引:
> db.testindx.dropIndexes()
{
"nIndexesWas" : 3,
"msg" : "non-_id indexes dropped for collection",
"ok" : 1
}
在Name字段上创建一个唯一索引:
> db.testindx.ensureIndex({"Name":1},{"unique":true})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
Name字段不允许插入重复记录,并且只保存第一条记录:
> db.testindx.insert({"Name":"uniquename"})
WriteResult({ "nInserted" : 1 })
> db.testindx.insert({"Name":"uniquename"})
WriteResult({
"nInserted" : 0,
"writeError" : {
"code" : 11000,
"errmsg" : "E11000 duplicate key error collection: mydb.testindx index: Name_1 dup key: { : \"uniquename\" }"
}
})
> db.testindx.find({"Name":"uniquename"})
{ "_id" : ObjectId("5afbfb168dda484c0dacf0bf"), "Name" : "uniquename" }
也可以为复合索引启用唯一性,意味着尽管单个字段可以重复,但是其组合将是唯一的;
如果先创建集合并且插入文档,然后在该集合上创建一个唯一索引,此时如果该集合的唯一索引字段存在重复值的话,索引的创建就会失败;为了满足这一场景,可以使用dropDups选项;
以下命令在Name字段上创建一个唯一索引,并且将删除所有重复的文档:
db.testindx.ensureIndex({"Name":1},{"unique":true, "dropDups":true})
4、查询、移除、重构索引
查询testindx集合上的所有索引:
> db.testindx.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "mydb.testindx"
},
{
"v" : 2,
"unique" : true,
"key" : {
"Name" : 1
},
"name" : "Name_1",
"ns" : "mydb.testindx"
}
]
移除Name字段索引:
> db.testindx.dropIndex({"Name":1})
{ "nIndexesWas" : 2, "ok" : 1 }
重构testindx集合的索引,重构索引会先删除索引,包括_id字段上的默认索引,然后重构:
> db.testindx.reIndex()
{
"nIndexesWas" : 1,
"nIndexes" : 1,
"indexes" : [
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "mydb.testindx"
}
],
"ok" : 1
}
索引的维护伴随着附加成本,所以需要定期检查使用一个索引的有效性,根据在系统上执行的读和写的比例来衡量,识别出较少使用的索引并且删除它们;