2013版：PostgreSql和mysql的插入，检索对比

网上最近的的资料也有好几年了，我重新测试一下吧：

一、环境：
（一）各软件版本：FreeBSD9.2，ports安装的PostgreSql9.3.1 和 Mysql5.5.33，用php测试，php版本为5.3.37，直接用命令行测试。至强Intel(R) Xeon(R) CPU E3-1240 V2 @ 3.40GHz，4G内存，500G SATA硬盘。
        mysql用的huge.cnf，PostgreSql仅添加了shared_buffers = 800MB。

（二）数据表：test，两个数据库都建一个专门的数据表，数据库放到同一下硬盘，同一个目录下，分别建子目录。先测试不带索引的情况，然后再建索引的情况。三个字段：id：int(6)，name:varchar(10)，text：varchr(250)。

（三）代码：注释掉的几行，是为了测试各种情形，防止干扰，pgsql和mysql没有放在一起比较，分成两个文件。
（1）pgsql：

<?php

$time_start = microtime(true);

$conn = pg_connect("dbname=test user=test password=test");

if (!$conn) {

    echo "An error occured.\n";
    exit;
}

$sql = 'BEGIN';
//pg_query($conn,$sql);

for($i=0;$i<=2000;$i++){
        $name_str = "zhang" . $i;
        $text_str = str_pad("The string is ", $i%200 , "d");
        //$sql = "INSERT INTO test( id,name,text) VALUES (" . $i .",'" . $name_str . "','" . $text_str ."')";
        //$sql = "SELECT * from test WHERE text = '" . $text_str . "'";
        $sql = "SELECT * from test WHERE id = " . $i ;
        $result = pg_query($conn,$sql);

        if (!$result) {
                echo "An error occured!" ;
                die("__");
        }

//      $rows = pg_fetch_all($result);

}

$sql = 'COMMIT';
//pg_query($conn,$sql);
$time_end = microtime(true);
$time = $time_end - $time_start;

$sql = "TRUNCATE test";
//pg_query($conn,$sql);

echo "\ntime is $time \n";


?>

（二）mysql

<?php

$time_start = microtime(true);

$conn = mysql_connect('localhost', 'test', 'test');
mysql_select_db("test");

if (!$conn) {

    echo "An error occured.\n";
    exit;
}

for($i=0;$i<=20000;$i++){
        $name_str = "zhang" . $i;
        $text_str = str_pad("The string is ", $i%200 , "d");
//      $sql = "INSERT INTO test( id,name,text) VALUES (" . $i .",'" . $name_str . "','" . $text_str ."')";
//        $sql = "SELECT * from test WHERE text = '" . $text_str . "'";
        $sql = "SELECT * from test WHERE id = " . $i ;
        $result = mysql_query($sql);

        if (!$result) {
                echo "An error occured!" ;
                die("__");
        }
}

$time_end = microtime(true);
$time = $time_end - $time_start;

$sql = "TRUNCATE test";
//mysql_query($sql);

echo "\ntime is $time \n";
?>

二、结果
（一）插入两万条记录：
pgsql第一列，为先写了begin，最后加上commit，相当于插入的时候忽略rollback功能，等效于myisam；

pgsql with begin	pgsql without begin	mysql with innodb	mysql with myisam
1.380398035	12.74742222	28.3199389	1.045269966
1.036226988	12.58433795	22.8662529	0.854581118
0.989035845	12.25525784	24.40100813	0.85179615
0.979285955	12.29677892	22.71812797	1.10293889
0.983509064	12.57599187	24.45858788	0.857715845

插入时磁盘的IO情况：

项目	pgsql	mysql with innodb
KB/t	32.23	32.22
tps	3237	3507
MB/s	102	110
%busy	74	80

（二）不带索引，检索text列，20000次：

查询大字符串
4.421151876	3.629865885	3.07119298
4.166283846	1.738970995	1.731379032
4.162822008	1.738083839	1.739454031
4.161303043	1.738959074	1.738510132
4.159893036	1.735637903	1.730456829
4.158488989	1.731363058	1.730481863

（三）检索带索引的id列：

索引id    2000次	20000次	20000次	20000次
0.415557146	1.524525881	1.396705866	1.199653864
0.127171993	1.247123957	0.376204967	0.374560833
0.125981808	1.249945879	0.37511301	0.368445873
0.125764847	1.246695995	0.373888969	0.368315935
0.125821114	1.250742912	0.373873949	0.366961002
0.127412081	1.254354954	0.374575138	0.371459961

三、总结：
1、简单的插入和查询，pgsql没有占到便宜，可能还有优化的空间。
2、在插入的时候，pgsql在带事务的情况下，性能还是比innodb强一些。在不带事务的时候，跟myisam也有一拼了。这两者可能都受到磁盘的影响。磁盘已经达到100MB/s，基本上sata硬盘的峰值了。
3、mysql查询的缓存好像做的比较好，pgsql这方面要差一些。

四、未尽事宜：
1、数据表还可以再大一些。
2、join和view可能更能考验数据表。
3、全文检索？




----续
插入2m个记录：
Postgresql：
98.395066976547

innodb 20分钟没完成，直接^+C了。

mysqlisam：
96.253983974457

查询，text字段 ,2000次，从2m记录中
Postgresql：
442.35978007317

Mysql with myisam：
1053.6569011211


查询,id字段
1.4101810455322
1.1692430973053


---Mysql
1.5967738628387
0.37165594100952