首页 > 代码库 > MongoDB索引

MongoDB索引

数据库中的索引就是用来提高查询操作的性能,但是会影响插入、更新和删除的效率,因为数据库不仅要执行这些操作,还要负责索引的更新。

通过建立索引,影响一部分插入、更新和删除的效率,但是能大大挺高查询的效率,这个还是很值得的。

为了开始后面的操作,首先通过MongoDB shell插入一些测试数据。

 1 for(var i=0;i<10;i++){ 2   var randAge = parseInt(5*Math.random()) + 20; 3   var gender = (randAge%2)?"Male":"Female"; 4   db.school.students.insert({"name":"Will"+i, "gender": gender, "age": randAge}); 5 } 6  7   8 /*    我的数据,以下测试都是基于这个测试,由于数据是随机生成,所以测试每次都会不同 9 { "name" : "Will0", "gender" : "Female", "age" : 22 },10 { "name" : "Will1", "gender" : "Female", "age" : 20 },11 { "name" : "Will2", "gender" : "Male", "age" : 24 },12 { "name" : "Will3", "gender" : "Male", "age" : 23 },13 { "name" : "Will4", "gender" : "Male", "age" : 21 },14 { "name" : "Will5", "gender" : "Male", "age" : 20 },15 { "name" : "Will6", "gender" : "Female", "age" : 20 },16 { "name" : "Will7", "gender" : "Female", "age" : 24 },17 { "name" : "Will8", "gender" : "Male", "age" : 21 },18 { "name" : "Will9", "gender" : "Female", "age" : 24 },19 */

 

索引的操作

创建索引:在MongoDB shell中,可以通过ensureIndex()来创建所以,第一个参数是指定要创建所以的键。

通过unique参数可以创建唯一索引。

1 > db.school.students.ensureIndex({"name": 1}, {"unique": true})2 >

 查看索引:

 1 > db.school.students.getIndexes() 2 [ 3         { 4                 "v" : 1, 5                 "key" : { 6                         "_id" : 1 7                 }, 8                 "ns" : "test.school.students", 9                 "name" : "_id_"10         },11         {12                 "v" : 1,13                 "key" : {14                         "name" : 115                 },16                 "unique" : true,17                 "ns" : "test.school.students",18                 "name" : "name_1"19         }20 ]21 >

 

删除索引:

1 > db.school.students.dropIndex("name_1")2 { "nIndexesWas" : 2, "ok" : 1 }3 >

 

索引名称:默认情况下,索引的名称是"键_值_键_值…"的形式,当键的数量很多的时候,索引的名字就会很长。

所以,在创建索引的时候,可以通过"name"参数自定义索引的名字。

1 > db.school.students.ensureIndex({"name": 1}, {"name": "myIndex"})2 >

 

 

explain()和hint()

通过explain()可以得到很多跟find相关的信息,对索引的分析很有帮助。

当有多个可以使用的索引时,MongoDB会自动选择最优索引,但是我们可以通过hint()操作选择我们想要使用的索引。

下面来看看没有索引时explain()的输出:

 1 > db.school.students.find({"name": "Will5"}).explain() 2 { 3         "cursor" : "BasicCursor", 4         "isMultiKey" : false, 5         "n" : 1, 6         "nscannedObjects" : 6, 7         "nscanned" : 6, 8         "nscannedObjectsAllPlans" : 6, 9         "nscannedAllPlans" : 6,10         "scanAndOrder" : false,11         "indexOnly" : false,12         "nYields" : 0,13         "nChunkSkips" : 0,14         "millis" : 0,15         "indexBounds" : {16 17         },18         "server" : "××××:27017"19 }20 >

 

分析:下面选择了几个我们比较关心的字段

  • cursor:BasicCursor表示是full Collection scan,即没有索引的全表扫描
  • n:满足查询条件的文档数量
  • nscannedObjects:总共扫描的文档的数量
  • nscanned:总共扫描的索引节点的数量
  • scanAndOrder:false表示,MongoDB现有索引下文档的顺序来返回排序结果;true表示,MongoDB需要在得到查询结果后重新排序
  • millis:完成查询需要的毫秒数

