mongodbExplainandIndex-創(chuàng)新互聯(lián)
Explain and Index
explain有三種模式，分別是：queryPlanner、executionStats、allPlansExecution?，F(xiàn)實(shí)開(kāi)發(fā)中，常用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", "COLLSCAN"表示全表掃描 "filter" : { "age" : { "$lte" : 2000 } }, "direction" : "forward" }, "rejectedPlans" : [ ] }, "executionStats" : { "executionSuccess" : true, "nReturned" : 2001, 表示查詢返回的條目數(shù) "executionTimeMillis" : 741, 表示執(zhí)行語(yǔ)句的執(zhí)行時(shí)間 "totalKeysExamined" : 0, "totalDocsExamined" : 2000000, 表示 文檔掃描的條目數(shù) "executionStages" : { "stage" : "COLLSCAN", "filter" : { "age" : { "$lte" : 2000 } }, "nReturned" : 2001, "executionTimeMillisEstimate" : 530, 表示整體的查詢時(shí)間 "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}) 創(chuàng)建索引，“1”：表示按照age進(jìn)行升序，“-1”：表示按照age進(jìn)行降序
創(chuàng)建索引時(shí)ensureIndex({field:1},{name:"indexName"}) 可以指定索引的名稱
也可以后臺(tái)創(chuàng)建索引： db.collection.ensureIndex({name:-1},{background:true})
{ "createdCollectionAutomatically" : false, "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1 }> db.person.find({"age":{"$lte":2001}}).explain("executionStats")
{ "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.person", 返回所查詢的表 "indexFilterSet" : false, 針對(duì)該query是否有indexfilter "parsedQuery" : { "age" : { "$lte" : 2001 } }, "winningPlan" : { 查詢優(yōu)化器針對(duì)該query所返回的最優(yōu)執(zhí)行計(jì)劃的詳細(xì)內(nèi)容 "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, 表示本次查詢返回的條目數(shù) "executionTimeMillis" : 28, 表示本次query整體查詢的時(shí)間 "totalKeysExamined" : 2002, 表示索引掃描的條目數(shù) "totalDocsExamined" : 2002, 表示文檔掃描的條目數(shù) "executionStages" : { "stage" : "FETCH", "nReturned" : 2002, "executionTimeMillisEstimate" : 10, 表示本次查詢根據(jù)index檢索document獲得結(jié)果數(shù)據(jù)的時(shí)間 "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所用的時(shí)間 "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 類(lèi)型。
類(lèi)型列舉如下：
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)行跳過(guò)
IDHACK：針對(duì)_id進(jìn)行查詢
SHARDING_FILTER：通過(guò)mongos對(duì)分片數(shù)據(jù)進(jìn)行查詢
COUNT：利用db.coll.explain().count()之類(lèi)進(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的返回
另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無(wú)理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國(guó)服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡(jiǎn)單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢(shì)，專(zhuān)為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場(chǎng)景需求。
網(wǎng)站標(biāo)題：mongodbExplainandIndex-創(chuàng)新互聯(lián)
文章地址：http://m.jiaotiyi.com/article/hggsc.html