MongoDB (3.0.6) Query performance analysis

In MongoDB, you can use the Db.collection.explain ("executionstats") statement to analyze query performance.

Create the table inventory in MongoDB and insert the test data, the initial data in addition to the ID field is not indexed.

{"_id": 1, "item": "F1", type: "Food", quantity:500}
{"_id": 2, "item": "F2", type: "Food", quantity:100}
{"_id": 3, "item": "P1", type: "Paper", quantity:200}
{"_id": 4, "item": "P2", type: "Paper", quantity:150}
{"_id": 5, "Item": "F3", type: "Food", quantity:300}
{"_id": 6, "item": "T1", type: "Toys", quantity:500}
{"_id": 7, "Item": "A1", type: "Apparel", quantity:250}
{"_id": 8, "Item": "A2", type: "Apparel", quantity:400}
{"_id": 9, "item": "T2", type: "Toys", quantity:50}
{"_id": Ten, "Item": "F4", type: "Food", quantity:75}

We do a conditional query without using an index, and the following search condition is quantity>=100 and quantity<= 200.

Db.inventory.find ({quantity: {$gte: +, $lte: 200}})

The query returns 3 records.

{"_id": 2, "item": "F2", "type": "Food", "Quantity":
{"_id": 3, "item": "P1", "type": "Paper", "Quantity":
{"_id": 4, "item": "P2", "type": "Paper", "Quantity": 150}

Then we look at the query plan.

Db.inventory.find (
   {quantity: {$gte: +, $lte: $}}
). Explain ("Executionstats")

Returns the following results:

{"
   Queryplanner": {
         "plannerversion": 1,
         ...
         " Winningplan ": {
            " stage ":" Collscan ",
            ...
         }
   ,
   " Executionstats ": {
      " executionsuccess ": True ,
      "nreturned": 3,
      "Executiontimemillis": 0,
      "totalkeysexamined": 0,
      "totaldocsexamined": 10,< c14/> "Executionstages": {
         "stage": "Collscan", ...}, ...}
   ,

QueryPlanner.winningPlan.stage: "Collscan" Instructions for full table scan

Executionstats.nreturned:3 description Query matched to 3 records

Executionstats.totaldocsexamined:10 that MongoDB scanned 10 records, we have a total of 10 test data, that is, a full table scan, if the test data has 1000, then this will be scanned 1000 times

Summary: Without an index, querying to 3 matching records requires a full table scan, which can result in poor performance if the data volume is very general.

Use index queries to create indexes first

Db.inventory.createIndex ({quantity:1})

View query Plans

Db.inventory.find (
   {quantity: {$gte: +, $lte: $}}
). Explain ("Executionstats")

Returns the following results

{"
   Queryplanner": {
         "plannerversion": 1,
         ...
         " Winningplan ": {
               " stage ":" FETCH ",
               " Inputstage ": {
                  " stage ":" IXSCAN ",
                  " Keypattern ": {
                     " Quantity " : 1
                  },
                  ...},
         "Rejectedplans": []
   },
   "Executionstats": {
         " Executionsuccess ": True,
         " nreturned ": 3,
         " Executiontimemillis ": 0,
         " totalkeysexamined ": 3,
         " Totaldocsexamined ": 3,
         " Executionstages ": {
            ...},
   ...
}

QueryPlanner.winningPlan.inputStage.stage: ' IXSCAN ' indicates that the index is used

Executionstats.nreturned:3 description Query matched to 3 records

Executionstats.totalkeysexamined:3 Description MongoDB queried 3 indexes

Executionstats.totaldocsexamined:3 that MongoDB scanned 3 records, using the index to directly locate the "document" location, so 3 scans to complete the query.

Summary: After using the index, query to 3 matching records need to scan 3 indexes, can complete the query, performance significantly improved.