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提示。

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