在本页面
该 和方法提供了有关查询的性能统计信息。这些统计信息可用于衡量查询是否以及如何使用索引。
提供关于其他操作(如)执行的信息。详细信息请参见
评估查询的性能
考虑一个包含以下文件的收集清单:
{ "_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 }
没有索引的查询
以下查询检索的文档中，quantity字段的值在100到200之间，包括:
db.inventory.find( { quantity: { $gte: 100, $lte: 200 } } )
查询返回以下文档:
{ "_id" : 2, "item" : "f2", "type" : "food", "quantity" : 100 }
{ "_id" : 3, "item" : "p1", "type" : "paper", "quantity" : 200 }
{ "_id" : 4, "item" : "p2", "type" : "paper", "quantity" : 150 }
db.inventory.find(
{ quantity: { $gte: 100, $lte: 200 } }
).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
...
"winningPlan" : {
"stage" : "COLLSCAN",
...
}
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 3,
"executionTimeMillis" : 0,
"totalKeysExamined" : 0,
"totalDocsExamined" : 10,
"executionStages" : {
"stage" : "COLLSCAN",
...
},
...
},
...
}
匹配文档的数量和检查文档的数量之间的差异可能表明，为了提高效率，查询可能会受益于索引的使用。
查询与索引
为了支持对quantity字段的查询，请在quantity字段上添加索引:
db.inventory.createIndex( { quantity: 1 } )
要查看查询计划统计信息，请使用**explain(“executionStats”)**方法:
db.inventory.find(
{ quantity: { $gte: 100, $lte: 200 } }
).explain("executionStats")
{
"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" : {
...
},
...
},
...
}
如果没有索引，查询将扫描包含10个文档的整个集合，以返回3个匹配的文档。查询还必须扫描每个文档的全部内容，可能会将它们拉到内存中。这将导致昂贵的查询操作，并且可能会很慢。
当使用索引运行时，查询扫描了3个索引项和3个文档，以返回3个匹配的文档，从而产生一个非常高效的查询。
比较索引的性能
考虑以下查询:
db.inventory.find( {
quantity: {
$gte: 100, $lte: 300
},
type: "food"
} )
查询返回以下文档:
{ "_id" : 2, "item" : "f2", "type" : "food", "quantity" : 100 }
{ "_id" : 5, "item" : "f3", "type" : "food", "quantity" : 300 }
例如，添加以下两个复合索引。第一个索引首先按数量字段排序，然后按类型字段排序。第二个索引首先按类型排序，然后是quantity字段。
db.inventory.createIndex( { quantity: 1, type: 1 } )
db.inventory.createIndex( { type: 1, quantity: 1 } )
评估第一个索引对查询的影响:
db.inventory.find(
{ quantity: { $gte: 100, $lte: 300 }, type: "food" }
).hint({ quantity: 1, type: 1 }).explain("executionStats")
{
"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" : {
...
}
},
...
}
评估第二个索引对查询的影响:
db.inventory.find(
{ quantity: { $gte: 100, $lte: 300 }, type: "food" }
).hint({ type: 1, quantity: 1 }).explain("executionStats")
{
"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" : {
...
}
},
...
}
对于这个示例查询，复合索引**{type: 1, quantity: 1}比复合索引{quantity: 1, type: 1}**更有效。
也可以看看
译者：杨帅
校对：杨帅
