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