添加索引,再次检查explain()的输出:

 1 > db.school.students.ensureIndex({"name": 1}, {"unique": true}) 2 > db.school.students.find({"name": "Will5"}).explain() 3 { 4         "cursor" : "BtreeCursor name_1", 5         "isMultiKey" : false, 6         "n" : 1, 7         "nscannedObjects" : 1, 8         "nscanned" : 1, 9         "nscannedObjectsAllPlans" : 1,10         "nscannedAllPlans" : 1,11         "scanAndOrder" : false,12         "indexOnly" : false,13         "nYields" : 0,14         "nChunkSkips" : 0,15         "millis" : 0,16         "indexBounds" : {17                 "name" : [18                         [19                                 "Will5",20                                 "Will5"21                         ]22                 ]23         },24         "server" : "××××:27017"25 }26 >

 

 

组合索引

单键索引还是比较简单的,当使用组合索引的时候,就要多考虑一些了。自己也不确定能否总结的很好,如果错误,希望大家指出、讨论。

索引建立可能有多种方式,我们的目标就是减少"nscanned"(当然也有特例,请参照"索引和排序")。

下面分析基于前面生成的数据来分析一下组合索引,假设我们要查询年龄大于等于23的女学生。

  • 使用"age_1"索引的输出如下
     1 > db.school.students.find({"age":{"$gte":23}, "gender":"Female"}).hint("age_1").explain() 2 { 3         "cursor" : "BtreeCursor age_1", 4         "isMultiKey" : false, 5         "n" : 2, 6         "nscannedObjects" : 4, 7         "nscanned" : 4, 8         "nscannedObjectsAllPlans" : 4, 9         "nscannedAllPlans" : 4,10         "scanAndOrder" : false,11         "indexOnly" : false,12         "nYields" : 0,13         "nChunkSkips" : 0,14         "millis" : 0,15         "indexBounds" : {16                 "age" : [17                         [18                                 23,19                                 1.7976931348623157e+30820                         ]21                 ]22         },23         "server" : "××××:27017"24 }25 >

     

    索引的分析:

Index

Documents

Result

age:20

{ "name" : "Will1", "gender" : "Female", "age" : 20 }

"n" : 2

age:20

{ "name" : "Will5", "gender" : "Male", "age" : 20 }

"nscannedObjects" : 4

age:20

{ "name" : "Will6", "gender" : "Female", "age" : 20 }

"nscanned" : 4

age:21

{ "name" : "Will4", "gender" : "Male", "age" : 21 }

 

age:21

{ "name" : "Will8", "gender" : "Male", "age" : 21 }

 

age:22

{ "name" : "Will0", "gender" : "Female", "age" : 22 }

 

age:23

{ "name" : "Will3", "gender" : "Male", "age" : 23 }

 

age:24

{ "name" : "Will2", "gender" : "Male", "age" : 24 }

 

age:24

{ "name" : "Will7", "gender" : "Female", "age" : 24 }

 

age:24

{ "name" : "Will9", "gender" : "Female", "age" : 24 }

 

 

 

  • 使用"age_1_gender_1"索引的输出如下
     1 > db.school.students.find({"age":{"$gte":23}, "gender":"Female"}).hint("age_1_gender_1").explain() 2 { 3         "cursor" : "BtreeCursor age_1_gender_1", 4         "isMultiKey" : false, 5         "n" : 2, 6         "nscannedObjects" : 2, 7         "nscanned" : 4, 8         "nscannedObjectsAllPlans" : 2, 9         "nscannedAllPlans" : 4,10         "scanAndOrder" : false,11         "indexOnly" : false,12         "nYields" : 0,13         "nChunkSkips" : 0,14         "millis" : 0,15         "indexBounds" : {16                 "age" : [17                         [18                                 23,19                                 1.7976931348623157e+30820                         ]21                 ],22                 "gender" : [23                         [24                                 "Female",25                                 "Female"26                         ]27                 ]28         },29         "server" : "××××:27017"30 }31 >

     

    索引的分析:

Index

Documents

Result

age:20, gender:Female

{ "name" : "Will1", "gender" : "Female", "age" : 20 }

"n" : 2

age:20, gender:Female

{ "name" : "Will6", "gender" : "Female", "age" : 20 }

