基本用法
db.collection.method(...).explain(<mode>)
db.collection.explain(<mode>).method(...)
explain有三种模式：
- queryPlanner (默认)
- 只列出所有可能执行的方案，不会执行实际的语句，显示已经胜出的方案winningPlan
- executionStats
- 只执行winningPlan方案，并输出结果
- allPlansExecution
- 执行所有的方案，并输出结果
// queryPlanner (默认)
db.collection.find().explain(); || db.collection.explain().find();
// executionStats 模式, 给explain函数传递参数即可
db.collection.find().explain('executionStats');
// allPlansExecution 模式
db.collection.find().explain('allPlansExecution');
explain内容解读
queryPlanner
简化版的结果描述
"queryPlanner": {
"plannerVersion": <int>,
"namespace": <string>,
"indexFilterSet": <boolean>,
"parsedQuery": {
...
},
"queryHash": <hexadecimal string>,
"planCacheKey": <hexadecimal string>,
"optimizedPipeline": <boolean>, // Starting in MongoDB 4.2, only appears if true
"winningPlan" : {
"stage" : <STAGE1>,
...
"inputStage" : {
"stage" : <STAGE2>,
...
"inputStage" : {
stage: <STAGE3>,
...
}
}
},
"rejectedPlans" : [
<candidate plan 1>,
...
]
}
每个阶段都会将其生成的文档或索引键传递给父节点。叶子节点访问集合或索引。内部节点使用由子节点产生的文档或索引键。根节点表示MongoDB最终导出结果集的阶段。
stage阶段类型如下：
- COLLSCAN：全表扫描
- IXSCAN：索引扫描
- FETCH：根据索引去检索指定document
- SHARD_MERGE：将各个分片返回数据进行merge
- SORT：表明在内存中进行了排序
- LIMIT：使用limit限制返回数
- SKIP：使用skip进行跳过
- IDHACK：针对_id进行查询
- SHARDING_FILTER：通过mongos对分片数据进行查询
- COUNT：利用db.coll.explain().count()之类进行count运算
- COUNTSCAN： count不使用Index进行count时的stage返回
- COUNT_SCAN： count使用了Index进行count时的stage返回
- SUBPLA：未使用到索引的$or查询的stage返回
- TEXT：使用全文索引进行查询时候的stage返回
- PROJECTION：限定返回字段时候stage的返回
| key | 描述 |
|---|---|
| queryPlaner.planerVersion | 执行计划的版本 |
| queryPlaner.namespace | 命名空间，包含数据库名称和集合名称(database.collection) |
| queryPlaner.indexFilterSet | 是否使用了索引 |
| queryPlaner.parsedQuery | |
| queryPlaner.queryHash | 查询的一个哈希值，帮助识别具有相同查询的慢查询 |
| queryPlaner.planCacheKey | 与查询关联的计划缓存键的哈希 |
| queryPlaner.winningPlan | 选择的执行计划，将以树的形式展现；每一个阶段可以有一个 inputStage，如果阶段有多个子阶段，则会返回多个 |
| queryPlaner.winningPlan.stage | 表示当前阶段的查询方式 |
| queryPlaner.winningPlan.inputStage | 子阶段的执行情况，子阶段向父阶段提供文档或索引键 |
| queryPlaner.rejectedPlans | 拒绝的执行计划 |
示例
- 表结构与sql
collection名；resource
索引：idx_resource_type
db.resource.find({resource_type: {$in : ["space"]}}).skip(3000).limit(100).explain('');
- 运行结果
{
"queryPlanner": {
"mongosPlannerVersion": 1,
"winningPlan": {
"stage": "SINGLE_SHARD",
"shards": [
{
"shardName": "cluster2770-shard-0",
"connectionString": "xxxx",
"serverInfo": {
"host": "xxxxxboe-1",
"port": 27017,
"version": "4.0.3.1-49-12-g03e212d",
"gitVersion": "xxxxx"
},
"plannerVersion": 1,
"namespace": "keel.resource",
"indexFilterSet": false,
"parsedQuery": {
"resource_type": {
"$eq": "space"
}
},
"winningPlan": {
"stage": "LIMIT",
"limitAmount": 100,
"inputStage": {
"stage": "FETCH",
"inputStage": {
"stage": "SKIP",
"skipAmount": 3000,
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"resource_type": 1
},
"indexName": "idx_resource_type",
"isMultiKey": false,
"multiKeyPaths": {
"resource_type": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"resource_type": [
"[\"space\", \"space\"]"
]
}
}
}
}
},
"rejectedPlans": []
}
]
}
}
}
executionStats
简化版结果描述
executionStats: {
executionSuccess: <boolean>,
nReturned: <int>,
executionTimeMillis: <int>,
totalKeysExamined: <int>,
totalDocsExamined: <int>,
executionStages: {
stage: <STAGE1>
nReturned: <int>,
executionTimeMillisEstimate: <int>,
opens: <int>, // Starting in MongoDB 5.1
closes: <int>, // Starting in MongoDB 5.1
works: <int>,
advanced: <int>,
needTime: <int>,
needYield: <int>,
saveState: <int>,
restoreState: <int>,
isEOF: <boolean>,
...
inputStage: {
stage: <STAGE2>,
nReturned: <int>,
...
numReads: <int>, // Starting in MongoDB 5.1
...
executionTimeMillisEstimate: <int>,
...
inputStage: {
...
}
}
}
}
| key | 描述 |
|---|---|
| executionStats.nReturned | 符合条件的文档数 |
| executionStats.executionTimeMillis | 解释和查询执行的总时间，单位：毫秒 |
| executionStats.totalKeysExamined | 扫描索引键数目 |
| executionStats.totalDocsExamined | 扫描的文档数目。不代表返回的文档数，而且并不是只扫描一次，可能在不同的步骤扫瞄多次 |
| executionStats.executionStages | 执行情况 |
| executionStats.executionStages.executionTimeMillisEstimate | 查询的一个预估时间，单位：毫秒 |
| executionStats.executionStages.inputStage | 子阶段查询 |
示例
db.resource.find({resource_type: {$in : ["space"]}}).
skip(3000).limit(100).explain('executionStats');
"executionStats": {
"nReturned": 100,
"executionTimeMillis": 11,
"totalKeysExamined": 3100,
"totalDocsExamined": 100,
"executionStages": {
"stage": "SINGLE_SHARD",
"nReturned": 100,
"executionTimeMillis": 11,
"totalKeysExamined": 3100,
"totalDocsExamined": 100,
"totalChildMillis": "3",
"shards": [
{
"shardName": "cluster2770-shard-0",
"executionSuccess": true,
"executionStages": {
"stage": "LIMIT",
"nReturned": 100,
"executionTimeMillisEstimate": 0,
"works": 3101,
"advanced": 100,
"needTime": 3000,
"needYield": 0,
"saveState": 24,
"restoreState": 24,
"isEOF": 1,
"invalidates": 0,
"limitAmount": 100,
"inputStage": {
"stage": "FETCH",
"nReturned": 100,
"executionTimeMillisEstimate": 0,
"works": 3100,
"advanced": 100,
"needTime": 3000,
"needYield": 0,
"saveState": 24,
"restoreState": 24,
"isEOF": 0,
"invalidates": 0,
"docsExamined": 100,
"alreadyHasObj": 0,
"inputStage": {
"stage": "SKIP",
"nReturned": 100,
"executionTimeMillisEstimate": 0,
"works": 3100,
"advanced": 100,
"needTime": 3000,
"needYield": 0,
"saveState": 24,
"restoreState": 24,
"isEOF": 0,
"invalidates": 0,
"skipAmount": 0,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 3100,
"executionTimeMillisEstimate": 0,
"works": 3100,
"advanced": 3100,
"needTime": 0,
"needYield": 0,
"saveState": 24,
"restoreState": 24,
"isEOF": 0,
"invalidates": 0,
"keyPattern": {
"resource_type": 1
},
"indexName": "idx_resource_type",
"isMultiKey": false,
"multiKeyPaths": {
"resource_type": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"resource_type": [
"[\"space\", \"space\"]"
]
},
"keysExamined": 3100,
"seeks": 1,
"dupsTested": 0,
"dupsDropped": 0,
"seenInvalidated": 0
}
}
}
}
}
]
}
}
allPlansExecution
就是把 queryPlanner 和 executionStats 的查询结果汇总起来了
