executeBatch()批量插入贼慢，求大神帮助啊，感激不尽，已经弄了几天了，硬是找不出问题
helloworldxk · 2017-12-18 18:18:18 +08:00 · 3528 次点击这是一个创建于 2532 天前的主题，其中的信息可能已经有所发展或是发生改变。
本人在做 excel 数据导入导数据库时碰到的问题，在执行 executeBatch()时就会卡住超级慢 数据库：mysql excel 文件：2W+行，56 列 步骤：首先把 excel 的数据解析出来处理放入 List<List<object>> list 中，然后循环批量插入到数据库
代码： //插入 excel 表中的一行数据
public void insert2(List<List<Object>> list) throws SQLException {
Connection connection = this.getConnection();
PreparedStatement ps = null;
int count = 0;
String sql = "INSERT INTO excel(type,batch_number,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,a20,a21,a22,a23,a24,a25,a26,a27,a28,a29,a30,a31,a32,a33,a34,a35,a36,a37,a38,a39,a40,a41,a42,a43,a44,a45,a46,a47,a48,a49,a50,a51,a52,a53,a54,a55,a56) SELECT ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? FROM DUAL WHERE NOT EXISTS(SELECT a1 FROM excel WHERE type = ? and a4 = ? and a8 = ? and a27 = ?)";
try{
connection.setAutoCommit(false);//设置手动提交
ps = connection.prepareStatement(sql);
for (int i = 1; i<list.size(); i++) {
int j1 = 0;
String str1 = null;
ps.setString(1, (String)list.get(i).get(0));
ps.setString(2, (String)list.get(i).get(1));
ps.setString(3, (String)list.get(i).get(2));
ps.setString(4, (String)list.get(i).get(3));
ps.setString(5, (String)list.get(i).get(4));
ps.setString(6, (String)list.get(i).get(5));
ps.setString(7, (String)list.get(i).get(6));
ps.setString(8, (String)list.get(i).get(7));
ps.setString(9, (String)list.get(i).get(8));
ps.setString(10, (String)list.get(i).get(9));
ps.setString(11, (String)list.get(i).get(10));
ps.setString(12, (String)list.get(i).get(11));
ps.setString(13, (String)list.get(i).get(12));
ps.setString(14, (String)list.get(i).get(13));
ps.setString(15, (String)list.get(i).get(14));
ps.setString(16, (String)list.get(i).get(15));
ps.setString(17, (String)list.get(i).get(16));
ps.setString(18, (String)list.get(i).get(17));
ps.setString(19, (String)list.get(i).get(18));
ps.setString(20, (String)list.get(i).get(19));
ps.setString(21, (String)list.get(i).get(20));
ps.setString(22, (String)list.get(i).get(21));
ps.setString(23, (String)list.get(i).get(22));
ps.setString(24, (String)list.get(i).get(23));
ps.setString(25, (String)list.get(i).get(24));
ps.setString(26, (String)list.get(i).get(25));
ps.setString(27, (String)list.get(i).get(26));
ps.setString(28, (String)list.get(i).get(27));
ps.setString(29, (String)list.get(i).get(28));
ps.setString(30, (String)list.get(i).get(29));
ps.setString(31, (String)list.get(i).get(30));
ps.setString(32, (String)list.get(i).get(31));
ps.setString(33, (String)list.get(i).get(32));
ps.setString(34, (String)list.get(i).get(33));
ps.setString(35, (String)list.get(i).get(34));
ps.setString(36, (String)list.get(i).get(35));
ps.setString(37, (String)list.get(i).get(36));
ps.setString(38, (String)list.get(i).get(37));
ps.setString(39, (String)list.get(i).get(38));
ps.setString(40, (String)list.get(i).get(39));
ps.setString(41, (String)list.get(i).get(40));
ps.setString(42, (String)list.get(i).get(41));
ps.setString(43, (String)list.get(i).get(42));
ps.setString(44, (String)list.get(i).get(43));
ps.setString(45, (String)list.get(i).get(44));
ps.setString(46, (String)list.get(i).get(45));
ps.setString(47, (String)list.get(i).get(46));
ps.setString(48, (String)list.get(i).get(47));
ps.setString(49, (String)list.get(i).get(48));
ps.setString(50, (String)list.get(i).get(49));
ps.setString(51, (String)list.get(i).get(50));
ps.setString(52, (String)list.get(i).get(51));
ps.setString(53, (String)list.get(i).get(52));
ps.setString(54, (String)list.get(i).get(53));
ps.setString(55, (String)list.get(i).get(54));
ps.setString(56, (String)list.get(i).get(55));
ps.setString(57, "");
ps.setString(58, "");
ps.setString(59, (String)list.get(i).get(0));
ps.setString(60, (String)list.get(i).get(5));
ps.setString(61, (String)list.get(i).get(9));
ps.setString(62, (String)list.get(i).get(28));
ps.addBatch();//加入批量处理
count++;
System.out.println(count);
if (i > 0 && i % 1000 == 0) {
ps.executeBatch();
connection.commit();
ps.clearBatch();
System.out.println("提交：" + i);
}
}
ps.executeBatch(); // 执行批量处理
connection.commit(); // 提交
connection.setAutoCommit(true);//在把自动提交打开
System.out.println("执行完毕:"+count);
}catch (Exception e) {
e.printStackTrace();
}finally {
this.close(connection, ps, null);
}
}
 |
