索引優(yōu)化-世人皆知Mysql,誰人懂我MongoDB

查看執(zhí)行計劃

索引優(yōu)化是一個永遠都繞不過的話題,作為NoSQL的MongoDB也不例外死嗦。Mysql中通過explain命令來查看對應的索引信息,MongoDB亦如此肪笋。

1. db.collection.explain().<method(...)>
    db.products.explain().remove( { category: "apparel" }, { justOne: true })

2. db.collection.<method(...)>.explain({})
    db.products.remove( { category: "apparel" }, { justOne: true }).explain()

如果你是在mongoshell 中第一種和第二種沒什么區(qū)別窒悔，如果你是在robot 3T這樣的客戶端工具中使用你必須在后面顯示調(diào)用finish()或者next()

db.collection.explain().find({}).finish()

explain有三種模式,分別是:

queryPlanner(默認) ：queryPlanner模式下并不會去真正進行query語句查詢呜袁，而是針對query語句進行執(zhí)行計劃分析并選出winning plan
executionStats ：MongoDB運行查詢優(yōu)化器以選擇獲勝計劃(winning plan)，執(zhí)行獲勝計劃直至完成简珠，并返回描述獲勝計劃執(zhí)行情況的統(tǒng)計信息阶界。
allPlansExecution: queryPlanner和executionStats都返回。相當于 explain("allPlansExecution") = explain({})

queryPlanner(查詢計劃)

日志表中存儲了用戶的操作日志,我們經(jīng)常查詢某一篇文章的操作日志,數(shù)據(jù)如下:

{
  "_id" : NumberLong(7277744),
  "operatorName" : "autotest_cp",
  "operateTimeUnix" : NumberLong(1586511800890),
  "module" : "ARTICLE",
  "opType" : "CREATE",
  "level" : "GENERAL",
  "recordData" : {
      "articleId" : "6153324",
      "categories" : "100006",
      "title" : "testCase-2 this article is created for cp edior to search",
      "status" : "DRAFT"
  },
  "responseCode" : 10002
}

集合中大概有700萬數(shù)據(jù),對于這樣的查詢語句

db.getCollection('operateLog').find({"module": "ARTICLE", "recordData.articleId": "6153324"}).sort({_id:-1})

首先看下queryPlanner返回的內(nèi)容：

"queryPlanner" : {
  "plannerVersion" : 1,
  "namespace" : "smcp.operateLog",
  "indexFilterSet" : false,
  "parsedQuery" : {
    "$and" : [ 
      {
        "module" : {
            "$eq" : "ARTICLE"
        }
      }, 
      {
        "recordData.articleId" : {
            "$eq" : "6153324"
        }
      }
    ]
  },
  "winningPlan" : {
    "stage" : "FETCH",
    "filter" : {
      "$and" : [ 
        {
          "module" : {
              "$eq" : "ARTICLE"
          }
        }, 
        {
          "recordData.articleId" : {
              "$eq" : "6153324"
          }
        }
      ]
    },
    "inputStage" : {
      "stage" : "IXSCAN",
      "keyPattern" : {
          "_id" : 1
      },
      "indexName" : "_id_",
      "isMultiKey" : false,
      "multiKeyPaths" : {
          "_id" : []
      },
      "isUnique" : true,
      "isSparse" : false,
      "isPartial" : false,
      "indexVersion" : 2,
      "direction" : "backward",
      "indexBounds" : {
          "_id" : [ 
              "[MaxKey, MinKey]"
          ]
      }
    }
  },
  "rejectedPlans" : [ 
    {
      "stage" : "SORT",
      "sortPattern" : {
          "_id" : -1.0
      },
      "inputStage" : {
        "stage" : "SORT_KEY_GENERATOR",
        "inputStage" : {
          "stage" : "FETCH",
          "filter" : {
              "recordData.articleId" : {
                  "$eq" : "6153324"
              }
          },
          "inputStage" : {
            "stage" : "IXSCAN",
            "keyPattern" : {
                "module" : 1.0,
                "opType" : 1.0
            },
            "indexName" : "module_1_opType_1",
            "isMultiKey" : false,
            "multiKeyPaths" : {
                "module" : [],
                "opType" : []
            },
            "isUnique" : false,
            "isSparse" : false,
            "isPartial" : false,
            "indexVersion" : 2,
            "direction" : "forward",
            "indexBounds" : {
                "module" : [ 
                    "[\"ARTICLE\", \"ARTICLE\"]"
                ],
                "opType" : [ 
                    "[MinKey, MaxKey]"
                ]
            }
          }
        }
      }
    }
  ]
}

