- MongoDB CRUD 操作 >
- MongoDB CRUD 概念 >
- 分析查询性能
分析查询性能¶
On this page
The cursor.explain("executionStats") and the db.collection.explain("executionStats") methods provide statistics about the performance of a query. This data output 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.
评估查询的性能¶
Consider a collection inventory with the following documents:
{ "_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" : 10, "item" : "f4", type: "food", quantity: 75 }
Query with No Index¶
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, use the explain("executionStats") method:
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.
- executionStats.nReturned displays 3 to indicate that the query matches and returns three documents.
- executionStats.totalDocsExamined display 10 to indicate that MongoDB had to scan ten documents (i.e. all documents in the collection) to find the three matching documents.
The difference between the number of matching documents and the number of examined documents may suggest that, to improve efficiency, the query might benefit from the use of an index.
Query with Index¶
To support the query on the quantity field, add an index on the quantity field:
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 display 3 to indicate that MongoDB scanned three index entries.
- executionStats.totalDocsExamined display 3 to indicate that MongoDB scanned three documents.
When run with an index, the query scanned 3 index entries and 3 documents to return 3 matching documents. Without the index, to return the 3 matching documents, the query had to scan the whole collection, scanning 10 documents.
比较索引的性能¶
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" : {
...
}
},
...
}
如果你使用不包含 hint() 的 explain() 方法，查询优化器在返回查询统计之前会重新评估这个查询并且再次执行多重索引。
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 }.
参见