MongoDB Query Operation analysis

Background

MONGODB provides SQL-like data query and operation methods, but also includes aggregation operations, indexes and other mechanisms;
In previous experience, improper library table operations or indexing patterns often cause problems such as slow query operations, low database throughput, and soaring CPU or disk IO.
Therefore, in the application development process, it is necessary to review the operation of the DB, especially the key business or complex condition query. MongoDB provides a explain method that allows us to
Analyze the DB query statements to analyze potential bottlenecks in advance.

Query plan

MongoDB describes the execution of a query statement through a query plan (Queryplan), and typically a query operation may correspond to multiple sets of query plans.
These query plans generate optimal planning through an electoral mechanism as the ultimate execution plan. In addition MongoDB provides a caching mechanism for query plans such as:

Figure HTTPS://DOCS.MONGODB.COM/MANUAL/_IMAGES/QUERY-PLANNER-DIAGRAM.BAKEDSVG.SV

Diagram of Query Planner logic

The query operation is mapped to a query shape, and the model contains the definition of the condition (predicate), sort, projection (projection);
Finding the existing plan cache with the query model as key, the next step in finding the cache still further evaluates query performance, and if performance evaluation results are not met, MongoDB will retire the cache and go into the query plan generation phase.
Each plan generation phase contains:

• Generation of candidate plans;
• Evaluate the optimization plan;
• Campaign best plan;
• Create a cache;

After the optimal plan is produced, the query executor executes the current plan and produces the final result.

Explain operation

The following statement allows you to expand the analysis of the current query plan

db.T_FooData.find({"appId":"s5WrMmrJV_8RBJG17FSVoY995Kga","nodeType":"SENSOR","creationTime":{    $gte : ISODate("2017-08-08T10:34:33.125Z"),    $lt : ISODate("2017-08-08T12:34:33.125Z")    }}).explain("executionStats")

Output results