字段含義

一些重要字段的含義

queryPlanner.namespace
查詢的哪個表
queryPlanner.winningPlan
查詢優(yōu)化器針對該query所返回的最優(yōu)執(zhí)行計劃的詳細內(nèi)容聋庵。
queryPlanner.winningPlan.stage
最優(yōu)計劃執(zhí)行的階段,每個階段都包含特定于該階段的信息膘融。例如，IXSCAN階段將包括索引范圍以及特定于索??引掃描的其他數(shù)據(jù)祭玉。如果一個階段具有一個子階段或多個子階段氧映，那么該階段將具有inputStage或inputStages。
queryPlanner.winningPlan.inputStage
描述子階段的文檔攘宙，該子階段向其父級提供文檔或索引鍵屯耸。如果父階段只有一個孩子，則該字段存在蹭劈。
queryPlanner.winningPlan.inputStage.indexName
winning plan所選用的index,這里是根據(jù)_id來進行排序的，所以使用了_id的索引
queryPlanner.winningPlan.inputStage.isMultiKey
是否是Multikey线召，此處返回是false铺韧，如果索引建立在array上，此處將是true
queryPlanner.winningPlan.inputStage.isUnique
使用的索引是否是唯一索引缓淹，這里的_id是唯一索引
queryPlanner.winningPlan.inputStage.isSparse
是否是稀疏索引
queryPlanner.winningPlan.inputStage.isPartial
是否是部分索引
queryPlanner.winningPlan.inputStage.direction
此query的查詢順序哈打，默認是forward，由于使用了sort({_id:-1})顯示backward
queryPlanner.winningPlan.inputStage.indexBounds
winningplan所掃描的索引范圍,由于這里使用的是sort({_id:-1}),對_id倒序排序,所以范圍是[MaxKey,MinKey]讯壶。如果是正序,則是[MinKey,MaxKey]
queryPlanner.rejectedPlans
拒絕的計劃詳細內(nèi)容,各字段含義同winningPlan

executionStats(執(zhí)行結(jié)果)

再來看下executionStats的返回結(jié)果

"executionStats" : {
  "executionSuccess" : true,
  "nReturned" : 1,
  "executionTimeMillis" : 24387,
  "totalKeysExamined" : 6998084,
  "totalDocsExamined" : 6998084,
  "executionStages" : {
    "stage" : "FETCH",
    "filter" : {
      "$and" : [ 
        {
          "module" : {
              "$eq" : "ARTICLE"
          }
        }, 
        {
          "recordData.articleId" : {
              "$eq" : "6153324"
          }
        }
      ]
    },
    "nReturned" : 1,
    "executionTimeMillisEstimate" : 1684,
    "works" : 6998085,
    "advanced" : 1,
    "needTime" : 6998083,
    "needYield" : 0,
    "saveState" : 71074,
    "restoreState" : 71074,
    "isEOF" : 1,
    "invalidates" : 0,
    "docsExamined" : 6998084,
    "alreadyHasObj" : 0,
    "inputStage" : {
      "stage" : "IXSCAN",
      "nReturned" : 6998084,
      "executionTimeMillisEstimate" : 290,
      "works" : 6998085,
      "advanced" : 6998084,
      "needTime" : 0,
      "needYield" : 0,
      "saveState" : 71074,
      "restoreState" : 71074,
      "isEOF" : 1,
      "invalidates" : 0,
      "keyPattern" : {
          "_id" : 1
      },
      "indexName" : "_id_",
      "isMultiKey" : false,
      "multiKeyPaths" : {
          "_id" : []
      },
      "isUnique" : true,
      "isSparse" : false,
      "isPartial" : false,
      "indexVersion" : 2,
      "direction" : "backward",
      "indexBounds" : {
          "_id" : [ 
              "[MaxKey, MinKey]"
          ]
      },
      "keysExamined" : 6998084,
      "seeks" : 1,
      "dupsTested" : 0,
      "dupsDropped" : 0,
      "seenInvalidated" : 0
    }
  },

  "allPlansExecution" : [
    {...},
    {...}
  ]
}

