查詢計劃解析

檢視查詢計劃、查詢計劃的執行統計的方法：db.collection.explain()、cursor.explain()、explain

explain輸出預設隻輸出queryPlanner、serverInfo部分，如果需要輸出executionStats，則可以指定explain(allPlansExecution)或者explain("executionStats")

queryPlanner：執行計劃相關描述

.COLLSCAN for a collection scan
    .IXSCAN for scanning index keys
    .FETCH for retrieving documents
    .SHARD_MERGE for merging results from shards
    .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的傳回
    .AND_SORTED 表示Index Intersection
    .SORT 表示在記憶體中排序
    .EOF 表示結果不存在
           

If MongoDB can use an index scan to obtain the requested sort order, the result will not include a SORT stage. Otherwise, if MongoDB cannot use the index to sort, the explain result will include a SORT stage.

樣例

db.common_log_new.explain("allPlansExecution").find({ctime:{$lte:1529474045}}).limit(10)

{

"queryPlanner" : {  --queryPlanner模式下并不會去真正進行query語句查詢，而是針對query語句進行執行計劃分析并選出winning plan。
            "plannerVersion" : 1,
            "namespace" : "dresslily.common_log_new",
            "indexFilterSet" : false,   --指定mongodb是否應用索引過濾
            "parsedQuery" : {
                    "ctime" : {
                            "$lte" : 1529474045   --具體的查詢語句
                    }
            },
            "winningPlan" : {   --查詢優化器選擇的最優執行計劃
                    "stage" : "LIMIT",  --将檢索出來的文檔做limit處理
                    "limitAmount" : 10,
                    "inputStage" : {   --描述子stage的文檔，提供文檔或者索引給他的父級stage，該值表示是否父級stage隻有一個子節點
                            "stage" : "FETCH",  --通過傳回的index位置去檢索具體的文檔
                            "inputStage" : {
                                    "stage" : "IXSCAN", --索引掃描，這個地方可以判斷該查詢是通過索引還是全集合掃描資料
                                    "keyPattern" : {   --所掃描的Index内容
                                            "ctime" : 1
                                    },
                                    "indexName" : "idx_ctime",  --索引名稱
                                    "isMultiKey" : false,  --是否為多鍵索引
                                    "isUnique" : false,    --是否為唯一索引
                                    "isSparse" : false,    --是否為稀疏索引
                                    "isPartial" : false,   --是否為部分索引
                                    "indexVersion" : 1,
                                    "direction" : "forward",  --query的查詢順序，如果用了.sort({w:-1})将顯示backward。
                                    "indexBounds" : {  --winningplan所掃描的索引範圍
                                            "ctime" : [
                                                    "[-inf.0, 1529474045.0]"
                                            ]
                                    }
                            }
                    }
            },
            "rejectedPlans" : [ ]
    },
    "executionStats" : {  --query語句查詢的過程
            "executionSuccess" : true,
            "nReturned" : 10,  --查詢的傳回條數
            "executionTimeMillis" : 53, --整體執行時長
            "totalKeysExamined" : 10,  --索引掃描條目數
            "totalDocsExamined" : 10,  --document掃描條目數，如果索引可以覆寫查詢，那麼這個地方應該是0，比較好的結果是totalDocsExamined=totalKeysExamined
            "executionStages" : {
                    "stage" : "LIMIT",  --與queryPlanner.winningPlan.stage部分對應，影響totalKeysExamined與totalDocsExamined
                    "nReturned" : 10,
                    "executionTimeMillisEstimate" : 0,
                    "works" : 11,  
                    "advanced" : 10,
                    "needTime" : 0,
                    "needYield" : 0,
                    "saveState" : 0,
                    "restoreState" : 0,
                    "isEOF" : 1,
                    "invalidates" : 0,
                    "limitAmount" : 10,
                    "inputStage" : {
                            "stage" : "FETCH",
                            "nReturned" : 10,
                            "executionTimeMillisEstimate" : 0,
                            "works" : 10,
                            "advanced" : 10,
                            "needTime" : 0,
                            "needYield" : 0,
                            "saveState" : 0,
                            "restoreState" : 0,
                            "isEOF" : 0,
                            "invalidates" : 0,
                            "docsExamined" : 10,
                            "alreadyHasObj" : 0,
                            "inputStage" : {
                                    "stage" : "IXSCAN",
                                    "nReturned" : 10,
                                    "executionTimeMillisEstimate" : 0,
                                    "works" : 10,
                                    "advanced" : 10,
                                    "needTime" : 0,
                                    "needYield" : 0,
                                    "saveState" : 0,
                                    "restoreState" : 0,
                                    "isEOF" : 0,
                                    "invalidates" : 0,
                                    "keyPattern" : {
                                            "ctime" : 1
                                    },
                                    "indexName" : "idx_ctime",
                                    "isMultiKey" : false,
                                    "isUnique" : false,
                                    "isSparse" : false,
                                    "isPartial" : false,
                                    "indexVersion" : 1,
                                    "direction" : "forward",
                                    "indexBounds" : {
                                            "ctime" : [
                                                    "[-inf.0, 1529474045.0]"
                                            ]
                                    },
                                    "keysExamined" : 10,
                                    "dupsTested" : 0,
                                    "dupsDropped" : 0,
                                    "seenInvalidated" : 0
                            }
                    }
            },
            "allPlansExecution" : [ ]
    },
    "serverInfo" : {
            "host" : "dresslilypcmongodb01.globalerow.com",
            "port" : 27017,
            "version" : "3.2.15",
            "gitVersion" : "e11e3c1b9c9ce3f7b4a79493e16f5e4504e01140"
    },
    "ok" : 1           

}