关于mybatis:mybatis-百万数据插入

开启批量插入

1. 数据库连贯 url 中减少一个 rewriteBatchedStatements=true（开启批量插入）

2. 用 foreach 拼接 sql 插入语句

环境配置

1. 环境阐明：WIN10+16G 内存 +i7-4770U3.40GHz+JDK8+MySQL8.0+

   上代码

@Test

public void monitor(){
    // 循环次数
    int count = 100;
    // 每次插入条数
    int size = 10000;
    Calendar calendar = Calendar.getInstance();
    //calendar 的月从 0 开始算
    calendar.set(2011,05,01,0,0,0);
    long dayTime = calendar.getTimeInMillis();
    long sumTime = 0L;
    for (int i = 0; i < count; i++) {List<Monitor> monitors = new ArrayList<Monitor>();
        for (int j = 0; j < size; j++) {Monitor monitor = new Monitor();
            monitor.setId(IdWorker.getId());
            monitor.setBdjm("铸造二期 3# 进线");
            monitor.setBdz("00000000023");
            monitor.setRqsj(new Date(dayTime));
            dayTime += 300_000L; // 5*60*1000 为 5 分钟
            monitor.setYgdl(RandomUtil.getRandomNumber(1,10));
            monitor.setWgdl(RandomUtil.getRandomNumber(1,10));
            monitor.setFwgdl(RandomUtil.getRandomNumber(1,10));
            monitor.setFygdl(RandomUtil.getRandomNumber(1,10));
            monitor.setOnexxwg(RandomUtil.getRandomNumber(1,10));
            monitor.setThreexxwg(RandomUtil.getRandomNumber(1,10));
            monitor.setTwoxxwg(RandomUtil.getRandomNumber(1,10));
            monitor.setFourxxwg(RandomUtil.getRandomNumber(1,10));
            monitor.setAa(RandomUtil.getRandomNumber(1,10));
            monitor.setAv(RandomUtil.getRandomNumber(1,10));
            monitor.setBa(RandomUtil.getRandomNumber(1,10));
            monitor.setBv(RandomUtil.getRandomNumber(1,10));
            monitor.setCa(RandomUtil.getRandomNumber(1,10));
            monitor.setCv(RandomUtil.getRandomNumber(1,10));
            monitor.setGlcs(RandomUtil.getRandomNumber(1,10));
            monitor.setSzgl(RandomUtil.getRandomNumber(1,10));
            monitor.setWggl(RandomUtil.getRandomNumber(1,10));
            monitor.setAxgl(RandomUtil.getRandomNumber(1,10));
            monitor.setAxglcs(RandomUtil.getRandomNumber(1,10));
            monitor.setAxwgl(RandomUtil.getRandomNumber(1,10));
            monitor.setBxgl(RandomUtil.getRandomNumber(1,10));
            monitor.setBxglcs(RandomUtil.getRandomNumber(1,10));
            monitor.setBxwgl(RandomUtil.getRandomNumber(1,10));
            monitor.setCxgl(RandomUtil.getRandomNumber(1,10));
            monitor.setCxglcs(RandomUtil.getRandomNumber(1,10));
            monitor.setCxwgl(RandomUtil.getRandomNumber(1,10));
            monitor.setYggl(RandomUtil.getRandomNumber(1,10));
            monitor.setDwpl(RandomUtil.getRandomNumber(1,10));
            monitor.setUabUaAngle(RandomUtil.getRandomNumber(1,10));
            monitor.setUbAngle(RandomUtil.getRandomNumber(1,10));
            monitor.setUcbUcAngle(RandomUtil.getRandomNumber(1,10));
            monitor.setZglcs(RandomUtil.getRandomNumber(1,10));
            monitor.setDlbph(RandomUtil.getRandomNumber(1,10));
            monitor.setIaAngle(RandomUtil.getRandomNumber(1,10));
            monitor.setIbAngle(RandomUtil.getRandomNumber(1,10));
            monitor.setIcAngle(RandomUtil.getRandomNumber(1,10));
            monitors.add(monitor);
        }
        Instant inst1 = Instant.now();
        monitorService.batchInsert(monitors);
        Instant inst2 = Instant.now();
        sumTime += ChronoUnit.MILLIS.between(inst1, inst2);
    }
    System.out.println(formatTime(sumTime) );
}
public static String formatTime(Long ms) {
    Integer ss = 1000;
    Integer mi = ss * 60;
    Integer hh = mi * 60;
    Integer dd = hh * 24;

    Long day = ms / dd;
    Long hour = (ms - day * dd) / hh;
    Long minute = (ms - day * dd - hour * hh) / mi;
    Long second = (ms - day * dd - hour * hh - minute * mi) / ss;
    Long milliSecond = ms - day * dd - hour * hh - minute * mi - second * ss;

    StringBuffer sb = new StringBuffer();
    if(day > 0) {sb.append(day+"天");
    }
    if(hour > 0) {sb.append(hour+"小时");
    }
    if(minute > 0) {sb.append(minute+"分");
    }
    if(second > 0) {sb.append(second+"秒");
    }
    if(milliSecond > 0) {sb.append(milliSecond+"毫秒");
    }
    return sb.toString();}