MongoDB 慢查询语句优化分析策略

2022-06-30 513

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 MongoDB，独享型 2核8GB

简介： MongoDB查询语句太慢了，开启 Profiling 功能进行分析后发现，问题其实很好解决，涨知识了

🍀MongoDB慢查询分析

开启 Profiling 功能，开启后会在运行的实例上收集有关MongoDB的写操作，游标，数据库命令等，可以在数据库级别开启该工具，也可以在实例级别开启。

该工具会把收集到的所有都写入到system.profile集合中，该集合是一个capped collection http://docs.mongodb.org/manual/tutorial/manage-the-database-profiler/

查询system.profile集合中，查询时间长的语句，比如执行超过200ms的
再通过.explain()解析影响行数，分析原因
优化查询语句 或 增加索引

🍀开启 Profiling 功能

mongo shell 中开启

进入mongo shell，输入以下指令开启

db.setProfilingLevel(2);

开启级别说明：
0：关闭，不收集任何数据。
1：收集慢查询数据，默认是100毫秒。
2：收集所有数据

如果在集合下操作，仅对该集合里的操作生效
在所有集合下面设置或者在启动mongodb时设置，则对整个实例生效

启动时开启

mongod --profile=1  --slowms=200

配置文件修改，正常启动

在配置文件里添加以下配置：

profile = 1
slowms = 200

其他指令

# 查看状态：级别和时间
db.getProfilingStatus()

# 查看级别
db.getProfilingLevel()

# 设置级别和时间
db.setProfilingLevel(1,200)

# 关闭Profiling
db.setProfilingLevel(0)

# 删除system.profile集合
db.system.profile.drop()

# 创建一个新的system.profile集合，大小为1M
db.createCollection( "system.profile", { capped: true, size:1000000 } )

# 重新开启Profiling
db.setProfilingLevel(1)

🍀通过`system.profile`进行分析

http://docs.mongodb.org/manual/reference /database-profiler/

通过 db.system.profile.find() 可查询记录的操作语句，如下的例子：

insert操作

{
    "op" : "insert",
    "ns" : "Gps905.onlineTemp",
    "command" : {
        "insert" : "onlineTemp",
        "ordered" : true,
        "$db" : "Gps905"
    },
    "ninserted" : 1,
    "keysInserted" : 1,
    "numYield" : 0,
    "locks" : {
        "Global" : {
            "acquireCount" : {
                "r" : NumberLong(1),
                "w" : NumberLong(1)
            }
        },
        "Database" : {
            "acquireCount" : {
                "w" : NumberLong(1)
            }
        },
        "Collection" : {
            "acquireCount" : {
                "w" : NumberLong(1)
            }
        }
    },
    "responseLength" : 60,
    "protocol" : "op_query",
    "millis" : 105,
    "ts" : ISODate("2022-06-29T08:41:51.858Z"),
    "client" : "127.0.0.1",
    "allUsers" : [],
    "user" : ""
}

其中重要字段含义如下
op：操作类型，有insert、query、update、remove、getmore、command
ns：操作的数据库和集合
millis：操作所花时间，毫秒
ts：时间戳

如果millis的值较大，就需要进行优化

比如query操作的例子

