MySql批量插入优化Sql执行效率实例详解

mysql批量插入优化sql执行效率实例详解

itemcontractprice数量1万左右，每条itemcontractprice 插入5条日志。

updateinsertsql.appendformat("update itemcontractprice as p inner join foreigncurrency as f on p.foreigncurrencyid = f.contractpriceid set p.remainprice = f.remainprice * {0},p.buyoutprice = f.buyoutprice * {0},p.reservedprice = f.reservedprice * {0},p.collectedprice = f.collectedprice * {0},p.accessprice = f.accessprice * {0} where p.currencyid = {1} and p.date between '{2:yyyy-mm-dd}' and '{3:yyyy-mm-dd}';", rate.exchangerate, exchangerate.currencyid, rate.begindate, rate.enddate); 
 
updateinsertsql.appendformat("insert into `itemcontractpricelog`(`contractpricetype`,`contractprice`,`fccontractprice`,`isexpire`,`logremark`,`createdbyname`,`createdbyid`,`createddate`,`logtypeid`,`providerid`,`stageid`,`date`,`currencyid`,`contractpriceid`,`stockpattern`,`itemid`) select 0,c.remainprice,f.remainprice,c.remainisexpire,'外币汇率调整，重新计算人民币底价','job',0,now(),5,c.providerid,c.stageid,c.date,c.currencyid,c.contractpriceid,0,c.itemid from itemcontractprice as c inner join foreigncurrency as f on c.foreigncurrencyid = f.contractpriceid where c.currencyid={0} and c.date between '{1:yyyy-mm-dd}' and '{2:yyyy-mm-dd}';", exchangerate.currencyid, rate.begindate, rate.enddate); 
 
updateinsertsql.appendformat(" insert into `itemcontractpricelog`(`contractpricetype`,`contractprice`,`fccontractprice`,`isexpire`,`logremark`,`createdbyname`,`createdbyid`,`createddate`,`logtypeid`,`providerid`,`stageid`,`date`,`currencyid`,`contractpriceid`,`stockpattern`,`itemid`) select 1,c.buyoutprice,f.buyoutprice,c.buyoutisexpire,'外币汇率调整，重新计算人民币底价','job',0,now(),5,c.providerid,c.stageid,c.date,c.currencyid,c.contractpriceid,0,c.itemid from itemcontractprice as c inner join foreigncurrency as f on c.foreigncurrencyid = f.contractpriceid where c.currencyid={0} and c.date between '{1:yyyy-mm-dd}' and '{2:yyyy-mm-dd}';", exchangerate.currencyid, rate.begindate, rate.enddate); 
 
updateinsertsql.appendformat("insert into `itemcontractpricelog`(`contractpricetype`,`contractprice`,`fccontractprice`,`isexpire`,`logremark`,`createdbyname`,`createdbyid`,`createddate`,`logtypeid`,`providerid`,`stageid`,`date`,`currencyid`,`contractpriceid`,`stockpattern`,`itemid`) select 2,c.reservedprice,f.reservedprice,c.reservedisexpire,'外币汇率调整，重新计算人民币底价','job',0,now(),5,c.providerid,c.stageid,c.date,c.currencyid,c.contractpriceid,0,c.itemid from itemcontractprice as c inner join foreigncurrency as f on c.foreigncurrencyid = f.contractpriceid where c.currencyid={0} and c.date between '{1:yyyy-mm-dd}' and '{2:yyyy-mm-dd}';", exchangerate.currencyid, rate.begindate, rate.enddate); 
 
updateinsertsql.appendformat("insert into `itemcontractpricelog`(`contractpricetype`,`contractprice`,`fccontractprice`,`isexpire`,`logremark`,`createdbyname`,`createdbyid`,`createddate`,`logtypeid`,`providerid`,`stageid`,`date`,`currencyid`,`contractpriceid`,`stockpattern`,`itemid`) select 3,c.collectedprice,f.collectedprice,c.collectedisexpire,'外币汇率调整，重新计算人民币底价','job',0,now(),5,c.providerid,c.stageid,c.date,c.currencyid,c.contractpriceid,0,c.itemid from itemcontractprice as c inner join foreigncurrency as f on c.foreigncurrencyid = f.contractpriceid where c.currencyid={0} and c.date between '{1:yyyy-mm-dd}' and '{2:yyyy-mm-dd}';", exchangerate.currencyid, rate.begindate, rate.enddate); 
updateinsertsql.appendformat("insert into `itemcontractpricelog`(`contractpricetype`,`contractprice`,`fccontractprice`,`isexpire`,`logremark`,`createdbyname`,`createdbyid`,`createddate`,`logtypeid`,`providerid`,`stageid`,`date`,`currencyid`,`contractpriceid`,`stockpattern`,`itemid`) select 4,c.accessprice,f.accessprice,c.accessisexpire,'外币汇率调整，重新计算人民币底价','job',0,now(),5,c.providerid,c.stageid,c.date,c.currencyid,c.contractpriceid,0,c.itemid from itemcontractprice as c inner join foreigncurrency as f on c.foreigncurrencyid = f.contractpriceid where c.currencyid={0} and c.date between '{1:yyyy-mm-dd}' and '{2:yyyy-mm-dd}';", exchangerate.currencyid, rate.begindate, rate.enddate); 
//var curcontractpricelist = itemcontractpricelist.where(o => o.currencyid == exchangerate.currencyid && o.date >= rate.begindate && o.date <= rate.enddate).tolist(); 
logger.infoformat("底价更新和日志sql:{0}", updateinsertsql.tostring()); 
//if (curcontractpricelist.count == 0) continue; 
int effctrows = 0; 
using (var tran = unitofworkmanager.begin()) 
{ 
  effctrows = taskrepository.executesql(updateinsertsql.tostring(), false); 
  tran.complete(); 
} 
logger.infoformat("底价更新影响行数:{0}", effctrows); 

正常情况下大概20秒钟就ok.

之前是用ef操作，查询出来 ，要耗时，然后再组装 update语句 ，然后再插入日志（每条数据5条日志），这个网络交互的时间加上数据库连接打开关闭的时间，总的执行时间，大概10多分钟。

用sql语句批量操作，可以说效率提升了 40倍，就是大量数据的传输和数据库的处理次数耗时。

所以说，软件开发不是开发完成就行，而是要解决性能上的问题，这才是开发的进阶。

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！