Analyze Query Performance分析查询性能

The following query retrieves documents where the quantity field has a value between 100 and 200, inclusive:

db.inventory.find( { quantity: { $gte: 100, $lte: 200 } } )

The query returns the following documents:

{ "_id" : 2, "item" : "f2", "type" : "food", "quantity" : 100 }
{ "_id" : 3, "item" : "p1", "type" : "paper", "quantity" : 200 }
{ "_id" : 4, "item" : "p2", "type" : "paper", "quantity" : 150 }

To view the query plan selected, chain the cursor.explain("executionStats") cursor method to the end of the find command:

db.inventory.find(
   { quantity: { $gte: 100, $lte: 200 } }
).explain("executionStats")

explain() returns the following results:

{
   queryPlanner: {
         ...
         winningPlan: {
            queryPlan: {
               stage: 'COLLSCAN',
               ...
            }
         }
   },
   executionStats: {
      executionSuccess: true,
      nReturned: 3,
      executionTimeMillis: 0,
      totalKeysExamined: 0,
      totalDocsExamined: 10,
      executionStages: {
         stage: 'COLLSCAN',
         ...
      },
      ...
   },
   ...
}

• queryPlanner.winningPlan.queryPlan.stage displays COLLSCAN to indicate a collection scan.

  Collection scans indicate that the mongod had to scan the entire collection document by document to identify the results. This is a generally expensive operation and can result in slow queries.

• executionStats.nReturned displays 3 to indicate that the query matches and returns three documents.

• executionStats.totalKeysExamined displays 0 to indicate that this is query is not using an index.

• executionStats.totalDocsExamined displays 10 to indicate that MongoDB had to scan ten documents (i.e. all documents in the collection) to find the three matching documents.

The following query retrieves documents where the quantity field has a value between 100 and 200, inclusive:

Copy the following filter into the Compass query bar and click Find:

{ quantity: { $gte: 100, $lte: 200 } }

The query returns the following documents:

To view the query plan selected:

1. Click the Explain Plan tab for the test.inventory collection.

2. Click Explain.

MongoDB Compass displays the query plan as follows:

Visual Tree

• The Query Performance Summary shows the execution stats of the query:

  • Documents Returned displays 3 to indicate that the query matches and returns three documents.

  • Index Keys Examined displays 0 to indicate that this query is not using an index.

  • Documents Examined displays 10 to indicate that MongoDB had to scan ten documents (i.e. all documents in the collection) to find the three matching documents.

• Below the Query Performance Summary, MongoDB Compass displays the COLLSCAN query stage to indicate that a collection scan was used for this query.

  Collection scans indicate that the mongod had to scan the entire collection document by document to identify the results. This is a generally expensive operation and can result in slow queries.

Raw JSON

The explain details can also be viewed in raw JSON format by clicking Raw JSON below the query bar:

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

To view the query plan statistics, use the explain() method:

db.inventory.find(
   { quantity: { $gte: 100, $lte: 200 } }
).explain("executionStats")

The explain() method returns the following results:

{
   queryPlanner: {
         ...
         winningPlan: {
            queryPlan: {
               stage: 'FETCH',
               inputStage: {
                  stage: 'IXSCAN',
                  keyPattern: {
                     quantity: 1
                  },
                  ...
               }
            }
         },
         rejectedPlans: [ ]
   },
   executionStats: {
         executionSuccess: true,
         nReturned: 3,
         executionTimeMillis: 0,
         totalKeysExamined: 3,
         totalDocsExamined: 3,
         executionStages: {
            ...
         },
         ...
   },
   ...
}

• queryPlanner.winningPlan.queryPlan.inputStage.stage displays IXSCAN to indicate index use.

• executionStats.nReturned displays 3 to indicate that the query matches and returns three documents.

• executionStats.totalKeysExamined displays 3 to indicate that MongoDB scanned three index entries. The number of keys examined match the number of documents returned, meaning that the mongod only had to examine index keys to return the results. The mongod did not have to scan all of the documents, and only the three matching documents had to be pulled into memory. This results in a very efficient query.

• executionStats.totalDocsExamined display 3 to indicate that MongoDB scanned three documents.

1. Click the Indexes tab for the test.inventory collection.

2. Click Create Index.

3. Select quantity from the Select a field name dropdown.

4. Select 1 (asc) from the type dropdown.

5. Click Create.

You can now see your newly created index in the Indexes tab:

Return to the Explain Plan tab for the inventory collection and re-run the query from the previous step:

{ quantity: { $gte: 100, $lte: 200 } }

MongoDB Compass displays the query plan as follows:

Visual Tree

• The Query Performance Summary shows the execution stats of the query:

  • Documents Returned displays 3 to indicate that the query matches and returns three documents.

  • Index Keys Examined displays 3 to indicate that MongoDB scanned three index entries. The number of keys examined match the number of documents returned, meaning that the mongod only had to examine index keys to return the results. The mongod did not have to scan all of the documents, and only the three matching documents had to be pulled into memory. This results in a very efficient query.

  • Documents Examined displays 3 to indicate that MongoDB scanned three documents.

  • On the right-hand side of the Query Performance Summary, MongoDB Compass shows that the query used the quantity index.

• Below the Query Performance Summary, MongoDB Compass displays the query stages FETCH and IXSCAN. IXSCAN indicates that the mongod used an index to satisfy the query before exeuting the FETCH stage and retrieving the documents.

Raw JSON

The explain details can also be viewed in raw JSON format by clicking Raw JSON below the query bar: