mongodb Index(2)

复合索引 <接上>

> 删除之前的collection，重新建立，如下所示：

> db.person.drop()

true

> for(var i=0;i<2000000;i++){

... db.person.insert({"name":"meteor"+i00,"age":20+i});

... }

WriteResult({ "nInserted" : 1 })

>

> db.person.ensureIndex({"age":1}) 创建单一索引

{"createdCollectionAutomatically" : false,"numIndexesBefore" : 1,"numIndexesAfter" : 2,"ok" : 1}

> db.person.ensureIndex({"name":1,"age":1}) 创建复合索引 {"name":1,"age":1}

{"createdCollectionAutomatically" : false,"numIndexesBefore" : 2,"numIndexesAfter" : 3,"ok" : 1}

> db.person.ensureIndex({"age":1,"name":1}) 创建复合索引{"age":1,"name":1}

{"createdCollectionAutomatically" : false,"numIndexesBefore" : 3,"numIndexesAfter" : 4,"ok" : 1}

> 查找时指定多个条件，使用hint强制指定使用单一索引速度比较慢，如下所示:

> db.person.find({"age":{"$gte":20,"$lte":30},"name":"meteor1"}).hint({"age":1}).explain("executionStats")

{"queryPlanner" : {"plannerVersion" : 1,"namespace" : "test.person","indexFilterSet" : false,"parsedQuery" : {"$and" : [{"name" : {"$eq" : "meteor1"}},{"age" : {"$lte" : 30}},{"age" : {"$gte" : 20}}]},"winningPlan" : {"stage" : "FETCH","filter" : {"name" : {"$eq" : "meteor1"}},"inputStage" : {"stage" : "IXSCAN","keyPattern" : {"age" : 1},"indexName" : "age_1","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "forward","indexBounds" : {"age" : ["[20.0, 30.0]"]}}},"rejectedPlans" : [ ]},"executionStats" : {"executionSuccess" : true,"nReturned" : 2000,"executionTimeMillis" : 2621,"totalKeysExamined" : 2000000,"totalDocsExamined" : 2000000,"executionStages" : {"stage" : "FETCH","filter" : {"name" : {"$eq" : "meteor1"}},"nReturned" : 2000,"executionTimeMillisEstimate" : 2050,"works" : 2000001,"advanced" : 2000,"needTime" : 1998000,"needYield" : 0,"saveState" : 15625,"restoreState" : 15625,"isEOF" : 1,"invalidates" : 0,"docsExamined" : 2000000,"alreadyHasObj" : 0,"inputStage" : {"stage" : "IXSCAN","nReturned" : 2000000,"executionTimeMillisEstimate" : 640,"works" : 2000001,"advanced" : 2000000,"needTime" : 0,"needYield" : 0,"saveState" : 15625,"restoreState" : 15625,"isEOF" : 1,"invalidates" : 0,"keyPattern" : {"age" : 1},"indexName" : "age_1","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "forward","indexBounds" : {"age" : ["[20.0, 30.0]"]},"keysExamined" : 2000000,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0}}},"serverInfo" : {"host" : "meteor.yeecall.com","port" : 27027,"version" : "3.2.8","gitVersion" : "ed70e33130c977bda0024c125b56d159573dbaf0"},"ok" : 1}

> 查找数据时指定多个条件，使用hint强制指定使用{"age":1,"name":1}复合索引，速度比较快，如下所示：

> db.person.find({"age":{"$gte":20,"$lte":30},"name":"meteor1"}).hint({"age":1,"name":1}).explain("executionStats")

{"queryPlanner" : {"plannerVersion" : 1,"namespace" : "test.person","indexFilterSet" : false,"parsedQuery" : {"$and" : [{"name" : {"$eq" : "meteor1"}},{"age" : {"$lte" : 30}},{"age" : {"$gte" : 20}}]},"winningPlan" : {"stage" : "FETCH","inputStage" : {"stage" : "IXSCAN","keyPattern" : {"age" : 1,"name" : 1},"indexName" : "age_1_name_1","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "forward","indexBounds" : {"age" : ["[20.0, 30.0]"],"name" : ["[\"meteor1\", \"meteor1\"]"]}}},"rejectedPlans" : [ ]},"executionStats" : {"executionSuccess" : true,"nReturned" : 2000,"executionTimeMillis" : 15,"totalKeysExamined" : 2010,"totalDocsExamined" : 2000,"executionStages" : {"stage" : "FETCH","nReturned" : 2000,"executionTimeMillisEstimate" : 10,"works" : 2011,"advanced" : 2000,"needTime" : 10,"needYield" : 0,"saveState" : 15,"restoreState" : 15,"isEOF" : 1,"invalidates" : 0,"docsExamined" : 2000,"alreadyHasObj" : 0,"inputStage" : {"stage" : "IXSCAN","nReturned" : 2000,"executionTimeMillisEstimate" : 10,"works" : 2011,"advanced" : 2000,"needTime" : 10,"needYield" : 0,"saveState" : 15,"restoreState" : 15,"isEOF" : 1,"invalidates" : 0,"keyPattern" : {"age" : 1,"name" : 1},"indexName" : "age_1_name_1","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "forward","indexBounds" : {"age" : ["[20.0, 30.0]"],"name" : ["[\"meteor1\", \"meteor1\"]"]},"keysExamined" : 2010,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0}}},"serverInfo" : {"host" : "meteor.yeecall.com","port" : 27027,"version" : "3.2.8","gitVersion" : "ed70e33130c977bda0024c125b56d159573dbaf0"},"ok" : 1}

