How to Using Index into MongoDB

Prepare Simple Data

for (var i = 0; i < 5000; i++) {
  db.user.insert(
    {
      "i":i,
      "username": "user"+i,
      "age": Math.floor(Math.random()*120),
      "created": new Date()
    }
  );
}

TL;DR


一个还没有使用Index的标准查询的Explain结果

> db.user_list.find({"username":"user100"}).explain()
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.user_list",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"username" : {
				"$eq" : "user100"
			}
		},
		"winningPlan" : {
			"stage" : "COLLSCAN",
			"filter" : {
				"username" : {
					"$eq" : "user100"
				}
			},
			"direction" : "forward"
		},
		"rejectedPlans" : [ ]
	},
	"serverInfo" : {
		"host" : "6cbc2254bc5a",
		"port" : 27017,
		"version" : "3.4.3",
		"gitVersion" : "f07437fb5a6cca07c10bafa78365456eb1d6d5e1"
	},
	"ok" : 1
}

“stage” : “COLLSCAN”代表全集合扫描,就是MySQL里的全表扫描

Add Index

db.user_list.ensureIndex({"username":1})
2017-04-28T06:24:05.603+0000 I INDEX    [conn3] build index on: test.user_list properties: { v: 2, key: { username: 1.0 }, name: "username_1", ns: "test.user_list" }
2017-04-28T06:24:05.603+0000 I INDEX    [conn3] 	 building index using bulk method; build may temporarily use up to 500 megabytes of RAM
2017-04-28T06:24:05.635+0000 I INDEX    [conn3] build index done.  scanned 5000 total records. 0 secs
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}

有了Index以后的查询Explain

db.user_list.find({"username":"user100"}).explain()
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.user_list",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"username" : {
				"$eq" : "user100"
			}
		},
		"winningPlan" : {
			"stage" : "FETCH",
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"username" : 1
				},
				"indexName" : "username_1",
				"isMultiKey" : false,
				"multiKeyPaths" : {
					"username" : [ ]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"username" : [
						"[\"user100\", \"user100\"]"
					]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"serverInfo" : {
		"host" : "6cbc2254bc5a",
		"port" : 27017,
		"version" : "3.4.3",
		"gitVersion" : "f07437fb5a6cca07c10bafa78365456eb1d6d5e1"
	},
	"ok" : 1
}

复合索引的创建和应用场景

db.user_list.find().sort("age":1, "username":1)
db.user_list.ensureIndex({"age":1, "username":1})

MongoDB使用索引进行查询,那么查询的结果文档通常是按照索引顺序排列的。比如下面这个查询,带有排序的话,效率还是有点受影响,即使有了索引。因为查询结果会要求以“username”升序排列,所以MongoDB会在内存里面对结果进行排序。

db.user_list.find({
  "age":{"$gte": 18}
}).sort("age":1, "username":1)

如果查询结果集的大小超过32M MongoDB就会报错 但不知道3.4.3以后的版本是不是会有所增加

另一个策略

上面那个索引的顺序是
{“age”:1, “username”:1} “age”放在前面 所以这个索引大致的构造就是
[0,”user001″] -> 0x0c965148
[0,”user002”] -> 0x0c965141

所以age是升序排列的,在相同age的前提下 username也是升序排列,。所以如果说你需要对username进行排序的话,不妨再建立一条索引 顺序调整成{“username”:1,”age”:1}

所以同样的查询,这种Point query,直接找到18那组index 然后逆序排列一下索引就可以了

db.user_list.find({
  "age":{"$gte": 18}
}).sort("username":-1)

强制使用某一个Index

db.user_list.find({
  "age":{"$gte": 18}
}).sort("age":1, "username":1).hint({"username":1, "age":1}).explain()

留意 scanAndOrder是不是false,如果是true的话说明又是全表扫描了一遍。但是根据测试,这种查询的时间还是很长,但是如果显示了查询的数据结果集的数量,第二种明显快很多

db.user.find({"age": {"$gte":21, "$lte":30}}).
sort({"username":1}).
limit(1000).
hint({"age":1,"username":1}).
explain()["millis"] // Just display millis

db.user.find({"age": {"$gte":21, "$lte":30}}).
sort({"username":1}).
limit(1000).
hint({"username":1,"age":1}).
explain()["millis"] // Just display millis

闲着无聊的时候可以对比测试一下时间。

所以使用Index和设计Index的准则就是一个简单的公式
{“sortKey”:1, “queryKey”:1}

什么样的$操作符会高效的使用Index

回头再补充

发表评论

Fill in your details below or click an icon to log in:

WordPress.com 徽标

You are commenting using your WordPress.com account. Log Out /  更改 )

Google photo

You are commenting using your Google account. Log Out /  更改 )

Twitter picture

You are commenting using your Twitter account. Log Out /  更改 )

Facebook photo

You are commenting using your Facebook account. Log Out /  更改 )

Connecting to %s