MongoDB4.0 explain执行计划分析

queryPlanner

queryPlanner是现版本explain的默认模式，queryPlanner模式下并不会去真正进行query语句查询，而是针对query语句进行执行计划分析并选出winning plan。

{
    "queryPlanner": {
        "plannerVersion": NumberInt("1"),
        "namespace": "energy_tool_gs_yanshi.pointData",//该查询所在的表
        "indexFilterSet": false,
        "parsedQuery": {//查询参数
            "$and": [
                {
                    "pointId": {
                        "$eq": "5d275157000dec2f15d926b0"
                    }
                },
                {
                    "timeStamp": {
                        "$lte": 1592971200
                    }
                },
                {
                    "timeStamp": {
                        "$gte": 1569919810
                    }
                }
            ]
        },
        "winningPlan": {//查询优化器针对该query所返回的最优执行计划的详细内容。
            "stage": "FETCH",//最优查询计划的状态，FETCH:根据索引查询、
            "inputStage": {
                "stage": "IXSCAN",//查询的子状态，此处是索引扫描
                "keyPattern": {//扫描的索引内容
                    "pointId": 1,
                    "timeStamp": -1
                },
                "indexName": "pointId_1_timeStamp_-1",//走的索引名称
                "isMultiKey": false,//如果索引建立在array上，此处将是true。
                "multiKeyPaths": {
                    "pointId": [ ],
                    "timeStamp": [ ]
                },
                "isUnique": false,
                "isSparse": false,
                "isPartial": false,
                "indexVersion": NumberInt("2"),
                "direction": "forward",//此query的查询顺序，此处是forward，如果用了.sort({w:-1})将显示backward。
                "indexBounds": {//索引包含的字段
                    "pointId": [
                        "[\"5d275157000dec2f15d926b0\", \"5d275157000dec2f15d926b0\"]"
                    ],
                    "timeStamp": [
                        "[1592971200.0, 1569919810.0]"
                    ]
                }
            }
        },
        "rejectedPlans": [//优化器拒绝的查询计划
            {
                "stage": "FETCH",
                "filter": {
                    "pointId": {
                        "$eq": "5d275157000dec2f15d926b0"
                    }
                },
                "inputStage": {
                    "stage": "IXSCAN",
                    "keyPattern": {
                        "timeStamp": NumberInt("-1")
                    },
                    "indexName": "timeStamp",
                    "isMultiKey": false,
                    "multiKeyPaths": {
                        "timeStamp": [ ]
                    },
                    "isUnique": false,
                    "isSparse": false,
                    "isPartial": false,
                    "indexVersion": NumberInt("2"),
                    "direction": "forward",
                    "indexBounds": {
                        "timeStamp": [
                            "[1592971200.0, 1569919810.0]"
                        ]
                    }
                }
            }
        ]
    },
    "serverInfo": {
        "host": "gs-server-6829",
        "port": NumberInt("27019"),
        "version": "4.0.10",
        "gitVersion": "c389e7f69f637f7a1ac3cc9fae843b635f20b766"
    },
    "ok": 1,
    "operationTime": Timestamp(1593311102, 2),
    "$clusterTime": {
        "clusterTime": Timestamp(1593311102, 2),
        "signature": {
            "hash": BinData(0, "AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
            "keyId": NumberLong("0")
        }
    }
}

executionStats

// 1
{
    "queryPlanner": {
        "plannerVersion": NumberInt("1"),
        "namespace": "suzhou_0.pointData",
        "indexFilterSet": false,
        "parsedQuery": {
            "$and": [
                {
                    "pointId": {
                        "$eq": "5ecb61cde0a772db005a75ec"
                    }
                },
                {
                    "timeStamp": {
                        "$lte": 1592971200
                    }
                },
                {
                    "timeStamp": {
                        "$gte": 1569919810
                    }
                }
            ]
        },
        "winningPlan": {
            "stage": "FETCH",
            "inputStage": {
                "stage": "IXSCAN",
                "keyPattern": {
                    "pointId": NumberInt("1"),
                    "timeStamp": NumberInt("-1")
                },
                "indexName": "pointId_1_timeStamp_-1",
                "isMultiKey": false,
                "multiKeyPaths": {
                    "pointId": [ ],
                    "timeStamp": [ ]
                },
                "isUnique": false,
                "isSparse": false,
                "isPartial": false,
                "indexVersion": NumberInt("2"),
                "direction": "forward",
                "indexBounds": {
                    "pointId": [
                        "[\"5ecb61cde0a772db005a75ec\", \"5ecb61cde0a772db005a75ec\"]"
                    ],
                    "timeStamp": [
                        "[1592971200.0, 1569919810.0]"
                    ]
                }
            }
        },
        "rejectedPlans": [
            {
                "stage": "FETCH",
                "filter": {
                    "$and": [
                        {
                            "timeStamp": {
                                "$lte": 1592971200
                            }
                        },
                        {
                            "timeStamp": {
                                "$gte": 1569919810
                            }
                        }
                    ]
                },
                "inputStage": {
                    "stage": "IXSCAN",
                    "keyPattern": {
                        "pointId": NumberInt("1")
                    },
                    "indexName": "pointId_1",
                    "isMultiKey": false,
                    "multiKeyPaths": {
                        "pointId": [ ]
                    },
                    "isUnique": false,
                    "isSparse": false,
                    "isPartial": false,
                    "indexVersion": NumberInt("2"),
                    "direction": "forward",
                    "indexBounds": {
                        "pointId": [
                            "[\"5ecb61cde0a772db005a75ec\", \"5ecb61cde0a772db005a75ec\"]"
                        ]
                    }
                }
            }
        ]
    },
    "executionStats": {
        "executionSuccess": true,//是否执行成功
        "nReturned": NumberInt("81470000"),//查询的返回条数
        "executionTimeMillis": NumberInt("245522"),//查询计划选择和查询执行所需的总时间，单位为毫秒
        "totalKeysExamined": NumberInt("81470000"),//索引扫描次数
        "totalDocsExamined": NumberInt("81470000"),//文档扫描次数
        "executionStages": {
            "stage": "FETCH",//状态，根据索引扫描文档
            "nReturned": NumberInt("81470000"),//查询的返回条数
            "executionTimeMillisEstimate": NumberInt("75962"),//根据索引去查询数据的时间
            "works": NumberInt("81470001"),//执行了多少个工作单元，查询过程会分解为不同的工作单元,例如，检查一个索引的key值，或者查询了一个文档等
            "advanced": NumberInt("81470000"),//返回到父阶段的结果数
            "needTime": NumberInt("0"),//
            "needYield": NumberInt("0"),//存储层请求查询系统产生锁定的次数
            "saveState": NumberInt("637673"),//查询阶段暂停处理并存储其当前执行状态的次数
            "restoreState": NumberInt("637673"),//查询阶段恢复一个执行状态的次数
            "isEOF": NumberInt("1"),//指定查询阶段是否已经结束,1为结束，0表示该阶段还有结果需要返回
            "invalidates": NumberInt("0"),
            "docsExamined": NumberInt("81470000"),//在查询执行阶段扫描的文档数。
            "alreadyHasObj": NumberInt("0"),
            "inputStage": {
                "stage": "IXSCAN",
                "nReturned": NumberInt("81470000"),
                "executionTimeMillisEstimate": NumberInt("10246"),//扫描索引所用的时间
                "works": NumberInt("81470001"),
                "advanced": NumberInt("81470000"),
                "needTime": NumberInt("0"),
                "needYield": NumberInt("0"),
                "saveState": NumberInt("637673"),
                "restoreState": NumberInt("637673"),
                "isEOF": NumberInt("1"),
                "invalidates": NumberInt("0"),
                "keyPattern": {
                    "pointId": NumberInt("1"),
                    "timeStamp": NumberInt("-1")
                },
                "indexName": "pointId_1_timeStamp_-1",
                "isMultiKey": false,
                "multiKeyPaths": {
                    "pointId": [ ],
                    "timeStamp": [ ]
                },
                "isUnique": false,
                "isSparse": false,
                "isPartial": false,
                "indexVersion": NumberInt("2"),
                "direction": "forward",
                "indexBounds": {
                    "pointId": [
                        "[\"5ecb61cde0a772db005a75ec\", \"5ecb61cde0a772db005a75ec\"]"
                    ],
                    "timeStamp": [
                        "[1592971200.0, 1569919810.0]"
                    ]
                },
                "keysExamined": NumberInt("81470000"),//在查询过程中，检查索引的总数
                "seeks": NumberInt("1"),
                "dupsTested": NumberInt("0"),
                "dupsDropped": NumberInt("0"),
                "seenInvalidated": NumberInt("0")
            }
        }
    },
    "serverInfo": {
        "host": "gs-server-6828",
        "port": NumberInt("27019"),
        "version": "4.0.10",
        "gitVersion": "c389e7f69f637f7a1ac3cc9fae843b635f20b766"
    },
    "ok": 1,
    "operationTime": Timestamp(1593310893, 1),
    "$clusterTime": {
        "clusterTime": Timestamp(1593310893, 1),
        "signature": {
            "hash": BinData(0, "AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
            "keyId": NumberLong("0")
        }
    }
}