字段解析

executionStats.executionSuccess
是否執(zhí)行成功
executionStats.nReturned
查詢的返回條數(shù)
executionStats.executionTimeMillis
選擇查詢計劃和執(zhí)行查詢所需的總時間（以毫秒為單位）
executionStats.totalKeysExamined
索引掃描次數(shù)
executionStats.totalDocsExamined
document掃描次數(shù)
executionStats.executionStages
以階段樹的形式詳細說明獲勝計劃的完成執(zhí)行情況料仗；即一個階段可以具有一個inputStage或多個inputStages。如上說明伏蚊。
executionStats.executionStages.inputStage.keysExamined
掃描了多少次索引
executionStats.executionStages.inputStage.docsExamined
掃描了多少次文檔立轧，一般當stage是 COLLSCAN的時候會有這個值。
exlexecutionStats.allPlansExecution
這里展示了所有查詢計劃的詳細躏吊。(winningPlan + rejectPlans),字段含義和winningPlan中一致氛改，不做贅述

15種stage

從上面可以看出stage是很重要的，一個查詢到底走的是索引還是全表掃描主要看的就是stage的值, 而stage有如下值

COLLSCAN : 掃描整個集合
IXSCAN : 索引掃描(index scan)
FETCH : 根據(jù)索引返回的結(jié)果去檢索文檔(如上我們的例子)
SHARD_MERGE : 將各個分片返回數(shù)據(jù)進行merge
SORT : 調(diào)用了sort方法,當出現(xiàn)這個階段的時候你可以看到memUsage以及memLimit這兩個字段
SORT_KEY_GENERATOR : 在內(nèi)存中進行了排序
LIMIT ：使用limit限制返回數(shù)
SKIP ：使用skip進行跳過
IDHACK ：針對_id進行查詢
SHARDING_FILTER ：通過mongos對分片數(shù)據(jù)進行查詢
COUNT: 利用db.coll.explain().count()之類進行count運算, 只要調(diào)用了count方法比伏，那么 executionStats.executionStages.stage = COUNT
COUNT_SCAN : count使用Index進行count時的stage返回

{
  country: "ID",
  name: "jjj",
  status: 0
},
{
  country: "ZH",
  name: "lisi",
  status: 1
}

我們對country字段建立了索引胜卤，同時執(zhí)行下面的語句

db.getCollection('testData').explain(true).count({country: "ID"})

那么查看執(zhí)行結(jié)果可以看到 executionStats.executionStages.inputStage.stage = COUNT_SCAN, COUNT_SCAN是COUNT的一個子階段。

COUNTSCAN : count不使用Index進行count時的stage返回赁项。

db.getCollection('testData').explain(true).count({status: 0})

此時 executionStats.executionStages.inputStage.stage = COUNTSCAN , COUNTSCAN是COUNT的一個子階段

SUBPLAN : 未使用到索引的$or查詢的stage返回

db.getCollection('testData').find({$or : [{name : "lisi"}, {status: 0}]}).explain(true);

