在 MongoDB 中使用 cursor.explain("executionStats") 和 db.collection.explain("executionStats") 方法来分析查询语句性能问题。
一、准备测试数据
db.inventory.insertMany([
{ "item" : "f1", type: "food", quantity: 500 },
{ "item" : "f2", type: "food", quantity: 100 },
{ "item" : "p1", type: "paper", quantity: 200 },
{ "item" : "p2", type: "paper", quantity: 150 },
{ "item" : "f3", type: "food", quantity: 300 },
{ "item" : "t1", type: "toys", quantity: 500 },
{ "item" : "a1", type: "apparel", quantity: 250 },
{ "item" : "a2", type: "apparel", quantity: 400 },
{ "item" : "t2", type: "toys", quantity: 50 },
{ "item" : "f4", type: "food", quantity: 75 }
])
查询示例如下：
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: {
...
winningPlan: {
queryPlan: {
stage: 'COLLSCAN',
...
}
}
},
executionStats: {
executionSuccess: true,
nReturned: 3,
executionTimeMillis: 0,
totalKeysExamined: 0,
totalDocsExamined: 10,
executionStages: {
stage: 'COLLSCAN',
...
},
...
},
...
}
queryPlanner.winningPlan.queryPlan.stage显示扫描集合，COLLSCAN表示扫描整个集合。这里类似MySQL中的select_typeexecutionStats.nReturned表明匹配的文档数executionStats.totalKeysExamined扫描到的索引文档数量executionStats.totalDocsExamined显示扫描的集合数量，这里 10 个，表示所有文档。
二、优化查询
2.1 建立索引
db.inventory.createIndex( { quantity: 1 } )
然后再执行查询分析：
db.inventory.find(
{ quantity: { $gte: 100, $lte: 200 } }
).explain("executionStats")
返回结果：
{
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为：IXSCANexecutionStats.nReturned返回三个文档executionStats.totalKeysExamined3个，扫描到索引条目executionStats.totalDocsExamined3个，扫描文档数目
如果没有索引，查询将扫描整个文档集合以返回匹配的文档。查询还会扫描整个文档内容，可能会存入内存中，导致慢查询。