{
    "op" : "query",  #操作类型，有insert、query、update、remove、getmore、command   
    "ns" : "onroad.route_model", #操作的集合
    "query" : {
        "$query" : {
            "user_id" : 314436841,
            "data_time" : {
                "$gte" : 1436198400
            }
        },
        "$orderby" : {
            "data_time" : 1
        }
    },
    "ntoskip" : 0, #指定跳过skip()方法 的文档的数量。
    "nscanned" : 2, #为了执行该操作，MongoDB在 index 中浏览的文档数。 一般来说，如果 nscanned 值高于 nreturned 的值，说明数据库为了找到目标文档扫描了很多文档。这时可以考虑创建索引来提高效率。
    "nscannedObjects" : 1,  #为了执行该操作，MongoDB在 collection中浏览的文档数。
    "keyUpdates" : 0, #索引更新的数量，改变一个索引键带有一个小的性能开销，因为数据库必须删除旧的key，并插入一个新的key到B-树索引
    "numYield" : 1,  #该操作为了使其他操作完成而放弃的次数。通常来说，当他们需要访问还没有完全读入内存中的数据时，操作将放弃。这使得在MongoDB为了放弃操作进行数据读取的同时，还有数据在内存中的其他操作可以完成
    "lockStats" : {  #锁信息，R：全局读锁；W：全局写锁；r：特定数据库的读锁；w：特定数据库的写锁
        "timeLockedMicros" : {  #该操作获取一个级锁花费的时间。对于请求多个锁的操作，比如对 local 数据库锁来更新 oplog ，该值比该操作的总长要长（即 millis ）
            "r" : NumberLong(1089485),
            "w" : NumberLong(0)
        },
        "timeAcquiringMicros" : {  #该操作等待获取一个级锁花费的时间。
            "r" : NumberLong(102),
            "w" : NumberLong(2)
        }
    },
    "nreturned" : 1,  // 返回的文档数量
    "responseLength" : 1669, // 返回字节长度，如果这个数字很大，考虑值返回所需字段
    "millis" : 544, #消耗的时间（毫秒）
    "execStats" : {  #一个文档,其中包含执行 查询 的操作，对于其他操作,这个值是一个空文件， system.profile.execStats 显示了就像树一样的统计结构，每个节点提供了在执行阶段的查询操作情况。
        "type" : "LIMIT", ##使用limit限制返回数  
        "works" : 2,
        "yields" : 1,
        "unyields" : 1,
        "invalidates" : 0,
        "advanced" : 1,
        "needTime" : 0,
        "needFetch" : 0,
        "isEOF" : 1,  #是否为文件结束符
        "children" : [
            {
                "type" : "FETCH",  #根据索引去检索指定document
                "works" : 1,
                "yields" : 1,
                "unyields" : 1,
                "invalidates" : 0,
                "advanced" : 1,
                "needTime" : 0,
                "needFetch" : 0,
                "isEOF" : 0,
                "alreadyHasObj" : 0,
                "forcedFetches" : 0,
                "matchTested" : 0,
                "children" : [
                    {
                        "type" : "IXSCAN", #扫描索引键
                        "works" : 1,
                        "yields" : 1,
                        "unyields" : 1,
                        "invalidates" : 0,
                        "advanced" : 1,
                        "needTime" : 0,
                        "needFetch" : 0,
                        "isEOF" : 0,
                        "keyPattern" : "{ user_id: 1.0, data_time: -1.0 }",
                        "boundsVerbose" : "field #0['user_id']: [314436841, 314436841], field #1['data_time']: [1436198400, inf.0]",
                        "isMultiKey" : 0,
                        "yieldMovedCursor" : 0,
                        "dupsTested" : 0,
                        "dupsDropped" : 0,
                        "seenInvalidated" : 0,
                        "matchTested" : 0,
                        "keysExamined" : 2,
                        "children" : [ ]
                    }
                ]
            }
        ]
    },
    "ts" : ISODate("2022-06-29T08:41:51.858Z"), #该命令在何时执行
    "client" : "127.0.0.1", #链接ip或则主机
    "allUsers" : [
        {
            "user" : "martin_v8",
            "db" : "onroad"
        }
    ],
    "user" : ""
}

type字段的参数：

COLLSCAN #全表扫描
IXSCAN #索引扫描
FETCH #根据索引去检索指定document
SHARD_MERGE #将各个分片返回数据进行merge
SORT #表明在内存中进行了排序（与老版本的scanAndOrder:true一致）
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的返回

如果nscanned数很大，或者接近记录总数（文档数），那么可能没有用到索引查询，而是全表扫描。
如果 nscanned 值高于 nreturned 的值，说明数据库为了找到目标文档扫描了很多文档。这时可以考虑创建索引来提高效率。

☘筛选条件中的语句

# 返回大于100毫秒慢的操作
db.system.profile.find({ millis : { $gt : 100 } } ).pretty()

# 返回最近的10条记录 {$natrual: -1} 代表按插入数序逆序
db.system.profile.find().sort({ ts : -1 }).limit(10).pretty()

# 返回所有的操作，除command类型的
db.system.profile.find( { op: { $ne : 'command' } }).pretty()

# 返回特定集合
db.system.profile.find( { ns : 'mydb.test' } ).pretty()

# 从一个特定的时间范围内返回信息
db.system.profile.find({ ts : { $gt : new ISODate("2015-10-18T03:00:00Z"), $lt : new ISODate("2015-10-19T03:40:00Z")}}).pretty()

