V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
helloworldxk
V2EX  ›  Java

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

  •  
  •   helloworldxk · 2017-12-18 18:18:18 +08:00 · 3508 次点击
    这是一个创建于 2518 天前的主题,其中的信息可能已经有所发展或是发生改变。

    本人在做 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);
    
    		}
    	}
    
    13 条回复    2018-01-26 21:07:00 +08:00
    lhx2008
        1
    lhx2008  
       2017-12-18 18:29:05 +08:00 via Android
    要不生成一个 sql 文件直接在命令行那边导入算了
    helloworldxk
        2
    helloworldxk  
    OP
       2017-12-18 18:40:27 +08:00
    @lhx2008 这个要和前台交互,用户上传 excel 文件,我后台把 excel 文件里的数据保存到数据库
    cxzl25
        3
    cxzl25  
       2017-12-18 18:59:37 +08:00
    jdbc url 加上了?
    rewriteBatchedStatements=true
    helloworldxk
        4
    helloworldxk  
    OP
       2017-12-18 19:02:57 +08:00
    @cxzl25 加了,还是没用(无奈)
    tmac
        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();
    helloworldxk
        6
    helloworldxk  
    OP
       2017-12-18 20:02:38 +08:00
    @tmac 谢谢您的建议。回答一个问题,我之前一开始也是写了一个 for 循环,后来感觉两个 for 循环嵌套,可能会费时就改了。您说的第二个问题,我是想分批次提交可能快点,我也试过直接一次全部提交,然后就没有然后了,20 分钟都没反应。
    第三四问题是我考虑不周到了哈哈哈。谢谢建议啦~
    forestyuan
        7
    forestyuan  
       2017-12-18 20:20:59 +08:00
    没有 beginTransaction()就直接 commit()了吧
    alcarl
        8
    alcarl  
       2017-12-18 22:05:55 +08:00 via Android
    我压一块,是你哪个 insert 语句里 where 条件的问题,你那个 select 太慢了吧
    tmac
        9
    tmac  
       2017-12-18 22:47:46 +08:00 via Android
    @helloworldxk 表里数据量多少,where 没中索引?
    helloworldxk
        10
    helloworldxk  
    OP
       2017-12-19 17:49:12 +08:00
    @tmac 数据库对应的表没有数据,where 没有对应的字段没有设置索引,因为我们主管想做成一张通用的表,就是说什么类型的 excel 都可以存入这张表。,所以就没有加索引,怕到时候索引太多了
    tmac
        11
    tmac  
       2017-12-19 19:43:57 +08:00   ❤️ 1
    @helloworldxk 那我觉得你可以试试就存 insert into, 后面的 select 就去掉。
    helloworldxk
        12
    helloworldxk  
    OP
       2018-01-26 20:55:03 +08:00
    @tmac 忘记回复了(捂脸),嗯嗯,那个 select 确实会影响效率,不过那不是最影响的,我把 select 也去掉过,效率相差无几。最终找到的原因是经理要我建的那个表有问题,他总共有 62 个字段,其中有 56 个字段的类型都是 char(200),所以导致了特别慢
    helloworldxk
        13
    helloworldxk  
    OP
       2018-01-26 21:07:00 +08:00
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1010 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 22:50 · PVG 06:50 · LAX 14:50 · JFK 17:50
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.