一直以来各种人都在争论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
测试经过如下：
建表语句
|
1 2 3 4 5 6 7 8 |
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、清空表，测试普通的插入
|
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 |
<?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 |
D:\wamp\www\local.test.com>php index.php 1474362678 ok1474362709 1474362709 31 |
1000条，31秒，平均32/s
这个结果是不是很意外，当然了，这不是mysql的问题，你如果起多个进程，时间还是这样，但是说明插入的更多，说明应该是mysql_query等的交互中有其他的网络或者io开销，拉低了性能
2、开启事务
还是清空表
|
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 |
<?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 |
D:\wamp\www\local.test.com>php index.php 1474366327 ok1474366342 1474366342 15 D:\wamp\www\local.test.com> |
插入10w,用了15秒，速度大概为：6666/s 提升很大吧
当然，我还是使用的比较老的mysql函数，应该使用mysqli更好，myisam应该和这个结果差不多。毕竟他没有事务。
3、load data in file
|
1 2 3 |
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 |
结果很惊人，500w，用了85秒，速度接近5.8w/s，当然这是没有索引的情况，
执行添加一个索引
|
1 2 3 4 |
[SQL]ALTER TABLE `user` ADD INDEX `name` (`name`) USING BTREE ; 受影响的行: 0 时间: 38.420s |
38秒，
4、清空，先建索引，再插入
|
1 2 3 |
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.3w/s
所以，结论就是上面说的，mysql自身的插入性能应该没有什么大问题，很多时候都是其他的一些因素影响的。当然机器性能好，肯定结果有提高的。
程序本天成，妙手偶得之！我们只是代码的搬运工！
转载请注明：http://www.521php.com/archives/1989/
2019年01月09日 下午 10:06 wordpress建站吧 | 引用 | #1
序本天成，妙手偶得之！