數據庫最 重要是的為前臺應用服務。 在眾多決定應用性能的因素中， 如何快速有效從后臺讀取數據很大程度上地影響到最終效果。本文將對不同的數據往返(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倍，幾個就慢好幾十倍了。