MySQL vs MongoDB 1000读取

我对MongoDb非常兴奋，最近一直在测试它。我在MySQL中有一个叫posts的表，大约有2000万条记录，索引只在一个名为“id”的字段上。

我想与MongoDB比较速度，我运行了一个测试，从我们巨大的数据库中随机获取并打印15条记录。我为mysql和MongoDB分别运行了大约1000次查询，我很惊讶我没有注意到速度上有很大的差异。也许MongoDB快1.1倍。这太令人失望了。我做错什么了吗?我知道我的测试并不完美，但当涉及到读取密集的杂务时，MySQL与MongoDb不相上下。

注意:

我有双核+(2线程)i7 cpu和4GB ram 我在MySQL上有20个分区，每个分区有100万条记录

用于测试MongoDB的示例代码

<?php
function microtime_float()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}
$time_taken = 0;
$tries = 100;
// connect
$time_start = microtime_float();

for($i=1;$i<=$tries;$i++)
{
    $m = new Mongo();
    $db = $m->swalif;
    $cursor = $db->posts->find(array('id' => array('$in' => get_15_random_numbers())));
    foreach ($cursor as $obj)
    {
        //echo $obj["thread_title"] . "<br><Br>";
    }
}

$time_end = microtime_float();
$time_taken = $time_taken + ($time_end - $time_start);
echo $time_taken;

function get_15_random_numbers()
{
    $numbers = array();
    for($i=1;$i<=15;$i++)
    {
        $numbers[] = mt_rand(1, 20000000) ;

    }
    return $numbers;
}

?>

测试MySQL的示例代码

<?php
function microtime_float()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}
$BASE_PATH = "../src/";
include_once($BASE_PATH  . "classes/forumdb.php");

$time_taken = 0;
$tries = 100;
$time_start = microtime_float();
for($i=1;$i<=$tries;$i++)
{
    $db = new AQLDatabase();
    $sql = "select * from posts_really_big where id in (".implode(',',get_15_random_numbers()).")";
    $result = $db->executeSQL($sql);
    while ($row = mysql_fetch_array($result) )
    {
        //echo $row["thread_title"] . "<br><Br>";
    }
}
$time_end = microtime_float();
$time_taken = $time_taken + ($time_end - $time_start);
echo $time_taken;

function get_15_random_numbers()
{
    $numbers = array();
    for($i=1;$i<=15;$i++)
    {
        $numbers[] = mt_rand(1, 20000000);

    }
    return $numbers;
}
?>

当前回答

https://github.com/reoxey/benchmark

基准

MySQL和MongoDB在GOLANG1.6和PHP5中的速度比较

用于基准测试的系统:DELL cpu i5第四代1.70Ghz * 4 ram 4GB GPU ram 2GB

RDBMS与NoSQL的INSERT, SELECT, UPDATE, DELETE执行不同数量的行10,100,1000,10000,100000,1000000的速度比较

用于执行的语言是:PHP5 &谷歌最快的语言GO 1.6

________________________________________________
GOLANG with MySQL (engine = MyISAM)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
            INSERT
------------------------------------------------
num of rows             time taken
------------------------------------------------
10                      1.195444ms
100                     6.075053ms
1000                    47.439699ms
10000                   483.999809ms
100000                  4.707089053s
1000000                 49.067407174s


            SELECT
------------------------------------------------
num of rows             time taken
------------------------------------------------
1000000                 872.709µs


        SELECT & DISPLAY
------------------------------------------------
num of rows             time taken
------------------------------------------------
1000000                 20.717354746s


            UPDATE
------------------------------------------------
num of rows             time taken
------------------------------------------------
1000000                 2.309209968s
100000                  257.411502ms
10000                   26.73954ms
1000                    3.483926ms
100                     915.17µs
10                      650.166µs


            DELETE
------------------------------------------------
num of rows             time taken
------------------------------------------------
1000000                 6.065949ms
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^


________________________________________________
GOLANG with MongoDB
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
            INSERT
------------------------------------------------
num of rows             time taken
------------------------------------------------
10                      2.067094ms
100                     8.841597ms
1000                    106.491732ms
10000                   998.225023ms
100000                  8.98172825s
1000000                 1m 29.63203158s


            SELECT
------------------------------------------------
num of rows             time taken
------------------------------------------------
1000000                 5.251337439s


        FIND & DISPLAY (with index declared)
------------------------------------------------
num of rows             time taken
------------------------------------------------
1000000                 21.540603252s


            UPDATE
