mongodb 如何在mongodb中查找不使用索引或缓慢的查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18451681/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find queries not using indexes or slow in mongodb
提问by DmitrySemenov
is there a way to find queries in mongodb that are not using Indexes or are SLOW? In MySQL that is possible with the following settings inside configuration file:
有没有办法在 mongodb 中找到不使用索引或慢的查询？在 MySQL 中，可以在配置文件中进行以下设置：
log-queries-not-using-indexes = 1
log_slow_queries = /tmp/slowmysql.log
采纳答案by Stennie
The equivalent approach in MongoDB would be to use the query profilerto track and diagnose slow queries.
MongoDB 中的等效方法是使用查询分析器来跟踪和诊断慢查询。
With profiling enabled for a database, slow operations are written to the system.profilecapped collection (which by default is 1Mb in size). You can adjust the threshold for slow operations (by default 100ms) using the slowmsparameter.
为数据库启用分析后，慢速操作将写入system.profile上限集合（默认大小为 1Mb）。您可以使用slowms参数调整慢速操作的阈值（默认为 100 毫秒）。
回答by Rafael Eyng
First, you must set up your profiling, specifying what the log level that you want. The 3 options are:
首先，您必须设置分析，指定所需的日志级别。3个选项是：
- 0 - logger off
- 1 - log slow queries
- 2 - log all queries
- 0 - 记录器关闭
- 1 - 记录慢查询
- 2 - 记录所有查询
You do this by running your mongoddeamon with the --profileoptions:
您可以通过mongod使用以下--profile选项运行您的守护程序来完成此操作：
mongod --profile 2 --slowms 20
mongod --profile 2 --slowms 20
With this, the logs will be written to the system.profilecollection, on which you can perform queries as follows:
这样，日志将写入system.profile集合，您可以按如下方式执行查询：
- find all logs in some collection, ordering by ascending timestamp:
- 查找某个集合中的所有日志，按时间戳升序排序：
db.system.profile.find( { ns:/<db>.<collection>/ } ).sort( { ts: 1 } );
db.system.profile.find( { ns:/<db>.<collection>/ } ).sort( { ts: 1 } );
- looking for logs of queries with more than 5 milliseconds:
- 查找超过 5 毫秒的查询日志：
db.system.profile.find( {millis : { $gt : 5 } } ).sort( { ts: 1} );
db.system.profile.find( {millis : { $gt : 5 } } ).sort( { ts: 1} );
回答by Ori Dar
You can use the following two mongod options. The first option fails queries not using index (V 2.4 only), the second records queries slower than some ms threshold (default is 100ms)
您可以使用以下两个 mongod 选项。第一个选项使不使用索引的查询失败（仅限 V 2.4），第二个记录查询慢于某些 ms 阈值（默认为 100 毫秒）
--notablescan
Forbids operations that require a table scan.
--slowms <value>
Defines the value of “slow,” for the --profile option. The database logs all slow queries to the log, even when the profiler is not turned on. When the database profiler is on, mongod the profiler writes to the system.profile collection. See the profile command for more information on the database profiler.
回答by Mariano Ruiz
You can use the command line tool mongotailto read the log from the profiler within a console and with a more readable format.
您可以使用命令行工具mongotail在控制台内以更易读的格式从分析器读取日志。
First activate the profiler and set the threshold in milliseconds for the profile to consider an operation to be slow. In the following example the threshold is set to 10 milliseconds for a database named "sales":
首先激活分析器并为配置文件设置阈值（以毫秒为单位）以将操作视为缓慢。在以下示例中，名为“sales”的数据库的阈值设置为 10 毫秒：
$ mongotail sales -l 1
Profiling level set to level 1
$ mongotail sales -s 10
Threshold profiling set to 10 milliseconds
Then, to see in "real time"the slow queries, with some extra information like the time each query took, or how many registries it need to "walk" to find a particular result:
然后，要“实时”查看缓慢的查询，以及一些额外的信息，例如每个查询花费的时间，或者需要“遍历”多少个注册表才能找到特定结果：
$ mongotail sales -f -m millis nscanned docsExamined
2016-08-11 15:09:10.930 QUERY [ops] : {"deleted": {"$exists": false}, "prod_id": "367133"}. 8 returned. nscanned: 344502. millis: 12
2016-08-11 15:09:10.981 QUERY [ops] : {"deleted": {"$exists": false}, "prod_id": "367440"}. 6 returned. nscanned: 345444. millis: 12
....
回答by Brian Brownton
In case somebody ends up here from Google on this old question, I found that explainreally helped me fix specific queries that I could see were causing COLLSCANs from the logs.
如果有人在这个老问题上从谷歌结束，我发现这explain真的帮助我修复了我可以看到的导致COLLSCAN日志中的特定查询。
Example:
例子：
db.collection.find().explain()
db.collection.find().explain()
This will let you know if the query is using a COLLSCAN(Basic Cursor) or an index(BTree), among other things.
这将让您知道查询是使用COLLSCAN（基本光标）还是index（BTree）等。
https://docs.mongodb.com/manual/reference/method/cursor.explain/
https://docs.mongodb.com/manual/reference/method/cursor.explain/
回答by HackerNews
While you can obviously use Profiler a very neat feature of Mongo DB due to which I actually fall in love with it is Mongo DB MMS. Takes less than 60 seconds and can manage from anywhere. I am sure you will Love it. https://mms.mongodb.com/
虽然您显然可以使用 Profiler，但 Mongo DB 的一个非常简洁的功能是 Mongo DB MMS，因此我真的爱上了它。只需不到 60 秒，并且可以从任何地方进行管理。我相信你会喜欢它。 https://mms.mongodb.com/
