php mysql 性能_php插入mysql方式性能分析

一直以来各种人都在争论mysql的插入性能，当然我这里说的人大部分是phper，使用的场景也是php操作mysql，有人说一秒怎么也要几万，有的说几千，还有的说几百。有的也的确是实验过，但是，其实，所有的结果都是因为有前置条件的。我这里就实际操作了一下。对比了一下，php操作mysql，在有索引，无索引，innodb开启事务、和不使用事务，以及mysql load data infile几种方式的对比。

有人说实践出真知，的确，我们不能完全相信书上，尤其是网上说的，应该自己实践一下，但是，即使自己真的动手实践，也可能因为自己知识面的原因，得出的结果可能是固定场景下的。先交代一下我的前提吧，我用的自己的电脑，戴尔笔记本，i5 2核,8g内存，所有的测试场景，cpu，内存，磁盘io都没有到峰值，这说明是某些其他的因素成为了短板。

先说一下结论，mysql插入性能的确可以达到10w/s，load data infile能达到5w/s以上，innodb批量的事务提交，能到达5k/s以上，但是普通的insert，只能40/s

测试经过如下：

建表语句

PHP

CREATE TABLE `user` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(100) NOT NULL,

`age` varchar(22) NOT NULL,

`title` varchar(100) NOT NULL,

`title2` varchar(100) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1;

1

2

3

4

5

6

7

8

CREATETABLE`user`(

`id`int(11)unsignedNOTNULLAUTO_INCREMENT,

`name`varchar(100)NOTNULL,

`age`varchar(22)NOTNULL,

`title`varchar(100)NOTNULL,

`title2`varchar(100)NOTNULL,

PRIMARYKEY(`id`)

)ENGINE=InnoDBAUTO_INCREMENT=1001DEFAULTCHARSET=latin1;

1、清空表，测试普通的插入

PHP

error_reporting(1);

$con = mysql_connect("localhost","root","");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

// some code

echo $s = time();

echo "\n";

mysql_query("set names utf8");

mysql_select_db('test',$con);

for($i=1;$i<=1000;$i++){

$sql = "insert into user(`name`,`age`,`title`,`title2`) values('name{$i}',{$i},'title{$i}','title{$i}')";

mysql_query($sql);

}

echo 'ok'.time();

echo "\n";

echo $t = time();

echo "\n";

echo ($t-$s);

echo "\n";

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

error_reporting(1);

$con=mysql_connect("localhost","root","");

if(!$con)

{

die('Could not connect: '.mysql_error());

}

// some code

echo$s=time();

echo"\n";

mysql_query("set names utf8");

mysql_select_db('test',$con);

for($i=1;$i<=1000;$i++){

$sql="insert into user(`name`,`age`,`title`,`title2`) values('name{$i}',{$i},'title{$i}','title{$i}')";

mysql_query($sql);

}

echo'ok'.time();

echo"\n";

echo$t=time();

echo"\n";

echo($t-$s);

echo"\n";

结果：

Shell

D:\wamp\www\local.test.com>php index.php

1474362678

ok1474362709

1474362709

31

1

2

3

4

5

D:\wamp\www\local.test.com>phpindex.php

1474362678

ok1474362709

1474362709

31

1000条，31秒，平均32/s

这个结果是不是很意外，当然了，这不是mysql的问题，你如果起多个进程，时间还是这样，但是说明插入的更多，说明应该是mysql_query等的交互中有其他的网络或者io开销，拉低了性能

2、开启事务

还是清空表

PHP

error_reporting(1);

$con = mysql_connect("localhost","root","");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

// some code

echo $s = time();

echo "\n";

mysql_query("set names utf8");

mysql_select_db('test',$con);

mysql_query("BEGIN");

for($i=1;$i<=100000;$i++){

$sql = "insert into user(`name`,`age`,`title`,`title2`) values('name{$i}',{$i},'title{$i}','title{$i}')";

mysql_query($sql);

}

echo 'ok'.time();

echo "\n";

mysql_query("COMMIT");

echo $t = time();

echo "\n";

echo ($t-$s);

echo "\n";

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

error_reporting(1);

$con=mysql_connect("localhost","root","");

if(!$con)

{

die('Could not connect: '.mysql_error());

}

// some code

echo$s=time();

echo"\n";

mysql_query("set names utf8");

mysql_select_db('test',$con);

mysql_query("BEGIN");

for($i=1;$i<=100000;$i++){

$sql="insert into user(`name`,`age`,`title`,`title2`) values('name{$i}',{$i},'title{$i}','title{$i}')";

mysql_query($sql);

}

echo'ok'.time();

echo"\n";

mysql_query("COMMIT");

echo$t=time();

echo"\n";

echo($t-$s);

echo"\n";

结果：

Shell

D:\wamp\www\local.test.com>php index.php

1474366327

ok1474366342

1474366342

15

D:\wamp\www\local.test.com>

1

2

3

4

5

6

7

D:\wamp\www\local.test.com>phpindex.php

1474366327

ok1474366342

1474366342

15

D:\wamp\www\local.test.com>

插入10w,用了15秒，速度大概为：6666/s 提升很大吧

当然，我还是使用的比较老的mysql函数，应该使用mysqli更好，myisam应该和这个结果差不多。毕竟他没有事务。

3、load data in file

Shell

mysql> load data infile "/wamp/www/local.test.com/1.txt" into table user fields terminated by ',' (name,age,title,title2);

Query OK, 5000000 rows affected (1 min 25.03 sec)

Records: 5000000 Deleted: 0 Skipped: 0 Warnings: 0

1

2

3

mysql>loaddatainfile"/wamp/www/local.test.com/1.txt"intotableuserfieldsterminatedby','(name,age,title,title2);

QueryOK,5000000rowsaffected(1min25.03sec)

Records:5000000Deleted:0Skipped:0Warnings:0

结果很惊人，500w，用了85秒，速度接近5.8w/s，当然这是没有索引的情况，

执行添加一个索引

Shell

[SQL]ALTER TABLE `user`

ADD INDEX `name` (`name`) USING BTREE ;

受影响的行: 0

时间: 38.420s

1

2

3

4

[SQL]ALTERTABLE`user`

ADDINDEX`name`(`name`)USINGBTREE;

受影响的行:0

时间:38.420s

38秒，

4、清空，先建索引，再插入

Shell

mysql> load data infile "/wamp/www/local.test.com/1.txt" into table user fields terminated by ',' (name,age,title,title2);

Query OK, 5000000 rows affected (6 min 8.13 sec)

Records: 5000000 Deleted: 0 Skipped: 0 Warnings: 0

1

2

3

mysql>loaddatainfile"/wamp/www/local.test.com/1.txt"intotableuserfieldsterminatedby','(name,age,title,title2);

QueryOK,5000000rowsaffected(6min8.13sec)

Records:5000000Deleted:0Skipped:0Warnings:0

结果慢了很多，速度大概1.3w/s

所以，结论就是上面说的，mysql自身的插入性能应该没有什么大问题，很多时候都是其他的一些因素影响的。当然机器性能好，肯定结果有提高的。

程序本天成，妙手偶得之！我们只是代码的搬运工！