MongoDB学习记录07-查询优化

来源：互联网发布：中国10大网络作家编辑：程序博客网时间：2024/06/06 12:22

概念

查询优化是找出慢查询，找出为什么查询会很慢，逐步让查询变快的一个过程.

准备工作

先导入一些测试数据
http://mng.bz/ii49 下载一个stocks.zip 下载 /home/no1下

解压

[no1@localhost ~]$ unzip stocks.zip [no1@localhost bin]$ ./mongorestore -h 127.0.0.1:27018 -d stocks /home/no1/dump/stocks

切换到mongodb安装目录进行导入

[no1@localhost ~]$ cd /usr/local/mongodb-linux-x86_64-3.4.4/bin/[no1@localhost bin]$ ./mongorestore -d stocks -c values /home/no1/dump/stocks

识别慢查询

执行查询语句

db.values.find({"stock_symbol":"GOOG"}).sort({ "date":-1 }).limit(1);

查看mongodb的日志文件可以看到一个警告

2017-05-05T17:33:04.428+0800 I COMMAND  [conn61] command stocks.values command: find { find: "values", filter: { stock_symbol: "GOOG" }, sort: { date: -1 }, limit: 1, batchSize: 101 } planSummary: COLLSCAN keysExamined:0 docsExamined:4308303 hasSortStage:1 cursorExhausted:1 numYields:33779 nreturned:1 reslen:278 locks:{ Global: { acquireCount: { r: 67560 } }, Database: { acquireCount: { r: 33780 } }, Collection: { acquireCount: { r: 33780 } } } protocol:op_query 5262ms

在正常的执行查询语句是不会出现这样的语句的，只有当查询时间超出了100m才会出现在日志中，这里可看到该警告中说明完成该查询所用5062ms也就是5秒之久.

使用剖析器
上面方式太麻烦了而且日志的内容会不断扩展，可读性不强.好在mongoDB提供了剖析器来解决这个问题，
默认情况下profile被分配了128kb，因此确保了不会消耗太多资源.

1：通过mongo shell:查看状态：级别和时间db.getProfilingStatus()   查看级别db.getProfilingLevel()    设置级别db.setProfilingLevel(2)0：关闭，不收集任何数据。1：收集慢查询数据，默认是100毫秒。2：收集所有数据设置级别和时间db.setProfilingLevel(1,200)以上要操作要是在集合下面的话，只对该集合里的操作有效，要是需要对整个实例有效，则需要在所有的集合下设置或则在开启的时候开启参数：2：不通过mongo shell:mongod --profile=1 --slowms=15或则在配置文件里添加2行：profile = 1slowms = 300

剖析的结果会存在 db.system.profile表里面
可以根据查询耗时进行排序

db.system.profile.find().sort({ millis:-1 });

返回最近的10条记录

db.system.profile.find().limit(10).sort({ ts : -1 }).pretty()

删除system.profile集合

db.system.profile.drop()

返回所有的操作，除command类型的

db.system.profile.find( { op: { $ne : 'command' } } )

profile结构如下:

