davafy 2015-10-09 18:07:12 19438次浏览 9条评论 42 14 0

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

$code = new Code();
foreach ($codeModel as $v) {
    $_code = clone $code;
    $_code->rid = $rid;
    $_code->created_at = time();
    $_code->setAttributes($v);
    $_code->save();
}

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

$db = Yii::$app->db;
foreach ($codeModel as $v) {
    $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();
}

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

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

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

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

这样的单条单条的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秒之内搞定。下面贴出部分参考代码

//下面是大于5000条数据拼接算法,小于5000条就没贴出来了
$chu = (int)($count/5000);//取整
$yu = $count%5000;//取余
    for ($i=0; $i < $chu; $i++) { 
        //每5000条数据组成一个insert语句,$codeModel是存放记录的一个数组
        $values = '';
        for ($j=$i*5000; $j < ($i+1)*5000; $j++) { 
            //拼接values的值
            $values .= '('.$codeModel[$j]['rid'].','.$codeModel[$j]['cid'].',"'.$codeModel[$j]['regcode'].'",0,'.$codeModel[$j]['status'].',0,'.time().'),';
        }
        $values = "insert into w_code (rid,cid,regcode,used_times,status,reason_id,created_at) values".substr($values,0,-1).';';
        Yii::$app->db->createCommand($values)->execute();
    }

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

啧啧,感觉优化成果非常好,所以这里给大家分享出来,有需要的同类可以参考一下,有什么问题或者本文错误请一定下方留言让我一一回答或更正本文,万分感激!

觉得很赞

Yii用户专享10000元红包

Yii开发者福利,万元红包可用于云服务器、高防服务器、裸金属服务器、云数据库、高防IP购买和续费

  • 评论于 2015-10-10 09:38 举报

    难道YII没有封装批量插入的方法?

    4 条回复
    评论于 2015-10-10 10:10 回复

    还望赐教。

    评论于 2015-10-10 10:16 回复
    $sql = $queryBuilder->batchInsert('user', ['name', 'age'], [
        ['Tom', 30],
        ['Jane', 20],
        ['Linda', 25],
    ]);
    

    试试这个。跟你拼凑的SQL一样的

    评论于 2015-10-10 10:31 回复

    恩恩,但还是需要分批次插入,我这里是分5000个一组插入。一次性插入太多数据库就直接提示崩溃

    评论于 2015-10-10 13:12 回复

    肯定要分批插入的。我一般是1000

  • 评论于 2015-10-11 21:20 举报

    为什么“多条insert放入事务中也会提升一点数据插入的性能”呢?

    8 条回复
    评论于 2015-10-12 09:18 回复

    批量写入时事物有助于性能,因为数据会后置写入,中间有一个错误就回滚了,他可以批量验证然后批量插入。而一个个执行SQL会一个一个验证,一个个插入。

    评论于 2015-10-13 07:51 回复

    批量插入是会比一个个插入快,但是不启用事务应该比启用事务要快呀

    评论于 2015-10-13 09:16 回复

    no no no,启用事物是mysql将所有的都check一遍,然后一次性执行写入。不启用就是一边验证一边写入,冗余等待时常比较大。如果你用U盘拷过东西,你应该知道100M的压缩包和100M的文件夹,肯定是前者拷贝的快,有点小差异但是道理类似。一个是文件一次性校验,一个是N个文件每次都校验。

    评论于 2015-10-13 09:20 回复

    事务的作用不是操作的原子性吗,多启动一项功能不是消耗更大,你说的check,验证是什么过程,

    评论于 2015-10-13 15:02 回复

    我醉了,我都说的很明白了。创建一个insert就是创建1个事物,插入5万数据,一条条插入就是5万个事物的创建与释放,如果通过事物提交,因为这些操作都在本次事物的body中,所以只创建一次,在本次操作中验证所有的语句,如果中间出现错误就回滚,如果没错就批量写入了。

    下面一段话是别人说的,我怕你看不明白,粘给你。

    这是因为进行一个INSERT操作时,MySQL内部会建立一个事务,在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗,所有插入都在执行后才进行提交操作。

    评论于 2015-10-13 15:04 回复

    也就是说,你不创建事物,你insert的时候,数据库还是会给你自动生成一个事物的。

    评论于 2015-10-15 13:06 回复

    觉得很赞!

    评论于 2015-10-23 14:18 回复
  • 评论于 2015-11-03 09:37 举报

    marking

  • 评论于 2015-11-20 23:09 举报

    mark一下

  • 评论于 2016-03-14 22:14 举报

    mark..

  • 评论于 2016-04-19 15:11 举报

    这里更新一点简化代码的写法,用array_chunk($array,5000)函数可以将一个一维数组$array分成若干个包含5000条数据的二维数组,代码会简洁易读很多,有兴趣的朋友可以百度下array_chunk的具体用法

    觉得很赞
  • 评论于 2016-06-12 18:15 举报

    学习了。我要插入几十万条数据。正愁呢~

  • 评论于 2017-04-17 16:28 举报

    good!

  • 评论于 2018-03-19 16:37 举报

    mark.

您需要登录后才可以评论。登录 | 立即注册