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" : 1 15 }, 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+308 20 ] 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+308 20 ] 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+308 26 ] 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+308 26 ] 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+308 26 ] 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" : 1 27 }, 28 { 29 "$maxElement" : 1 30 } 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" : 1 27 }, 28 { 29 "$maxElement" : 1 30 } 31 ] 32 ], 33 "age" : [ 34 [ 35 23, 36 1.7976931348623157e+308 37 ] 38 ] 39 }, 40 "server" : "xxxx:27017" 41 } 42 >
总结
MongoDB中，索引还有很多东西，本文只是通过一些例子来介绍了索引的使用，以及组合索引的简单分析
Ps: 本文中所有例子中的命令都可以参考以下链接
https://files.cnblogs.com/wilber2013/index.js
