mysql jdbc查询慢 spring,为什么Spring的jdbcTemplate.batchUpdate（）这么慢？

I'm trying to find the faster way to do batch insert.

I tried to insert several batches with jdbcTemplate.update(String sql), where

sql was builded by StringBuilder and looks like:

INSERT INTO TABLE(x, y, i) VALUES(1,2,3), (1,2,3), ... , (1,2,3)

Batch size was exactly 1000. I inserted nearly 100 batches.

I checked the time using StopWatch and found out insert time:

min[38ms], avg[50ms], max[190ms] per batch

I was glad but I wanted to make my code better.

After that, I tried to use jdbcTemplate.batchUpdate in way like:

jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

@Override

public void setValues(PreparedStatement ps, int i) throws SQLException {

// ...

}

@Override

public int getBatchSize() {

return 1000;

}

});

where sql was look like

INSERT INTO TABLE(x, y, i) VALUES(1,2,3);

and I was disappointed! jdbcTemplate executed every single insert of 1000 lines batch in separated way. I loked at mysql_log and found there a thousand inserts.

I checked the time using StopWatch and found out insert time:

min[900ms], avg[1100ms], max[2000ms] per Batch

So, can anybody explain to me, why jdbcTemplate doing separated inserts in this method? Why method's name is batchUpdate?

Or may be I am using this method in wrong way?

解决方案

These parameters in the JDBC connection URL can make a big difference in the speed of batched statements --- in my experience, they speed things up:

?useServerPrepStmts=false&rewriteBatchedStatements=true