mysql executebatch_executeBatch()批量插入贼慢，求大神帮助啊，感激不尽，已经弄了几天了，硬是找不出问题...

本人在做 excel 数据导入导数据库时碰到的问题，在执行 executeBatch()时就会卡住超级慢

数据库：mysql

excel 文件：2W+行，56 列

步骤：首先把 excel 的数据解析出来处理放入 List> list 中，然后循环批量插入到数据库

代码：

//插入 excel 表中的一行数据

public void insert2(List> 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

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);

}

}