mongoDB中的正则和索引_mongodb 正则表达式 索引

发现regex也会走索引的小实验。

> db.namestest.insertMany([{"name":"abc jin"},{"name":"jinabc111"},{"name":"dbcabcll"},{"name":"abc jji"},{"name":"jinjinabc abc"},{"name":"abc ciuab"},{"name":"abciu \n aaa"},{"name":"abcabcabc ciuciu"},{"name":"ciujcb ABC"},{"name":"ABCABC"}]);
{
	"acknowledged" : true,
	"insertedIds" : [
		ObjectId("6235e9e4d44bf71ca2a42706"),
		ObjectId("6235e9e4d44bf71ca2a42707"),
		ObjectId("6235e9e4d44bf71ca2a42708"),
		ObjectId("6235e9e4d44bf71ca2a42709"),
		ObjectId("6235e9e4d44bf71ca2a4270a"),
		ObjectId("6235e9e4d44bf71ca2a4270b"),
		ObjectId("6235e9e4d44bf71ca2a4270c"),
		ObjectId("6235e9e4d44bf71ca2a4270d"),
		ObjectId("6235e9e4d44bf71ca2a4270e"),
		ObjectId("6235e9e4d44bf71ca2a4270f")
	]
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

包含abc
> db.namestest.find({"name":{"$regex":"abc"}});
{ "_id" : ObjectId("6235e9e4d44bf71ca2a42706"), "name" : "abc jin" }
{ "_id" : ObjectId("6235e9e4d44bf71ca2a42707"), "name" : "jinabc111" }
{ "_id" : ObjectId("6235e9e4d44bf71ca2a42708"), "name" : "dbcabcll" }
{ "_id" : ObjectId("6235e9e4d44bf71ca2a42709"), "name" : "abc jji" }
{ "_id" : ObjectId("6235e9e4d44bf71ca2a4270a"), "name" : "jinjinabc abc" }
{ "_id" : ObjectId("6235e9e4d44bf71ca2a4270b"), "name" : "abc ciuab" }
{ "_id" : ObjectId("6235e9e4d44bf71ca2a4270c"), "name" : "abciu \n aaa" }
{ "_id" : ObjectId("6235e9e4d44bf71ca2a4270d"), "name" : "abcabcabc ciuciu" }
1
2
3
4
5
6
7
8
9
10

不包含abc
> db.namestest.find({"name":{"$not":/abc/}});
{ "_id" : ObjectId("6235e9e4d44bf71ca2a4270e"), "name" : "ciujcb ABC" }
{ "_id" : ObjectId("6235e9e4d44bf71ca2a4270f"), "name" : "ABCABC" }
1
2
3
4

比较一下有和没有索引的情况：

没有索引的情况：
> db.namestest.find({"name":{"$regex":"abc"}}).explain();
...
		"winningPlan" : {
			"stage" : "COLLSCAN",
			"filter" : {
				"name" : {
					"$regex" : "abc"
				}
			},
			"direction" : "forward"
		},
...
1
2
3
4
5
6
7
8
9
10
11
12
13

> db.nametest.createIndex({"name":1});
{
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"createdCollectionAutomatically" : true,
	"ok" : 1
1
2
3
4
5
6

db.nametest.find({"name":{"$regex":/abc/}}).explain("executionStats");
...
		"winningPlan" : {
			"stage" : "FETCH",
			"inputStage" : {
				"stage" : "IXSCAN",
				"filter" : {
					"name" : {
						"$regex" : "abc"
					}
				},
				"keyPattern" : {
					"name" : 1
				},
				"indexName" : "name_1",
				"isMultiKey" : false,
				"multiKeyPaths" : {
					"name" : [ ]
				},
				"isUnique" : true,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"name" : [
						"[\"\", {})",
						"[/abc/, /abc/]"
					]
				}
			}
		}

db.nametest.find({"name":{"$regex":/^abc/}}).explain("executionStats");
...
		"winningPlan" : {
			"stage" : "FETCH",
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"name" : 1
				},
				"indexName" : "name_1",
				"isMultiKey" : false,
				"multiKeyPaths" : {
					"name" : [ ]
				},
				"isUnique" : true,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"name" : [
						"[\"abc\", \"abd\")",
						"[/^abc/, /^abc/]"
					]
				}
			}
		},
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

很奇怪啊，为什么/abc/也走了索引，认知范围内%abc%这样的匹配应该走collscan。经过查询得知：

Actually according to the documentation,

If an index exists for the field, then MongoDB matches the regular expression against the values in the index, which can be faster than a collection scan. Further optimization can occur if the regular expression is a “prefix expression”, which means that all potential matches start with the same string. This allows MongoDB to construct a “range” from that prefix and only match against those values from the index that fall within that range.

http://docs.mongodb.org/manual/reference/operator/query/regex/#index-use

In other words:

For /Jon Skeet/ regex ,mongo will full scan the keys in the index then will fetch the matched documents, which can be faster than collection scan.

For /^Jon Skeet/ regex ,mongo will scan only the range that start with the regex in the index, which will be faster.

和/^abc/不同的是indexBounds，/abc/相当于是没有扫描范围，全部扫描，但是也是通过索引进行的全部扫描，正常走索引应该是通过二分查找来缩小查询速度，这里走索引只是为了快速获得name的所有值，即遍历整个索引。

Reference:

https://stackoverflow.com/questions/17501798/mongodb-performance-of-query-by-regular-expression-on-indexed-fields

https://docs.mongodb.com/manual/reference/operator/query/regex/#index-use