數(shù)據(jù)庫最 重要是的為前臺(tái)應(yīng)用服務(wù)。 在眾多決定應(yīng)用性能的因素中， 如何快速有效從后臺(tái)讀取數(shù)據(jù)很大程度上地影響到最終效果。本文將對(duì)不同的數(shù)據(jù)往返(round-trip)讀取進(jìn)行比較和歸納總結(jié)。最后的結(jié)果非常出人意 料。往往在時(shí)間緊迫的情況下，我們會(huì)本能地使用最簡(jiǎn)單的方法來完成任務(wù)，但是這種編譯習(xí)慣會(huì)讓我們的前臺(tái)應(yīng)用的性能大打折扣。
返回 15,000 條數(shù)據(jù)：這個(gè)測(cè)試會(huì)從一個(gè)表格里面讀取15000條數(shù)據(jù)。我們通過用三種不同的編譯方式來看如何提高數(shù)據(jù)庫提取的效率。
以下這個(gè)腳本用來創(chuàng)建表格然后放入一百萬條數(shù)據(jù)。因?yàn)槲覀冃枰銐蚨嗟臄?shù)據(jù)來完成3個(gè)測(cè)試，每個(gè)測(cè)試讀取新鮮的數(shù)據(jù)，所以創(chuàng)建了一百萬條。我創(chuàng)建的這個(gè) 列表每15000條數(shù)據(jù)一小組，這樣確保了測(cè)試讀取15000條數(shù)據(jù)的準(zhǔn)確性。不會(huì)因?yàn)閿?shù)據(jù)的不同，而影響測(cè)試的結(jié)果。
這個(gè)腳本稍作修改就可以放在MS SQL服務(wù)器上跑：
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
|
|
測(cè)試－：基本代碼
最簡(jiǎn)單的代碼就是通過一個(gè)直白的查詢語句跑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";
測(cè)試二：準(zhǔn)備語句（Prepared Statement） 這個(gè)代碼通過在循環(huán)前做一個(gè)準(zhǔn)備語句，雖然還是跑15000個(gè)往返，但是每次只是變化準(zhǔn)備語句的參數(shù)。
# 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";
|
測(cè)試三：一個(gè)往返
我們準(zhǔn)備一個(gè)語句命令去拿到所有15000條數(shù)據(jù)，然后把他們一次返回過來。
# 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";
|
結(jié)果
一共跑了5次，平均結(jié)果如下
基本 準(zhǔn)備 一次往返
~1.800 秒 ~1.150 秒 ~0.045 秒
相比基本代碼，最后一個(gè)一次往返的邏輯快了大概40倍，比用準(zhǔn)備語句快了25倍左右。
服務(wù)器和語言是否會(huì)影響性能呢？
這個(gè)測(cè)試是在PHP/PostgresSQL上做的。其他語言和服務(wù)器上會(huì)不會(huì)得到不同的結(jié)果呢？如果是同樣的硬件，有可能這個(gè)數(shù)據(jù)絕對(duì)值會(huì)有所差異，但是相對(duì)的差距應(yīng)該是差不多。從一個(gè)往返里面讀取所有要索引的數(shù)據(jù)條比人和多次往返的語句都要快。
活用活學(xué)
這次測(cè)試最顯而易見的結(jié)論就是任何多于一條數(shù)據(jù)的索引都應(yīng)該使用這個(gè)方法。實(shí)際上，我們應(yīng)該把這個(gè)設(shè)置為默認(rèn)語法，除非有絕好的理由。那么有哪些好理由呢？
我跟我們的程序員聊過，有一位同學(xué)說：“你看，我們的應(yīng)用每次都是只要20－100個(gè)數(shù)據(jù)。絕對(duì)不會(huì)多了。我實(shí)在想 象不出20－100個(gè)數(shù)據(jù)的讀取值得翻新所有代碼。”所以我聽了以后，又去試了一下，實(shí)際上是這個(gè)方法確實(shí)只有100以上的才能看見顯著區(qū)別。在20的時(shí) 候，幾乎沒有區(qū)別。到了100, 一次往返的比基本的快6倍，比第二種方法快4倍。所以，使用與否的判斷在于個(gè)人。
但是這里還有一個(gè)要考慮的因素是有多少同時(shí)進(jìn)行的讀取在進(jìn)行。如果你的系統(tǒng)是基于實(shí)時(shí)的設(shè)計(jì)，那么就有可能是不同的情況。我們這個(gè)測(cè)試是基于一個(gè)用戶，如果是多個(gè)用戶同時(shí)讀取，這種用戶行為會(huì)帶給數(shù)據(jù)庫一些額外的負(fù)擔(dān)，我們需要一個(gè)更加宏觀的環(huán)境來比較。
還有一個(gè)反對(duì)的聲音有可能是“我們是從不同的表格里面讀取數(shù)據(jù)。我們?cè)谀承┚€程上我們走一條條的，需要從不同的表格里面一條條讀取。”如果是這樣的話，你絕對(duì)需要使用一次往返，用幾個(gè)JOIN一次拿到。如果一個(gè)表格，都是慢10倍，幾個(gè)就慢好幾十倍了。