MongoDB Explain and Index

Explain and Index

Explain there are three modes, namely: Queryplanner, Executionstats, Allplansexecution. In the reality development, the common executionstats pattern, this example analyzes this mode.

> db.createcollection ("Person")

{"OK": 1}

> for (var i=0;i<2000000;i++) {

... db.person.insert ({"Name": "Ryan" +i, "age": i})

... }

Writeresult ({"ninserted": 1})

> Db.person.find ({"Age": {"$lte": +}}). Explain ("Executionstats")

{

"Queryplanner": {

"Plannerversion": 1,

"Namespace": "Test.person",

"Indexfilterset": false,

"Parsedquery": {

"Age": {

"$lte": 2000

}

},

"Winningplan": {

"Stage": "Collscan",

"Filter": {

"Age": {

"$lte": 2000

}

},

"Direction": "Forward"

},

"Rejectedplans": []

},

"Executionstats": {

"Executionsuccess": true,

"Nreturned": 2001, indicating the number of entries returned by the query

"Executiontimemillis": 741, indicating the execution time of the execution statement

"totalkeysexamined": 0,

"totaldocsexamined": 2000000, indicates the number of entries scanned by the document

"Executionstages": {

"Stage": "Collscan",

"Filter": {

"Age": {

"$lte": 2000

}

},

"Nreturned": 2001,

"Executiontimemillisestimate": 530, indicates the overall query time

"Works": 2000002,

"Advanced": 2001,

"Needtime": 1998000,

"Needyield": 0,

"SaveState": 15625,

"Restorestate": 15625,

"IsEOF": 1,

"Invalidates": 0,

"Direction": "Forward",

"Docsexamined": 2000000

}

},

"ServerInfo": {

"Host": "Meteor.yeecall.com",

"Port": 27027,

"Version": "3.2.8",

"Gitversion": "Ed70e33130c977bda0024c125b56d159573dbaf0"

},

"OK": 1

}

> Db.person.ensureIndex ({age:1}) CREATE INDEX, "1": Indicates ascending by age, "1": Indicates descending by age

{

"Createdcollectionautomatically": false,

"Numindexesbefore": 1,

"Numindexesafter": 2,

"OK": 1

}

> Db.person.find ({"Age": {"$lte": 2001}}). Explain ("Executionstats")

{

"Queryplanner": {

"Plannerversion": 1,

"Namespace": "Test.person", returns the table queried

"Indexfilterset": false, whether there is a indexfilter for the query

"Parsedquery": {

"Age": {

"$lte": 2001

}

},

"Winningplan": { details of the optimal execution plan returned by the query optimizer for this query

"Stage": "FETCH",

"Inputstage": {

"Stage": "IXSCAN",

"Keypattern": {

"Age": 1

},

"IndexName": "Age_1",

"Ismultikey": false,

"IsUnique": false,

"Issparse": false,

"Ispartial": false,

"Indexversion": 1,

"Direction": "Forward",

"Indexbounds": {

"Age": [

"[-inf.0, 2001.0]"

]

}

}

},

"Rejectedplans": []

},

"Executionstats": {

"Executionsuccess": true,

"Nreturned": 2002, indicating the number of entries returned by this query

"Executiontimemillis": to indicate the time of the query as a whole

"Totalkeysexamined": 2002, indicates the number of entries scanned by the index

"Totaldocsexamined": 2002, indicates the number of entries scanned by the document

"Executionstages": {

"Stage": "FETCH",

"Nreturned": 2002,

"Executiontimemillisestimate": the time that this query retrieves the result data based on index to retrieve the document

"Works": 2003,

"Advanced": 2002,

"Needtime": 0,

"Needyield": 0,

"SaveState": 15,

"Restorestate": 15,

"IsEOF": 1,

"Invalidates": 0,

"Docsexamined": 2002,

"Alreadyhasobj": 0,

"Inputstage": {

"Stage": "IXSCAN",

"Nreturned": 2002,

"Executiontimemillisestimate": 0, indicating the time it took for the query to scan 2002 rows of index

"Works": 2003,

"Advanced": 2002,

"Needtime": 0,

"Needyield": 0,

"SaveState": 15,

"Restorestate": 15,

"IsEOF": 1,

"Invalidates": 0,

"Keypattern": {

"Age": 1

},

"IndexName": "Age_1",

"Ismultikey": false,

"IsUnique": false,

"Issparse": false,

"Ispartial": false,

"Indexversion": 1,

"Direction": "Forward",

"Indexbounds": {

"Age": [

"[-inf.0, 2001.0]"

]

},

"Keysexamined": 2002,

"dupstested": 0,

"dupsdropped": 0,

"Seeninvalidated": 0

}

}

},

"ServerInfo": {

"Host": "Meteor.yeecall.com",

"Port": 27027,

"Version": "3.2.8",

"Gitversion": "Ed70e33130c977bda0024c125b56d159573dbaf0"

},

"OK": 1

}

>

The stage type that affects totalkeysexamined and totaldocsexamined. 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

This article is from the "11462293" blog, please be sure to keep this source http://11472293.blog.51cto.com/11462293/1836611

MongoDB Explain and Index