MongoDB的SQL优化
一、MongoDB查询优化器
1、MongoDB查询优化器
1)MongoDB查询优化器会选择最优的一条执行计划来执行SQL。
2)查询优化器会缓存那些有多条可用索引的SQL的执行计划条目
2、查询优化器原理
1)对于每个SQL,查询优化器会先在在执行计划缓存中查找执行计划
2)如果没有匹配到相应的执行计划,查询优化器会生成备选执行计划,并评估他们各自的消耗,选择中最佳执行计划,并将23)这些执行计划放入缓存中
3)MongoDB根据最优执行计划返回结果
4)如果匹配到可用的执行计划,MongoDB会通过replanning的机制再次评估当前执行计划的性能
5)如果评估成功,使用该执行计划返回结果
6)如果评估失败,查询优化器重复2)操作,最终选择最优执行计划返回结果
3、执行计划缓存刷新机制
1)删除集合或者索引会重新刷新执行计划缓存
2)执行计划缓存在MongoDB重启后会失效
3)MongoDB2.6版本之后可以执行db.collection.getPlanCache().clear()手动刷新执行计划缓存
二、执行计划解析
1、语法
方法一:
db.collection.find().explain() 查看help帮助文档: db.collection.explain().help()
方法二:
db.collection.explain().find() 查看help帮助文档: db.collection.explain().find().help()
2、执行计划的三种模式
queryPlanner Mode:只会显示 winning plan 的 queryPlanner,自建MongoDB默认模式 executionStats Mode:只会显示 winning plan 的 queryPlanner + executionStats allPlansExecution Mode:会显示所有执行计划的 queryPlanner + executionStats,阿里云MongoDB默认模式
不论哪种模式下,查看一个SQL的执行计划,都是通过查询优化器来判断的,对于所有写操作,查询执行计划只会限制起操作的消耗,不会执行操作进行变更。
3、Mongodb 执行计划解析
MongoDB执行计划主要分为两个部分:queryPlanner、executionStats
示例:
db.user_skin_plan_goods.find({"userId": "$numberLong(4971923)", "hidden": 0, "skinPlanId": "$numberLong(45391626)", "entityMid": "f44cb34fc0e0e51d52d271da35946b90"}).explain() [ { v: 1, key: { _id: 1 }, name: "_id_", ns: "bevol_test.user_skin_plan_goods" }, { v: 1, key: { userId: 1.0 }, name: "userId_1", ns: "bevol_test.user_skin_plan_goods" }, { v: 1, key: { hidden: 1.0 }, name: "hidden_1", ns: "bevol_test.user_skin_plan_goods" } ]
1、queryPlanner
queryPlanner主要有三大部分:parsedQuery、winningPlan、rejectedPlans。
我们关注比较多的是winningPlan,查看SQL当前执行走了什么索引
queryPlanner: { plannerVersion: 1, namespace: "bevol_test.entity_like_find", //database.collection indexFilterSet: false, //针对该query是否有indexfilter parsedQuery: { //执行计划解析 }, winningPlan: { //最终执行计划 }, rejectedPlans: [ //竞争执行计划 ] }
1)parsedQuery - SQL解析
该部分解析了SQL的所有过滤条件
parsedQuery: { $and: [ { entityMid: { $eq: "f44cb34fc0e0e51d52d271da35946b90" } }, { hidden: { $eq: 0.0 } }, { skinPlanId: { $eq: "$numberLong(45391626)" } }, { userId: { $eq: "$numberLong(4971923)" } } ] },
2)winningPlan - SQL最终选择的执行计划
winningPlan可以分三部分来看:stage、filter、inputStage
stage:说明该步骤的类型
COLLSCAN:集合扫描 IXSCAN:扫描索引键 FETCH:检索文档 SHARD_MERGE:合并分片的结果 PROJECTION:指定显示的字段 ENSURE_SORTED:
filter:需要扫描文档进行过滤的条件
inputStage:
winningPlan: { stage: "FETCH", //通过返回的index位置去检索过滤文档 filter: { //需要扫描文档进行过滤的条件 $and: [ { entityMid: { $eq: "f44cb34fc0e0e51d52d271da35946b90" } }, { hidden: { $eq: 0.0 } }, { skinPlanId: { $eq: "$numberLong(45391626)" } } ] }, inputStage: { //子stage,为其父stage提供文档和索引关键字 stage: "IXSCAN", //索引检索 keyPattern: { userId: 1.0 //索引字段 }, indexName: "userId_1", //索引名字 isMultiKey: false, //是否为多键索引 isUnique: false, //是否为唯一索引 isSparse: false, //是否为稀疏索引 isPartial: false, //是否为部分索引 indexVersion: 1, //索引版本 direction: "forward", //查询顺序 -- 暂时不理解 indexBounds: { //索引范围 userId: [ "["$numberLong(4971923)", "$numberLong(4971923)"]" ] } } },
3)rejectedPlans - 被淘汰的执行计划
2、executionStats
最好的情况是:nReturned = totalKeysExamined = totalDocsExamined
executionStats: { executionSuccess: true, //是否执行成功 nReturned: 0, //返回记录行数 executionTimeMillis: 1, //整体执行时间 ms totalKeysExamined: 0, //索引扫描数 totalDocsExamined: 0, //文档扫描数 executionStages: { stage: "FETCH", //通过返回的索引进行文档检索 filter: { $and: [ { entityMid: { $eq: "f44cb34fc0e0e51d52d271da35946b90" } }, { hidden: { $eq: 0.0 } }, { skinPlanId: { $eq: "$numberLong(45391626)" } } ] }, nReturned: 0, //返回行数 executionTimeMillisEstimate: 0, //通过返回索引记录检索文档所用时间 ms works: 2, //查询期间所操作的工作单元个数 advanced: 0, //优先返回给父stage的中间结果集中文档个数 needTime: 0, // needYield: 0, //请求查询阶段暂停处理并产生锁定的次数 saveState: 0, //查询阶段暂停处理并保存其当前执行状态的次数,例如准备产生锁定 restoreState: 0, //查询阶段恢复已保存的执行状态的次数,例如在恢复先前已生成的锁之后。 isEOF: 1, invalidates: 0, docsExamined: 0, //文档扫描 alreadyHasObj: 0, inputStage: { stage: "IXSCAN", //检索索引 nReturned: 0, executionTimeMillisEstimate: 0, //扫描索引所用时间 ms works: 1, advanced: 0, needTime: 0, needYield: 0, saveState: 0, restoreState: 0, isEOF: 1, invalidates: 0, keyPattern: { userId: 1.0 //使用索引 }, indexName: "userId_1", //索引相关信息 isMultiKey: false, isUnique: false, isSparse: false, isPartial: false, indexVersion: 1, direction: "forward", indexBounds: { userId: [ "["$numberLong(4971923)", "$numberLong(4971923)"]" ] }, keysExamined: 0, //索引扫描数 dupsTested: 0, dupsDropped: 0, seenInvalidated: 0 } }, allPlansExecution:[ //被淘汰的执行计划的执行效率 ] }
3、serverInfo 服务器信息
阿里云MonogoDB实例上其实将这个信息隐藏掉了。
“serverInfo”:{ “host”:<string>, //主机 “port”:<int>, //端口 “version”:<string>, //版本 “gitVersion”:<string> }
4、indexFilterSet
indexFilter仅仅决定对于该查询MongoDB可选择的索引是由什么决定的。若indexFilterSet为true,说明该查询只能选择indexFilter设置的一些可选索引,最终选择使用哪个索引由优化器决定;若indexFilterSet=false,说明该查询可以选择该集合所有的索引,最终选择使用哪个索引由优化器确定。
1)如何设置indexFilter
db.runCommand( { planCacheSetFilter: <collection>, //需要创建indexFilter集合 query: <query>, //指定哪类查询使用indexFilter sort: <sort>, //排序条件 projection: <projection>, //查询字段 indexes: [ <index1>, <index2>, ...] //indexFilter可使用索引 } )
2)如何删除indexFilter
db.runCommand( { planCacheClearFilters: <collection>, //指定集合 query: <query pattern>, //指定查询类别 sort: <sort specification>, //排序条件 projection: <projection specification> //查询字段 } )
3)如何查看一个集合所有的indexFilter
db.runCommand( { planCacheListFilters: <collection> } )
4)示例:
集合数据如下:
> db.scores.find() { "_id" : ObjectId("523b6e32fb408eea0eec2647"), "userid" : "newbie" } { "_id" : ObjectId("523b6e61fb408eea0eec2648"), "userid" : "abby", "score" : 82 } { "_id" : ObjectId("523b6e6ffb408eea0eec2649"), "userid" : "nina", "score" : 90 } { "_id" : ObjectId("5d303213cd8afaa592e23990"), "userid" : "AAAAAAA", "score" : 43 } { "_id" : ObjectId("5d303213cd8afaa592e23991"), "userid" : "BBBBBBB", "score" : 34 } { "_id" : ObjectId("5d303213cd8afaa592e23992"), "userid" : "CCCCCCC" } { "_id" : ObjectId("5d303213cd8afaa592e23993"), "userid" : "DDDDDDD" } > db.scores.createIndex({userid:1,score:1})
创建indexFilter:
> db.runCommand( ... { ... planCacheSetFilter: "scores", ... query: { userid: "abby" }, ... indexes: [ ... { "userid" : 1, "score" : 1}, ... ] ... } ... ) { "ok" : 1 }
查看执行计划:
> db.scores.find({userid: "abbyc"}).explain() { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.scores", "indexFilterSet" : true, //表示使用了indexFilter指定的索引 "parsedQuery" : { "userid" : { "$eq" : "abbyc" } }, "winningPlan" : { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "userid" : 1, "score" : 1 }, "indexName" : "userid_1_score_1", "isMultiKey" : false, "multiKeyPaths" : { "userid" : [ ], "score" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "userid" : [ "[\"abbyc\", \"abbyc\"]" ], "score" : [ "[MinKey, MaxKey]" ] } } }, "rejectedPlans" : [ ] }, "serverInfo" : { "host" : "dbslave2", "port" : 28002, "version" : "4.0.10-5", "gitVersion" : "7dab0a3a7b7b40cf71724b5a11eff871f8c3885c" }, "ok" : 1 }
5)注意点
当使用index filter的时候,使用hint强制走index filter之外的索引会失效,
4、stage类型
stage的类型:
COLLSCAN:全表扫描 IXSCAN:索引扫描 FETCH:根据索引去检索指定document SHARD_MERGE:将各个分片返回数据进行merge SORT:表明在内存中进行了排序 LIMIT:使用limit限制返回数 SKIP:使用skip进行跳过 IDHACK:针对_id进行查询 SHARDING_FILTER:通过mongos对分片数据进行查询 COUNT:利用db.coll.explain().count()之类进行count运算 COUNTSCAN:count不使用Index进行count时的stage返回 COUNT_SCAN:count使用了Index进行count时的stage返回 SUBPLA:未使用到索引的$or查询的stage返回 TEXT:使用全文索引进行查询时候的stage返回 PROJECTION:限定返回字段时候stage的返回
对于普通查询,我希望看到stage的组合(查询的时候尽可能用上索引):
Fetch+IDHACK Fetch+ixscan Limit+(Fetch+ixscan) PROJECTION+ixscan SHARDING_FITER+ixscan COUNT_SCAN
如下的stage效率比较低下:
COLLSCAN(全表扫描),SORT(使用sort但是无index),不合理的SKIP,SUBPLA(未用到index的$or),COUNTSCAN(不使用index进行count)
三、如何排查MongoDB性能问题
1、对于当前正在发生的情况
1)查看当前会话情况,抓取正在慢的SQL
db.currentOp() 或者 db.currentOp( { "active" : true, "secs_running" : { "$gt" : 3 }, "ns" : /^db1\./ } )
重点关注:
client #请求是由哪个客户端发起 opid #操作的opid,可以通过 db.killOp(opid) 直接杀掉会话 secs_running/microsecs_running #这个值重点关注,代表请求运行的时间,如果这个值特别大,就得注意了,看看请求是否合理 query/ns: #这个能看出是对哪个集合正在执行什么操作 lock*: #还有一些跟锁相关的参数
2)查看问题SQL执行计划
db.collection.find().explain()
2、对于历史问题
1)查看慢日志以及运行日志
如何调整慢日志参数:
查看当前慢日志设置参数 > db.getProfilingStatus() //查看当前慢日志参数状态 { "was" : 1, "slowms" : 100 } 动态修改慢日志参数 > db.setProfilingLevel(1,10) //动态修改参数 { "was" : 1, "slowms" : 100, "ok" : 1 } > db.getProfilingStatus() { "was" : 1, "slowms" : 10 }
慢日志参数解释:
was: 慢日志模式 0:不开启慢日志 1:开启慢日志,只记录超过一定阈值的慢SQL 2:开启慢日志,记录所有操作 slowms:慢SQL阈值,单位为ms
查看慢日志信息:
> db.system.profile.find().sort({$natrual: -1}) //查询最近慢日志记录
2)查看问题SQL执行计划
db.collection.find().explain()
四、读写操作的一些优化
1、查询优化
1)建立合适索引
1.在选择性较好的字段建立索引 2.单列索引不需要考虑升降序,但是复合索引可以根据业务需求创建对应升降序的复合索引 3.覆盖索引查询,查询和过滤的所有字段都在复合索引中 db.inventory.createIndex( { type: 1, item: 1 } ) db.inventory.find( { type: "food", item:/^c/ }, { item: 1, _id: 0 } ) //需要强制_id字段不显示以保证走覆盖索引查询
2)使用limit限定返回结果,减轻网络开销
3)需要哪些字段查询哪些字段
4)使用hint强制走指定索引
2、写操作优化
1)集合上的索引会增加该集合写入/更新操作的资源消耗,适度创建索引
MMAPv1存储引擎中,当一个update操作所需要的空间超过了原本分配的空间时,MMAPv1存储引会将该文档移动到磁盘上一个新的位置,并全部将该集合的索引进行更新指向新的文档位置,整个过程是非常消耗资源的。
从MongoDB 3.0开始,MonogoDB使用 Power of 2 Sized Allocations,保证MongoDB尽量对空间的空间重用,尽量减少重新分配空间位置的发生。
2)硬件优化,固态SSD的性能要优于HDDs
3)合理设置journal相关参数
1.journal日志实现日志预写功能,开启journal保证了数据的持久化,但也存在一定的性能消耗 2.尽量将数据文件与journal日志文件放在不同的磁盘喜爱,避免I/O资源争用,提高写操作能力 3.j:true参数会增加写操作的负载,根据业务实际情况合理使用write concern参数 4.设置合理的commitIntervalMs参数 减小该参数会减少日志提交的时间间隔、增加写操作的数量,但是会限制MongoDB写能力。 增大该参数会增加日志提交的时间间隔、减少写操作的数量,但是增加了MongoDB意外宕机期间日志没有落盘的可能。