MongoDB的SQL优化

太阳11个月前技术文章607

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


相关文章

kubernetes调度和调度器

一、Kubernetes调度Scheduler 是 kubernetes 的调度器,主要的任务是把定义的 pod 分配到集群的节点上。听起来非常简单,但有很多要考虑的问题:公平:如何保证每个节点都能被...

Redis 大 key

1、bigkeysRedis提供了 bigkeys 参数能够使 redis-cli 以遍历的方式分析 Redis 实例中的所有 Key ,并返回 Key 的整体统计信息与每个数据类型中 Top1 的大...

podman相关使用

Podman介绍Podman 是一个开源的容器运行时项目,可在大多数 Linux 平台上使用。Podman 提供与 Docker 非常相似的功能。正如前面提到的那样,它不需要在你的系统上运行任何守护进...

Flink sql 集成hive metastore-测试

Flink sql 集成hive metastore-测试

FQA1、如何不使用catalog命令,默认进入hive catalog在sql-client-defaults.yaml中添加以下配置2、如何使用mysql使用./bin/sql-client.sh...

CDH实操--客户端安装

CDH实操--客户端安装

概述安装CDH客户端,主要是方便在CDH部署节点以外,通过客户端的方式连接CDH上的hdfs,hive和hbase服务1、安装jdk(适配CDH即可,一般1.8)2、获取安装包3、部署安装包把安装包解...

Linux解锁线程基本概念和线程控制,步入多线程学习的大门(1)

Linux解锁线程基本概念和线程控制,步入多线程学习的大门(1)

1、线程初识1.1线程的概念线程是进程内部的一个执行分支,线程是CPU调度的基本单位那什么是进程呢?我们之前学习了解到的进程是加载到内存中的程序进程 = 内核数据结构 + 进程代码和数据。今天我们要推...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。