批量插入数据速度问题？

客官°小女子只卖身不卖艺 2024-04-18 08:59 90阅读 0赞

# 原文：[https://www.cnblogs.com/tommy-huang/p/4540407.html][https_www.cnblogs.com_tommy-huang_p_4540407.html] #

# [JDBC的PreparedStatement启动事务使用批处理executeBatch()][https_www.cnblogs.com_tommy-huang_p_4540407.html] #

JDBC使用MySQL处理大数据的时候,自然而然的想到要使用批处理，
    
    普通的执行过程是：每处理一条数据，就访问一次数据库；
    
    而批处理是：累积到一定数量，再一次性提交到数据库，减少了与数据库的交互次数，所以效率会大大提高
    
    至于事务：事务指逻辑上的一组操作，组成这组操作的各个单元，要不全部成功，要不全部不成功，默认是关闭事务的。
    
    1. PreparedStatement使用批处理 executeBatch()
           1.1. 不使用executeBatch()，而使用executeUpdate()
              代码如下：  
    
             Class.forName("com.mysql.jdbc.Driver");
             Connection conn = DriverManager.getConnection(dbUrl, user, password);
             PreparedStatement pstmt = conn.prepareStatement("update content set introtext=? where id=?");
    
             for(int i=0; i<10000; i++){
                 pstmt.setString(1, "abc"+i);
                 pstmt.setInt(2, id);
                 pstmt.executeUpdate();
             }
    
             这样，更新10000条数据，就得访问数据库10000次
    
          1.2. 而使用executeBatch()
              代码如下：
    
             Class.forName("com.mysql.jdbc.Driver");
             Connection conn = DriverManager.getConnection(dbUrl, user, password);
             PreparedStatement pstmt = conn.prepareStatement("update content set introtext=? where id=?");
    
             for(int i=0; i<10000; i++){
                 pstmt.setString(1, "abc"+i);
                 pstmt.setInt(2, id);
                 pstmt.addBatch();//添加到同一个批处理中
             }
    
             pstmt.executeBatch();//执行批处理
    
    
    
             注意：1. 如果使用了 addBatch() -> executeBatch() 还是很慢，那就得使用到这个参数了
    
                          rewriteBatchedStatements=true (启动批处理操作)
    
                          在数据库连接URL后面加上这个参数：      
    
                              String dbUrl =  "jdbc:mysql://localhost:3306/User? rewriteBatchedStatements=true";
    
                          2. 在代码中，pstmt的位置不能乱放，
    
                              //必须放在循环体外
    
                         pstmt = conn.prepareStatement("update content set introtext=? where id=?");
    
                         for(int i=0; i<10000; i++){
    
                               //放这里，批处理会执行不了，因为每次循环重新生成了pstmt，不是同一个了
    
                               //pstmt = conn.prepareStatement("update content set introtext=? where id=?");
                               pstmt.setString(1, "abc"+i);
                               pstmt.setInt(2, id);
                               pstmt.addBatch();//添加到同一个批处理中
                         }
    
                         pstmt.executeBatch();//执行批处理
    
    2. 启用事务处理
              Class.forName("com.mysql.jdbc.Driver");
    
    
    
              Connection conn = DriverManager.getConnection(dbUrl, user, password);
    
              conn.setAutoCommit(false);//将自动提交关闭
              PreparedStatement pstmt = conn.prepareStatement("update content set introtext=? where id=?");
              pstmt.setString(1, tempintrotext);
              pstmt.setInt(2, id);
              pstmt.addBatch();
              pstmt.executeBatch();
              pstmt.close();
    
    
              conn.commit();//执行完后，手动提交事务
              conn.setAutoCommit(true);//在把自动提交打开
              conn.close();
    
    3. 事务和批处理混合使用      
              Class.forName("com.mysql.jdbc.Driver");
    
              Connection conn = DriverManager.getConnection(dbUrl, user, password);
    
              conn.setAutoCommit(false);//将自动提交关闭
              PreparedStatement pstmt = conn.prepareStatement("update content set introtext=? where id=?");
    
              for(int i=0; i<1000000; i++){
                   pstmt.setString(1, tempintrotext);
                   pstmt.setInt(2, id);
                   pstmt.addBatch();
    
                   //每500条执行一次，避免内存不够的情况，可参考，Eclipse设置JVM的内存参数
    
                   if(i>0 && i%500==0){
                        pstmt.executeBatch();
    
                        //如果不想出错后，完全没保留数据，则可以每执行一次提交一次，但得保证数据不会重复
    
                        conn.commit();
    
                    }
    
             }
              pstmt.executeBatch();//执行最后剩下不够500条的
              pstmt.close();
    
              conn.commit();//执行完后，手动提交事务
              conn.setAutoCommit(true);//在把自动提交打开
              conn.close();
    
    
    
    代码：
    
    public class ExecuteBatchTest {
        private Connection conn;
        private PreparedStatement pstmt;
        private PreparedStatement pstmt2;
        private ResultSet rs;
        private String user = "root";
        private String password = "123456";
        private String dbUrl = "jdbc:mysql://localhost:3306/user?rewriteBatchedStatements=true";
        private int limitNum = 10000;
    
        public void changeData() {
            try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection(dbUrl, user, password);
                
                //既不用batch,也不用事务
                testBatch(false,false);
                //只用batch, 不用事务
                testBatch(false,true);
                //只用事务，不用batch
                testBatch(true,false);
                //不仅用事务，还用batch
                testBatch(true,true);
                
                pstmt.close();
                conn.close();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        public void testBatch(Boolean openTransaction, Boolean useBatch) throws SQLException{
            if(openTransaction)
                conn.setAutoCommit(false);
            
            if(pstmt!=null){
                pstmt.clearParameters();
                pstmt.clearBatch();
            }
            
            pstmt = conn.prepareStatement("insert into person (name) values (?)");
            long start = System.currentTimeMillis();
            for(int a = 0;a<limitNum;a++){
                String name = "tommy"+a;
                pstmt.setString(1, name);
                if(useBatch)
                    pstmt.addBatch();
                else
                    pstmt.executeUpdate();
            }
            
            if(useBatch)
               pstmt.executeBatch();
             
            if(openTransaction){
                conn.commit();
                conn.setAutoCommit(true);
            }
            long end = System.currentTimeMillis();
            System.out.println("use time:"+(end-start)+" ms");
            
        }
        
        //main method
        publi static void main(String[] args){
            ExecuteBatchTest ebt = new ExecuteBatchTest();
            ebt.changeData();
        }
           
    }

[https_www.cnblogs.com_tommy-huang_p_4540407.html]: https://www.cnblogs.com/tommy-huang/p/4540407.html