前言
db.collection.explain("executionStats")提供了关于查询性能的统计信息。这些统计信息在衡量是否使用索引以及如何使用上提供了有效的帮助。
创建测试文档并插入测试数据
db.inventory.insertMany([
{ "_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 }
])无索引时查询
查询inventory文档指定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,
namespace: 'db01.inventory',
indexFilterSet: false,
parsedQuery: {
'$and': [ { quantity: { '$lte': 200 } }, { quantity: { '$gte': 100 } } ]
},
winningPlan: {
stage: 'COLLSCAN',
filter: {
'$and': [
{ quantity: { '$lte': 200 } },
{ quantity: { '$gte': 100 } }
]
},
direction: 'forward'
},
rejectedPlans: []
},
executionStats: {
executionSuccess: true,
nReturned: 3,
executionTimeMillis: 0,
totalKeysExamined: 0,
totalDocsExamined: 10,
executionStages: {
stage: 'COLLSCAN',
filter: {
'$and': [
{ quantity: { '$lte': 200 } },
{ quantity: { '$gte': 100 } }
]
},
nReturned: 3,
executionTimeMillisEstimate: 0,
works: 12,
advanced: 3,
needTime: 8,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
direction: 'forward',
docsExamined: 10
}
},
serverInfo: {
host: 'guizhou_hp-pop-10-150-57-13',
port: 27018,
version: '4.4.12',
gitVersion: '51475a8c4d9856eb1461137e7539a0a763cc85dc'
},
ok: 1,
'$clusterTime': {
clusterTime: Timestamp({ t: 1648718283, i: 1 }),
signature: {
hash: Binary(Buffer.from("0000000000000000000000000000000000000000", "hex"), 0),
keyId: Long("0")
}
},
operationTime: Timestamp({ t: 1648718283, i: 1 })
}
执行计划解释：
queryPlanner.winningPlan.stage值为COLLSCAN说明查询操作是集合扫描，未使用到索引（quantity字段未创建索引）。集合扫描类似于MySQL全表扫描，扫描全部集合中的文档以获取最终的结果。这是导致缓慢查询的一个原因。executionStats.nReturned值为3说明查询操作匹配并返回3个文档。executionStats.totalKeysExamined值为0说明查询操作使用0个索引检查。executionStats.totalDocsExamined值为10说明查询操作检查10个文档才能找到匹配的3个文档。
有索引时查询
在inventory集合quantity字段创建索引：
db.inventory.createIndex( { quantity: 1 } )
查看执行计划：
db.inventory.find(
{ quantity: { $gte: 100, $lte: 200 } }
).explain("executionStats")执行计划返回结果：
{
queryPlanner: {
plannerVersion: 1,
namespace: 'db01.inventory',
indexFilterSet: false,
parsedQuery: {
'$and': [ { quantity: { '$lte': 200 } }, { quantity: { '$gte': 100 } } ]
},
winningPlan: {
stage: 'FETCH',
inputStage: {
stage: 'IXSCAN',
keyPattern: { quantity: 1 },
indexName: 'quantity_1',
isMultiKey: false,
multiKeyPaths: { quantity: [] },
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: { quantity: [ '[100, 200]' ] }
}
},
rejectedPlans: []
},
executionStats: {
executionSuccess: true,
nReturned: 3,
executionTimeMillis: 0,
totalKeysExamined: 3,
totalDocsExamined: 3,
executionStages: {
stage: 'FETCH',
nReturned: 3,
executionTimeMillisEstimate: 0,
works: 4,
advanced: 3,
needTime: 0,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
docsExamined: 3,
alreadyHasObj: 0,
inputStage: {
stage: 'IXSCAN',
nReturned: 3,
executionTimeMillisEstimate: 0,
works: 4,
advanced: 3,
needTime: 0,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
keyPattern: { quantity: 1 },
indexName: 'quantity_1',
isMultiKey: false,
multiKeyPaths: { quantity: [] },
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: { quantity: [ '[100, 200]' ] },
keysExamined: 3,
seeks: 1,
dupsTested: 0,
dupsDropped: 0
}
}
},
serverInfo: {
host: 'guizhou_hp-pop-10-150-57-13',
port: 27018,
version: '4.4.12',
gitVersion: '51475a8c4d9856eb1461137e7539a0a763cc85dc'
},
ok: 1,
'$clusterTime': {
clusterTime: Timestamp({ t: 1648719869, i: 7 }),
signature: {
hash: Binary(Buffer.from("0000000000000000000000000000000000000000", "hex"), 0),
keyId: Long("0")
}
},
operationTime: Timestamp({ t: 1648719869, i: 7 })
}执行计划解释：
queryPlanner.winningPlan.inputStage.stage值为IXSCAN说明使用了索引，其下面部分是使用的索引的信息。executionStats.nReturned值为3说明查询操作结果匹配到了3个文档。executionStats.totalKeysExamined值为3说明扫描了3个索引项。mongod检查的键数与返回的文档数相同，所以只用扫描索引就可以返回匹配的结果。mongod不需要扫描全部文档，只需要扫描符合条件的3个文档到内存中。这对于提升查询性能非常有效。executionStats.totalDocsExamined值为3说明查询操作总共检查了3个文档就能出匹配的3个文档 。
有无索引对比
有索引查询和无索引查询对比：
- 无索引时：需要检查inventory集合中10个文档才能返回符合条件的3个文档。
- 有索引时：只需要检查索引中的3个条目就能返回符合条件的3个文档。
db.collection.explain()使用
db.collection.explain()有三种输出结果模式：
- queryPlanner：缺省模式，列出查询优化器选择的执行计划和拒绝的执行计划
- executionStats：比缺省模式信息更详细，列出选择执行计划的详细信息，但是不列出拒绝的执行计划信息
- allPlansExecution：包含所有执行计划的信息
queryPlanner模式：
> db.inventory.find({quantity: {$lt: 250}}).explain("queryPlanner")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "db01.inventory",
"indexFilterSet" : false,
"parsedQuery" : {
"quantity" : {
"$lt" : 250
}
},
"queryHash" : "9B59DECC",
"planCacheKey" : "DA89EC86",
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"quantity" : 1
},
"indexName" : "quantity_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"quantity" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"quantity" : [
"[-inf.0, 250.0)"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "guizhou_hp-pop-10-150-57-13",
"port" : 27018,
"version" : "4.4.12",
"gitVersion" : "51475a8c4d9856eb1461137e7539a0a763cc85dc"
},
"ok" : 1,
"$clusterTime" : {
"clusterTime" : Timestamp(1648798395, 1),
"signature" : {
"hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
"keyId" : NumberLong(0)
}
},
"operationTime" : Timestamp(1648798395, 1)
}
executionStats模式：
> db.inventory.find({quantity: {$lt: 250}}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "db01.inventory",
"indexFilterSet" : false,
"parsedQuery" : {
"quantity" : {
"$lt" : 250
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"quantity" : 1
},
"indexName" : "quantity_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"quantity" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"quantity" : [
"[-inf.0, 250.0)"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 5,
"executionTimeMillis" : 0,
"totalKeysExamined" : 5,
"totalDocsExamined" : 5,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 6,
"advanced" : 5,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"docsExamined" : 5,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 6,
"advanced" : 5,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"keyPattern" : {
"quantity" : 1
},
"indexName" : "quantity_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"quantity" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"quantity" : [
"[-inf.0, 250.0)"
]
},
"keysExamined" : 5,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
},
"serverInfo" : {
"host" : "guizhou_hp-pop-10-150-57-13",
"port" : 27018,
"version" : "4.4.12",
"gitVersion" : "51475a8c4d9856eb1461137e7539a0a763cc85dc"
},
"ok" : 1,
"$clusterTime" : {
"clusterTime" : Timestamp(1648798435, 1),
"signature" : {
"hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
"keyId" : NumberLong(0)
}
},
"operationTime" : Timestamp(1648798435, 1)
}
allPlansExecution模式：
> db.inventory.find({quantity: {$lt: 250}}).explain("allPlansExecution")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "db01.inventory",
"indexFilterSet" : false,
"parsedQuery" : {
"quantity" : {
"$lt" : 250
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"quantity" : 1
},
"indexName" : "quantity_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"quantity" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"quantity" : [
"[-inf.0, 250.0)"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 5,
"executionTimeMillis" : 0,
"totalKeysExamined" : 5,
"totalDocsExamined" : 5,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 6,
"advanced" : 5,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"docsExamined" : 5,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 6,
"advanced" : 5,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"keyPattern" : {
"quantity" : 1
},
"indexName" : "quantity_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"quantity" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"quantity" : [
"[-inf.0, 250.0)"
]
},
"keysExamined" : 5,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
}
},
"allPlansExecution" : [ ]
},
"serverInfo" : {
"host" : "guizhou_hp-pop-10-150-57-13",
"port" : 27018,
"version" : "4.4.12",
"gitVersion" : "51475a8c4d9856eb1461137e7539a0a763cc85dc"
},
"ok" : 1,
"$clusterTime" : {
"clusterTime" : Timestamp(1648798475, 2),
"signature" : {
"hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
"keyId" : NumberLong(0)
}
},
"operationTime" : Timestamp(1648798475, 2)
}
参考官方文档：