1
lhx2008 2017-12-18 18:29:05 +08:00 via Android
要不生成一个 sql 文件直接在命令行那边导入算了
|
 |
2
helloworldxk OP @lhx2008 这个要和前台交互，用户上传 excel 文件，我后台把 excel 文件里的数据保存到数据库
|
 |
3
cxzl25 2017-12-18 18:59:37 +08:00
jdbc url 加上了？
rewriteBatchedStatements=true |
|
4
helloworldxk OP @cxzl25 加了，还是没用（无奈）
|
 |
5
tmac 2017-12-18 19:37:19 +08:00
第一个问题， 那些重复的 pst.setString(x,x),可以用一个循环替代。
第二个，if (i > 0 && i % 1000 == 0) { ps.executeBatch(); connection.commit(); //没必要的 commit ps.clearBatch(); System.out.println("提交：" + i); } 第三个,最后面的 executeBatch()，commit. 最好加上 if(i % 1000 != 0) 第四个，catch(){} 要加上 con.rollback(); |
|
6
helloworldxk OP @tmac 谢谢您的建议。回答一个问题，我之前一开始也是写了一个 for 循环，后来感觉两个 for 循环嵌套，可能会费时就改了。您说的第二个问题，我是想分批次提交可能快点，我也试过直接一次全部提交，然后就没有然后了，20 分钟都没反应。
第三四问题是我考虑不周到了哈哈哈。谢谢建议啦~ |
 |
7
forestyuan 2017-12-18 20:20:59 +08:00
没有 beginTransaction()就直接 commit()了吧
|
 |
8
alcarl 2017-12-18 22:05:55 +08:00 via Android
我压一块，是你哪个 insert 语句里 where 条件的问题，你那个 select 太慢了吧
|
|
9
tmac 2017-12-18 22:47:46 +08:00 via Android
@helloworldxk 表里数据量多少，where 没中索引？
|
|
10
helloworldxk OP @tmac 数据库对应的表没有数据，where 没有对应的字段没有设置索引，因为我们主管想做成一张通用的表，就是说什么类型的 excel 都可以存入这张表。，所以就没有加索引，怕到时候索引太多了
|
|
11
tmac 2017-12-19 19:43:57 +08:00  1
@helloworldxk 那我觉得你可以试试就存 insert into， 后面的 select 就去掉。
|
|
12
helloworldxk OP @tmac 忘记回复了（捂脸），嗯嗯，那个 select 确实会影响效率，不过那不是最影响的，我把 select 也去掉过，效率相差无几。最终找到的原因是经理要我建的那个表有问题，他总共有 62 个字段，其中有 56 个字段的类型都是 char(200)，所以导致了特别慢
|
|
13
helloworldxk OP  |
Select Language