MySql大局部插入來完成優(yōu)化Sql執(zhí)行效率的案例代碼

MySql批量插入優(yōu)化Sql執(zhí)行效率實(shí)例詳解

itemcontractprice數(shù)量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,'外幣匯率調(diào)整，重新計(jì)算人民幣底價(jià)','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,'外幣匯率調(diào)整，重新計(jì)算人民幣底價(jià)','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,'外幣匯率調(diào)整，重新計(jì)算人民幣底價(jià)','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,'外幣匯率調(diào)整，重新計(jì)算人民幣底價(jià)','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,'外幣匯率調(diào)整，重新計(jì)算人民幣底價(jià)','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("底價(jià)更新和日志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("底價(jià)更新影響行數(shù):{0}", effctRows);

正常情況下大概20秒鐘就ok.

之前是用EF操作，查詢出來 ，要耗時(shí)，然后再組裝 update語句 ，然后再插入日志（每條數(shù)據(jù)5條日志），這個(gè)網(wǎng)絡(luò)交互的時(shí)間加上數(shù)據(jù)庫連接打開關(guān)閉的時(shí)間，總的執(zhí)行時(shí)間，大概10多分鐘。

用sql語句批量操作，可以說效率提升了 40倍，就是大量數(shù)據(jù)的傳輸和數(shù)據(jù)庫的處理次數(shù)耗時(shí)。

所以說，軟件開發(fā)不是開發(fā)完成就行，而是要解決性能上的問題，這才是開發(fā)的進(jìn)階。

以上就是MySql批量插入來實(shí)現(xiàn)優(yōu)化Sql執(zhí)行效率的實(shí)例代碼的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！