MongoDB与MySQL性能-简单查询 [英] MongoDB vs MySQL Performance - Simple Query

问题描述

我正在对mongodb和mysql进行比较，并将mysql数据导入mongodb集合(> 500000条记录)中. 该集合看起来像这样:

I am doing a comparison of mongodb with respect to mysql and imported the mysql data into the mongodb collection (>500000 records). the collection looks like this:

{
    "_id" : ObjectId(""),
    "idSequence" : ,
    "TestNumber" : ,
    "TestName" : "",
    "S1" : ,
    "S2" : ,
    "Slottxt" : "",
    "DUT" : ,
    "DUTtxt" : "",
    "DUTver" : "",
    "Voltage" : ,
    "Temperature" : ,
    "Rate" : ,
    "ParamX" : "",
    "ParamY" : "",
    "Result" : ,
    "TimeStart" : new Date(""),
    "TimeStop" : new Date(""),
    "Operator" : "",
    "ErrorNumber" : ,
    "ErrorText" : "",
    "Comments" : "",
    "Pos" : ,
    "SVNURL" : "",
    "SVNRev" : ,
    "Valid" : 
}

比较查询时(均返回15条记录):

When comparing the queries (which both return 15 records):

mysql -> SELECT TestNumber FROM db WHERE Valid=0 AND DUT=68 GROUP BY TestNumber

使用

mongodb -> db.results.distinct("TestNumber", {Valid:0, DUT:68}).sort()

结果是相同的，但是从mongodb花费(iro)17秒，而从mysql花费0.03秒.

The results are equivalent, but it takes (iro) 17secs from mongodb, compared with 0.03 secs from mysql.

我意识到很难在两种数据库体系结构之间进行比较，并且我进一步意识到mongodb admin的一项技能是相应地组织数据结构(因此，仅导入mysql结构不是一项公平的测试)Ref: MySQL vs MongoDB 1000次读取

I appreciate that it is difficult to make a comparison between the two db architectures and i further appreciate one of the skills of mongodb admin is to organise the data structure accordingly (therefore it is not a fair test to just import the mysql structure) Ref: MySQL vs MongoDB 1000 reads

但是返回差值的时间太长了，不能成为调整问题. 我的(默认)mongodb日志文件显示为:

But the time to return difference is too great to be a tuning issue. My (default) mongodb log file reads:

3月5日星期三04:56:36.415 [conn4089]命令NTV_Results.$ cmd命令:{不同:结果"，键:"TestNumber"，查询:{有效:0.0，DUT:68.0}} ntoreturn:1键更新:0 numYields:6个锁(micros)r:21764672 reslen:250 16525ms

Wed Mar 05 04:56:36.415 [conn4089] command NTV_Results.$cmd command: { distinct: "results", key: "TestNumber", query: { Valid: 0.0, DUT: 68.0 } } ntoreturn:1 keyUpdates:0 numYields: 6 locks(micros) r:21764672 reslen:250 16525ms

我也尝试过查询:

db.results.group( {
               key: { "TestNumber": 1 },
               cond: {"Valid": 0, "DUT": 68 },
               reduce: function ( curr, result ) { },
               initial: { }
            } )

具有类似的结果(17秒)，关于我在做什么错的任何线索? 两项服务都在具有Windows 7和16Gb RAM的同一octo-core i7 3770台式机上运行.

With similar (17 seconds) results, any clues as to what I am doing wrong? Both services are running on the same octo-core i7 3770 desktop PC with Windows 7 and 16Gb RAM.

推荐答案

性能下降的原因可能很多，其中有太多细节需要介绍.但我可以照原样为您提供入门包".

There can be many reasons for slow performance, much of which is too much detail to go into here. But I can offer you a "starter pack" as it were.

在Valid和DUT字段上创建索引改善这些查询和其他查询的结果.在这种情况下，可以使用 ensureIndex 命令

Creating Indexes on your Valid and DUT fields are going to improve results for these and other queries. Consider this compound form this case using the ensureIndex command

db.collection.ensureIndex({ "Valid": 1, "DUT": 1})

对于这些情况，建议使用聚合操作类型:

Also the use of aggregate is recommended for these types of operations:

db.collection.aggregate([
    {$match: { "Valid": 0, "DUT": 68 }},
    {$group: { _id: "$TestNumber" }}
])

应该等同于您所引用的SQL.

Should be the equivalent of the SQL you are referring to.

有一个 SQL到聚合的映射图可能会给出您在思想上有一些帮助.同样值得您熟悉一下聚合运算符的不同之处，以便编写有效的查询.

There is a SQL to Aggregation Mapping Chart that may give you some assistance with the thinking. Also worth familiarizing yourself with the difference aggregation operators in order to write effective queries.

我花了很多年为高级任务编写非常复杂的SQL.对于各种解决问题的案例，我发现聚合框架充满了新鲜感.

I have spent many years writing very complex SQL for advanced tasks. And I find the aggregation framework a breath of fresh air for various problem solving cases.

值得您花时间学习.

也值得注意.您的默认" MongoDB日志文件正在报告这些操作，因为它们被认为是慢查询"，然后通过默认"引起您的注意.您还可以根据需要通过调整数据库探查器来满足您的需求.

Also worth noting. Your "default" MongoDB log file is reporting those operations because they are considered to be "slow queries" and are then brought to your attention by "default". You can also see more or less information, as you require by tuning the database profiler to meet your needs.

这篇关于MongoDB与MySQL性能-简单查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！