# 特定时间，限制用户，按照消耗时间排序
db.system.profile.find( { ts : { $gt : newISODate("2015-10-12T03:00:00Z") , $lt : newISODate("2015-10-12T03:40:00Z") } }, { user : 0 } ).sort( { millis : -1 } )

# 查看最新的 Profile  记录： 
db.system.profile.find().sort({$natural:-1}).limit(1)

# 列出最近5 条执行时间超过1ms的 Profile 记录
show profile

🍀explain 对执行语句进行分析

https://docs.mongodb.org/manual/reference/database-profiler/

同MySQL类似，MongoDB 也提供了一个 explain 命令获知系统如何处理查询请求。
以下利用 explain 命令，针对执行语句进行优化

SECONDARY> db.route_model.find({ "user_id" : 313830621, "data_time" : { "$lte" : 1443715200, "$gte" : 1443542400 } }).explain()
{
    "cursor" : "BtreeCursor user_id_1_data_time_-1",  #返回游标类型，有BasicCursor和BtreeCursor，后者意味着使用了索引。
    "isMultiKey" : false,
    "n" : 23, #返回的文档行数。
    "nscannedObjects" : 23,  #这是MongoDB按照索引指针去磁盘上查找实际文档的次数。如果查询包含的查询条件不是索引的一部分，或者说要求返回不在索引内的字段，MongoDB就必须依次查找每个索引条目指向的文档。
    "nscanned" : 23,  #如果有使用索引，那么这个数字就是查找过的索引条目数量，如果本次查询是一次全表扫描，那么这个数字就代表检查过的文档数目
    "nscannedObjectsAllPlans" : 46,
    "nscannedAllPlans" : 46,
    "scanAndOrder" : false,  #MongoDB是否在内存中对结果集进行了排序
    "indexOnly" : false, #MongoDB是否只使用索引就能完成此次查询
    "nYields" : 1,  #为了让写入请求能够顺利执行，本次查询暂停暂停的次数。如果有写入请求需求处理，查询会周期性的释放他们的锁，以便写入能够顺利执行
    "nChunkSkips" : 0,
    "millis" : 1530,  #数据库执行本次查询所耗费的毫秒数。这个数字越小，说明效率越高
    "indexBounds" : {  #这个字段描述了索引的使用情况，给出了索引的遍历范围
        "user_id" : [
            [
                313830621,
                313830621
            ]
        ],
        "data_time" : [
            [
                1443715200,
                1443542400
            ]
        ]
    },
    "server" : "a7cecd4f9295:27017",
    "filterSet" : false,
    "stats" : {
        "type" : "FETCH",
        "works" : 25,
        "yields" : 1,
        "unyields" : 1,
        "invalidates" : 0,
        "advanced" : 23,
        "needTime" : 0,
        "needFetch" : 0,
        "isEOF" : 1,
        "alreadyHasObj" : 0,
        "forcedFetches" : 0,
        "matchTested" : 0,
        "children" : [
            {
                "type" : "IXSCAN",#这里使用了索引
                "works" : 23,
                "yields" : 1,
                "unyields" : 1,
                "invalidates" : 0,
                "advanced" : 23,
                "needTime" : 0,
                "needFetch" : 0,
                "isEOF" : 1,
                "keyPattern" : "{ user_id: 1.0, data_time: -1.0 }",
                "boundsVerbose" : "field #0['user_id']: [313830621.0, 313830621.0], field #1['data_time']: [1443715200.0, 1443542400.0]",
                "isMultiKey" : 0,
                "yieldMovedCursor" : 0,
                "dupsTested" : 0,
                "dupsDropped" : 0,
                "seenInvalidated" : 0,
                "matchTested" : 0,
                "keysExamined" : 23,
                "children" : [ ]
            }
        ]
    }
}

分析可参考上诉system.profile分析

MongoDB 慢查询语句优化分析策略

🍀MongoDB慢查询分析

🍀开启 Profiling 功能

🍀通过`system.profile`进行分析

☘筛选条件中的语句

🍀explain 对执行语句进行分析

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MongoDB 慢查询语句优化分析策略

🍀MongoDB慢查询分析

🍀开启 Profiling 功能

🍀通过system.profile进行分析

☘筛选条件中的语句

🍀explain 对执行语句进行分析

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

🍀通过`system.profile`进行分析