测试mysql绑定变量功能

测试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.000102
0.000102
0.000094
0.00013
0.000102
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

real    0m0.039s
user    0m0.025s
sys     0m0.012s

real    0m0.039s
user    0m0.026s
sys     0m0.012s

real    0m0.040s
user    0m0.021s
sys     0m0.019s

real    0m0.040s
user    0m0.029s
sys     0m0.010s

real    0m0.039s
user    0m0.020s
sys     0m0.018s

real    0m0.040s
user    0m0.025s
sys     0m0.013s

real    0m0.039s
user    0m0.029s
sys     0m0.009s

real    0m0.040s
user    0m0.026s
sys     0m0.014s

平均执行时间：0.0395s

不使用绑定变量测试，插入10条记录句执行10次，每次数据库所用时间如下（使用profile计算，见mysqli_nobind.php）：
0.000103
0.000104
0.000114
0.000103
0.000119
0.000113
0.000114
0.000102
0.00012
0.00012
平均执行时间：0.000112

使用linux time命令计算不绑定变量的php脚本执行时间：
real    0m0.040s
user    0m0.026s
sys     0m0.014s

real    0m0.040s
user    0m0.027s
sys     0m0.011s

real    0m0.039s
user    0m0.028s
sys     0m0.009s

real    0m0.040s
user    0m0.027s
sys     0m0.011s

real    0m0.039s
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;

                //执行
                $stmt->execute();


                $name="michael";
                $sex=0;

                //执行
                $stmt->execute();


                $name="happy";
                $sex=1;

                //执行
                $stmt->execute();

                $name="php";
                $sex=1;

                //执行
                $stmt->execute();


                $name="mysql";
                $sex=1;

                //执行
                $stmt->execute();

                $name="linux";
                $sex=1;

                //执行
                $stmt->execute();

                $name="oracle";
                $sex=1;

                //执行
                $stmt->execute();

                $name="unix";
                $sex=1;

                //执行
                $stmt->execute();

                $name="cisco";
                $sex=1;

                //执行
                $stmt->execute();


                $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            
<?php
                $mysqli=new mysqli("localhost", "root", "123321", "test");

                $sql1="set @@profiling=1";
                $result1=$mysqli->query($sql1);

                $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);

                }



                $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;
                $mysqli->close();
?>