{    "op" : "query",         #操作类型，有insert、query、update、remove、getmore、command       "ns" : "stocks.values", #操作的集合    "query" : {             #查询语句        "find" : "values",          "filter" : {        },        "sort" : {            "close" : -1        },        "limit" : 1,        "batchSize" : 101    },    "keysExamined" : 0,    "docsExamined" : 4308303,    "hasSortStage" : true,    "cursorExhausted" : true,    "numYield" : 33827,    "locks" : {        "Global" : {            "acquireCount" : {                "r" : NumberLong("67656")            }        },        "Database" : {            "acquireCount" : {                "r" : NumberLong("33828")            }        },        "Collection" : {            "acquireCount" : {                "r" : NumberLong("33828")            }        }    },    "nreturned" : 1,    "responseLength" : 278,    "protocol" : "op_query",    "millis" : 5415,           #查询消耗时间    "planSummary" : "COLLSCAN",    "execStats" : {        "stage" : "SORT",        "nReturned" : 1,        "executionTimeMillisEstimate" : 5190,        "works" : 4308308,        "advanced" : 1,        "needTime" : 4308306,        "needYield" : 0,        "saveState" : 33827,        "restoreState" : 33827,        "isEOF" : 1,        "invalidates" : 0,        "sortPattern" : {            "close" : -1        },        "memUsage" : 182,        "memLimit" : 33554432,        "limitAmount" : 1,        "inputStage" : {            "stage" : "SORT_KEY_GENERATOR",            "nReturned" : 4308303,            "executionTimeMillisEstimate" : 4145,            "works" : 4308306,            "advanced" : 4308303,            "needTime" : 2,            "needYield" : 0,            "saveState" : 33827,            "restoreState" : 33827,            "isEOF" : 1,            "invalidates" : 0,            "inputStage" : {                "stage" : "COLLSCAN",                "nReturned" : 4308303,                "executionTimeMillisEstimate" : 1342,                "works" : 4308305,                "advanced" : 4308303,                "needTime" : 1,                "needYield" : 0,                "saveState" : 33827,                "restoreState" : 33827,                "isEOF" : 1,                "invalidates" : 0,                "direction" : "forward",                "docsExamined" : 4308303            }        }    },    "ts" : ISODate("2017-05-08T02:22:51.715Z"),    "client" : "192.168.7.237",    "allUsers" : [ ],    "user" : ""},

分析慢查询

使用 explain()

explain 操作提供了查询信息，使用索引及查询统计等。有利于我们对索引的优化。

在mongodb3.0以后
修改后的explain（）需要填写参数。”queryPlanner”, “executionStats”, “allPlansExecution”，如果不填写默认值 ”queryPlanner”

db.values.find({}).sort({ close:-1 }).limit(1).explain();

{    "queryPlanner" : {        "plannerVersion" : 1,        "namespace" : "stocks.values",        "indexFilterSet" : false,        "parsedQuery" : {        },        "winningPlan" : {            "stage" : "SORT",            "sortPattern" : {                "close" : -1            },            "limitAmount" : 1,            "inputStage" : {                "stage" : "SORT_KEY_GENERATOR",                "inputStage" : {                    "stage" : "COLLSCAN",                    "direction" : "forward"                }            }        },        "rejectedPlans" : [ ]    },    "serverInfo" : {        "host" : "localhost.localdomain",        "port" : 27018,        "version" : "3.4.4",        "gitVersion" : "888390515874a9debd1b6c5d36559ca86b44babd"    },    "ok" : 1}

db.values.find({}).sort({ close:-1 }).limit(1).explain("executionStats")

{    "queryPlanner" : {        "plannerVersion" : 1,        "namespace" : "stocks.values",        "indexFilterSet" : false,        "parsedQuery" : {        },        "winningPlan" : {            "stage" : "SORT",            "sortPattern" : {                "close" : -1            },            "limitAmount" : 1,            "inputStage" : {                "stage" : "SORT_KEY_GENERATOR",                "inputStage" : {                    "stage" : "COLLSCAN",                    "direction" : "forward"                }            }        },        "rejectedPlans" : [ ]    },    "executionStats" : {        "executionSuccess" : true,        "nReturned" : 1,        "executionTimeMillis" : 5578,        "totalKeysExamined" : 0,        "totalDocsExamined" : 4308303,        "executionStages" : {            "stage" : "SORT",            "nReturned" : 1,            "executionTimeMillisEstimate" : 5326,            "works" : 4308308,            "advanced" : 1,            "needTime" : 4308306,            "needYield" : 0,            "saveState" : 33830,            "restoreState" : 33830,            "isEOF" : 1,            "invalidates" : 0,            "sortPattern" : {                "close" : -1            },            "memUsage" : 182,            "memLimit" : 33554432,            "limitAmount" : 1,            "inputStage" : {                "stage" : "SORT_KEY_GENERATOR",                "nReturned" : 4308303,                "executionTimeMillisEstimate" : 4090,                "works" : 4308306,                "advanced" : 4308303,                "needTime" : 2,                "needYield" : 0,                "saveState" : 33830,                "restoreState" : 33830,                "isEOF" : 1,                "invalidates" : 0,                "inputStage" : {                    "stage" : "COLLSCAN",                    "nReturned" : 4308303,                    "executionTimeMillisEstimate" : 1197,                    "works" : 4308305,                    "advanced" : 4308303,                    "needTime" : 1,                    "needYield" : 0,                    "saveState" : 33830,                    "restoreState" : 33830,                    "isEOF" : 1,                    "invalidates" : 0,                    "direction" : "forward",                    "docsExamined" : 4308303                }            }        }    },    "serverInfo" : {        "host" : "localhost.localdomain",        "port" : 27018,        "version" : "3.4.4",        "gitVersion" : "888390515874a9debd1b6c5d36559ca86b44babd"    },    "ok" : 1}

具体参考文档 https://docs.mongodb.com/manual/reference/method/db.collection.explain/#explain-method-verbosity

使用hint

虽然MongoDB查询优化器一般工作的很不错，但是也可以使用 hint 来强制 MongoDB 使用一个指定的索引

0 0