foreach 和 数据库批量执行 效率比较

 

第一种写法： foreach 批量插入 20W数据，1000条提交一次

 

	@ApiOperation(value = "单线程,800批量插入，10W条，简单数据，耗时月4秒",notes="TPS约2.5W")
	@RequestMapping(value="insert/singleThreadBatch", method= {RequestMethod.GET})
	public String singleThreadBatch(BatchIn in) throws Exception{
		int batchCount = in.getBatchCount();
		
		List<Goods> goodsList = new ArrayList<>();
		for(int i= 1;i<=in.getExecuteCount();i++) {
			goodsList.add( Goods.randomGoods() );
			
			if(i != 0 &&  i%batchCount==0 ) {
				goodsService.addBatch( goodsList );
				goodsList.clear();
			}
		}
		if( !goodsList.isEmpty() ) {
			goodsService.addBatch( goodsList );
			goodsList.clear();
		}
		
		return "OK";
	}

　　

 

	@Override
	@Transactional
	@ShowParam
	public void addBatch(List<Goods> goodsList) {
		goodsExMapper.addBatch(goodsList);
	}

　　

 <insert id="addBatch">
        insert into goods (id,
        name,
        stock,
        des,
        data,
        create_date)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.id,jdbcType=BIGINT},
            #{item.name,jdbcType=VARCHAR},
            #{item.stock,jdbcType=INTEGER},
            #{item.des,jdbcType=VARCHAR},
            #{item.data,jdbcType=LONGVARCHAR},
            #{item.createDate,jdbcType=TIMESTAMP})
        </foreach>
    </insert>

　　

 

 

 

第二种写法   指定 ExecutorType.BATCH 一次提交

	@RequestMapping(value="insert/addBatchByExecutorType", method= {RequestMethod.GET})
	public String singleThreadBatch2(BatchIn in) throws Exception{
		int batchCount = in.getBatchCount();

		goodsService.addBatchByExecutorType( in );

		return "OK";
	}

　

	@Transactional
	@Override
	public void addBatchByExecutorType(BatchIn in) {
		SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
		GoodsExMapper mapper = session.getMapper(GoodsExMapper.class);
		int batchCount = in.getBatchCount();
		List<Goods> goodsList = new ArrayList<>();
		for(int i= 1;i<=in.getExecuteCount();i++) {
			mapper.insert(Goods.randomGoods());
		}
		session.commit();
	}

　　

 

 

 

 

--------------------------------------------------------------下面的测试数据都是三次测试区取中间值,不精确，但是有一定参考性-------------------------------------------------

 

在 没有指定 rewriteBatchedStatements=true 的时候比较

 

　　foreach 插入10W条耗时： 12.7秒

　　ExecutorType.BATCH 插入10 W条耗时：15.2秒

 

开启 rewriteBatchedStatements=true 的情况下

 

　　foreach 插入10W条耗时： 14.1秒 （

　　ExecutorType.BATCH 插入10 W条耗时：8.4秒

 

 

实测 rewriteBatchedStatements=true 并不会导致事务数据不一致的问题。前提是要放在事务环境中（@Transactional），实测不放在事务环境略快（session.commit() 是必须的）。

 

 

 

至于单次插入如果每次插入一个单独提交确实很慢，如果 多个单次(100-1000个单次插入在一个事务中提交)插入 在 一个 事务中国提交 ，也能做到每秒写入2000 多条数据。

 

结论

　　1  foreach 依旧是 比较合理 批量插入方案，

　　2 如果你的程序只是为了批量插入服务，那么开启rewriteBatchedStatements=true 和 ExecutorType.BATCH 确实效率比 foreach 快 50% 左右

以上测试数据库版本是mysql5.7 。

 

 

 

　　实测mysql 8 不开 rewriteBatchedStatements=true 的时候   ExecutorType.BATCH 写法的 效率是 foreach 1/2。

　　　　

　　　　20W 数据  ExecutorType.BATCH  写法  37.5秒（ 直接使用preparedStatement.executeBatch() 更加快，能到27.5秒）

 

　　　　20W 数据  foreach  写法  21秒

 

　　　　

　　实测mysql 8 开启  rewriteBatchedStatements=true 的时候  ExecutorType.BATCH 写法的 效率是 foreach的2倍。

　　　　20W 数据  ExecutorType.BATCH  写法  11.2秒（ 直接使用preparedStatement.executeBatch() 更加快，能到4秒）

　　　　20W 数据  foreach  写法  22秒

　　硬件更差的情况下mysql8.0比 mysql5.7 都要快不少。mysql 8的硬件 是 4代i5   stata sdd    ddr3 16g，mysql 5.7 的是 2代 r5      pcie ssd   ddr4  16g。

 

mysql8  window   i5   stata sdd    ddr3 16g 默认配置下：

　　数据数据在800W 的时候 SELECT count(id) from goods 的时间约3.2秒（innodb 是去统计的，myisam 不会）。

　　数据数据在400W 的时候 SELECT count(id) from goods 的时间约1.8秒）。

　　数据数据在260W 的时候 SELECT count(id) from goods 的时间约1.1秒。

　　数据数据在100W的时候 SELECT count(id) from goods 的时间约0.58秒。

　　数据数据在50W的时候 SELECT count(id) from goods 的时间约0.28秒。

　　数据数据在25W的时候 SELECT count(id) from goods 的时间约0.21秒。

　　数据数据在15W的时候 SELECT count(id) from goods 的时间约0.19秒。

　　数据数据在10W的时候 SELECT count(id) from goods 的时间约0.03秒。

 

 

备注：通常来说， select count(*) from goods 的写法更加好，这样是自动选择统计列 普通索引列(数据结构比主键索引列矮胖)>主键>非索引列

 

扫描数据在 10W-15W 的时候 查询速度有数量级的变化？是因为 跨区了还是用了多段数据？