数据库最 重要是的为前台应用服务。 在众多决定应用性能的因素中， 如何快速有效从后台读取数据很大程度上地影响到最终效果。本文将对不同的数据往返(round-trip)读取进行比较和归纳总结。最后的结果非常出人意 料。往往在时间紧迫的情况下，我们会本能地使用最简单的方法来完成任务，但是这种编译习惯会让我们的前台应用的性能大打折扣。
返回 15,000 条数据：这个测试会从一个表格里面读取15000条数据。我们通过用三种不同的编译方式来看如何提高数据库提取的效率。
以下这个脚本用来创建表格然后放入一百万条数据。因为我们需要足够多的数据来完成3个测试，每个测试读取新鲜的数据，所以创建了一百万条。我创建的这个 列表每15000条数据一小组，这样确保了测试读取15000条数据的准确性。不会因为数据的不同，而影响测试的结果。
这个脚本稍作修改就可以放在MS SQL服务器上跑：
createtabletest000 (
intpkintprimarykey
,fillerchar(40)
)
-- BLOCK 1, first 5000 rows
-- pgAdmin3: run as pgScript
-- All others: modify as required
--
declare@x,@y;
set@x = 1;
set@y = string(40,40,1);
while @x <= 5000begin
insertintotest000 (intpk,filler)
values((@x-1)*200 +1,'@y');
set@x = @x + 1;
end
-- BLOCK 2, put 5000 rows aside
--
select *intotest000_tempfromtest000
-- BLOCK 3, Insert the 5000 rows 199 more
-- times to get 1million altogether
-- pgAdmin3: run as pgScript
-- All others: modify as required
--
declare@x;
set@x = 1;
while @x <= 199begin
insertintotest000 (intpk,filler)
selectintpk+@x,fillerfromtest000_temp;
set@x = @x + 1;
end
|
|
测试－：基本代码
最简单的代码就是通过一个直白的查询语句跑15000次往返。
# Make adatabaseconnection
$dbConn = pg_connect("dbname=roundTrips user=postgres");
# Program 1, Individual explicit fetches
$x1 = rand(0,199)*5000 + 1;
$x2 = $x1 + 14999;
echo"\nTest 1, using $x1 to $x2";
$timeBegin = microtime(true);
while ($x1++ <= $x2) {
$dbResult = pg_exec("select * from test000 where intpk=$x1");
$row = pg_fetch_array($dbResult);
}
$elapsed = microtime(true)-$timeBegin;
echo"\nTest 1, elapsed time: ".$elapsed;
echo"\n";
测试二：准备语句（Prepared Statement） 这个代码通过在循环前做一个准备语句，虽然还是跑15000个往返，但是每次只是变化准备语句的参数。
# Make a database connection
$dbConn = pg_connect("dbname=roundTrips user=postgres");
# Program 2, Individual fetches with prepared statements
$x1 = rand(0,199)*5000 + 1;
$x2 = $x1 + 14999;
echo "\nTest 2, using $x1 to $x2";
$timeBegin = microtime(true);
$dbResult = pg_prepare("test000","select * from test000 where intpk=$1");
while ($x1++ <= $x2) {
$pqResult = pg_execute("test000",array($x1));
$row = pg_fetch_all($pqResult);
}
$elapsed = microtime(true)-$timeBegin;
echo "\nTest 2, elapsed time: ".$elapsed;
echo "\n";
|
测试三：一个往返
我们准备一个语句命令去拿到所有15000条数据，然后把他们一次返回过来。
# Make a database connection
$dbConn = pg_connect("dbname=roundTrips user=postgres");
# Program 3, One fetch, pull all rows
$timeBegin = microtime(true);
$x1 = rand(0,199)*5000 + 1;
$x2 = $x1 + 14999;
echo "\nTest 3, using $x1 to $x2";
$dbResult = pg_exec(
"select * from test000 where intpk between $x1 and $x2"
);
$allRows = pg_fetch_all($dbResult);
$elapsed = microtime(true)-$timeBegin;
echo "\nTest 3, elapsed time: ".$elapsed;
echo "\n";
|
结果
一共跑了5次，平均结果如下
基本 准备 一次往返
~1.800 秒 ~1.150 秒 ~0.045 秒
相比基本代码，最后一个一次往返的逻辑快了大概40倍，比用准备语句快了25倍左右。
服务器和语言是否会影响性能呢？
这个测试是在PHP/PostgresSQL上做的。其他语言和服务器上会不会得到不同的结果呢？如果是同样的硬件，有可能这个数据绝对值会有所差异，但是相对的差距应该是差不多。从一个往返里面读取所有要索引的数据条比人和多次往返的语句都要快。
活用活学
这次测试最显而易见的结论就是任何多于一条数据的索引都应该使用这个方法。实际上，我们应该把这个设置为默认语法，除非有绝好的理由。那么有哪些好理由呢？
我跟我们的程序员聊过，有一位同学说：“你看，我们的应用每次都是只要20－100个数据。绝对不会多了。我实在想 象不出20－100个数据的读取值得翻新所有代码。”所以我听了以后，又去试了一下，实际上是这个方法确实只有100以上的才能看见显著区别。在20的时 候，几乎没有区别。到了100, 一次往返的比基本的快6倍，比第二种方法快4倍。所以，使用与否的判断在于个人。
但是这里还有一个要考虑的因素是有多少同时进行的读取在进行。如果你的系统是基于实时的设计，那么就有可能是不同的情况。我们这个测试是基于一个用户，如果是多个用户同时读取，这种用户行为会带给数据库一些额外的负担，我们需要一个更加宏观的环境来比较。
还有一个反对的声音有可能是“我们是从不同的表格里面读取数据。我们在某些线程上我们走一条条的，需要从不同的表格里面一条条读取。”如果是这样的话，你绝对需要使用一次往返，用几个JOIN一次拿到。如果一个表格，都是慢10倍，几个就慢好几十倍了。