MongoDB索引

索引通常能够极大的提高查询的效率，如果没有索引，MongoDB在读取数据时必须扫描集合中的每个文档并选取那些符合查询条件的记录, 这种扫描全集合的查询效率是非常低的，特别在处理大量的数据时，查询可以要花费几十秒甚至几分钟，这对网站的性能是非常致命的

索引是特殊的数据结构，索引存储在一个易于遍历读取的数据集合中，索引是对数据库表中一列或多列的值进行排序的一种结构

ensureIndex()建立索引

db.文档名.ensureIndex({KEY:1})

KEY为要创建的索引字段, 1为指定按照升序创建索引, -1为降序

explain

explain可以获知系统如何处理查询请求, 利用explain命令, 可以观察系统如何使用索引来加快检索, 同时可以针对性优化索引

---

现在有数据

> db.goods.find()
{ "_id" : 1, "item" : "f1", "type" : "food", "quantity" : 500 }
{ "_id" : 2, "item" : "f2", "type" : "food", "quantity" : 100 }
{ "_id" : 3, "item" : "p1", "type" : "paper", "quantity" : 200 }
{ "_id" : 4, "item" : "p2", "type" : "paper", "quantity" : 150 }
{ "_id" : 5, "item" : "f3", "type" : "food", "quantity" : 300 }
{ "_id" : 6, "item" : "t1", "type" : "toys", "quantity" : 500 }
{ "_id" : 7, "item" : "a1", "type" : "apparel", "quantity" : 250 }
{ "_id" : 8, "item" : "a2", "type" : "apparel", "quantity" : 400 }
{ "_id" : 9, "item" : "t2", "type" : "toys", "quantity" : 50 }
{ "_id" : 10, "item" : "f4", "type" : "food", "quantity" : 75 }
>

查询

> db.goods.find({quantity:150}).explain('executionStats')
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.goods",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"quantity" : {
				"$eq" : 150
			}
		},
		"winningPlan" : {
			"stage" : "COLLSCAN",
			"filter" : {
				"quantity" : {
					"$eq" : 150
				}
			},
			"direction" : "forward"
		},
		"rejectedPlans" : [ ]
	},
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 1,
		"executionTimeMillis" : 0,
		"totalKeysExamined" : 0,
		"totalDocsExamined" : 10,
		"executionStages" : {
			"stage" : "COLLSCAN",    // collection scan 集合扫描
			"filter" : {
				"quantity" : {
					"$eq" : 150
				}
			},
			"nReturned" : 1,    // 返回1条
			"executionTimeMillisEstimate" : 0,
			"works" : 12,
			"advanced" : 1,
			"needTime" : 10,
			"needYield" : 0,
			"saveState" : 0,
			"restoreState" : 0,
			"isEOF" : 1,
			"invalidates" : 0,
			"direction" : "forward",
			"docsExamined" : 10    // 查询了10条数据, 才返回1条
		}
	},
	"serverInfo" : {
		"host" : "ql",
		"port" : 27017,
		"version" : "3.2.10",
		"gitVersion" : "79d9b3ab5ce20f51c272b4411202710a082d0317"
	},
	"ok" : 1
}
>

创建索引, 以下两种方法都行

• ensureIndex({KEY: 1})
• createIndex({KEY: 1})

> db.goods.ensureIndex({quantity: 1})
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}
>

再次检索

> db.goods.find({quantity:150}).explain('executionStats')
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.goods",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"quantity" : {
				"$eq" : 150
			}
		},
		"winningPlan" : {
			"stage" : "FETCH",
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"quantity" : 1
				},
				"indexName" : "quantity_1",
				"isMultiKey" : false,
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 1,
				"direction" : "forward",
				"indexBounds" : {
					"quantity" : [
						"[150.0, 150.0]"
					]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 1,    // 返回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,    // 检索1条结果, 就返回了一个结果
			"alreadyHasObj" : 0,
			"inputStage" : {
				"stage" : "IXSCAN",    // Index scan 说明命中索引
				"nReturned" : 1,
				"executionTimeMillisEstimate" : 0,
				"works" : 2,
				"advanced" : 1,
				"needTime" : 0,
				"needYield" : 0,
				"saveState" : 0,
				"restoreState" : 0,
				"isEOF" : 1,
				"invalidates" : 0,
				"keyPattern" : {
					"quantity" : 1
				},
				"indexName" : "quantity_1",
				"isMultiKey" : false,
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 1,
				"direction" : "forward",
				"indexBounds" : {
					"quantity" : [
						"[150.0, 150.0]"
					]
				},
				"keysExamined" : 1,
				"dupsTested" : 0,
				"dupsDropped" : 0,
				"seenInvalidated" : 0
			}
		}
	},
	"serverInfo" : {
		"host" : "ql",
		"port" : 27017,
		"version" : "3.2.10",
		"gitVersion" : "79d9b3ab5ce20f51c272b4411202710a082d0317"
	},
	"ok" : 1
}
>

查看索引

db.集合名称.getIndex()

> db.goods.getIndexes()
[
	{
		"v" : 1,
		"key" : {
			"_id" : 1    // id也是索引
		},
		"name" : "_id_",
		"ns" : "test.goods"
	},
	{
		"v" : 1,
		"key" : {
			"quantity" : 1    // 刚刚新建的索引
		},
		"name" : "quantity_1",
		"ns" : "test.goods"
	}
]
>

优缺点

索引会增加写操作的负担, 但是可以非常快速的查询