【Java-Mybatis-MySQL】创建和插入百万级别数据的优化及时间比较

先进行调优：

1. 数据库连接url中增加一个rewriteBatchedStatements=true（开启批量插入）

    url: jdbc:mysql://192.168.0.147:3306/gnnykj_dev?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2B8

2. 用foreach拼接sql插入语句

 <insert id="batchInsert" parameterType="java.util.List" >
        insert into ele_monitor
        (
            id,bdjm,bdz,rqsj,ygdl,wgdl,fwgdl,fygdl,onexxwg,threexxwg,twoxxwg,fourxxwg,aa,av,ba,bv,ca,cv,glcs,szgl,wggl,axgl,axglcs,axwgl,bxgl,bxglcs,bxwgl,cxgl,cxglcs,cxwgl,yggl,dwpl,uab_ua_angle,ub_angle,ucb_uc_angle,zglcs,dlbph,ia_angle,ib_angle,ic_angle
        )
        values
        <foreach collection="list" index="index" item="monitor" separator=",">
            (
                #{monitor.id},#{monitor.bdjm},#{monitor.bdz},#{monitor.rqsj},#{monitor.ygdl},#{monitor.wgdl},
                #{monitor.fwgdl},#{monitor.fygdl},#{monitor.onexxwg},#{monitor.threexxwg},#{monitor.twoxxwg},
                #{monitor.fourxxwg},#{monitor.aa},#{monitor.av},#{monitor.ba},#{monitor.bv},#{monitor.ca},
                #{monitor.cv},#{monitor.glcs},#{monitor.szgl},#{monitor.wggl},#{monitor.axgl},#{monitor.axglcs},
                #{monitor.axwgl},#{monitor.bxgl},#{monitor.bxglcs},#{monitor.bxwgl},#{monitor.cxgl},#{monitor.cxglcs},
                #{monitor.cxwgl},#{monitor.yggl},#{monitor.dwpl},#{monitor.uabUaAngle},#{monitor.ubAngle},
                #{monitor.ucbUcAngle},#{monitor.zglcs},#{monitor.dlbph},#{monitor.iaAngle},#{monitor.ibAngle},
                #{monitor.icAngle}
            )
        </foreach>
    </insert>

环境配置

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

4. 上代码

    @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