mongodb 執(zhí)行計(jì)劃說明

創(chuàng)建documents：

for (i=0;i<1000000;i++){
db.users.insert(
{
"i":i,
"username":"user"+i,
"age":Math.floor(Math.random()*120),
"create":new Date()
}
);
}

創(chuàng)建索引：

db.users.createIndex({i:1},{background:1})

執(zhí)行計(jì)劃：

mongodb 3 explain有三種模式

db.users.find({i:90000}).explain()

db.users.find({i:90000}).explain("queryPlanner")  #explain的默認(rèn)模式

db.users.find({i:90000}).explain("executionStats")

db.users.find({i:90000}).explain("allPlansExecution")

說明：

queryPlanner模式下并不會(huì)去真正進(jìn)行query語句查詢，而是針對(duì)query語句進(jìn)行執(zhí)行計(jì)劃分析并選出winning plan。

repsetzhou:PRIMARY> db.users.find({i:9}).explain("queryPlanner")
{
     "queryPlanner" : {  #queryPlanner的返回
         "plannerVersion" : 1,
         "namespace" : "app_1.users",  #該值返回的是該query所查詢的表
         "indexFilterSet" : false,  #針對(duì)該query是否有indexfilter
         "parsedQuery" : {
             "i" : {
                 "$eq" : 9
             }
         },
         "winningPlan" : { #查詢優(yōu)化器針對(duì)該query所返回的最優(yōu)執(zhí)行計(jì)劃的詳細(xì)內(nèi)容
             "stage" : "FETCH", #最優(yōu)執(zhí)行計(jì)劃的stage，這里返回是FETCH，可以理解為通過返回的index位置去檢索具體的文檔
             "inputStage" : {  # 用來描述子stage，并且為其父stage提供文檔和索引關(guān)鍵字
                 "stage" : "IXSCAN",  #queryPlanner.winningPlan.stage的child stage，此處是IXSCAN，表示進(jìn)行的是index scanning
                 "keyPattern" : {  #掃描的index內(nèi)容,此處是  "i" : 1
                     "i" : 1
                 },
                 "indexName" : "i_1",  #winning plan所選用的index，使用db.users.getIndexes() 查看索引信息
                 "isMultiKey" : false,  #是否是Multikey，此處返回是false，如果索引建立在array上，此處將是true
                 "isUnique" : false, #是否為唯一鍵
                 "isSparse" : false,
                 "isPartial" : false,
                 "indexVersion" : 1,
                 "direction" : "forward", #query的查詢順序，此處是forward
                 "indexBounds" : {
                     "i" : [
                         "[9.0, 9.0]"
                     ]
                 }
             }
         },
         "rejectedPlans" : [ ]  #其他執(zhí)行計(jì)劃（非最優(yōu)而被查詢優(yōu)化器reject的）的詳細(xì)返回，具體信息與winningPlan的返回中意義相同
     },
     "serverInfo" : {   #server的一些信息
         "host" : "my1.ml.com",  #主機(jī)名字
         "port" : 27017,   #數(shù)據(jù)庫端口
         "version" : "3.2.13",   #數(shù)據(jù)庫版本
         "gitVersion" : "23899209cad60aaafe114f6aea6cb83025ff51bc"
     },
     "ok" : 1
}

executionStats分析：