------------------------------------------------
num of rows             time taken
------------------------------------------------
1                       1.330954ms
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

________________________________________________
PHP5 with MySQL (engine = MyISAM)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
            INSERT
------------------------------------------------
num of rows             time taken
------------------------------------------------
 10                     0.0040680000000001s
 100                    0.011595s
 1000                   0.049718s
 10000                  0.457164s
 100000                 4s
 1000000                42s


            SELECT
------------------------------------------------
num of rows             time taken
------------------------------------------------
 1000000                <1s


            SELECT & DISPLAY
------------------------------------------------
num of rows             time taken
------------------------------------------------
  1000000               20s
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

________________________________________________
PHP5 with MongoDB 
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
            INSERT
------------------------------------------------
num of rows             time taken
------------------------------------------------
10                      0.065744s
100                     0.190966s
1000                    0.2163s
10000                   1s
100000                  8s
1000000                 78s


            FIND
------------------------------------------------
num of rows             time taken
------------------------------------------------
1000000                 <1s


            FIND & DISPLAY
------------------------------------------------
num of rows             time taken
------------------------------------------------
1000000                 7s


            UPDATE
------------------------------------------------
num of rows             time taken
------------------------------------------------
1000000                 9s

2016-06-06 09:54:29

其他回答

答案是你基本上是在测试PHP而不是数据库。

不要费心迭代结果，不管是否注释掉打印结果。还有很多时间。

   foreach ($cursor as $obj)
    {
        //echo $obj["thread_title"] . "<br><Br>";
    }

而另一部分则是花在一堆兰特数字上。

function get_15_random_numbers()
{
    $numbers = array();
    for($i=1;$i<=15;$i++)
    {
        $numbers[] = mt_rand(1, 20000000) ;

    }
    return $numbers;
}

然后有一个主要的区别b/w内爆和在。

最后是这里发生了什么。看起来像是每次都创建一个连接，因此它测试连接时间加上查询时间。

$m = new Mongo();

$db = new AQLDatabase();

因此，对于去除jazz的底层查询，您的101%的速度可能会提高1000%。

呃。

2014-01-02 08:13:09

在单服务器上，给定表/doc, MongoDb在读写方面不会比mysql MyISAM更快大小从1gb到20gb不等。在多节点集群上，MonoDB在并行缩减(Parallel Reduce)上速度更快，而Mysql不能水平扩展。

2013-10-03 20:21:42

来源:https://github.com/webcaetano/mongo-mysql

10行

mysql insert: 1702ms
mysql select: 11ms

mongo insert: 47ms
mongo select: 12ms

100行

mysql insert: 8171ms
mysql select: 10ms

mongo insert: 167ms
mongo select: 60ms

1000行

mysql insert: 94813ms (1.58 minutes)
mysql select: 13ms

mongo insert: 1013ms
mongo select: 677ms

10.000行

mysql insert: 924695ms (15.41 minutes)
mysql select: 144ms

mongo insert: 9956ms (9.95 seconds)
mongo select: 4539ms (4.539 seconds)

2015-09-16 16:56:21

这里有一个小研究，探讨了RDBMS vs NoSQL使用MySQL vs Mongo，结论是一致的@Sean Reilly的回应。简而言之，好处来自于设计，而不是一些原始的速度差异。35-36页结论:

RDBMS vs NoSQL:性能和伸缩性比较

The project tested, analysed and compared the performance and scalability of the two database types. The experiments done included running different numbers and types of queries, some more complex than others, in order to analyse how the databases scaled with increased load. The most important factor in this case was the query type used as MongoDB could handle more complex queries faster due mainly to its simpler schema at the sacrifice of data duplication meaning that a NoSQL database may contain large amounts of data duplicates. Although a schema directly migrated from the RDBMS could be used this would eliminate the advantage of MongoDB’s underlying data representation of subdocuments which allowed the use of less queries towards the database as tables were combined. Despite the performance gain which MongoDB had over MySQL in these complex queries, when the benchmark modelled the MySQL query similarly to the MongoDB complex query by using nested SELECTs MySQL performed best although at higher numbers of connections the two behaved similarly. The last type of query benchmarked which was the complex query containing two JOINS and and a subquery showed the advantage MongoDB has over MySQL due to its use of subdocuments. This advantage comes at the cost of data duplication which causes an increase in the database size. If such queries are typical in an application then it is important to consider NoSQL databases as alternatives while taking in account the cost in storage and memory size resulting from the larger database size.

2017-04-11 17:43:46