測試mysql的綁定變量功能
一般編寫連接配接oracle資料庫的程式時,都會使用綁定變量技術,oracle的綁定變量功能為為它增色不少,大大提高了資料庫的性能,在資料庫優化的時候也能獲得更多更詳細的資訊。
在關于mysql是否支援綁定變量功能,和mysql的綁定變量功能是不是雞肋的說法和讨論比較多,今天我就做個小實驗,測試下php+mysql的綁定變量功能是否能夠提高web和mysql的性能,提高的比率有多少。
測試方法,主要采取,在綁定變量前後對PHP腳本的執行時間的測試,和pfiles工具對sql語句執行的資源使用情況統計。
測試環境如下:
RHEL5.4
mysql> select version();
+------------+
| version() |
| 5.1.44-log |
[root xxx]# php -version
PHP 5.2.10 with Suhosin-Patch 0.9.7 (cli) (built: Aug 13 2010 09:14:57)
Copyright (c) 1997-2009 The PHP Group
Zend Engine v2.2.0, Copyright (c) 1998-2009 Zend Technologies
with Zend Extension Manager v1.2.2, Copyright (c) 2003-2007, by Zend Technologies
with Zend Optimizer v3.3.3, Copyright (c) 1998-2007, by Zend Technologies
綁定變量測試,插入10條記錄句執行10次,每次資料庫所用時間如下(使用profile計算,見mysqli_stmt.php):
0.000102
0.000093
0.000094
0.00013
0.0001
0.000138
0.000095
總共平均時間:0.0001058
使用linux time指令計算綁定變量的php腳本執行時間
real 0m0.039s
user 0m0.024s
sys 0m0.013s
real 0m0.041s
user 0m0.027s
sys 0m0.011s
user 0m0.025s
sys 0m0.012s
user 0m0.026s
real 0m0.040s
user 0m0.021s
sys 0m0.019s
user 0m0.029s
sys 0m0.010s
user 0m0.020s
sys 0m0.018s
sys 0m0.009s
sys 0m0.014s
平均執行時間:0.0395s
不使用綁定變量測試,插入10條記錄句執行10次,每次資料庫所用時間如下(使用profile計算,見mysqli_nobind.php):
0.000103
0.000104
0.000114
0.000119
0.000113
0.00012
平均執行時間:0.000112
使用linux time指令計算不綁定變量的php腳本執行時間:
user 0m0.028s
user 0m0.023s
sys 0m0.015s
平均執行時間(由于精度影響,是以看不出差别,也就是在網站壓力小的時候可以忽略):0.0395s
結論:(未綁定變量的sql平均執行時間-綁定變量sql的平均執行時間)/未綁定變量的執行時間=0.0553571428571429約為6%。
總體提升了6%左右的性能。
由于性能提升不是太大,加上時間敏感度太高,我就不接着往下挖掘在執行哪步驟出現的時間差别了,根據現有日志觀察,在開始執行語句以後的一切操作應該是
一樣的,包括打開表,獲得鎖資源,執行語句,記錄日志,釋放資源等。
不過對于綁定變量的可用性還是不容懷疑的,在大型系統上5%的性能提高已經很不錯了,加上綁定變量的安全性,可以很好的規範SQL語句的
驗證,避免自己單獨去寫驗證語句,推薦使用綁定變量。
一下是測試用的兩個PHP腳本,修改下資料庫賬号和密碼,就可以在本地使用。
[root@zj7 learn_php]# cat mysqli_stmt.php
<?php
$mysqli=new mysqli("localhost", "root", "123321", "test");
$sql1="set @@profiling=1";
$result1=$mysqli->query($sql1);
//準備好一條語句放到伺服器中,插入語句
$sql="insert into t(name,sex) values (?,?)";
$stmt=$mysqli->prepare($sql);
//給占位符号每個?号傳值(綁定參數) i d s b
$stmt->bind_param("si", $name, $sex);
$name="andy";
$sex=0;
//執行
$stmt->execute();
$name="mandy";
$sex=1;
$name="michael";
$name="happy";
$name="php";
$name="mysql";
$name="linux";
$name="oracle";
$name="unix";
$name="cisco";
$stmt->close();
$sql2="show profiles";
$result2=$mysqli->query($sql2);
echo '<table border=1 align="center" width=800>';
while($rows=$result2->fetch_assoc()){
echo '<tr align="center">';
foreach($rows as $value){
echo '<td>' . $value . '</td>';
}
echo '</tr>';
$i=0;
$i=$i+$rows["Duration"];
}
echo '</table>';
echo $i;
?>
[root@zj7 learn_php]# cat mysqli_nobind.php
$nobind=array("insert into t(name,sex) values ('andy',0)",
"insert into t(name,sex) values ('mandy',1)",
"insert into t(name,sex) values ('michael',0)",
"insert into t(name,sex) values ('happy',0)",
"insert into t(name,sex) values ('php',0)",
"insert into t(name,sex) values ('mysql,0)",
"insert into t(name,sex) values ('linux',0)",
"insert into t(name,sex) values ('oracle',0)",
"insert into t(name,sex) values ('cisco',0)",
"insert into t(name,sex) values ('unix',0)");
foreach($nobind as $sql){
echo $sql;
$result=$mysqli->query($sql);
$mysqli->close();