repsetzhou:PRIMARY> db.users.find({i:9}).explain("executionStats")
{
     "queryPlanner" : {
         "plannerVersion" : 1,
         "namespace" : "app_1.users",
         "indexFilterSet" : false,
         "parsedQuery" : {
             "i" : {
                 "$eq" : 9
             }
         },
         "winningPlan" : {
             "stage" : "FETCH",
             "inputStage" : {
                 "stage" : "IXSCAN",
                 "keyPattern" : {
                     "i" : 1
                 },
                 "indexName" : "i_1",
                 "isMultiKey" : false,
                 "isUnique" : false,
                 "isSparse" : false,
                 "isPartial" : false,
                 "indexVersion" : 1,
                 "direction" : "forward",
                 "indexBounds" : {
                     "i" : [
                         "[9.0, 9.0]"
                     ]
                 }
             }
         },
         "rejectedPlans" : [ ]
     },
     "executionStats" : {
         "executionSuccess" : true,
         "nReturned" : 1,  #查詢返回的條目
         "executionTimeMillis" : 0,    #該query的整體查詢時(shí)間
         "totalKeysExamined" : 1,  #索引掃描條目
         "totalDocsExamined" : 1, #文檔掃描條目     

對(duì)于一個(gè)查詢來講，最理想的結(jié)果是：nReturned=totalKeysExamined=totalDocsExamined

         "executionStages" : {
             "stage" : "FETCH",   #此類型比較重要，如下列出可能的類型：

stage的類型：

    COLLSCAN：全表掃描

   IXSCAN：索引掃描

    FETCH：根據(jù)索引去檢索指定document

    SHARD_MERGE：將各個(gè)分片返回?cái)?shù)據(jù)進(jìn)行merge

    SORT：表明在內(nèi)存中進(jìn)行了排序

    LIMIT：使用limit限制返回?cái)?shù)

    SKIP：使用skip進(jìn)行跳過

    IDHACK：針對(duì)_id進(jìn)行查詢

    SHARDING_FILTER：通過mongos對(duì)分片數(shù)據(jù)進(jìn)行查詢

    COUNT：利用db.coll.explain().count()之類進(jìn)行count運(yùn)算

    COUNTSCAN：count不使用Index進(jìn)行count時(shí)的stage返回

    COUNT_SCAN：count使用了Index進(jìn)行count時(shí)的stage返回

    SUBPLA：未使用到索引的$or查詢的stage返回

    TEXT：使用全文索引進(jìn)行查詢時(shí)候的stage返回

    PROJECTION：限定返回字段時(shí)候stage的返回

    對(duì)于普通查詢，我希望看到stage的組合(查詢的時(shí)候盡可能用上索引)：

    Fetch+IDHACK

    Fetch+ixscan

    Limit+（Fetch+ixscan）

    PROJECTION+ixscan

    SHARDING_FITER+ixscan

    COUNT_SCAN

    如下的stage效率比較低下：

    COLLSCAN(全表掃描),SORT(使用sort但是無index),不合理的SKIP,SUBPLA(未用到index的$or),COUNTSCAN(不使用index進(jìn)行count)

             "nReturned" : 1,
             "executionTimeMillisEstimate" : 0,   #該query查詢根據(jù)index去檢索document獲得1條數(shù)據(jù)的時(shí)間
             "works" : 2,
             "advanced" : 1,
             "needTime" : 0,
             "needYield" : 0,
             "saveState" : 0,
             "restoreState" : 0,
             "isEOF" : 1,
             "invalidates" : 0,
             "docsExamined" : 1,
             "alreadyHasObj" : 0,
             "inputStage" : {
                 "stage" : "IXSCAN",
                 "nReturned" : 1,
                 "executionTimeMillisEstimate" : 0,  #該查詢掃描1行index所用時(shí)間
                 "works" : 2,
                 "advanced" : 1,
                 "needTime" : 0,
                 "needYield" : 0,
                 "saveState" : 0,
                 "restoreState" : 0,
                 "isEOF" : 1,
                 "invalidates" : 0,
                 "keyPattern" : {
                     "i" : 1
                 },
                 "indexName" : "i_1",
                 "isMultiKey" : false,
                 "isUnique" : false,
                 "isSparse" : false,
                 "isPartial" : false,
                 "indexVersion" : 1,
                 "direction" : "forward",
                 "indexBounds" : {
                     "i" : [
                         "[9.0, 9.0]"
                     ]
                 },
                 "keysExamined" : 1,
                 "dupsTested" : 0,
                 "dupsDropped" : 0,
                 "seenInvalidated" : 0
             }
         }
     },
     "serverInfo" : {
         "host" : "my1.ml.com",
         "port" : 27017,
         "version" : "3.2.13",
         "gitVersion" : "23899209cad60aaafe114f6aea6cb83025ff51bc"
     },
     "ok" : 1
}