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

创建索引建议：先做等值查询，在做排序，在做范围查询。