初识MongoDB(八) MongoDB查看执行计划

MongoDB中的explain()函数可以帮助我们查看查询相关的信息，这有助于我们快速查找到搜索瓶颈进而解决它。

整体来说，explain()的用法和sort()、limit()用法差不多，不同的是explain()必须放在最后面。

基本用法

先来看一个基本用法：

db.collec_3.find({x:1}).explain()

直接跟在find()函数后面，表示查看find()函数的执行计划，结果如下：

{
        "explainVersion" : "1",
        "queryPlanner" : {
                "namespace" : "nameicc.collec_3",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "x" : {
                                "$eq" : 1
                        }
                },
                "queryHash" : "716F281A",
                "planCacheKey" : "3B5193C6",
                "maxIndexedOrSolutionsReached" : false,
                "maxIndexedAndSolutionsReached" : false,
                "maxScansToExplodeReached" : false,
                "winningPlan" : {
                        "stage" : "COLLSCAN",
                        "filter" : {
                                "x" : {
                                        "$eq" : 1
                                }
                        },
                        "direction" : "forward"
                },
                "rejectedPlans" : [ ]
        },
        "command" : {
                "find" : "collec_3",
                "filter" : {
                        "x" : 1
                },
                "$db" : "nameicc"
        },
        "serverInfo" : {
                "host" : "01E281721049843",
                "port" : 27017,
                "version" : "5.0.3",
                "gitVersion" : "657fea5a61a74d7a79df7aff8e4bcf0bc742b748"
        },
        "serverParameters" : {
                "internalQueryFacetBufferSizeBytes" : 104857600,
                "internalQueryFacetMaxOutputDocSizeBytes" : 104857600,
                "internalLookupStageIntermediateDocumentMaxSizeBytes" : 104857600,
                "internalDocumentSourceGroupMaxMemoryBytes" : 104857600,
                "internalQueryMaxBlockingSortMemoryUsageBytes" : 104857600,
                "internalQueryProhibitBlockingMergeOnMongoS" : 0,
                "internalQueryMaxAddToSetBytes" : 104857600,
                "internalDocumentSourceSetWindowFieldsMaxMemoryBytes" : 104857600
        },
        "ok" : 1
}

queryPlanner即查询计划，看一下涉及到的参数：

参数	含义
namespace	要查询的集合
indexFilterSet	是否使用索引
parsedQuery	查询条件，此处为x=1
winningPlan	最佳执行计划
stage	查询方式，常见的有COLLSCAN全表扫描、IXSCAN索引扫描、FETCH根据索引检索文档、SHARD_MERGE分片合并结果、IDHACK针对_id进行查询
filter	过滤条件
direction	搜索方向
rejectedPlans	拒绝的执行计划

添加参数

explain()也接收不同的String类型参数，通过设置不同参数我们可以查看更详细的执行计划。

queryPlanner

queryPlanner是默认参数，添加queryPlanner参数的查询结果就是我们上文看到的查询结果。

executionStats

executionStats会返回最佳执行计划的一些统计信息，如下：

{
        "explainVersion" : "1",
        "queryPlanner" : {
                "namespace" : "nameicc.collec_3",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "x" : {
                                "$eq" : 1
                        }
                },
                "maxIndexedOrSolutionsReached" : false,
                "maxIndexedAndSolutionsReached" : false,
                "maxScansToExplodeReached" : false,
                "winningPlan" : {
                        "stage" : "COLLSCAN",
                        "filter" : {
                                "x" : {
                                        "$eq" : 1
                                }
                        },
                        "direction" : "forward"
                },
                "rejectedPlans" : [ ]
        },
        "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 1,
                "executionTimeMillis" : 0,
                "totalKeysExamined" : 0,
                "totalDocsExamined" : 3,
                "executionStages" : {
                        "stage" : "COLLSCAN",
                        "filter" : {
                                "x" : {
                                        "$eq" : 1
                                }
                        },
                        "nReturned" : 1,
                        "executionTimeMillisEstimate" : 0,
                        "works" : 5,
                        "advanced" : 1,
                        "needTime" : 3,
                        "needYield" : 0,
                        "saveState" : 0,
                        "restoreState" : 0,
                        "isEOF" : 1,
                        "direction" : "forward",
                        "docsExamined" : 3
                }
        },
        "command" : {
                "find" : "collec_3",
                "filter" : {
                        "x" : 1
                },
                "$db" : "nameicc"
        },
        "serverInfo" : {
                "host" : "01E281721049843",
                "port" : 27017,
                "version" : "5.0.3",
                "gitVersion" : "657fea5a61a74d7a79df7aff8e4bcf0bc742b748"
        },
        "serverParameters" : {
                "internalQueryFacetBufferSizeBytes" : 104857600,
                "internalQueryFacetMaxOutputDocSizeBytes" : 104857600,
                "internalLookupStageIntermediateDocumentMaxSizeBytes" : 104857600,
                "internalDocumentSourceGroupMaxMemoryBytes" : 104857600,
                "internalQueryMaxBlockingSortMemoryUsageBytes" : 104857600,
                "internalQueryProhibitBlockingMergeOnMongoS" : 0,
                "internalQueryMaxAddToSetBytes" : 104857600,
                "internalDocumentSourceSetWindowFieldsMaxMemoryBytes" : 104857600
        },
        "ok" : 1
}

这里除了上文介绍的一些参数之外，还多了executionStats参数，含义如下：

参数	含义
executionSuccess	是否执行成功
nReturned	返回的结果数
executionTimeMillis	执行耗时
totalKeysExamined	索引扫描次数
totalDocsExamined	文档扫描次数
executionStages	描述执行的状态
stage	扫描方式，与上文相同
executionTimeMillisEstimate	预估耗时
works	工作单元数，一个查询会分解成小的工作单元
advanced	优先返回的结果数
docsExamined	文档检查数目，与totalDocsExamined一致

allPlansExecution

allPlansExecution用来获取所有执行计划，结果参数基本与上文相同。