14.Mongdb查询计划分析(14. Mongdb query plan analysis)

  在集合上执行查询分心方法 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服务器的详细信息

被选中执行计划的stage(查询方式),常见的有:collscan(全表扫描)、ixscan(索引扫描)、FETCH(根据索引去检索文档)、

shard_merge(合并分片结果)、idhack(针对_id进行查询等)

示例:

> 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用来获取所有执行计划,结果参数基本上与前面大概相同

————————

Execute the query distraction method dB on the collection collection. Explain() will return the statistics related to the query. Through the analysis of the statistics, you can observe whether the query statement has executed the desired plan, and judge whether the query performance is efficient and time-consuming.

1. Syntax < / strong >

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

Here you can see that there are three parameters after explain, of which queryplanner is the default parameter.

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服务器的详细信息

The stage (query method) selected to execute the plan is common: collscan (full table scan), ixscan (index scan), fetch (retrieve documents according to the index)

shard_ Merge (merge fragment results), idhack (query for _id, etc.)

Example:

> 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 < / strong >: executionstats will return some statistics of the execution plan

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

Examples

> 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 is used to obtain all execution plans, and the result parameters are basically the same as before