postgresql和mysql速度测试

测试机器：赛扬II 1G/512M内存/7200转磁盘。
操作系统：WINDOWS SERVER 2003 和 Linux（UBUNTU 7.10)。Linux使用XFS文件系统。
下面是Linux记录。
数据库接口：C接口，PG用libpq，MYSQL用libmysql/libmysqlclient。
速度计时：采用time.h里面的gettimeofday函数，计时精度为1ms。
PG关闭fsync。
MYSQL关闭查询缓存。
测试时关闭其它占用CPU的程序。

表items：含42个字段，共1090980行。
itemid(int)主键
cid(int)分类ID
cityid(int)城市ID
...其它字段省略。

测试1：输入itemid找到一行记录
select * from items where itemid=...
取出整行－－－－PG－－－－MYSQL
－－－－－－－－2.3ms－－1.3ms
取出主键ID－－－PG－－－－MYSQL
－－－－－－－－1.6－－－－0.8
取出一个普通字段PG－－－－MYSQL
－－－－－－－－1.6－－－－0.85
MYSQL的速度还是比PG快些。

测试2：无where条件输出一页（40条记录）。
说明：PG由于会随机输出，不排序会更慢而且速度不稳定，所以按主键排序。MYSQL按排序比不排序慢，所以不排序。
PG: select * from items order by itemid limit 40 offset ...
MYSQL: select * from items limit 40 offset ...
offset值－－－－PG整行－－MYSQL整行－－PG取主键－－MYSQL取主键－－PG取普通字段－－MYSQL取普通字段(排序)
0－－－－－－－4.9－－－－4.1－－－－－－－1.7－－－0.84－－－－－－－2.4－－－－－－2.3(1.3)
200－－－－－－5.0－－－－5.1－－－－－－－1.9－－－1.04－－－－－－－3.3－－－－－－3.4(3.Cool
400－－－－－－5.9－－－－7.5－－－－－－－2.9－－－1.2－－－－－－－－4.2－－－－－－5.8(6.1)
800－－－－－－6.6－－－－9.8－－－－－－－3.6－－－1.6－－－－－－－－5.9－－－－－－8(10.Cool
2000－－－－－－8.2－－－－19－－－－－－－5.6－－－2.6－－－－－－－－11.4－－－－－－18(25)
4000－－－－－－10.7－－－35－－－－－－－8.6－－－4.4－－－－－－－－24－－－－－－－33(48)
20000－－－－－39－－－－167－－－－－－－43－－－－19－－－－－－－－103－－－－－－165(236)
随着所取记录的增加，PG的性能比MYSQL越来越好。对于PG，这里有条重要的规律值得注意，如果选取的字段只是全部字段其中一部分，当所取记录的增加到一定数量时反而不如取出全部字段快。

测试3：两个where条件输出一页（40条记录）。
说明：PG由于会随机输出，不排序会更慢而且速度不稳定，所以按主键排序。MYSQL按主键排序比不排序慢，所以不排序。
PG: select * from items where cid=11 and cityid=201 order by itemid limit 40 offset ...
MYSQL: select * from items where cid=11 and cityid=201 limit 40 offset ...
offset值－－－－PG－－－－MYSQL（两者都选取全部字段）
0－－－－－－－5.9－－－－4.1
200－－－－－－6.3－－－－7.2
400－－－－－－6.9－－－－10.4
800－－－－－－8.4－－－－16.6
2000－－－－－－11.5－－－34
4000－－－－－－18－－－－64
除了输出前面的一两页外，PG全面胜出。而且，PG可以顺排序或逆序，MYSQL如果逆序（和索引相反）则性能进一步降低，我就不列出来了。

测试4：把一页结果记录join到一个关联的表。当把表按垂直拆分时，查询就会用到这种语句。
为了简化处理我把大约70行记录存放到一个result表中。这些记录非常随机，轻微设置offset值就导致重新读取索引。
select i.itemid,cid,cityid from items i inner join result r on i.itemid=r.itemid limit 40 offset ...
offset值－－－－PG－－－－MYSQL
0－－－－－－－4.2－－－－3.2
10－－－－－－－4.6－－－－－3.1
首次都需要较长的时间，上面列的是第二次查询的时间。这条查询很耗内存。

测试5：统计记录数。
全表统计：select count(itemid) from items
条件统计：select count(itemid) from items where cid=...
类型－－－－－－PG－－－－－MYSQL
大表全表统计－－9609－－－－0.56
条件统计-------10585-------发生错误
小表全表统计－－PG－－－－－MYSQL
－－－－－－－－1162－－－－1730
大表是指109万记录的有42个字段的表，小表指109万记录有两个字段的表。
MYSQL大表是MYSQL原有表增量导入记录到109万，MYSQL直接得出总记录数，但按条件统计时错误（上次测试时50万记录能够按条件统计）。小表是由外部程序用ODBC导入的，还不清楚为何MYSQL要扫描全表来统计。

测试6：更新一条记录。
update items set click=click+1 where itemid=...
MYSQL一般只要0.9ms即可更新一行。PG要几毫秒到几十毫秒。对于这项测试，我还在寻找改进PG的办法。

对于本次测试和上次测试：上次测试PG有40万记录，现在109万，上次MYSQL有54万，现在109万。
对于PG，即使使用原来那讨测试程序来测试，PG在分页测试中的性能也是不降反升，主要得益于删除了旧索引并重新建立索引。
对于MYSQL，也删除旧索引重新建立，但仍看得出，数据量增加对MYSQL的多方面性能有负面影响。在用单表支持大数据量方面，PG拥有优势。