此時 executionStats.executionStages.stage = SUBPLAN

TEXT : 使用全文索引進行查詢時候的stage返回
PROJECTION : 限定返回字段時候stage的返回

查看executionStats.executionStages.stage以及其下各個inputStage(子階段)的值是什么,可以判定存在哪些優(yōu)化點葛躏。

一個查詢它掃描的文檔數(shù)要盡可能的少,才能更快澈段，明顯我們我們不希望看到COLLSCAN, SORT_KEY_GENERATOR, COUNTSCAN, SUBPLAN 以及不合理的 SKIP 這些stage,當你看到這些stage的時候就要注意了。

查詢優(yōu)化

當你看winningPlan或者rejectPlan的時候舰攒，你就可以知道執(zhí)行順序是怎樣的败富，比如我們rejectPlan中，先是通過 "module_1_opType_1"檢索 "module = ARTICLE"的數(shù)據(jù)芒率，然后FETCH階段再通過 "recordData.articleId=6153324"進行過濾囤耳，最后在內(nèi)存中排序后返回數(shù)據(jù)。明顯這樣的計劃被拒絕了偶芍，至少它沒有winningPlan執(zhí)行快充择。

再來看看executionStats返回的數(shù)據(jù)
**
nReturned 為 1，即符合條件的只有1條
executionTimeMillis 值為24387,執(zhí)行時間為24秒
totalKeysExamined 值為 6998084,雖然用到了索引匪蟀，但是幾乎是掃描了所有的key
totalDocsExamined的值為6998084,也是掃描了所有文檔
**

從上面的輸出結(jié)果可以看出來椎麦，雖然我們使用了索引，但是速度依然很慢材彪。很明顯現(xiàn)在的索引观挎，并不適合我們，為了排除干擾段化，我們先將module_1_opType_1這個索引刪除嘁捷。由于我們這里使用了兩個字段進行查詢，而 recordData.articleId這個字段并不是每個document(集合中還存儲了其他類型的數(shù)據(jù))都存在显熏，所以建立索引的時候recordData.articleId需要建立部分索引

db.getCollection('operateLog').createIndex(
{'module': 1, 'recordData.articleId': 1 },
{
  "partialFilterExpression": {
    "recordData.articleId": {
      "$exists": true
    }
  },

  "background": true
}
)

我先吃個蘋果雄嚣，等它把索引建立好，大家有啥吃啥喘蟆。在索引建立完成之后缓升，我們來看看 executionStats 的結(jié)果

"executionStats" : {
  "executionSuccess" : true,
  "nReturned" : 1,
  "executionTimeMillis" : 3,
  "totalKeysExamined" : 1,
  "totalDocsExamined" : 1,
  "executionStages" : {
    "stage" : "SORT",
    "sortPattern" : {
        "_id" : -1.0
    },
    "memUsage" : 491,
    "memLimit" : 33554432,
    "inputStage" : {
      "stage" : "SORT_KEY_GENERATOR",
      "inputStage" : {
          "stage" : "FETCH",
          "nReturned" : 1,
          "inputStage" : {
            "stage" : "IXSCAN",
            "keyPattern" : {
                "module" : 1.0,
                "recordData.articleId" : 1.0
            },
            "indexName" : "module_1_recordData.articleId_1",
            "isMultiKey" : false,
            "multiKeyPaths" : {
              "module" : [],
              "recordData.articleId" : []
            },
            "isPartial" : true,
            "indexVersion" : 2,
            "direction" : "forward",
            "indexBounds" : {
              "module" : [ 
                "[\"ARTICLE\", \"ARTICLE\"]"
              ],
              "recordData.articleId" : [ 
                "[\"6153324\", \"6153324\"]"
              ]
          }
        }
      }
    }
  }
}

