Shulou(Shulou.com)06/01 Report--

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

}

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.