关于mysql上万条数据同时插入时的性能优化

由于项目需要，要求是要单次往数据库里插入10000条数据，刚开始写得代码如下：

1. $code = new Code();

2. foreach ($codeModel as $v) {

3. $_code = clone $code;

4. $_code->rid = $rid;

5. $_code->created_at = time();

6. $_code->setAttributes($v);

7. $_code->save();

8. }

这段代码是将这10000条数据循环插入数据库，效率是比较低，但还可以忍受，这里插入的时间没有测算，估计在10秒以内。这时候我手贱，搞了五万条数据给同时插入，这时候问题来了，浏览器直接提示内存溢出（后来试了插入三万条数据没有提示溢出，但依然花了大概30秒时间）。有大神解释是用了yii2的语法会导致多余内存占用，建议用原生sql语句，然后我把上面的代码改造下面的：

1. $db = Yii::$app->db;

2. foreach ($codeModel as $v) {

3. $db->createCommand('insert into w_code (rid,cid,regcode,used_times,status,reason_id,created_at) values (:rid,:cid,:regcode,:used_times,:status,:reason_id,:created_at)', [':rid'=>$rid,':cid'=>$v['cid'],':regcode'=>$v['regcode'],':used_times'=>0,':status'=>$v['status'],':reason_id'=>0,':created_at'=>time()])->execute();

4. }

然后客户端浏览器依然提示内存溢出(这时候插入三万条数据的时候花了大概23秒时间，有进步，但还是不理想，所以继续倒腾)，所以只好在index.php里加上一句

ini_set('memory_limit','1024M');

将客户端内存大小设置为1GB(不知道这样表述正不正确，望指正)，这时候插入五万条数据的时候没有提示内存溢出，但是执行速度还是很慢，五万条数据30秒内都插不完，最后提示超时。
所以总结下来，将yii2语法改成了原生sql性能也只是提升一些，但也并不是想要的效果。后来在网上找了一些插入大量数据性能优化资料，提到了比较重要的一点是将

1. insert into tablename(f1,f2,...) values (d1,d2,...);

2. insert into tablename(f1,f2,...) values (d1,d2,...);

3. ...

这样的单条单条的insert语句改造成

insert into tablename(f1,f2,...) values (d1,d2,...),(d1,d2,...),(d1,d2,...);

这种一次insert多条记录，性能会提升比较明显，所以我就开始试验这种方法，将每条记录在代码里循环拼接成一条原生insert语句再进行插入（想想感觉可行性很高），拼接完成后依然继续插入五万条数据，拼接出来的sql语句就成了

insert into tablename(f1,f2,...) values (d1,d2,...),(d1,d2,...),(d1,d2,...)...;//此处省略了49997条记录

浏览器运行插入数据的页面，bong...，提示Mysql server has gone away!，mysql崩溃了。蛋疼~！然后寻思着将这五万条数据分批次进行插入，这样就不会产生数据库崩溃的情况，所以我将这五万条数据按照五千个一组分批插入，最后再运行这个页面，bong...五万条数据两秒之内就给全部插入进去了，两秒。。(这里已经去掉了前面加上的ini_set('memory_limit','1024M');)效率跟之前比提高了几十倍，瞬间感觉整个人都变好了。又试了再插入三万条数据，1秒之内搞定。下面贴出部分参考代码

1. //下面是大于5000条数据拼接算法，小于5000条就没贴出来了

2. $chu = (int)($count/5000);//取整

3. $yu = $count%5000;//取余

4. for ($i=0; $i < $chu; $i++) {

5. //每5000条数据组成一个insert语句,$codeModel是存放记录的一个数组

6. $values = '';

7. for ($j=$i*5000; $j < ($i+1)*5000; $j++) {

8. //拼接values的值

9. $values .= '('.$codeModel[$j]['rid'].','.$codeModel[$j]['cid'].',"'.$codeModel[$j]['regcode'].'",0,'.$codeModel[$j]['status'].',0,'.time().'),';

10. }

11. $values = "insert into w_code (rid,cid,regcode,used_times,status,reason_id,created_at) values".substr($values,0,-1).';';

12. Yii::$app->db->createCommand($values)->execute();

13. }

另外，这些代码外层都放了事务回滚的！将多条insert放入事务中也会提升一点数据插入的性能！

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

另：

 

第一种方法：使用insert into 插入，代码如下：

?

1 2 3 4 5 6 7

$params = array(‘value'=>'50′); set_time_limit(0); echo date(“H:i:s”); for($i=0;$i<2000000;$i++){ $connect_mysql->insert($params); }; echo date(“H:i:s”);

最后显示为：23:25:05 01:32:05 也就是花了2个小时多!

第二种方法：使用事务提交，批量插入数据库(每隔10W条提交下)最后显示消耗的时间为：22:56:13 23:04:00 ，一共8分13秒  ，代码如下：

?

1 2 3 4 5 6 7 8 9 10 11 12

echo date(“H:i:s”); $connect_mysql->query(‘BEGIN'); $params = array(‘value'=>'50′); for($i=0;$i<2000000;$i++){ $connect_mysql->insert($params); if($i%100000==0){ $connect_mysql->query(‘COMMIT'); $connect_mysql->query(‘BEGIN'); } } $connect_mysql->query(‘COMMIT'); echo date(“H:i:s”);

第三种方法：使用优化SQL语句：将SQL语句进行拼接，使用 insert into table () values  (),(),(),()然后再一次性插入，如果字符串太长，

则需要配置下MYSQL，在mysql 命令行中运行 ：set global max_allowed_packet =  2*1024*1024*10;消耗时间为：11:24:06 11:25:06;

插入200W条测试数据仅仅用了1分钟!代码如下：

?

1 2 3 4 5 6

$sql= “insert into twenty_million (value) values”; for($i=0;$i<2000000;$i++){ $sql.=”('50′),”; }; $sql = substr($sql,0,strlen($sql)-1); $connect_mysql->query($sql);

最后总结下，在插入大批量数据时，第一种方法无疑是最差劲的，而第二种方法在实际应用中就比较广泛，第三种方法在插入测试数据或者其他低要求时比较合适，速度确实快。

转载于:https://my.oschina.net/jack088/blog/3070600