我忽略了一些不重要的字段,可以看到，現(xiàn)在執(zhí)行時間是3毫秒(executionTimeMillis=3),掃描了1個index(totalKeysExamined=1),掃描了1個document(totalDocsExamined=1)蕴轨。相比于之前的24387毫秒港谊，我可以說我的執(zhí)行速度提升了8000倍，我就問還有誰橙弱。如果此事讓UC 震驚部小編知道了歧寺，肯定又可以起一個震驚的標題了

但是這個執(zhí)行計劃仍然有問題，有問題膘螟，有問題成福，重要的事情說三遍。 executionStages.stage = sort,證明它在內(nèi)存中排序了,在數(shù)據(jù)量大的時候荆残，是很消耗性能的奴艾，所以千萬不能忽視它，我們要改進這個點内斯。

我們要在 nReturned = totalDocsExamined的基礎上蕴潦，讓排序也走索引像啼。所以我們先將之前的索引刪除，然后重新創(chuàng)建索引潭苞，這里我們將_id字段也加入到索引中忽冻，三個字段形成組合索引

db.getCollection('operateLog').createIndex(
{'module': 1, 'recordData.articleId': 1, '_id': -1},
{
  "partialFilterExpression": {
    "recordData.articleId": {
      "$exists": true
    }
  },

  "background": true
}
)

同樣的再來看看我們的執(zhí)行結(jié)果:

"executionStats" : {
  "executionSuccess" : true,
  "nReturned" : 1,
  "executionTimeMillis" : 0,
  "totalKeysExamined" : 1,
  "totalDocsExamined" : 1,
  "executionStages" : {
    "stage" : "FETCH",
    "nReturned" : 1,
    "executionTimeMillisEstimate" : 0,
    "docsExamined" : 1,
    "inputStage" : {
      "stage" : "IXSCAN",
      "nReturned" : 1,
      "keyPattern" : {
          "module" : 1.0,
          "recordData.articleId" : 1.0,
          "_id" : -1.0
      },
      "indexName" : "module_1_recordData.articleId_1__id_-1",
      "multiKeyPaths" : {
          "module" : [],
          "recordData.articleId" : [],
          "_id" : []
      },
      "isPartial" : true,
      "direction" : "forward",
      "indexBounds" : {
          "module" : [ 
              "[\"ARTICLE\", \"ARTICLE\"]"
          ],
          "recordData.articleId" : [ 
              "[\"6153324\", \"6153324\"]"
          ],
          "_id" : [ 
              "[MaxKey, MinKey]"
          ]
      }
    }
  }
}

可以看到我們這次的stage是FETCH+IXSCAN,同時 nReturned = totalKeysExamined = totalDocsExamined = 1，并且利用了index排序此疹，而非在內(nèi)存中排序僧诚。從executionTimeMillis=0也可以看出來，性能相比于之前的3毫秒也有所提升蝗碎，至此這個索引就是我們需要的了湖笨。

最開頭的結(jié)果和優(yōu)化的過程告訴我們,使用了索引你的查詢?nèi)匀豢赡芎苈?我們要將更多的目光集中到掃描的文檔或者行數(shù)中。

索引優(yōu)化準則

根據(jù)ESR原則創(chuàng)建索引
精確(Equal)匹配的字段放最前面,排序(Sort)條件放中間,范圍(Range)匹配的字段放最后面,同樣適用于ES,ER蹦骑。
每一個查詢都必須要有對應的索引
盡量使用覆蓋索引 Covered Indexes(可以避免讀數(shù)據(jù)文件)
需要查詢的條件以及返回值均在索引中
使用 projection 來減少返回到客戶端的的文檔的內(nèi)容
盡可能不要計算總數(shù),特別是數(shù)據(jù)量大和查詢不能命中索引的時候
避免使用skip/limit形式的分頁慈省，特別是數(shù)據(jù)量大的時候

