mongodb Explain and Index

標籤：index and explain

Explain and Index

    explain有三種模式，分別是：queryPlanner、executionStats、allPlansExecution。現實開發中，常用executionStats模式，本例分析這種模式。

> db.createCollection("person")

{ "ok" : 1 }

> for(var i=0;i<2000000;i++) {

... db.person.insert({"name":"ryan"+i,"age":i})

... }

WriteResult({ "nInserted" : 1 })

> db.person.find({"age":{"$lte":2000}}).explain("executionStats")

{

"queryPlanner" : {

"plannerVersion" : 1,

"namespace" : "test.person",

"indexFilterSet" : false,

"parsedQuery" : {

"age" : {

"$lte" : 2000

}

},

"winningPlan" : {

"stage" : "COLLSCAN",

"filter" : {

"age" : {

"$lte" : 2000

}

},

"direction" : "forward"

},

"rejectedPlans" : [ ]

},

"executionStats" : {

"executionSuccess" : true,

"nReturned" : 2001,                表示查詢返回的條目數

"executionTimeMillis" : 741,    表示執行語句的執行時間

"totalKeysExamined" : 0,

"totalDocsExamined" : 2000000,    表示 文檔掃描的條目數

"executionStages" : {

"stage" : "COLLSCAN",

"filter" : {

"age" : {

"$lte" : 2000

}

},

"nReturned" : 2001,

"executionTimeMillisEstimate" : 530,    表示整體的查詢時間

"works" : 2000002,

"advanced" : 2001,

"needTime" : 1998000,

"needYield" : 0,

"saveState" : 15625,

"restoreState" : 15625,

"isEOF" : 1,

"invalidates" : 0,

"direction" : "forward",

"docsExamined" : 2000000

}

},

"serverInfo" : {

"host" : "meteor.yeecall.com",

"port" : 27027,

"version" : "3.2.8",

"gitVersion" : "ed70e33130c977bda0024c125b56d159573dbaf0"

},

"ok" : 1

}

> db.person.ensureIndex({age:1})    建立索引，“1”：表示按照age進行升序，“-1”：表示按照age進行降序

{

"createdCollectionAutomatically" : false,

"numIndexesBefore" : 1,

"numIndexesAfter" : 2,

"ok" : 1

}

> db.person.find({"age":{"$lte":2001}}).explain("executionStats")

{

"queryPlanner" : {

"plannerVersion" : 1,

"namespace" : "test.person",    返回所查詢的表

"indexFilterSet" : false,        針對該query是否有indexfilter

"parsedQuery" : {

"age" : {

"$lte" : 2001

}

},

"winningPlan" : {        查詢最佳化工具針對該query所返回的最優執行計畫的詳細內容

"stage" : "FETCH",

"inputStage" : {

"stage" : "IXSCAN",

"keyPattern" : {

"age" : 1

},

"indexName" : "age_1",

"isMultiKey" : false,

"isUnique" : false,

"isSparse" : false,

"isPartial" : false,

"indexVersion" : 1,

"direction" : "forward",

"indexBounds" : {

"age" : [

"[-inf.0, 2001.0]"

]

}

}

},

"rejectedPlans" : [ ]

},

"executionStats" : {

"executionSuccess" : true,

"nReturned" : 2002,                        表示本次查詢返回的條目數

"executionTimeMillis" : 28,            表示本次query整體查詢的時間

"totalKeysExamined" : 2002,        表示索引掃描的條目數

"totalDocsExamined" : 2002,        表示文檔掃描的條目數

"executionStages" : {

"stage" : "FETCH",

"nReturned" : 2002,

"executionTimeMillisEstimate" : 10,    表示本次查詢根據index檢索document獲得結果資料的時間

"works" : 2003,

"advanced" : 2002,

"needTime" : 0,

"needYield" : 0,

"saveState" : 15,

"restoreState" : 15,

"isEOF" : 1,

"invalidates" : 0,

"docsExamined" : 2002,

"alreadyHasObj" : 0,

"inputStage" : {

"stage" : "IXSCAN",

"nReturned" : 2002,

"executionTimeMillisEstimate" : 0,    表示該查詢掃描2002行index所用的時間

"works" : 2003,

"advanced" : 2002,

"needTime" : 0,

"needYield" : 0,

"saveState" : 15,

"restoreState" : 15,

"isEOF" : 1,

"invalidates" : 0,

"keyPattern" : {

"age" : 1

},

"indexName" : "age_1",

"isMultiKey" : false,

"isUnique" : false,

"isSparse" : false,

"isPartial" : false,

"indexVersion" : 1,

"direction" : "forward",

"indexBounds" : {

"age" : [

"[-inf.0, 2001.0]"

]

},

"keysExamined" : 2002,

"dupsTested" : 0,

"dupsDropped" : 0,

"seenInvalidated" : 0

}

}

},

"serverInfo" : {

"host" : "meteor.yeecall.com",

"port" : 27027,

"version" : "3.2.8",

"gitVersion" : "ed70e33130c977bda0024c125b56d159573dbaf0"

},

"ok" : 1

}

>

影響totalKeysExamined和totalDocsExamined的是stage類型。類型列舉如下：

    COLLSCAN：全表掃描

    IXSCAN：索引掃描

    FETCH：根據索引去檢索指定document

    SHARD_MERGE：將各個分區返回資料進行merge

    SORT：表明在記憶體中進行了排序

    LIMIT：使用limit限制返回數

    SKIP：使用skip進行跳過

    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的返回

本文出自 “11462293” 部落格，請務必保留此出處http://11472293.blog.51cto.com/11462293/1836611

mongodb Explain and Index