# mongoDb 执行计划

返回：mongoDB

// 语法：cursor.explain(verbosity)
db.msg_device_data
  .find({})
  .sort({ _id: 1 })
  .explain();

1
2
3
4
5

{
  "plannerVersion": "1",
  "namespace": "faithmsg.msg_device_data",
  "indexFilterSet": false,
  "parsedQuery": {},
  "queryHash": "F44244FE",
  "planCacheKey": "F44244FE",
  "winningPlan": {
    "stage": "FETCH",
    "inputStage": {
      "stage": "IXSCAN",
      "keyPattern": { "_id": "1" },
      "indexName": "_id_",
      "isMultiKey": false,
      "multiKeyPaths": { "_id": [] },
      "isUnique": true,
      "isSparse": false,
      "isPartial": false,
      "indexVersion": "2",
      "direction": "forward",
      "indexBounds": { "_id": ["[MinKey, MaxKey]"] }
    }
  },
  "rejectedPlans": []
}

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

{
  "plannerVersion": "1",
  "namespace": "faithmsg.msg_device_data",
  "indexFilterSet": false,
  "parsedQuery": {},
  "queryHash": "8B3D4AB8",
  "planCacheKey": "8B3D4AB8",
  "winningPlan": {
    "stage": "LIMIT",
    "limitAmount": "12",
    "inputStage": { "stage": "COLLSCAN", "direction": "forward" }
  },
  "rejectedPlans": []
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14

{
  "executionSuccess": true,
  "nReturned": "12",
  "executionTimeMillis": "20",
  "totalKeysExamined": "0",
  "totalDocsExamined": "12",
  "executionStages": {
    "stage": "LIMIT",
    "nReturned": "12",
    "executionTimeMillisEstimate": "20",
    "works": "14",
    "advanced": "12",
    "needTime": "1",
    "needYield": "0",
    "saveState": "1",
    "restoreState": "1",
    "isEOF": "1",
    "limitAmount": "12",
    "inputStage": {
      "stage": "COLLSCAN",
      "nReturned": "12",
      "executionTimeMillisEstimate": "20",
      "works": "13",
      "advanced": "12",
      "needTime": "1",
      "needYield": "0",
      "saveState": "1",
      "restoreState": "1",
      "isEOF": "0",
      "direction": "forward",
      "docsExamined": "12"
    }
  }
}

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

verbose：{String}，可选参数。
- 指定冗长模式的解释输出，方式指定后会影响 explain()的行为及输出信息。
- 可选值有："queryPlanner"、"executionStats"、"allPlansExecution"，默认为"queryPlanner"

返回信息

queryPlanner（查询计划）：查询优化选择的计划细节和被拒绝的计划。其可能包括以下值：
- queryPlanner.namespace－一个字符串，运行查询的指定命名空间
- queryPlanner.indexFilterSet－一个布尔什，表示 MongoDB 在查询中是否使用索引过滤
- queryPlanner.winningPlan－由查询优化选择的计划文档
- queryPlanner.rejectedPlans－被查询优化备选并被拒绝的计划数组
- queryPlanner.allPlansExecution－包含在计划选择阶段期间捕获的部分执行信息，包括选择计划和拒绝计划
- queryPlanner.nReturned－匹配查询条件的文档数
- queryPlanner.executionTimeMillis－计划选择和查询执行所需的总时间（毫秒数）
- queryPlanner.totalKeysExamined－扫描的索引总数
- queryPlanner.totalDocsExamined－扫描的文档总数
- queryPlanner.totalDocsExamined－扫描的文档总数
- queryPlanner.executionStages－显示执行成功细节的查询阶段树
- winningPlan.stage－表示查询阶段的字符串
- winningPlan.inputStage－表示子过程的文档
- winningPlan.inputStages－表示子过程的文档数组
- winningPlan.shards－包括每个访问片的 queryPlanner 和 serverInfo 的文档数组
executionStats,（执行状态）：被选中执行计划和被拒绝执行计划的详细说明： executionStages.works－指定查询执行阶段执行的“工作单元”的数量 executionStages.advanced－返回的中间结果数 executionStages.needTime－未将中间结果推进到其父级的工作周期数 executionStages.needYield－存储层要求查询系统产生的锁的次数 executionStages.isEOF－指定执行阶段是否已到达流结束
serverInfo,（服务器信息）：MongoDB 实例的相关信息：
- serverInfo.winningPlan－使用的执行计划

# stage 含义

COLLSCAN 集合扫描
IXSCAN 索引扫描
FETCH 检出文档
SHARD_MERGE 合并分片中结果
SHARDING_FILTER 分片中过滤掉孤立文档
LIMIT 使用 limit 限制返回数
PROJECTION 使用 skip 进行跳过
IDHACK 针对\_id 进行查询
COUNT 利用 db.coll.explain().count()之类进行 count 运算
COUNTSCAN count 不使用 Index 进行 count 时的 stage 返回
COUNT_SCAN count 使用了 Index 进行 count 时的 stage 返回
SUBPLA 未使用到索引的$or 查询的 stage 返回
TEXT 使用全文索引进行查询时候的 stage 返回
PROJECTION 限定返回字段时候 stage 的返回

# 我们怎么分析这个索引的命中与有效情况呢

db.getCollection('msg_device_data').createIndex({'gatherTime':-1,'createTime':-1});

# executionStats 执行统计

{
  "executionSuccess": true,
  "nReturned": "12",
  "executionTimeMillis": "2845",
  "totalKeysExamined": "2344466",
  "totalDocsExamined": "12",
  "executionStages": {
    "stage": "LIMIT",
    "nReturned": "12",
    "executionTimeMillisEstimate": "202",
    "works": "2344467",
    "advanced": "12",
    "needTime": "2344454",
    "needYield": "0",
    "saveState": "2344",
    "restoreState": "2344",
    "isEOF": "1",
    "limitAmount": "12",
    "inputStage": {
      "stage": "FETCH",
      "nReturned": "12",
      "executionTimeMillisEstimate": "193",
      "works": "2344466",
      "advanced": "12",
      "needTime": "2344454",
      "needYield": "0",
      "saveState": "2344",
      "restoreState": "2344",
      "isEOF": "0",
      "docsExamined": "12",
      "alreadyHasObj": "0",
      "inputStage": {
        "stage": "SKIP",
        "nReturned": "12",
        "executionTimeMillisEstimate": "188",
        "works": "2344466",
        "advanced": "12",
        "needTime": "2344454",
        "needYield": "0",
        "saveState": "2344",
        "restoreState": "2344",
        "isEOF": "0",
        "skipAmount": "0",
        "inputStage": {
          "stage": "IXSCAN",
          "nReturned": "2344466",
          "executionTimeMillisEstimate": "184",
          "works": "2344466",
          "advanced": "2344466",
          "needTime": "0",
          "needYield": "0",
          "saveState": "2344",
          "restoreState": "2344",
          "isEOF": "0",
          "keyPattern": {
            "gatherTime": "-1",
            "createTime": "-1"
          },
          "indexName": "idx_gather_create",
          "isMultiKey": false,
          "multiKeyPaths": {
            "gatherTime": [],
            "createTime": []
          },
          "isUnique": false,
          "isSparse": false,
          "isPartial": false,
          "indexVersion": "2",
          "direction": "backward",
          "indexBounds": {
            "gatherTime": ["[MinKey, MaxKey]"],
            "createTime": ["[MinKey, MaxKey]"]
          },
          "keysExamined": "2344466",
          "seeks": "1",
          "dupsTested": "0",
          "dupsDropped": "0"
        }
      }
    }
  }
}

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

nReturned 实际返回数据行数
executionTimeMillis 命令执行总时间,单位毫秒
totalKeysExamined 表示MongoDB 扫描了N个索引数据。
totalDocsExamined 文档扫描数