"nscannedObjects" : 2

age:20, gender:Male

{ "name" : "Will5", "gender" : "Male", "age" : 20 }

"nscanned" : 4

age:21, gender:Male

{ "name" : "Will4", "gender" : "Male", "age" : 21 }

 

age:21, gender:Male

{ "name" : "Will8", "gender" : "Male", "age" : 21 }

 

age:22, gender:Female

{ "name" : "Will0", "gender" : "Female", "age" : 22}

 

age:23, gender:Male

{ "name" : "Will3", "gender" : "Male", "age" : 23 }

 

age:24, gender:Female

{ "name" : "Will7", "gender" : "Female", "age" : 24 }

 

age:24, gender:Female

{ "name" : "Will9", "gender" : "Female", "age" : 24 }

 

age:24, gender:Male

{ "name" : "Will2", "gender" : "Male", "age" : 24 }

 

 

  • 使用"gender_1_age_1"索引的输出如下
     1 > db.school.students.find({"age":{"$gte":23}, "gender":"Female"}).hint("gender_1_age_1").explain() 2 { 3         "cursor" : "BtreeCursor gender_1_age_1", 4         "isMultiKey" : false, 5         "n" : 2, 6         "nscannedObjects" : 2, 7         "nscanned" : 2, 8         "nscannedObjectsAllPlans" : 2, 9         "nscannedAllPlans" : 2,10         "scanAndOrder" : false,11         "indexOnly" : false,12         "nYields" : 0,13         "nChunkSkips" : 0,14         "millis" : 0,15         "indexBounds" : {16                 "gender" : [17                         [18                                 "Female",19                                 "Female"20                         ]21                 ],22                 "age" : [23                         [24                                 23,25                                 1.7976931348623157e+30826                         ]27                 ]28         },29         "server" : "××××:27017"30 }31 >

     

    索引的分析:

Index

Documents

Result

gender:Female, age:20

{ "name" : "Will1", "gender" : "Female", "age" : 20 }

"n" : 2

gender:Female, age:20

{ "name" : "Will6", "gender" : "Female", "age" : 20 }

"nscannedObjects" : 2

gender:Female, age:22

{ "name" : "Will0", "gender" : "Female", "age" : 22 }

"nscanned" : 2

gender:Female, age:24

{ "name" : "Will7", "gender" : "Female", "age" : 24 }

 

gender:Female, age:24

{ "name" : "Will9", "gender" : "Female", "age" : 24 }

 

gender:Male, age:20

{ "name" : "Will5", "gender" : "Male", "age" : 20 }

 

gender:Male, age:21

{ "name" : "Will4", "gender" : "Male", "age" : 21 }

 

gender:Male, age:21

{ "name" : "Will8", "gender" : "Male", "age" : 21 }

 

gender:Male, age:23

{ "name" : "Will3", "gender" : "Male", "age" : 23 }

 

gender:Male, age:24

{ "name" : "Will2", "gender" : "Male", "age" : 24 }

 

 

通过上面的例子可以看出,在使用组合索引的时候还是要考虑很多东西的,所以可以结合explain()来进行分析。

 

索引选择机制

由于我们前面创建了三个索引,下面我们直接使用默认查询。

 1 > db.school.students.find({"age":{"$gte":23}, "gender":"Female"}).explain() 2 { 3         "cursor" : "BtreeCursor gender_1_age_1", 4         "isMultiKey" : false, 5         "n" : 2, 6         "nscannedObjects" : 2, 7         "nscanned" : 2, 8         "nscannedObjectsAllPlans" : 2, 9         "nscannedAllPlans" : 2,10         "scanAndOrder" : false,11         "indexOnly" : false,12         "nYields" : 0,13         "nChunkSkips" : 0,14         "millis" : 0,15         "indexBounds" : {16                 "gender" : [17                         [18                                 "Female",19                                 "Female"20                         ]21                 ],22                 "age" : [23                         [24                                 23,25                                 1.7976931348623157e+30826                         ]27                 ]28         },29         "server" : "××××:27017"30 }31 >

 

存在多条索引的情况下,MongoDB首选nscanned值最低的索引。

 

索引和排序