{"Queryplanner": {"plannerversion": 1, "namespace": "DB".                    T_foodata "," Indexfilterset ": false," Parsedquery ": {" $and ": [{                "AppId": {"$eq": "S5wrmmrjv_8rbjg17fsvoy995kga"}},                {"NodeType": {"$eq": "SENSOR"} }, {"CreationTime": {"$lt": Isodate ("2017-08-08t12:34:33.12                        5Z ")}}, {" CreationTime ": { "$gte": Isodate ("2017-08-08t10:34:33.125z")}}]}, "WINNINGP Lan ": {...}," Rejectedplans ": [...],}," Executionstats ": {" executionsuccess ": true," Nreturned ": 62848," Executiontimemillis ": 3058," totAlkeysexamined ": 1510833," totaldocsexamined ": 1510833," executionstages ": {...} }, "ServerInfo": {"host": "Nb3000w_mongodb_01", "Port": 50001, "version": "3.4.7", "GI Tversion ":" 4249c1d2b5999ebbf1fdf3bc0e0e3b3ff5c0aaf2 "}," OK ": 1," $gleStats ": {" Lastoptime ": Timesta MP (1504498101, 1), "Electionid": ObjectId ("7fffffff0000000000000001")}}

Result description

• Queryplanner describes the current query plan;
• Queryplanner.namespace describes the current collection namespace, {db}. {CollectionName}
• Queryplanner.indexfilterset Set Indexfilter,filter determines how the query optimizer will use the index for a query
• Queryplanner.parsedquery Query Information after parsing
• Queryplanner.winningplan Best Plan

• Queryplanner.rejectplans List of rejected plans

• Executionstats execution of process statistics, capturing relevant information during the execution of the plan
• Executionstats.executionsuccess whether the execution succeeds
• Executionstats.nreturned returns the number of entries
• Executionstats.executiontimemilis Execution Time (MS)
• Executionstats.totalkeysexamined Index Detection entry
• Executionstats.totaldocsexamined Document Detection Entry

• Executionstats.executionstages Execution Stage Details

Explain mode

MongoDB provides several modes for explain operations:

• Queryplanner The default mode, only the query plan analysis, unable to output the execution process statistics;
• Executionstats execution mode, after the query plan analysis, will execute the Winningplan and statistical process information;
• Allplansexecution full plan execution mode, all plans (including Rejectplans) will be executed and process statistics returned;
  Executionstats.allplansexecution contains the execution process statistics for all plans (except Winningplan)

Detailed implementation Plan

The execution plan decomposes the entire process into stages, and the stage is organized in a tree-like structure, which is matched to the execution process.

The stage is divided into several types, as follows:

Stage	Description
Collscan	Full table Scan
IXSCAN	Index Scan
FETCH	Retrieves the specified document according to the index
PROJECTION	Qualifying return fields
Shard_merge	Return data for each shard for merge
SORT	Indicates that sorting is done in memory
LIMIT	To limit the number of returns with limit
SKIP	Skip by using Skip
Idhack	Querying against the _id
Sharding_filter	Querying the Shard data by MONGOs
COUNT	Use Db.coll.explain (). Count () to perform the count operation
Countscan	Count is not used with index
Count_scan	Count uses index for count
Subpla	$or queries that are not used to the index
TEXT	Querying using a full-text index

Winningplan Sample Example

"Winningplan": {"stage": "FETCH", "filter": {"$and": [{                    "NodeType": {"$eq": "GATEWAY"} }, {"CreationTime": {"$lt": Isodate ("2017-08-08  t12:34:33.125z ")}}, {" CreationTime "                 : {"$gte": Isodate ("2017-08-08t10:34:33.125z")}}  ]}, "Inputstage": {"stage": "IXSCAN", "Keypattern" : {"appId": 1}, "IndexName": "AppId", "Ismultikey":                False, "IsUnique": false, "Issparse": false, "ispartial": false, "Indexversion": 1, "direction": "Forward", "Indexbounds": {"appId": [            "[\" S5wrmmrjv_8rbjg17fsvoy995kga\ ", \" S5wrmmrjv_8rbjg17fsvoy995kga\ "]"} }        },

Field description

Properties	Description
Winningplan.stage	Best plan Stage,fetch means retrieving documents based on index
Winningplan.filter	Best-plan filters, which are query criteria
Winningplan.inputstage	Child stage of the optimal planning stage
WinningPlan.inputStage.stage	Child stage, here is Ixscan, indicating the index scanning
WinningPlan.inputStage.keyPattern	Indexed mode for scanning
WinningPlan.inputStage.indexName	Select Index Name
WinningPlan.inputStage.isMultiKey	Whether it is Multikey, true if the index is set on an array
WinningPlan.inputStage.isSparse	Whether sparse indexes
WinningPlan.inputStage.isPartial	Whether the partition index
WinningPlan.inputStage.direction	The query order, here is forward, if the. Sort ({w:-1}) will be displayed backward
WinningPlan.inputStage.indexBounds	The range of indexes scanned

Detailed process Statistics

Executionstats Sample Example

"Executionstats": {"executionsuccess": True, "nreturned": 62848, "Executiontimemillis": 3058, "totalkeysexamined": 1510833, "totaldocsexamined": 1510833, "executionstages": {"Stage"                            : "FETCH", "filter": {"$and": [{"NodeType": {                        "$eq": "GATEWAY"}}, {                        "CreationTime": {"$lt": Isodate ("2017-08-08t12:34:33.125z") }}, {"CreationTime": {"$gte            ": Isodate (" 2017-08-08t10:34:33.125z ")}}]}, "nreturned": 62848, "executiontimemillisestimate": 2765, "Works": 1510834, "Adva          nced ": 62848,  "Needtime": 1447985, "Needyield": 0, "saveState": 11807, "restorestate": 11807,            "IsEOF": 1, "invalidates": 0, "docsexamined": 1510833, "Alreadyhasobj": 0, "Inputstage": {"stage": "IXSCAN", "nreturned": 1510833, "Executi Ontimemillisestimate ": 792," Works ": 1510834," advanced ": 1510833," needti                Me ": 0," Needyield ": 0," saveState ": 11807," restorestate ": 11807,                "IsEOF": 1, "invalidates": 0, "Keypattern": {"appId": 1 }, "IndexName": "AppId", "Ismultikey": false, "IsUnique": Fals                E, "Issparse": false, "ispartial": false, "indexversion": 1, "Direction": "Forward"," Indexbounds ": {" appId ": [" [\ "] S5wrmmrjv_8rbjg17fsvoy995kga                \ ", \" S5wrmmrjv_8rbjg17fsvoy995kga\ "]"]}, "keysexamined": 1510833, "dupstested": 0, "dupsdropped": 0, "seeninvalidated": 0}} }

Field description

Properties	Description
Executionstats.executionsuccess	Whether the execution succeeds
executionstats.nreturned	Returns the number of items
Executionstats.executiontimemilis	Execution Time (MS)
executionstats.totalkeysexamined	Index detection Entries
executionstats.totaldocsexamined	Document Detection Entry
Executionstats.executionstages	Execution stage details, most fields inherit from Winningplan.inputstage
ExecutionStats.executionStages.stage	Execution phase, fetch means getting documents based on index
executionStats.executionStages.nReturned	Number of stages returned items
ExecutionStats.executionStages.executionTimeMillisEstimate	Stage Execution Time
executionStats.executionStages.docsExamined	Document detection entries in stages
ExecutionStats.executionStages.works	Number of scan tasks in phase
executionStats.executionStages.advanced	Number of submissions up in the stage
ExecutionStats.executionStages.needTime	Number of times required to position the index position in the stage
ExecutionStats.executionStages.needYield	Get lock wait time in phase
ExecutionStats.executionStages.isEOF	Whether the end bit of the stream is reached in the stage, the query for the limit limit may be 0
ExecutionStats.executionStages.inputStage	The sub-stage of the execution phase, here is a ixscan sub-process

Reference documents

Explain official description

Http://www.mongoing.com/eshu_explain1
https://docs.mongodb.com/manual/reference/explain-results/#explain-output

Several modes of explain
https://docs.mongodb.com/manual/reference/method/cursor.explain/

Understand the query behavior of MONGO
https://www.compose.com/articles/explain-explain-understanding-mongo-query-behavior/

Query cache for MONGO
https://docs.mongodb.com/manual/core/query-plans/#index-filters

MongoDB Query Operation analysis