实际环境是一个黄页数据库,170万左右数据,两个主要数据表,其中一个表压力特别大,查询有时候很慢。
很多动态的东西,生成静态页面比较烦,squid呢,又有比较多的更新,总之是懒。思路一直想从数据库本身着手。
今天简单测试了下,主要是简单对比下,测试过程和方法都欠严谨,发出来做参考。
数据库结构如下: CREATE TABLE IF NOT EXISTS `list` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`cate` tinyint(3) unsigned NOT NULL,
`subcate` smallint(5) unsigned NOT NULL,
`province` tinyint(5) unsigned NOT NULL,
`city` smallint(12) unsigned NOT NULL,
`name` varchar(42) COLLATE gb2312_bin NOT NULL,
PRIMARY KEY (`id`),
KEY `cate` (`cate`),
KEY `subcate` (`subcate`),
KEY `province` (`province`),
KEY `city` (`city`)
) ENGINE=MyISAM ;
sql语句
| 结果数 | 返回值 | mysql | pgsql | 较优 | select count(id) as count from list | Null | 1659811 | Null | 15ms | Null | select count(id) as count from list where province=17 | Null | 198930 | Null | 188ms | Null | select count(id) as count from list where cate=35 | Null | 122973 | Null | 94ms | Null | select count(id) as count from list where province=17 | Null | 23196 | Null | 109ms | Null | select * from list limit 10000,100 | 100 | / | 4ms | 15ms | mysql | select * from list limit 800000,100 | 100 | / | 278ms | 94ms | pgsql | select * from list limit 1600000,100 | 100 | / | 560ms | 188ms | pgsql | select * from list where province=17 limit 10000,100 | 100 | / | 44ms | 31ms | pgsql | select * from list where province=17 limit 90000,100 | 100 | / | 394ms | 93ms | pgsql | select * from list where province=17 limit 190000,100 | 100 | / | 854ms | 156ms | pgsql | select * from list where cate=35 limit 10000,100 | 100 | / | 45ms | 47ms | mysql | select * from list where cate=35 limit 60000,100 | 100 | / | 271ms | 63ms | pgsql | select * from list where cate=35 limit 120000,100 | 100 | / | 536ms | 109ms | pgsql | select * from list where province=17 and cate=35 limit 1000,100 | 100 | / | 5ms | 31ms | mysql | select * from list where province=17 and cate=35 limit 12000,100 | 100 | / | 223ms | 78ms | pgsql | select * from list where province=17 and cate=35 limit 23000,100 | 100 | / | 553ms | 109ms | pgsql |
注:1、mysql与pgsql语法的少许不一致,如limit用法的不同,上述以mysql为准
2、mysql时间以phpmyadmin为准,pgsql时间以pgAdmin III时间为准
3、测试时间以首次运行为准,运行前均优化或者整理数据表
4、测试环境为windows xp sp2,T7500 CPU+2G内存
测试的16个sql语句就是目前瓶颈所在。对于我来说,可能pgsql更适合点。:mrgreen:
测试结果:http://docs.google.com/Doc?docid=0AbxR-4BqL-UpZGhqanoyOV8wZ2dtamNrZjU |