分析查询性能

The cursor.explain("executionStats") and the db.collection.explain("executionStats") methods provide statistics about the performance of a query. These statistics can be useful in measuring if and how a query uses an index.

db.collection.explain() provides information on the execution of other operations, such as db.collection.update(). See db.collection.explain() for details.

The documents appear in MongoDB Compass as the following:

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" : {
         "plannerVersion" : 1,
         ...
         "winningPlan" : {
            "stage" : "COLLSCAN",
            ...
         }
   },
   "executionStats" : {
      "executionSuccess" : true,
      "nReturned" : 3,
      "executionTimeMillis" : 0,
      "totalKeysExamined" : 0,
      "totalDocsExamined" : 10,
      "executionStages" : {
         "stage" : "COLLSCAN",
         ...
      },
      ...
   },
   ...
}

• queryPlanner.winningPlan.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.

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

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

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

The explain() method 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 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.

Compare Performance of Indexes

To manually compare the performance of a query using more than one index, you can use the hint() method in conjunction with the explain() method.

Consider the following query:

db.inventory.find( {
   quantity: {
      $gte: 100, $lte: 300
   },
   type: "food"
} )

The query returns the following documents:

{ "_id" : 2, "item" : "f2", "type" : "food", "quantity" : 100 }
{ "_id" : 5, "item" : "f3", "type" : "food", "quantity" : 300 }

To support the query, add a compound index. With compound indexes, the order of the fields matter.

For example, add the following two compound indexes. The first index orders by quantity field first, and then the type field. The second index orders by type first, and then the quantity field.

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

Evaluate the effect of the first index on the query:

db.inventory.find(
   { quantity: { $gte: 100, $lte: 300 }, type: "food" }
).hint({ quantity: 1, type: 1 }).explain("executionStats")

The explain() method returns the following output:

{
   "queryPlanner" : {
      ...
      "winningPlan" : {
         "stage" : "FETCH",
         "inputStage" : {
            "stage" : "IXSCAN",
            "keyPattern" : {
               "quantity" : 1,
               "type" : 1
            },
            ...
            }
         }
      },
      "rejectedPlans" : [ ]
   },
   "executionStats" : {
      "executionSuccess" : true,
      "nReturned" : 2,
      "executionTimeMillis" : 0,
      "totalKeysExamined" : 5,
      "totalDocsExamined" : 2,
      "executionStages" : {
      ...
      }
   },
   ...
}

MongoDB scanned 5 index keys (executionStats.totalKeysExamined) to return 2 matching documents (executionStats.nReturned).

Evaluate the effect of the second index on the query:

db.inventory.find(
   { quantity: { $gte: 100, $lte: 300 }, type: "food" }
).hint({ type: 1, quantity: 1 }).explain("executionStats")

The explain() method returns the following output:

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

MongoDB scanned 2 index keys (executionStats.totalKeysExamined) to return 2 matching documents (executionStats.nReturned).

For this example query, the compound index { type: 1, quantity: 1 } is more efficient than the compound index { quantity: 1, type: 1 } .