这次是需要用到批量insert和批量update。原理是Mysql本身支持批量xx的。
1.
MyBatis批量插入几千条数据慎用foreach
2.
Mybatis中进行批量更新
mybatis批量更新及其效率问题
优化:
问题仍然是xml里#{}搞循环(foreach)[预处理耗时]引起的性能问题,上次通过转为在java里循环,xml换成${} 。
这次优化:
MyBatis批量插入几千条数据慎用foreach
2020年4月14日18:06:54欲优化,经测试,发现Mybatis+Mysql的“一条sql批量insert(foreach)”性能还行啊,不用优化了:
准备工作:制作“set字段”代码:
//先获取项目中insert之前做了哪些字段:重写对象的toString,在insert之前打印一下
//拿到一个对象的信息,然后写了个main,把这些字段做成“set字段”代码
public class Demo {
public static void main(String[] args) {
String s=" month = \"202001\",\n" +
" salaryType = \"SlaryType01\", \n" +
" hrId = \"C2011\", \n" +
" specialItemTotal = null, \n" +
" socialFundTotal = null, \n" +
" personalSocialSecurity = null, \n" +
" personalAccumulationFund = 0.0, \n" +
" companySocialSecurity = null, \n" +
" companyAccumulationFund = null, \n" +
" deductedTotal = null, \n" +
" overtimeTotal = null, \n" +
" salaryFileTotal = null, \n" +
" comprehensiveSalary = null, \n" +
" grossPay = null, \n" +
" taxableIncome = null, \n" +
" personalIncomeTax = null, \n" +
" lastMonthTax = null, \n" +
" taxDifference = null, \n" +
" accumulativeTax = null, \n" +
" lastMonthDeserved = null, \n" +
" leaveDays = null, \n" +
" sickLeaveDays = null, \n" +
" sickLeaveSubsidy = null, \n" +
" nightShiftDays = null, \n" +
" dutyAttendanceDays = null,\n" +
" realAttendanceDays = null,\n" +
" realWages = null, \n" +
" checkStatus = null,\n" +
" accountingStatus = null, \n" +
" accountingId = \"null\", \n" +
" accountingTime = null, \n" +
" updatorId = \"C344\", \n" +
" updataTime = Tue Apr 14 16:28:17 CST 2020, \n" +
" checkTime = null,\n" +
" cumulativeMonths = null, \n" +
" cumulativeRealWages = null,\n" +
" isDel = null, \n" +
" accumulativeSpecialItemTotal = null, \n" +
" accumulativeSocialFundTotal = null, \n" +
" accumulativeOvertimeTotal = null,\n" +
" accumulativeGrossPay = null, \n" +
" threshold = null, \n" +
" accumulativeThreshold = null,\n" +
" preTaxSalary = null, \n" +
" busPayEntity = null, \n" +
" socialFundEntity = null, \n" +
" specialItemEntity = null, \n" +
" salaryChangeEntity = null, \n" +
" salaryTypeName = \"null\", \n" +
" grossPayP = \"null\", \n" +
" cName = \"蔡徐鲲\", \n" +
" cDeptId = 66, \n" +
" cDeptName = \"管理部\",\n" +
" attendanceDayNum = null, \n" +
" merits = \"300.0\", \n" +
" mealAllowance = 0.0,\n" +
" travelSubsidy = 0.0,\n" +
" otherPay = 0.0, \n" +
" rewardAndPunishNum = null, \n" +
" sickManHour = null,\n" +
" staySubsidy = null, \n" +
" nightSubsidy = null,\n" +
" childEducation = 0.0,\n" +
" houseLoan = 0.0, \n" +
" houseRent = 0.0, \n" +
" olderSupport = 0.0, \n" +
" continueEducation = 0.0, \n" +
" illnessSubsidy = 0.0, \n" +
" cumulativeRealPay = null,\n" +
" cumulativeMonthNum = null,\n" +
" creditCardNum = \"1234554321\", \n" +
" credType = \"null\", \n" +
" credNum = \"null\",\n" +
" pension = 235.2, \n" +
" medicalCare = 58.8, \n" +
" unemployment = 14.7, \n" +
" personFund = 0.0,\n" +
" enterpriseAnnuity = null,\n" +
" commercialHealthInsurance = null, \n" +
" taxDeferredPension = null, \n" +
" other = null, \n" +
" allowSubDonateNum = null,\n" +
" taxDeduction = null, \n" +
" remark = \"null\",\n" +
" payUse = \"null\", \n" +
" certType = \"居民身份证\", \n" +
" certNum = \"3205831970\" ";
// System.out.println(s);
String[] split1 = s.split(",");
for (int i = 0; i < split1.length; i++) {
String[] split2 = split1[i].split("=");
String x=split2[0].trim().substring(0,1).toUpperCase()+split2[0].trim().substring(1);
// System.out.println("x--"+x);
String front="es.set"+x+"(";
String mid=split2[1];
String back=");";
System.out.println(front+mid+back);
}
}
}
//批量查询 测试批量插入性能
@Test
public void test() {
List<EhrSalaryCheckingEntity> esList = null;
esList = new ArrayList<>();
List<EhrSalaryCheckingEntity> initFullCheckingList=new ArrayList<EhrSalaryCheckingEntity>();
for(int i=0;i<50000;i++) {
EhrSalaryCheckingEntity es = new EhrSalaryCheckingEntity();
es.setMonth("202001");
es.setSalaryType("SlaryType01");
es.setHrId("C2011"+i);
es.setSpecialItemTotal(null);
es.setSocialFundTotal(null);
es.setPersonalSocialSecurity(null);
es.setPersonalAccumulationFund(new BigDecimal(0.0));
es.setCompanySocialSecurity(null);
es.setCompanyAccumulationFund(null);
es.setDeductedTotal(null);
es.setOvertimeTotal(null);
es.setSalaryFileTotal(null);
es.setComprehensiveSalary(null);
es.setGrossPay(null);
es.setTaxableIncome(null);
es.setPersonalIncomeTax(null);
es.setLastMonthTax(null);
es.setTaxDifference(null);
es.setAccumulativeTax(null);
es.setLastMonthDeserved(null);
es.setLeaveDays(null);
es.setSickLeaveDays(null);
es.setSickLeaveSubsidy(null);
es.setNightShiftDays(null);
es.setDutyAttendanceDays(null);
es.setRealAttendanceDays(null);
es.setRealWages(null);
es.setCheckStatus(null);
es.setAccountingStatus(null);
es.setAccountingId("null");
es.setAccountingTime(null);
es.setUpdatorId("CWA344");
es.setUpdataTime(new Date());
es.setCheckTime(null);
es.setCumulativeMonths(null);
es.setCumulativeRealWages(null);
es.setIsDel(null);
es.setAccumulativeSpecialItemTotal(null);
es.setAccumulativeSocialFundTotal(null);
es.setAccumulativeOvertimeTotal(null);
es.setAccumulativeGrossPay(null);
es.setThreshold(null);
es.setAccumulativeThreshold(null);
es.setPreTaxSalary(null);
es.setBusPayEntity(null);
es.setSocialFundEntity(null);
es.setSpecialItemEntity(null);
es.setSalaryChangeEntity(null);
es.setSalaryTypeName("null");
es.setGrossPayP("null");
es.setcName("蔡徐鲲");
es.setcDeptId(66);
es.setcDeptName("管理部");
es.setAttendanceDayNum(null);
es.setMerits("300.0");
es.setMealAllowance(new BigDecimal(0.0));
es.setTravelSubsidy(new BigDecimal(0.0));
es.setOtherPay(new BigDecimal(0.0));
es.setRewardAndPunishNum(null);
es.setSickManHour(null);
es.setStaySubsidy(null);
es.setNightSubsidy(null);
es.setChildEducation(new BigDecimal(0.0));
es.setHouseLoan(new BigDecimal(0.0));
es.setHouseRent(new BigDecimal(0.0));
es.setOlderSupport(new BigDecimal(0.0));
es.setContinueEducation(new BigDecimal(0.0));
es.setIllnessSubsidy(new BigDecimal(0.0));
es.setCumulativeRealPay(null);
es.setCumulativeMonthNum(null);
es.setCreditCardNum("1234554321"+i);
es.setCredType("null");
es.setCredNum("null");
es.setPension(new BigDecimal(235.2));
es.setMedicalCare(new BigDecimal(58.8));
es.setUnemployment(new BigDecimal(14.7));
es.setPersonFund(new BigDecimal(0.0));
es.setEnterpriseAnnuity(null);
es.setCommercialHealthInsurance(null);
es.setTaxDeferredPension(null);
es.setOther(null);
es.setAllowSubDonateNum(null);
es.setTaxDeduction(null);
es.setRemark("null");
es.setPayUse("null");
es.setCertType("居民身份证");
es.setCertNum("32058319906"+i);
initFullCheckingList.add(es);
}
//计算 批量插入 耗时
System.out.println("开始计算---");
int total = 0;
long start = System.currentTimeMillis();
Integer insert = ehrSalaryCheckingDao.setEntity(initFullCheckingList);
System.out.println("计算完毕---");
long end = System.currentTimeMillis();
System.out.println("用时:---" + (end - start) + "ms.");
EhrSalaryCheckingEntity esNB = new EhrSalaryCheckingEntity();
esNB.setMonth((end - start)+"");
esNB.setHrId("耗时");
ehrSalaryCheckingDao.insert(esNB);
System.out.println("-----游戏结束------");
}
/**
* 汇总:
* setEntity方法:
* 5条--178ms
* 1000条--3457ms
* 3000条--6755ms
* 6000条--10780ms
* 10000条--9,297ms
* 50000条--119,369ms
* 经测试,88列,3000行,使用“一条sql批量insert(foreach)”,耗时6s,还可以,没有优化的意义。博文那人花了14分钟。他有毒^_^。
*/