Mongodb execution Plan description
Create a documents:
For (iTuno db.users.find). Explain ("queryPlanner")
{
"queryPlanner": {# queryPlanner return
"plannerVersion": 1
"namespace": "app_1.users", # this value returns the table queried by the query
"indexFilterSet": false, # for whether there is an indexfilter for the query
"parsedQuery": {
"I": {
"$eq": 9
}
}
"winningPlan": {# details of the optimal execution plan returned by the query optimizer for this query
"stage": "FETCH", # stage of the optimal execution plan. FETCH is returned here, which can be understood as retrieving a specific document through the returned index location.
"inputStage": {# is used to describe the child stage and provide document and index keywords for its parent stage
"stage": "IXSCAN", the child stage of # queryPlanner.winningPlan.stage, here is IXSCAN, indicating that index scanning is in progress.
"keyPattern": {# scanned index content, here is "I": 1
"I": 1
}
"indexName": "iTune1", the index selected by # winning plan, use db.users.getIndexes () to view the index information
"isMultiKey": whether false, # is Multikey, returned here is false. If the index is built on array, this will be true.
"isUnique": false, whether # is the unique key
"isSparse": false
"isPartial": false
"indexVersion": 1
"direction": "forward", the query order of # query, here is forward
"indexBounds": {
"I": [
"[9.0,9.0]"
]
}
}
}
"rejectedPlans": [] # detailed return of other execution plans (non-optimal but queried optimizer reject). The specific information is the same as in the return of winningPlan.
}
"serverInfo": {# server some information
"host": "my1.ml.com", # host name
"port": 27017, # database port
"version": "3.2.13", # database version
"gitVersion": "23899209cad60aaafe114f6aea6cb83025ff51bc"
}
"ok": 1
}
ExecutionStats Analysis:
Repsetzhou:PRIMARY > db.users.find ({iVav 9}) .explain ("executionStats")
{
"queryPlanner": {
"plannerVersion": 1
"namespace": "app_1.users"
"indexFilterSet": false
"parsedQuery": {
"I": {
"$eq": 9
}
}
"winningPlan": {
"stage": "FETCH"
"inputStage": {
"stage": "IXSCAN"
"keyPattern": {
"I": 1
}
"indexName": "iTunes 1"
"isMultiKey": false
"isUnique": false
"isSparse": false
"isPartial": false
"indexVersion": 1
"direction": "forward"
"indexBounds": {
"I": [
"[9.0,9.0]"
]
}
}
}
"rejectedPlans": []
}
"executionStats": {
"executionSuccess": true
"nReturned": 1, # entries returned by query
"executionTimeMillis": 0, # the overall query time of the query
"totalKeysExamined": 1, # Index scan entries
"totalDocsExamined": 1, # document scan entry
For a query, the ideal result is: nReturned=totalKeysExamined=totalDocsExamined
"executionStages": {
"stage": "FETCH", # this type is more important, and the possible types are listed below:
Type of stage:
COLLSCAN: full table scan
IXSCAN: index scan
FETCH: retrieves the specified document according to the index
SHARD_MERGE: merge the returned data of each shard
SORT: indicates that sorting is done in memory
LIMIT: use limit to limit the number of returns
SKIP: skip with skip
IDHACK: query for _ id
SHARDING_FILTER: query sharded data through mongos
COUNT: use db.coll.explain (). Count () and so on for count operation
Stage return when COUNTSCAN:count does not use Index for count
The stage return when COUNT_SCAN:count uses Index for count
SUBPLA: the stage return of the $or query that did not use the index
TEXT: stage return when querying with full-text index
PROJECTION: qualifies the return of stage when a field is returned
For normal queries, I would like to see a combination of stage (using indexes whenever possible when querying):
Fetch+IDHACK
Fetch+ixscan
Limit+ (Fetch+ixscan)
PROJECTION+ixscan
SHARDING_FITER+ixscan
COUNT_SCAN
The following stage is relatively inefficient:
COLLSCAN (full table scan), SORT (using sort but no index), unreasonable SKIP,SUBPLA (not using index's $or), COUNTSCAN (not using index for count)
"nReturned": 1
"executionTimeMillisEstimate": 0, # the time when the query query retrieves a piece of data from document based on index
"works": 2
"advanced": 1
"needTime": 0
"needYield": 0
"saveState": 0
"restoreState": 0
"isEOF": 1
"invalidates": 0
"docsExamined": 1
"alreadyHasObj": 0
"inputStage": {
"stage": "IXSCAN"
"nReturned": 1
"executionTimeMillisEstimate": 0, # time it takes for this query to scan 1 line of index
"works": 2
"advanced": 1
"needTime": 0
"needYield": 0
"saveState": 0
"restoreState": 0
"isEOF": 1
"invalidates": 0
"keyPattern": {
"I": 1
}
"indexName": "iTunes 1"
"isMultiKey": false
"isUnique": false
"isSparse": false
"isPartial": false
"indexVersion": 1
"direction": "forward"
"indexBounds": {
"I": [
"[9.0,9.0]"
]
}
"keysExamined": 1
"dupsTested": 0
"dupsDropped": 0
"seenInvalidated": 0
}
}
}
"serverInfo": {
"host": "my1.ml.com"
"port": 27017
"version": "3.2.13"
"gitVersion": "23899209cad60aaafe114f6aea6cb83025ff51bc"
}
"ok": 1
}
