Explain and Index
explain有三种模式，分别是：queryPlanner、executionStats、allPlansExecution。现实开发中，常用executionStats模式，本例分析这种模式。
> db.createCollection("person")
|
1
|
{
"ok"
: 1 }
|
> for(var i=0;i<2000000;i++) {
... db.person.insert({"name":"ryan"+i,"age":i})
... }
|
1
|
WriteResult({
"nInserted"
: 1 })
|
> db.person.find({"age":{"$lte":2000}}).explain("executionStats")
|
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
|
{
"queryPlanner"
: {
"plannerVersion"
: 1,
"namespace"
:
"test.person"
,
"indexFilterSet"
:
false
,
"parsedQuery"
: {
"age"
: {
"$lte"
: 2000
}
},
"winningPlan"
: {
"stage"
:
"COLLSCAN"
,
"COLLSCAN"
表示全表扫描
"filter"
: {
"age"
: {
"$lte"
: 2000
}
},
"direction"
:
"forward"
},
"rejectedPlans"
: [ ]
},
"executionStats"
: {
"executionSuccess"
:
true
,
"nReturned"
: 2001, 表示查询返回的条目数
"executionTimeMillis"
: 741, 表示执行语句的执行时间
"totalKeysExamined"
: 0,
"totalDocsExamined"
: 2000000, 表示 文档扫描的条目数
"executionStages"
: {
"stage"
:
"COLLSCAN"
,
"filter"
: {
"age"
: {
"$lte"
: 2000
}
},
"nReturned"
: 2001,
"executionTimeMillisEstimate"
: 530, 表示整体的查询时间
"works"
: 2000002,
"advanced"
: 2001,
"needTime"
: 1998000,
"needYield"
: 0,
"saveState"
: 15625,
"restoreState"
: 15625,
"isEOF"
: 1,
"invalidates"
: 0,
"direction"
:
"forward"
,
"docsExamined"
: 2000000
}
},
"serverInfo"
: {
"host"
:
"meteor.yeecall.com"
,
"port"
: 27027,
"version"
:
"3.2.8"
,
"gitVersion"
:
"ed70e33130c977bda0024c125b56d159573dbaf0"
},
"ok"
: 1
}
|
> db.person.ensureIndex({age:1}) 创建索引，“1”：表示按照age进行升序，“-1”：表示按照age进行降序
创建索引时ensureIndex({field:1},{name:"indexName"}) 可以指定索引的名称
也可以后台创建索引： db.collection.ensureIndex({name:-1},{background:true})
|
1
2
3
4
5
6
|
{
"createdCollectionAutomatically"
:
false
,
"numIndexesBefore"
: 1,
"numIndexesAfter"
: 2,
"ok"
: 1
}
|
> db.person.find({"age":{"$lte":2001}}).explain("executionStats")
|
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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
|
{
"queryPlanner"
: {
"plannerVersion"
: 1,
"namespace"
:
"test.person"
, 返回所查询的表
"indexFilterSet"
:
false
, 针对该query是否有indexfilter
"parsedQuery"
: {
"age"
: {
"$lte"
: 2001
}
},
"winningPlan"
: { 查询优化器针对该query所返回的最优执行计划的详细内容
"stage"
:
"FETCH"
,
"inputStage"
: {
"stage"
:
"IXSCAN"
,
"keyPattern"
: {
"age"
: 1
},
"indexName"
:
"age_1"
,
"isMultiKey"
:
false
,
"isUnique"
:
false
,
"isSparse"
:
false
,
"isPartial"
:
false
,
"indexVersion"
: 1,
"direction"
:
"forward"
,
"indexBounds"
: {
"age"
: [
"[-inf.0, 2001.0]"
]
}
}
},
"rejectedPlans"
: [ ]
},
"executionStats"
: {
"executionSuccess"
:
true
,
"nReturned"
: 2002, 表示本次查询返回的条目数
"executionTimeMillis"
: 28, 表示本次query整体查询的时间
"totalKeysExamined"
: 2002, 表示索引扫描的条目数
"totalDocsExamined"
: 2002, 表示文档扫描的条目数
"executionStages"
: {
"stage"
:
"FETCH"
,
"nReturned"
: 2002,
"executionTimeMillisEstimate"
: 10, 表示本次查询根据index检索document获得结果数据的时间
"works"
: 2003,
"advanced"
: 2002,
"needTime"
: 0,
"needYield"
: 0,
"saveState"
: 15,
"restoreState"
: 15,
"isEOF"
: 1,
"invalidates"
: 0,
"docsExamined"
: 2002,
"alreadyHasObj"
: 0,
"inputStage"
: {
"stage"
:
"IXSCAN"
,
"nReturned"
: 2002,
"executionTimeMillisEstimate"
: 0, 表示该查询扫描2002行index所用的时间
"works"
: 2003,
"advanced"
: 2002,
"needTime"
: 0,
"needYield"
: 0,
"saveState"
: 15,
"restoreState"
: 15,
"isEOF"
: 1,
"invalidates"
: 0,
"keyPattern"
: {
"age"
: 1
},
"indexName"
:
"age_1"
,
"isMultiKey"
:
false
,
"isUnique"
:
false
,
"isSparse"
:
false
,
"isPartial"
:
false
,
"indexVersion"
: 1,
"direction"
:
"forward"
,
"indexBounds"
: {
"age"
: [
"[-inf.0, 2001.0]"
]
},
"keysExamined"
: 2002,
"dupsTested"
: 0,
"dupsDropped"
: 0,
"seenInvalidated"
: 0
}
}
},
"serverInfo"
: {
"host"
:
"meteor.yeecall.com"
,
"port"
: 27027,
"version"
:
"3.2.8"
,
"gitVersion"
:
"ed70e33130c977bda0024c125b56d159573dbaf0"
},
"ok"
: 1
}
|
>
影响 totalKeysExamined 和 totalDocsExamined 的是 stage 类型。
类型列举如下：
COLLSCAN：全表扫描
IXSCAN：索引扫描
FETCH：根据索引去检索指定document
SHARD_MERGE：将各个分片返回数据进行merge
SORT：表明在内存中进行了排序
LIMIT：使用limit限制返回数
SKIP：使用skip进行跳过
IDHACK：针对_id进行查询
SHARDING_FILTER：通过mongos对分片数据进行查询
COUNT：利用db.coll.explain().count()之类进行count运算
COUNTSCAN：count不使用Index进行count时的stage返回
COUNT_SCAN：count使用了Index进行count时的stage返回
SUBPLA：未使用到索引的$or查询的stage返回
TEXT：使用全文索引进行查询时候的stage返回
PROJECTION：限定返回字段时候stage的返回
