关于mysql上万条数据同时插入时的性能优化一次几万条应该怎么做

由于项目需要,要求是要单次往数据库里插入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秒以内。这时间我手贱,搞了五万条数据给同时插入,这时间问题来了,browser直接提示内存溢出(后来试了插入三万条数据没有提示溢出,但依然花了大概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(); }

然后客户端browser依然提示内存溢出(这时间插入三万条数据的时间花了大概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,...)...;

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

$chu = (int)($count/ 5000 ); $yu = $count% 5000 ; for ($i= 0 ; $i < $chu; $i++) { $values = '' ; for ($j=$i* 5000 ; $j < ($i+ 1 )* 5000 ; $j++) { $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放入事务中也会提高一点数据插入的性能!

啧啧,感觉优化结果非常好,以是这里给大家分享出来,有需要的同类可以参考一下,有什么问题要么本文错误请肯定下方留言让我逐一答复或改正本文,万分感谢!

insert最多能一次多少条数据

对于一些数据量较大的系统,数据库面对的问题除了查询服从低下,另有就是数据入库时间长。特殊像报表系统,天天耗费在数据导入上的时间大概会长达几个小时或十几个小时之久。因此,优化数据库插入性能是很故意义的。经过对MySQL InnoDB的一些性能测试,发现一些可以进步insert服从的方法,供大家参考参考。 1、insert批量实行,禁止单条insert value(留意单条sql长度限定,可暂时调整max_allowed_packet) 2、开启事务处置,批量提交。原理类似上1(条件容许可得当增大innodb_log_buffer_size,增长单事务提交日记量,该参数read only) 3、主键次序插入,服从更高 4、业务容许,临时disable keys 5.对要插入的数据进行分组批量插入 INSERT INTO table (column1, column2, ..., column_n) VALUES !(value11, value12, ..., value1n), (value21, value22, ... value2n), ..., (value_n1, value_n2, ... value_nn) 常用的插入语句如: INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0); INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('1', 'userid_1', 'content_1', 1); 修改成: INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0'!, 'content_0', 0), ('1', 'userid_1', 'content_1', 1); 修改后的插入操作可以或许进步程序的插入服从。这里第二种SQL实行服从高的重要缘故是归并后日记量(MySQL的binlog和innodb的事务让日记)淘汰了,降低日记刷盘的数据量和频率,从而进步服从。通过归并SQL语句,同时也能淘汰SQL语句剖析的次数,淘汰网络传输的IO。 在事务中进行插入处置。 把插入修改成: START TRANSACTION; INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0); INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('1', 'userid_1', 'content_1', 1); ... COMMIT; 使用事务可以进步数据的插入服从,这是由于进行一个INSERT操作时,MySQL内部会创建一个事务,在事务内才进行真正插入处置操作。通过使用事务可以淘汰创建事务的消耗,全部插入都在实行后才进行提交操作。 数据有序插入。 数据有序的插入是指插入记载在主键上是有序分列,比方datetime是记载的主键: INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('1', 'userid_1', 'content_1', 1); INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0); INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('2', 'userid_2', 'content_2',2); 修改成: INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0); INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('1', 'userid_1', 'content_1', 1); INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('2', 'userid_2', 'content_2',2); 由于数据库插入时,需要维护索引数据,无序的记载会增大维护索引的本钱。我们可以参照InnoDB使用的B+tree索引,假如每次插入记载都在索引的最背面,索引的定位服从很高,而且对索引调整较小;假如插入的记载在索引中间,需要B+tree进行破裂归并等处置,会消耗比较多盘算资源,而且插入记载的索引定位服从会降落,数据量较大时会有频繁的磁盘操作。 从测试结果来看,该优化方法的性能有所进步,但是进步并不是很显着。 SQL语句是有长度限定,在进行数据归并在同一SQL中务必不能超越SQL长度限定,通过max_allowed_packet设置可以修改,默认是1M,测试时修改为8M。 事务需要控制大小,事务太大大概会影响实行的服从。MySQL有innodb_log_buffer_size设置项,超越这个值会把innodb的数据刷到磁盘中,这时,服从会有所降落。以是比较好的做法是,在数据到达这个这个值进步行事务提交。

现在碰到比较多的开发同窗来问有关insert批量插入相关的事情,如今测试了下两种批量insert写法的服从。
本次测试只比较相同环境下insert单条插入和归并插入的服从问题,不思量类似“机器性能对mysql插入的影响”要么“每秒能插几多条数据”这种课题

表布局如下 root:test> show create table t_insert_single\G *************************** 1. row *************************** Table: t_insert_single Create Table: CREATE TABLE `t_insert_single` ( `id` int(11) NOT NULL AUTO_INCREMENT, `col1` int(11) DEFAULT NULL, `col2` varchar(50) DE!FAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.01 sec) root:test> show create table t_insert_mult\G *************************** 1. row *************************** Table: t_insert_mult Create Table: CREATE TABLE `t_insert_mult` ( `id` int(11) NOT NULL AUTO_INCREMENT, `col1` int(11) DEFAULT NULL, `col2` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
单条插入:
insert into t_insert_single(col1,col2) values(1,'dsadkhjhfsfiuofdgfdsfdsfdsfdsfdf'); insert into t_insert_single(col1,col2) values(2,'dsadkhjhfsfiuofdgfdsfdsfdsfdsfdf');
归并插入:
insert into t_insert_mult(col1,col2) values (1,'dsadkhjhfsfiuofdgfdsfdsfdsfdsfdf'),(2,'dsadkhjhfsfiuofdgfdsfdsfdsfdsfdf');

测试结果

结论:

1、insert归并插入的服从高于单条插入。
2、insert一条语句一个事务的服从低于多条语句一个事务的服从。
3、在现有的测试环境下,单条插入(N个事务)和归并插入(一个事务)相差的服从最高能到达10倍。
4、在一个事务中,单条插入和归并插入服从均匀相差2~3倍。

提议

1、日常多条insert语句,接纳归并插入的写法
2、一次性插入批量数控制在500或1000左右(假如有大字段要再小点)

附上测试脚本

单条插入(N个事务)脚本 #!/bin/sh insert_sql='' for i in {1..10000} do #echo $i insert_sql=$insert_sql"insert into t_insert_single(col1,col2) values($i,'dsadkhjhfsfiuofdgfdsfdsfdsfdsfdf');" done echo $insert_sql>a.sql time mysql -uroot -p123 test -e"source a.sql" 单条插入(1个事务)脚本 #!/bin/sh insert_sql='' for i in {1..10000} do #echo $i insert_sql=$insert_sql"insert into t_insert_single(col1,col2) values($i,'dsadkhjhfsfiuofdgfdsfdsfdsfdsfdf');" done echo $insert_sql>a.sql time mysql -uroot -p123 test -e"set autocommit=0;source a.sql;commit;" 归并插入脚本(1个事务) #!/bin/sh insert_sql='' for i in {1..9999} do #echo $i insert_sql=$insert_sql",($i,'dsadkhjhfsfiuofdgfdsfdsfdsfdsfdf')" done insert_sql="insert into t_insert_mult(col1,col2) values (0,'dsadkhjhfsfiuofdgfdsfdsfdsfdsfdf')"$insert_sql";" echo $insert_sql > b.sql time mysql -uroot -p123 test -e"source b.sql"

本文网址: http://www.edoghaitao.com/p/2020926132327_7760_211434535/home

推荐阅读

tags

最新发布