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意外宕机期间日志没有落盘的可能。
