MongoDB涉及的業務比較慢--慢查詢最佳化分析案例--以及參數說明

標籤：最佳化 參數分析

描述：該最佳化案例是想表達要瞭解各個參數的含義，結合業務的分析以及邏輯實現、以及建立索引和列順序是如何選擇的等(這裡不再敘述)

環境描述一下：

MongoDB版本 3.0.9，複本集3節點，記憶體64G，cpu 16 core，磁碟2TB SSD，使用WT儲存引擎。。。

該表資料量2.6億多。

大致分析如下：

1. 通過mloginfo統計查看日誌中慢查詢的分類(將生產系統日誌scp到測試伺服器做的)

# mloginfo --queries mongod.log-20160427  

namespace    operation  pattern                                            count    min (ms) max (ms)  mean (ms)  95%-ile (ms) sum (ms)

資料庫.集合  query     {"gender": 1, "icial": 1, "stVal": 1, "version": 1}  997090   366      3961      802        n/a         51923475

2.抓取程式在慢的時間點日誌資訊：

……

2016-04-26T14:28:48.536+0800 I COMMAND  [conn241925] query 資料庫.集合 query: { orderby: { goals: 1, diff: 1 }, $query: { version: true, icial: true, stVal: { $gte: 20 }, gender: "f" } } planSummary: IXSCAN { gender: 1.0, goals: 1.0, difficulty: 1.0, stateValue: 1.0, version: -1.0 } ntoreturn:1000 ntoskip:0 nscanned:145640 nscannedObjects:145628 keyUpdates:0 writeConflicts:0 numYields:1137 nreturned:10 reslen:510 locks:{ Global: { acquireCount: { r: 2276 }, acquireWaitCount: { r: 28 }, timeAcquiringMicros: { r: 22753 } }, Database: { acquireCount: { r: 1138 } }, Collection: { acquireCount: { r: 1138 } } } 1675ms

這樣的SQL語句很多，只拿一條分析。

分析各個參數的含義：

(1)目前該查詢sql使用的索引：IXSCAN { gender: 1.0, goals: 1.0, diff: 1.0, stVal: 1.0, version: -1.0 }

(2)ntoreturn:1000  期望返回數量，query語句期望返回的數量，如limit（40）

(3)nreturned:10  實際返回的數量

(4)ntoskip:0     skip()方法跳過的記錄數

(5)nscanned:145640

   掃描次數，當掃描次數大於返回的數量（ntoreturn），考慮使用索引

   nscanned和nscannedObjects區別：

   1、nscanned：根據索引掃描文檔，掃描的可能返回實際返回的數量(nreturned:10)

   2、nscannedObjects：掃描完整的文檔，掃描實際返回的資料(nscannedObjects:145628)

    http://stackoverflow.com/questions/13910097/explain-in-mongodb-differences-between-nscanned-and-nscannedobjects 

說明

nscanned審議了項目（檔案或索引項目）的數量。項目可能是對象或索引鍵。如果一個“覆蓋索引”參與， nscanned可能比nscannedObjects高

【nscanned Number of items (documents or index entries) examined. Items might be objects or index keys. If a "covered index" is involved, nscanned may be higher than nscannedObjects.】

nscannedObjects：掃描的文檔數量.

(6)acquireCount: 特定模式下擷取鎖的操作次數

(7)millis: 1675ms  操作執行時間

說明：

沒有該值，說明一下，這個值也特別重要

scanAndOrder:布爾值，當為true時，表明排序未使用到索引，只有true時該欄位才顯示

(8)numYields：1137 

就是查詢等待插入的次數

查詢是需要給寫操作讓路的

numYields是報告的次數的操作已經產生，以允許其它操作來完成的數量的計數器。

https://docs.mongodb.org/manual/reference/method/db.currentOp/

通常情況下，操作產生時，他們需要訪問的MongoDB還沒有完全讀入記憶體中的資料。

這允許在記憶體中的資料，以快速完成，而在MongoDB的資料屈服操作讀取等操作。

[

numYields is a counter that reports the number of times the operation has yielded to allow other operations to complete.

Typically, operations yield when they need access to data that MongoDB has not yet fully read into memory. 

This allows other operations that have data in memory to complete quickly while MongoDB reads in data for the yielding operation.

]

可能還有其他動作，比如索引建的有問題，即使走索引，需要掃描整個索引，

並且索引不覆蓋查詢，需要回行載入資料。另外看是不是排序沒有用上索引，

導致很多需要單獨放記憶體排序，耗效能耗記憶體。

另外如果有in查詢，資料分散，載入資料可能需要多次隨機IO等等。。

(9)觀察執行計畫、慢日誌如下參數(不在說明)

nscannedObjects

nscanned

scanAndOrder

millis

3.在secondary(業務不忙時)分析該sql執行計畫

說明：如果該表資料量特別大，比如上億，加入allPlansExecution參數會執行的非常慢，謹慎線上上資料庫執行(我是在測試資料庫執行的)。

db.集合.find({ version: true, icial: true, stVal: { $gte: 20 }, gender: "f" }).sort({ goals: 1, diff: 1 }).explain("allPlansExecution")

……"gender": 1, "icial": 1, "stVal": 1, "version": 1

 [

{

"stage" : "FETCH",

"filter" : {

"icial" : {

"$eq" : true

}

},

"inputStage" : {

"stage" : "IXSCAN",

"keyPattern" : {

"gender" : 1,

"goals" : 1,

"diff" : 1,

"stVal" : 1,

"version" : -1

},

"indexName" : "gender_1_goals_1_diff_1_stVal_1_version_-1",

"isMultiKey" : false,

"direction" : "forward",

……

}

]

……

索引沒有正確添加：執行計畫

"executionStats" : {

"executionSuccess" : true,

"nReturned" : 10,  實際返回行數

"executionTimeMillis" : 2000,執行的毫秒

"totalKeysExamined" : 3030000,掃描索引行數

"totalDocsExamined" : 2910000,掃描文檔行數

而且有filter過濾操作(即回表操作)。目前該sql選擇了gender_1_goals_1_diff_1_stVal_1_version_-1索引。

4.建議

結合業務分析，該sql在業務中每天執行了997090次；分析了該業務和相關sql後，決定違反mongodb建議的聯合索引最多5個列的限制：

建議建立如下索引：

db.集合.createIndex({gender:1,version:1,icial:1,goals:1,diff:1,stVal:1},{background:true});

我這邊大概執行了90分鐘(業務不繁忙時執行的，這邊業務晚上比較忙。。。)

再次執行執行計畫

……

{

"stage" : "FETCH",

"inputStage" : {

"stage" : "IXSCAN",

"keyPattern" : {

"gender" : 1,

"version" : 1,

"icial" : 1,

"goals" : 1,

"diff" : 1,

"stVal" : 1

},

"indexName" : "gender_1_version_1_icial_1_goals_1_diff_1_stVal_1",

"isMultiKey" : false,

"direction" : "forward",

       ……

}

}

……

"executionStats" : {

"executionSuccess" : true,

"nReturned" : 10,

"executionTimeMillis" : 0,

"totalKeysExamined" : 10,

"totalDocsExamined" : 10,

訪問資料量明顯減少了30W倍左右。

在業務實現中使用了hint提示。

建立索引建議：先做等值查詢，在做排序，在做範圍查詢。

本文出自 “九指神丐” 部落格，請務必保留此出處http://beigai.blog.51cto.com/8308160/1768353

MongoDB涉及的業務比較慢--慢查詢最佳化分析案例--以及參數說明