=================================================================================

查询结束再次排序(按name排序)，并使用limit截取其中一部分，使用hint强制指定使用{"age":1,"name":1}索引时速度较慢，如下所示：

> db.person.find({"age":{"$gte":20,"$lte":30}}).sort({"name":1}).limit(100).hint({"age":1,"name":1}).explain("executionStats")

{"queryPlanner" : {"plannerVersion" : 1,"namespace" : "test.person","indexFilterSet" : false,"parsedQuery" : {"$and" : [{"age" : {"$lte" : 30}},{"age" : {"$gte" : 20}}]},"winningPlan" : {"stage" : "SORT","sortPattern" : {"name" : 1},"limitAmount" : 100,"inputStage" : {"stage" : "SORT_KEY_GENERATOR","inputStage" : {"stage" : "FETCH","inputStage" : {"stage" : "IXSCAN","keyPattern" : {"age" : 1,"name" : 1},"indexName" : "age_1_name_1","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "forward","indexBounds" : {"age" : ["[20.0, 30.0]"],"name" : ["[MinKey, MaxKey]"]}}}}},"rejectedPlans" : [ ]},"executionStats" : {"executionSuccess" : true,"nReturned" : 100,"executionTimeMillis" : 6991,"totalKeysExamined" : 2000000,"totalDocsExamined" : 2000000,"executionStages" : {"stage" : "SORT","nReturned" : 100,"executionTimeMillisEstimate" : 5980,"works" : 2000103,"advanced" : 100,"needTime" : 2000002,"needYield" : 0,"saveState" : 15625,"restoreState" : 15625,"isEOF" : 1,"invalidates" : 0,"sortPattern" : {"name" : 1},"memUsage" : 6100,"memLimit" : 33554432,"limitAmount" : 100,"inputStage" : {"stage" : "SORT_KEY_GENERATOR","nReturned" : 0,"executionTimeMillisEstimate" : 5680,"works" : 2000002,"advanced" : 0,"needTime" : 1,"needYield" : 0,"saveState" : 15625,"restoreState" : 15625,"isEOF" : 1,"invalidates" : 0,"inputStage" : {"stage" : "FETCH","nReturned" : 2000000,"executionTimeMillisEstimate" : 4870,"works" : 2000001,"advanced" : 2000000,"needTime" : 0,"needYield" : 0,"saveState" : 15625,"restoreState" : 15625,"isEOF" : 1,"invalidates" : 0,"docsExamined" : 2000000,"alreadyHasObj" : 0,"inputStage" : {"stage" : "IXSCAN","nReturned" : 2000000,"executionTimeMillisEstimate" : 2400,"works" : 2000001,"advanced" : 2000000,"needTime" : 0,"needYield" : 0,"saveState" : 15625,"restoreState" : 15625,"isEOF" : 1,"invalidates" : 0,"keyPattern" : {"age" : 1,"name" : 1},"indexName" : "age_1_name_1","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "forward","indexBounds" : {"age" : ["[20.0, 30.0]"],"name" : ["[MinKey, MaxKey]"]},"keysExamined" : 2000000,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0}}}}},"serverInfo" : {"host" : "meteor.yeecall.com","port" : 27027,"version" : "3.2.8","gitVersion" : "ed70e33130c977bda0024c125b56d159573dbaf0"},"ok" : 1}>

查询结束再次排序(按name排序)，并使用limit截取其中一部分，使用hint强制指定使用{"name":1,"age":1}索引时速度较快，如下所示：

> db.person.find({"age":{"$gte":20,"$lte":30}}).sort({"name":1}).limit(100).hint({"name":1,"age":1}).explain("executionStats")

