Explain execution plan for MongoDB learning

mongodb 3.0 and the previous version of the explain execution plan. Here we only learn to introduce the usage after 3.0

Support to view the execution plan of the following operations

Basic usage

db.collection.find().explain(verbose)

verbose is explain execution plan has the following three

• queryPlanner

This is the default output mode explain

Contains basic detailed information of the execution plan, including: query plan, set information, query conditions, best execution plan, query method and basic information of mongodb

• executionStats

This output method will output some statistical information of the best execution plan based on queryPlanner

• allPlansExecution

Get all execution plans, that is, generate an execution plan content for each index in the collection, and give the user judgment

explain field in detail

Sample

The following is allPlansExecution mode, and also contains the content output by the other two modes

{
  "queryPlanner":{
    "plannerVersion":1,
    "namespace":"mock.users",
    "indexFilterSet":false,
    "parsedQuery":{
      "age":{
        "$gte":10
      }
    },
    "winningPlan":{
      "stage":"FETCH",
      "inputStage":{
        "stage":"IXSCAN",
        "keyPattern":{
          "age":1,
          "username":1
        },
        "indexName":"age_1_username_1",
        "isMultiKey":false,
        "multiKeyPaths":{
          "age":[
          ],
          "username":[
          ]
        },
        "isUnique":false,
        "isSparse":false,
        "isPartial":false,
        "indexVersion":2,
        "direction":"forward",
        "indexBounds":{
          "age":[
            "[10, inf.0]"
          ],
          "username":[
            "[MinKey, MaxKey]"
          ]
        }
      }
    },
    "rejectedPlans":[
      {
        "stage":"FETCH",
        "inputStage":{
          "stage":"IXSCAN",
          "keyPattern":{
            "age":1
          },
          "indexName":"age_1",
          "isMultiKey":false,
          "multiKeyPaths":{
            "age":[
            ]
          },
          "isUnique":false,
          "isSparse":false,
          "isPartial":false,
          "indexVersion":2,
          "direction":"forward",
          "indexBounds":{
            "age":[
              "[10, inf.0]"
            ]
          }
        }
      }
    ]
  },
  "executionStats":{   --这个集合是executionStats&allPlansExecution模式才有的
    "executionSuccess":true,
    "nReturned":680520,
    "executionTimeMillis":1121,
    "totalKeysExamined":680520,
    "totalDocsExamined":680520,
    "executionStages":{
      "stage":"FETCH",
      "nReturned":680520,
      "executionTimeMillisEstimate":143,
      "works":680521,
      "advanced":680520,
      "needTime":0,
      "needYield":0,
      "saveState":680,
      "restoreState":680,
      "isEOF":1,
      "docsExamined":680520,
      "alreadyHasObj":0,
      "inputStage":{
        "stage":"IXSCAN",
        "nReturned":680520,
        "executionTimeMillisEstimate":43,
        "works":680521,
        "advanced":680520,
        "needTime":0,
        "needYield":0,
        "saveState":680,
        "restoreState":680,
        "isEOF":1,
        "keyPattern":{
          "age":1,
          "username":1
        },
        "indexName":"age_1_username_1",
        "isMultiKey":false,
        "multiKeyPaths":{
          "age":[
          ],
          "username":[
          ]
        },
        "isUnique":false,
        "isSparse":false,
        "isPartial":false,
        "indexVersion":2,
        "direction":"forward",
        "indexBounds":{
          "age":[
            "[10, inf.0]"
          ],
          "username":[
            "[MinKey, MaxKey]"
          ]
        },
        "keysExamined":680520,
        "seeks":1,
        "dupsTested":0,
        "dupsDropped":0
      }
    },
    "allPlansExecution":[  --这是allPlansExecution执行才会有的返回集合
      {
        "nReturned":101,
        "executionTimeMillisEstimate":0,
        "totalKeysExamined":101,
        "totalDocsExamined":101,
        "executionStages":{
          .......
        }
      }
    ]
  },
  "serverInfo":{
    "host":"supman",
    "port":27017,
    "version":"4.4.10",
    "gitVersion":"58971da1ef93435a9f62bf4708a81713def6e88c"
  },
  "ok":1
}