替代方案:使用查詢條件+唯一排序條件
第一頁：db.posts.find({}).sort({_id: 1}).limit(20);
第二頁：db.posts.find({_id: { $gt: <第一頁最后一個_id>}}).sort({_id: 1}).limit(20); 第三頁：db.posts.find({_id: {$ gt: <第二頁最后一個_id>}}).sort({_id: 1}).limit(20);

?著作權歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末，一起剝皮案震驚了整個濱河市眠菇，隨后出現(xiàn)的幾起案子边败，更是在濱河造成了極大的恐慌，老刑警劉巖捎废，帶你破解...
沈念sama閱讀 219,110評論 6贊 508
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件笑窜，死亡現(xiàn)場離奇詭異，居然都是意外死亡登疗，警方通過查閱死者的電腦和手機怖侦，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 93,443評論 3贊 395
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來谜叹，“玉大人，你說我怎么就攤上這事搬葬『衫埃” “怎么了？”我有些...
開封第一講書人閱讀 165,474評論 0贊 356
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵急凰，是天一觀的道長女仰。經(jīng)常有香客問我，道長抡锈，這世上最難降的妖魔是什么疾忍？我笑而不...
開封第一講書人閱讀 58,881評論 1贊 295
?港島之戀（遺憾婚禮）
正文為了忘掉前任，我火速辦了婚禮床三，結(jié)果婚禮上一罩，老公的妹妹穿的比我還像新娘。我一直安慰自己撇簿，他們只是感情好聂渊，可當我...
茶點故事閱讀 67,902評論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布差购。她就那樣靜靜地躺著，像睡著了一般汉嗽。火紅的嫁衣襯著肌膚如雪欲逃。梳的紋絲不亂的頭發(fā)上，一...
開封第一講書人閱讀 51,698評論 1贊 305
城市分裂傳說
那天饼暑，我揣著相機與錄音稳析，去河邊找鬼。笑死弓叛，一個胖子當著我的面吹牛彰居，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播邪码，決...
沈念sama閱讀 40,418評論 3贊 419
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼裕菠，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了闭专？” 一聲冷哼從身側(cè)響起奴潘，我...
開封第一講書人閱讀 39,332評論 0贊 276
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎影钉，沒想到半個月后画髓，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
沈念sama閱讀 45,796評論 1贊 316
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡平委，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 37,968評論 3贊 337
?白月光啟示錄
正文我和宋清朗相戀三年奈虾，在試婚紗的時候發(fā)現(xiàn)自己被綠了。大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片廉赔。...
茶點故事閱讀 40,110評論 1贊 351
活死人
序言：一個原本活蹦亂跳的男人離奇死亡肉微，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出蜡塌，到底是詐尸還是另有隱情碉纳，我是刑警寧澤，帶...
沈念sama閱讀 35,792評論 5贊 346
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布馏艾，位于F島的核電站劳曹，受9級特大地震影響，放射性物質(zhì)發(fā)生泄漏琅摩。R本人自食惡果不足惜铁孵，卻給世界環(huán)境...
茶點故事閱讀 41,455評論 3贊 331
男人毒藥：我在死后第九天來索命
文/蒙蒙一、第九天我趴在偏房一處隱蔽的房頂上張望房资。院中可真熱鬧蜕劝，春花似錦、人聲如沸。這莊子的主人今日做“春日...
開封第一講書人閱讀 32,003評論 0贊 22
一樁弒父案鳖擒，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽。三九已至烫止，卻和暖如春蒋荚，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背馆蠕。一陣腳步聲響...
開封第一講書人閱讀 33,130評論 1贊 272
情欲美人皮
我被黑心中介騙來泰國打工期升，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留，地道東北人互躬。一個月前我還...
沈念sama閱讀 48,348評論 3贊 373
代替公主和親
正文我出身青樓播赁，卻偏偏與公主長得像，于是被迫代替她去往敵國和親吼渡。傳聞我的和親對象是個殘疾皇子容为，可洞房花燭夜當晚...
茶點故事閱讀 45,047評論 2贊 355