mysql 处理大数据太慢_jdbc 大数据批量插入很慢问题解决

普通的执行过程是：每处理一条数据，就访问一次数据库；

而批处理是：累积到一定数量，再一次性提交到数据库，减少了与数据库的交互次数，所以效率会大大提高

但是使用了addBatch() -> executeBatch() 还是很慢

解决：

a.需配置 参数在数据库连接URL后面加上这个参数：

dbUrl"jdbc:mysql://localhost:3306/User?rewriteBatchedStatements=true";

b.conn.setAutoCommit(false);    需要设置 手动提交

完整例子：

1.正常JDBC 版本

String sql = "insert into table *****";

con.setAutoCommit(false);

ps = con.prepareStatement(sql);

for(int i=1; i<65536; i++){

ps.addBatch();

// 1w条记录插入一次

if (i % 10000 == 0){

ps.executeBatch();

con.commit();

}

}

// 最后插入不足1w条的数据

ps.executeBatch();

con.commit();

2.Spring boot jdbc版本

@Autowired

private JdbcTemplate jdbcTemplate;

private void batchInsertOrUpdateGoodsTemp(List list){

StringBuilder sql = new StringBuilder("INSERT INTO `gds_goods_temp` (`CAT_CODE`, `BRAND_CODE`, `GOODS_SN`, `GOODS_STATUS`, `GOODS_NAME` ").append(

", `IS_MARKET`, `IS_DELETE`, `GOODS_INTRO`, `GOODS_DETAIL`, `UNIT`, `WEIGHT`, `SPEC`, `BARCODE`, `GOODS_IMG`, `GOODS_THUMB`, `GOODS_GALLERY_IMGS`")

.append(", `MARKET_PRICE`, `REMARK`, `ADD_TYPE`, `ADD_MCH_NO`) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

KeyHolder key = new GeneratedKeyHolder();

this.jdbcTemplate.update(con -> {

con.setAutoCommit(false);

PreparedStatement preState =null;

try {

preState = con.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);

for(GoodsTemp temp : list){

preState.setString(1, temp.getCatCode());

preState.setString(2, temp.getBrandCode());

preState.setString(3, temp.getGoodsSn());

preState.setString(4, temp.getGoodsStatus());

preState.setString(5, temp.getGoodsName());

preState.setString(6, temp.getIsMarket());

preState.setString(7, temp.getIsDelete());

preState.setString(8, temp.getGoodsIntro());

preState.setString(9, temp.getGoodsDetail());

preState.setString(10, temp.getUnit());

preState.setBigDecimal(11, temp.getWeight());

preState.setString(12, temp.getSpec());

preState.setString(13, temp.getBarcode());

preState.setString(14, temp.getGoodsImg());

preState.setString(15, temp.getGoodsThumb());

preState.setString(16, temp.getGoodsGalleryImgs());

preState.setBigDecimal(17, temp.getMarketPrice());

preState.setString(18, temp.getRemark());

preState.setString(19, temp.getAddType());

preState.setString(20, temp.getAddMchNo());

preState.addBatch();

}

preState.executeBatch();

con.commit();

} catch (SQLException e) {

log.error("sql语句{}，批量执行错误：{}", sql,e.getMessage());

}

return preState;

}, key);

}