mysql百万级数据高效插入和更新
测试环境:win10系统+16G内存+mysql5.7.251.创建测试表(表结构如下)2.插入数据方法@Override@Transactional(rollbackFor = Exception.class)public long batchInsert() {//初始化1000000条数据List<Map...
·
测试环境:win10系统+16G内存+mysql5.7.25
1.创建测试表(表结构如下)
2.插入数据方法
@Override
@Transactional(rollbackFor = Exception.class)
public long batchInsert() {
//初始化1000000条数据
List<Map<String,String>> data = createList(1000000);
Long startTime = System.currentTimeMillis();
String addSql = "insert into aaa (first, second, three, four,five) VALUES(?,?,?,?,?)";
String updateSql = "update aaa set first = ?,second=?,three=?,four=?,five=? where id = ?";
jdbcTemplate.batchUpdate(addSql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setString(1, data.get(i).get("first"));
ps.setString(2, data.get(i).get("second"));
ps.setString(3, data.get(i).get("three"));
ps.setString(4, data.get(i).get("four"));
ps.setString(5, data.get(i).get("five"));
}
@Override
public int getBatchSize() {
return data.size();
}
});
Long endTime = System.currentTimeMillis();
System.out.println("插入"+data.size()+"条数据用时:" + (endTime - startTime) + "毫秒");
System.out.println("插入"+data.size()+"条数据用时:" + (endTime - startTime) / 1000 + "秒");
return data.size();
}
3.执行结果:
4.更新数据方法
@Override
@Transactional(rollbackFor = Exception.class)
public long batchInsert() {
List<Map<String,String>> data = createList(1000000);
Long startTime = System.currentTimeMillis();
String updateSql = "update aaa set first = ?,second=?,three=?,four=?,five=? where id = ?";
jdbcTemplate.batchUpdate(updateSql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setString(1, data.get(i).get("first"));
ps.setString(2, data.get(i).get("second"));
ps.setString(3, data.get(i).get("three"));
ps.setString(4, data.get(i).get("four"));
ps.setString(5, data.get(i).get("five"));
ps.setInt(6, 3212021 + i);
}
@Override
public int getBatchSize() {
return data.size();
}
});
Long endTime = System.currentTimeMillis();
System.out.println("更新"+data.size()+"条数据用时:" + (endTime - startTime) + "毫秒");
System.out.println("更新"+data.size()+"条数据用时:" + (endTime - startTime) / 1000 + "秒");
return data.size();
}
5.更新结果,一百万条数据五个字段全部被更新
6.注意
要开启批处理,方法一定要开启事务,数据量再大的话可多线程处理
更多推荐
已为社区贡献1条内容
所有评论(0)