14.Mongdb查询计划分析

　　在集合上执行查询分心方法 db.collection.explain()，会返回查询相关的统计数据，通过对统计数据的分析，可以观察查询语句是否执行了期望的计划，还可以判断查询性能是否高效、查询的耗时情况等。

1.语法　

db.collection.find(过滤条件).explain(queryPlanner|executionStats|allPlansExecution)

　 这里可以看到explain后面有三个参数，其中queryPlanner是默认参数。　

2.queryPlanner　　

参数	含义
plannerVersion	查询版本
namespace	要查询的集合(该值返回的是该query所查询的表) 数据库.集合
indexFilterSet	针对该query是否有indexFilter（索引过滤）
parsedQuery	查询条件
winningPlan	被选中的执行计划
winningPlan.stage	被选中执行计划的stage(查询方式)，常见的有：collscan(全表扫描)、ixscan(索引扫描)、FETCH(根据索引去检索文档)、 shard_merge(合并分片结果)、idhack(针对_id进行查询等)
winningPlan.inputStage	用来描述子stage，并且为其父stage提供文档和索引关键字
winningPlan.stage的childstage	如果此处是ixscan，表示进行的是index scanning
winningPlan.keyPattern	所扫描的index内容
winningPlan.indexName	winning plan 所选用的index
winningPlan.isMultiKey	是否是Multikey，此处返回的是false，如果索引建立在arry上，此处将是true
winningPlan.direction	此query的查询顺序，此处是forward，如果用了sort({字段:-1}) 将显示backward
filter	过滤条件
winningPlain.indexBounds	winningplan所扫描的索引范围，如果没有制定返回就是[maxkey,minkey]，这主要是直接定位到mongodb的chunk中查找数据，加快数据读取
rejectedPlans	被拒绝的执行计划的详细返回，其中具体信息与winningPlan的返回中意义相同
serverinfo	Mongodb服务器的详细信息

 

 

 

 

 

 

 

示例：

> db.student.find({"name":'zl'}).explain()
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.student",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "name" : {
                "$eq" : "zl"
            }
        },
        "queryHash" : "01AEE5EC",
        "planCacheKey" : "0BE5F32C",
        "winningPlan" : {
            "stage" : "FETCH",            //表示根据索引去检索文档
            "inputStage" : {
                "stage" : "IXSCAN",     //这里用到了索引扫描
                "keyPattern" : {
                    "name" : 1         //索引所在的字段
                },
                "indexName" : "name_1", //索引名字
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "name" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "name" : [
                        "[\"zl\", \"zl\"]"
                    ]
                }
            }
        },
        "rejectedPlans" : [
            {
                "stage" : "FETCH",
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "name" : 1,
                        "age" : 1
                    },
                    "indexName" : "name_1_age_1",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "name" : [ ],
                        "age" : [ ]
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "name" : [
                            "[\"zl\", \"zl\"]"
                        ],
                        "age" : [
                            "[MinKey, MaxKey]"
                        ]
                    }
                }
            }
        ]
    },
    "serverInfo" : {
        "host" : "mongodb",
        "port" : 27019,
        "version" : "4.4.12",
        "gitVersion" : "51475a8c4d9856eb1461137e7539a0a763cc85dc"
    },
    "ok" : 1
}

2.executionStats：executionStats会返回执行计划的一些统计信息

参数	含义
executionSuccess	是否执行成功
nRetured	返回的文档数
executionTimeMillis	执行耗时
totalKeysExamined	索引扫描次数
totalDocsExamined	文档扫描次数
stage	扫描方式，具体可选值与上下的相同
nRetured	查询document获得数据的时间
executionTimeMillsEstimate	检索document获得数据的时间
inputStage.executionTimeMillisEstimate	该查询扫描文档index所用时间
works	工作单元数，一个查询会分解成小的工作单元
advanced	优先返回的结果数
docsExamined	文档检查数目，与totalDocsExamined一致，检查了总共的document个数，从而返回上面的nReturned数量

 

 

 

 

 

 

 

 

 

 

示例

> db.student.find({"name":'zl'}).explain("executionStats")
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.student",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "name" : {
                "$eq" : "zl"
            }
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "name" : 1
                },
                "indexName" : "name_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "name" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "name" : [
                        "[\"zl\", \"zl\"]"
                    ]
                }
            }
        },
        "rejectedPlans" : [
            {
                "stage" : "FETCH",
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "name" : 1,
                        "age" : 1
                    },
                    "indexName" : "name_1_age_1",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "name" : [ ],
                        "age" : [ ]
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "name" : [
                            "[\"zl\", \"zl\"]"
                        ],
                        "age" : [
                            "[MinKey, MaxKey]"
                        ]
                    }
                }
            }
        ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 0,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 0,
        "totalDocsExamined" : 0,
        "executionStages" : {
            "stage" : "FETCH",
            "nReturned" : 0,
            "executionTimeMillisEstimate" : 0,
            "works" : 2,
            "advanced" : 0,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "docsExamined" : 0,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 0,
                "executionTimeMillisEstimate" : 0,
                "works" : 1,
                "advanced" : 0,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 0,
                "restoreState" : 0,
                "isEOF" : 1,
                "keyPattern" : {
                    "name" : 1
                },
                "indexName" : "name_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "name" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "name" : [
                        "[\"zl\", \"zl\"]"
                    ]
                },
                "keysExamined" : 0,
                "seeks" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0
            }
        }
    },
    "serverInfo" : {
        "host" : "mongodb",
        "port" : 27019,
        "version" : "4.4.12",
        "gitVersion" : "51475a8c4d9856eb1461137e7539a0a763cc85dc"
    },
    "ok" : 1
}

3.allPlansExecution

　　allPlansExecution用来获取所有执行计划，结果参数基本上与前面大概相同