基于上面的例子,我们加上对"name"的排序操作。这时,我们可以看到"scanAndOrder"变成了"true"。

 1 > db.school.students.find({"age":{"$gte":23}, "gender":"Female"}).sort({"name":1}).explain() 2 { 3         "cursor" : "BtreeCursor gender_1_age_1", 4         "isMultiKey" : false, 5         "n" : 2, 6         "nscannedObjects" : 2, 7         "nscanned" : 2, 8         "nscannedObjectsAllPlans" : 7, 9         "nscannedAllPlans" : 9,10         "scanAndOrder" : true,11         "indexOnly" : false,12         "nYields" : 0,13         "nChunkSkips" : 0,14         "millis" : 0,15         "indexBounds" : {16                 "gender" : [17                         [18                                 "Female",19                                 "Female"20                         ]21                 ],22                 "age" : [23                         [24                                 23,25                                 1.7976931348623157e+30826                         ]27                 ]28         },29         "server" : "××××:27017"30 }

 

在这个例子中,"nscanned"是最小的,所以这个方案是查询效率最高的。但是,我们要注意一下"scanAndOrder",根据MongoDB文档的解释,查询结果的排序不能利用现有的索引,MongoDB会把find找到的结果放入内存重新排序。这样的话,如果数据量很大,会对性能产生很大的影响。

最好的办法是利用索引来进行排序。

在这种情况下,就要加入一个"name"的索引,同时在find操作时使用hint来指定索引方式,因为默认情况MongoDB会选择"nscanned"最小的方式。

 1 > db.school.students.ensureIndex({"gender":1,"name":1}) 2 > db.school.students.find({"age":{"$gte":23}, "gender":"Female"}).sort({"name":1}).hint("gender_1_name_1").explain() 3 { 4         "cursor" : "BtreeCursor gender_1_name_1", 5         "isMultiKey" : false, 6         "n" : 2, 7         "nscannedObjects" : 5, 8         "nscanned" : 5, 9         "nscannedObjectsAllPlans" : 5,10         "nscannedAllPlans" : 5,11         "scanAndOrder" : false,12         "indexOnly" : false,13         "nYields" : 0,14         "nChunkSkips" : 0,15         "millis" : 0,16         "indexBounds" : {17                 "gender" : [18                         [19                                 "Female",20                                 "Female"21                         ]22                 ],23                 "name" : [24                         [25                                 {26                                         "$minElement" : 127                                 },28                                 {29                                         "$maxElement" : 130                                 }31                         ]32                 ]33         },34         "server" : "xxxx:27017"35 }36 >

 

通过这种方式,就可以利用索引的排序来避免"scanAndOrder"为true的情况。但是再看看上面的方式,似乎可以进一步优化,虽然不能减少"nscanned",但是可以减少"nscannedObjects"。

 1 > db.school.students.ensureIndex({"gender":1,"name":1,"age":1}) 2 > db.school.students.find({"age":{"$gte":23}, "gender":"Female"}).sort({"name":1}).hint("gender_1_name_1_age_1").explain() 3 { 4         "cursor" : "BtreeCursor gender_1_name_1_age_1", 5         "isMultiKey" : false, 6         "n" : 2, 7         "nscannedObjects" : 2, 8         "nscanned" : 5, 9         "nscannedObjectsAllPlans" : 2,10         "nscannedAllPlans" : 5,11         "scanAndOrder" : false,12         "indexOnly" : false,13         "nYields" : 0,14         "nChunkSkips" : 0,15         "millis" : 0,16         "indexBounds" : {17                 "gender" : [18                         [19                                 "Female",20                                 "Female"21                         ]22                 ],23                 "name" : [24                         [25                                 {26                                         "$minElement" : 127                                 },28                                 {29                                         "$maxElement" : 130                                 }31                         ]32                 ],33                 "age" : [34                         [35                                 23,36                                 1.7976931348623157e+30837                         ]38                 ]39         },40         "server" : "xxxx:27017"41 }42 >

 

 

总结

MongoDB中,索引还有很多东西,本文只是通过一些例子来介绍了索引的使用,以及组合索引的简单分析

Ps: 本文中所有例子中的命令都可以参考以下链接

http://files.cnblogs.com/wilber2013/index.js

 

MongoDB索引