{"queryPlanner" : {"plannerVersion" : 1,"namespace" : "test.person","indexFilterSet" : false,"parsedQuery" : {"$and" : [{"age" : {"$lte" : 30}},{"age" : {"$gte" : 20}}]},"winningPlan" : {"stage" : "LIMIT","limitAmount" : 100,"inputStage" : {"stage" : "FETCH","filter" : {"$and" : [{"age" : {"$lte" : 30}},{"age" : {"$gte" : 20}}]},"inputStage" : {"stage" : "IXSCAN","keyPattern" : {"name" : 1,"age" : 1},"indexName" : "name_1_age_1","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "forward","indexBounds" : {"name" : ["[MinKey, MaxKey]"],"age" : ["[MinKey, MaxKey]"]}}}},"rejectedPlans" : [ ]},"executionStats" : {"executionSuccess" : true,"nReturned" : 100,"executionTimeMillis" : 5,"totalKeysExamined" : 100,"totalDocsExamined" : 100,"executionStages" : {"stage" : "LIMIT","nReturned" : 100,"executionTimeMillisEstimate" : 0,"works" : 101,"advanced" : 100,"needTime" : 0,"needYield" : 0,"saveState" : 0,"restoreState" : 0,"isEOF" : 1,"invalidates" : 0,"limitAmount" : 100,"inputStage" : {"stage" : "FETCH","filter" : {"$and" : [{"age" : {"$lte" : 30}},{"age" : {"$gte" : 20}}]},"nReturned" : 100,"executionTimeMillisEstimate" : 0,"works" : 100,"advanced" : 100,"needTime" : 0,"needYield" : 0,"saveState" : 0,"restoreState" : 0,"isEOF" : 0,"invalidates" : 0,"docsExamined" : 100,"alreadyHasObj" : 0,"inputStage" : {"stage" : "IXSCAN","nReturned" : 100,"executionTimeMillisEstimate" : 0,"works" : 100,"advanced" : 100,"needTime" : 0,"needYield" : 0,"saveState" : 0,"restoreState" : 0,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"name" : 1,"age" : 1},"indexName" : "name_1_age_1","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "forward","indexBounds" : {"name" : ["[MinKey, MaxKey]"],"age" : ["[MinKey, MaxKey]"]},"keysExamined" : 100,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0}}}},"serverInfo" : {"host" : "meteor.yeecall.com","port" : 27027,"version" : "3.2.8","gitVersion" : "ed70e33130c977bda0024c125b56d159573dbaf0"},"ok" : 1}

> 如果按age排序，索引使用{"name":1,"age":1}速度非常慢；如果按age排序，索引使用{"age":1,"name":1}速度比较快

>分析：第一种索引，需要找到所有复合查询条件的值（依据索引，键和文档可以快速找到），但是找到后，需要对文档在内存中进行排序，这个步骤消耗了非常多的时间。第二种索引，效果非常好，因为不需要在内存中对大量数据进行排序。但是，MongoDB不得不扫描整个索引以便找到所有文档。因此，如果对查询结果的范围做了限制，那么MongoDB在几次匹配之后就可以不再扫描索引，在这种情况下，将排序键放在第一位是一个非常好的策略。

查看索引

> db.person.getIndexes()

[{"v" : 1,"key" : {"_id" : 1},"name" : "_id_","ns" : "test.person"},{"v" : 1,"key" : {"age" : 1},"name" : "age_1","ns" : "test.person"},{"v" : 1,"key" : {"name" : 1,"age" : 1},"name" : "name_1_age_1","ns" : "test.person"},{"v" : 1,"key" : {"age" : 1,"name" : 1},"name" : "age_1_name_1","ns" : "test.person"}]

> db.person.dropIndex("name_1_age_1") 删除索引

{ "nIndexesWas" : 4, "ok" : 1 }

> db.person.dropIndex("age_1_name_1")

{ "nIndexesWas" : 3, "ok" : 1 }

> db.person.dropIndex("age_1")

{ "nIndexesWas" : 2, "ok" : 1 }

> db.runCommand({dropIndexes:"person",index:"*"}) 删除索引的另一种方法

{"nIndexesWas" : 1,"msg" : "non-_id indexes dropped for collection","ok" : 1}

> db.person.ensureIndex({"name":1,"age":1},{"unique":true}) 创建唯一索引 （本例没有成功，因为集合中有重复内容)

{"ok" : 0,"errmsg" : "E11000 duplicate key error collection: test.person index: name_1_age_1 dup key: { : \"meteor0\", : 20.0 }","code" : 11000}

MongoDB 索引限制

额外开销

每个索引占据一定的存储空间，在进行插入，更新和删除操作时也需要对索引进行操作。所以，如果你很少对集合进行读取操作，建议不使用索引。

内存(RAM)使用

由于索引是存储在内存(RAM)中,应该确保该索引的大小不超过内存的限制。(如上文中提示sort排序后，如果没有limit字段系统会提示错误，因为索引大小超过了内存的限制)

如果索引的大小大于内存的限制，MongoDB会删除一些索引，这将导致性能下降。

查询限制

索引不能被以下的查询使用：正则表达式及非操作符，如 $nin, $not, 等；算术运算符，如 $mod, 等；$where 子句

所以，检测语句是否使用索引是一个好的习惯，可以用explain来查看。

索引键限制

从2.6版本开始，如果现有的索引字段的值超过索引键的限制，MongoDB中不会创建索引。

插入文档超过索引键限制

如果文档的索引字段值超过了索引键的限制，MongoDB不会将任何文档转换成索引的集合。与mongorestore和mongoimport工具类似。

最大范围

集合中索引不能超过64个；索引名的长度不能超过125个字符

一个复合索引最多可以有31个字段