Detailed

• queryPlanner

  • plannerVersion execution plan version
  • namespace query collection
  • indexFilterSet whether to use the index filter (search the Internet a lot of that is whether to use the index pit father)
  • parsedQuery query conditions
  • winningPlan Best execution plan

  • stage query method

    state	describe
    COLLSCAN	Full table scan
    IXSCAN	Index scan
    FETCH	Retrieve the specified document according to the index
    SHARD_MERGE	Combine the returned data from each shard
    SORT	Sorted in memory
    LIMIT	Use limit to limit the number of returns
    SKIP	Use skip to skip
    IDHACK	Query _id
    SHARDING_FILTER	Query sharded data through mongos
    COUNTSCAN	The stage returns when count does not use Index for count
    COUNT_SCAN	The stage returns when count uses Index for count
    SUBPLA	The stage return of the $or query that does not use the index
    TEXT	Stage return when using full-text index for query
    PROJECTION	The return of the stage when the return field is limited

  • inputStage used to describe the child stage , and provide documents and index keywords for its parent stage , which contains the more important information in the execution plan

    • statge
    • keyPattern Scanned index content
    • indexName index name
    • isMultiKey an index array, that is, if the index is built on the array, it is true `
    • `MultiKeyPath If the index is built on the array, display the index field

    • isUnique is a unique index, each value of the attribute where this index is located must be unique and unique, and it needs to be specified when creating a unique index

      db.users.createIndex({"username":1},{unique:true"})

- `isSparse` 是否为稀疏索引，这种索引上面的字段可以不存在，一般用于可选字段创建的索引，比如说`email`这种在个人信息中可填可不填的字段，同样这种索引在创建的时候也需要指定

  ```json
  db.users.createIndex({"email":1},{sparse:true"})
  ```

- `isPartial` 是否为部分索引，这种索引只为满足筛选条件的数据创建索引

  ```json
  db.users.createIndex({age:1},{partialFilterExpression:{age:{"$gte":25}}})
  ```

  如上，这种索引只在查询条件为`age ≥ 25`的时候生效

- `direction` 这里代表查询顺序，有两种情况`forward`或者`backward`,现在创建一个`{age:1}`索引

  ```json
  db.users.find().sort()         --这种情况是forward
  db.users.find().sort({age:-1}) --这种情况是backward
  ```

- `indexVersion` 索引版本

- `indexBounds` 所扫描的索引范围,例如`indexBounds: { age: [ '[36, 38]' ] } } }`就是代表扫描`[36,38]`这个区间的`age`字段的索引

- `rejectedPlans` 拒绝计划,非最优的执行计划，它的字段与`winningPlan`一样不再描述

  - .......

• serverInfo MongoDB server information

  • host host name
  • port port
  • version service version
  • gitVersion git version number

• executionStats contains some statistics

  • executionSuccesss is executed
  • nReturned indicates the number of rows returned
  • executionTimeMillis execution time, in milliseconds
  • totalKeysExamined Index scan times
  • totalDocsExamined Document scan times
  • executionStages The details of the successful plan, many of the fields below have been stated above, and I will not write it here.
  • works number of work units, a query will be broken down into many small query units
  • advanced Number of results returned first
  • needTime number of work cycles in which the intermediate result is not advanced to its parent
  • needYield The number of locks generated by the storage layer query
  • isEOF specifies whether the execution phase has reached the end of the flow
  • ......

  • inputStage

    • seeks In order to complete the index scan (stage), the number of times the executor must position the cursor to a new position
    • ......

• allPlansExecution contains all execution plans

  • .......

The end of spreading flowers

Haha, I finally finished it. There are still a few fields that have not been clarified. dupsTested , saveState , but with the above content, it is definitely not a problem to grasp the basic execution plan.