Play Turn MongoDB (v): MongoDB 3.0+ Query Performance analysis

MongoDB Performance Analysis method:explain ()

To demonstrate the effect, let's start by creating a record with 2 million documents. (My own computer took about 15 minutes to insert the completed.) If you want to plug in more documents It's okay, just be patient and wait. ）

1  for (var i=0;i<2000000;i++) {2     db.person.insert ({"Name": "Ryan" +i, "age": i}); 3 }

After MongoDB 3.0, the return and use of explain has changed a lot with the previous version, the excellent feature after 3.0 and the 3.0.7 version We are currently using, this article is only for the explain of MongoDB 3.0+. 3.0+ 's explain has three modes, namely: Queryplanner, Executionstats, Allplansexecution. In the real development, the Executionstats mode is commonly used, and the main analysis of this model.

The index to create the age key for this person collection: Db.person.createIndex ({"1}")

1 {2"Queryplanner" : {3"Plannerversion": 1,4"Namespace": "Personmap.person",5"Indexfilterset":false,6"Parsedquery" : {7"Age" : {8"$lte": 2000.09             }Ten         }, One"Winningplan" : { A"Stage": "FETCH", -"Inputstage" : { -"Stage": "IXSCAN", the"Keypattern" : { -"Age": 1.0 -                 }, -"IndexName": "Age_1", +"Ismultikey":false, -"Direction": "Forward", +"Indexbounds" : { A"Age" : [  at[ -1. #INF, 2000.0] " -                     ] -                 } -             } -         }, -"Rejectedplans" : [] in     }, -"Executionstats" : { to"Executionsuccess":true, +"Nreturned": 2001, -"Executiontimemillis": 143, the"Totalkeysexamined": 2001, *"Totaldocsexamined": 2001, $"Executionstages" : {Panax Notoginseng"Stage": "FETCH", -"Nreturned": 2001, the"Executiontimemillisestimate": 0, +"Works": 2002, A"Advanced": 2001, the"Needtime": 0, +"Needfetch": 0, -"SaveState": 16, $"Restorestate": 16, $"IsEOF": 1, -"Invalidates": 0, -"Docsexamined": 2001, the"Alreadyhasobj": 0, -"Inputstage" : {Wuyi"Stage": "IXSCAN", the"Nreturned": 2001, -"Executiontimemillisestimate": 0, Wu"Works": 2002, -"Advanced": 2001, About"Needtime": 0, $"Needfetch": 0, -"SaveState": 16, -"Restorestate": 16, -"IsEOF": 1, A"Invalidates": 0, +"Keypattern" : { the"Age": 1.0 -                 }, $"IndexName": "Age_1", the"Ismultikey":false, the"Direction": "Forward", the"Indexbounds" : { the"Age" : [  -[ -1. #INF, 2000.0] " in                     ] the                 }, the"Keysexamined": 2001, About"Dupstested": 0, the"Dupsdropped": 0, the"Seeninvalidated": 0, the"Matchtested": 0 +             } -         } the     },Bayi"ServerInfo" : { the"Host": "Qinxiongzhou", the"Port": 27017, -"Version": "3.0.7", -"Gitversion": "6CE7CBE8C6B899552DADD907604559806AA2E9BD" the     }, the"OK": 1.0 the}

db.getcollection (' person '). The find ({"Age": {"$lte": +}}). Explain ("Executionstats")

Analysis of Queryplanner

Return of the Queryplanner:queryplanner

Queryplanner.namespace: This value returns the table queried by the query

Queryplanner.indexfilterset: Is there a indexfilter for this query?

Queryplanner.winningplan: The detailed content of the optimal execution plan returned by the query optimizer for this query.

QueryPlanner.winningPlan.stage: The stage of the optimal execution plan, where the return is fetch, can be understood as retrieving the specific document by returning the index position (there are several patterns in the stage, which will be explained later in this article).

QueryPlanner.winningPlan.inputStage: Used to describe the child stage and provide document and index keywords for its parent stage.

QueryPlanner.winningPlan.stage's child stage, here is Ixscan, indicates that the index scanning is being carried out.

QueryPlanner.winningPlan.keyPattern: The index content scanned, here is did:1,status:1,modify_time:-1 and Scid:1

Index selected by QueryPlanner.winningPlan.indexName:winning plan.

If the QueryPlanner.winningPlan.isMultiKey is Multikey, the return here is false, if the index is built on an array, this will be true.

QueryPlanner.winningPlan.direction: This query order, here is forward, if the use of. Sort ({modify_time:-1}) will display backward.

QueryPlanner.winningPlan.indexBounds:winningplan the index range scanned, if not set to the scope is [Maxkey, Minkey], This is primarily directed to the Chunck of MongoDB to find data and speed up data reading.

Queryplanner.rejectedplans: Detailed return of other execution plans (non-optimal and reject by the query optimizer), where the specific information is the same as in the return of the Winningplan, so do not repeat it here.

Analysis of executionstats return by layer

First floor, Executiontimemillis.

The most intuitive explain return value is the Executiontimemillis value, which refers to the execution time of our statement, which is of course the less desirable the better.

There are 3 Executiontimemillis, respectively:

Executionstats.executiontimemillis

The query time for the overall lookup.

executionstats.executionstages. executiontimemillisestimate

The query retrieves the time that the document obtained 2001 data based on index.

ExecutionStats.executionStages.inputStage.executionTimeMillisEstimate

The query scans the time taken for 2001 rows of index.

second level, index and document scan number and query return entry number

This main discussion consists of 3 return items, nreturned, totalkeysexamined, and totaldocsexamined, which represent the entries returned by the query, index scan entries, and document scan entries, respectively.

These are intuitively affecting the executiontimemillis, and the less we need to scan the faster the more.

For a query, our ideal state is:

nreturned=totalkeysexamined=totaldocsexamined

third layer, stage state analysis

So what's the impact on totalkeysexamined and totaldocsexamined? Is the type of stage. The types are listed below:

Collscan: Full table scan

IXSCAN: Index Scan

FETCH: Retrieves the specified document according to the index

Shard_merge: Return data for each shard for MERGE

Sort: Indicates that sorting is done in memory

Limit: The number of returns that are restricted with limit

Skip: Skip by using Skip

Idhack: Querying for _id

Sharding_filter: Querying the Shard data by MONGOs

Count: Use Db.coll.explain (). Count () to perform the count operation

Countscan:count Stage returns when Count is not used with index

Count_scan:count the stage returns when index is used for count

Subpla: The stage of the $or query that is not used to the index is returned

Text: Stage return when querying with full-text index

PROJECTION: The return of the stage when the return field is qualified

For normal queries, I would like to see a combination of the stage (as far as the query is indexed):

Fetch+idhack

Fetch+ixscan

limit+ (Fetch+ixscan)

Projection+ixscan

Sharding_fiter+ixscan

Count_scan

You do not want to see the stage that contains the following:

Collscan (full table scan), sort (with sort but no index), unreasonable skip,subpla ($or without index), Countscan (no count with index)

Play Turn MongoDB (v): MongoDB 3.0+ Query Performance analysis