php连接mysql配合循环_[Php-Mysql]多条数据的循环插入和一次性插入的性能测试

/**

* Author:RDP

* My mail:abel1314520@gmail.com

* My blog:

blog.teamhaka.com

*/

A 测试说明：

主要比较所花时间，包括sql执行时间和最低限度php程序执行时间，不包括数据库链接和关闭数据库的时间。

表结构中有9列。

测试以分别插入10、20、50、100、200、500、1000、2000行数据来进行。

B 测试环境：

1 php和mysql同在一台Vista主机上

2 mysql版本5.1.41

3 php版本5.3.1

C 测试结论：

一次性插入快于循环插入。

结果数据如下(单位秒)：

各行三部分的数据含义为 结束时间、起始时间、结束时间与起始时间差值

Insert Rows:10

Once insert: 1309098893.96521309098893.9640.0011539459228516

Loop insert: 1309098893.97721309098893.97260.0045840740203857

Insert Rows:20

Once insert: 1309098893.98151309098893.98030.0012009143829346

Loop insert: 1309098893.98921309098893.98410.0051271915435791

Insert Rows:50

Once insert: 1309098893.99381309098893.99290.00085210800170898

Loop insert: 1309098894.00511309098893.99640.0087730884552002

Insert Rows:100

Once insert: 1309098894.00961309098894.00830.0012550354003906

Loop insert: 1309098894.03071309098894.01280.017880916595459

Insert Rows:200

Once insert: 1309098894.03561309098894.03350.0021400451660156

Loop insert: 1309098894.0721309098894.03850.033463954925537

Insert Rows:500

Once insert: 1309098894.08141309098894.07570.00565505027771

Loop insert: 1309098894.18011309098894.08460.095527172088623

Insert Rows:1000

Once insert: 1309098894.19341309098894.18380.0095570087432861

Loop insert: 1309098894.37631309098894.19620.18014216423035

Insert Rows:2000

Once insert: 1309098894.42751309098894.37930.048200130462646

Loop insert: 1309098894.82031309098894.43180.38853096961975

D 测试代码及表结构：

1 表结构

CREATE TABLE IF NOT EXISTS `insertTest` (

`id` int(10) ,

`data1` varchar(20) NOT NULL DEFAULT '',

`data2` varchar(20) NOT NULL DEFAULT '',

`data3` varchar(20) NOT NULL DEFAULT '',

`data4` varchar(20) NOT NULL DEFAULT '',

`data5` varchar(20) NOT NULL DEFAULT '',

`data6` varchar(20) NOT NULL DEFAULT '',

`data7` varchar(20) NOT NULL DEFAULT '',

`data8` varchar(20) NOT NULL DEFAULT ''

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

2 测试代码

$dbConfig["hostName"] = "localhost";

$dbConfig["userName"] = "root";

$dbConfig["userPw"] = "";

$dbConfig['dbName'] = 'test';

$dbConfig['tbName'] = 'insertTest';

function insertTest($dbConfig,$rows=1){

//input check

if($rows<1){

return false;

}

/***** 一次性插入 TEST *****/

//db connect

$dbHander = @mysql_connect($dbConfig['hostName'], $dbConfig['userName'], $dbConfig['userPw']);

if(!dbHander){

die("Db connect: Faile");

}

if(!@mysql_select_db($dbConfig['dbName'],$dbHander)) {

die('Db select: Faile');

}

//start

$resultOnce['startTime'] = microtimeFloat();

$sqlOnce = " INSERT INTO $dbConfig[tbName] (id,data1,data2,data3,data4,data5,data6,data7,data8) values ";

$onceRows = $rows;

while($onceRows>0){

$sqlOnce .= " ( '$onceRows','data1','data2','data3','data4','data5','data6','data7','data8' ),";

$onceRows--;

}

$sqlOnce = substr( $sqlOnce,0,-1);

//$resultOnce['sqlExecStart'] = microtimeFloat();

$query = mysql_query($sqlOnce,$dbHander);

//$resultOnce['sqlExecEnd'] = microtimeFloat();

//end

$resultOnce['endTime'] = microtimeFloat();

if(!$query){

die('Query Error: '.$sqlOnce);

}

// db close

@mysql_close($dbHander);

/***** 一次性插入 TEST *****/

/***** 循环插入 TEST *****/

//db connect

$dbHander = @mysql_connect($dbConfig['hostName'], $dbConfig['userName'], $dbConfig['userPw']);

if(!dbHander){

die("Db connect: Faile");

}

if(!@mysql_select_db($dbConfig['dbName'],$dbHander)) {

die('Db select: Faile');

}

//start

$resultLoop['startTime'] = microtimeFloat();

$loopRows = $rows;

while($loopRows>0){

$query = mysql_query(" INSERT INTO $dbConfig[tbName](id,data1,data2,data3,data4,data5,data6,data7,data8) values( '$loopRows','data1','data2','data3','data4','data5','data6','data7','data8' ) ",$dbHander);

if(!$query){

die('Loop Query Error');

}

$loopRows--;

}

//end

$resultLoop['endTime'] = microtimeFloat();

// db close

@mysql_close($dbHander);

/***** 循环插入 TEST *****/

echo 'Insert Rows:'.$rows;

echo 'Once insert: '.$resultOnce['endTime'].''.$resultOnce['startTime'].''.($resultOnce['endTime']-$resultOnce['startTime']);

echo 'Loop insert: '.$resultLoop['endTime'].''.$resultLoop['startTime'].''.($resultLoop['endTime']-$resultLoop['startTime']).'

';

}

function microtimeFloat() {

list($usec, $sec) = explode(" ", microtime());

return ((float)$usec + (float)$sec);

}

insertTest($dbConfig,10);

insertTest($dbConfig,20);

insertTest($dbConfig,50);

insertTest($dbConfig,100);

insertTest($dbConfig,200);

insertTest($dbConfig,500);

insertTest($dbConfig,1000);

insertTest($dbConfig,2000);