jdbc插入大量数据时优化处理:转载https://www.jianshu.com/p/b455d5d7d659

最新推荐文章于 2022-03-15 12:10:41 发布

wb54979

最新推荐文章于 2022-03-15 12:10:41 发布

阅读量555

点赞数

分类专栏： MySQL jdbc 文章标签： jdbc处理千万数据

MySQL 同时被 2 个专栏收录

5 篇文章

订阅专栏

jdbc

1 篇文章

订阅专栏

博客介绍了JDBC使用MySQL处理大数据时的优化方法。普通执行每条数据都访问一次数据库，而批处理累积一定数量再提交，可减少交互、提高效率。还介绍了事务概念，默认关闭。通过代码展示了PreparedStatement批处理、启用事务处理及二者混合使用，结果显示二者结合处理大批量数据最快。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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();//执行批处理

注意：

如果使用了 addBatch() -> executeBatch() 还是很慢，那就得使用到这个参数了

              rewriteBatchedStatements=true (启动批处理操作)

              在数据库连接URL后面加上这个参数：      

                  String dbUrl =  "jdbc:mysql://localhost:3306/User? rewriteBatchedStatements=true";

在代码中，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=?");
          for(int i=0; i<10000; i++){
             pstmt.setString(1, "abc"+i);
             pstmt.setInt(2, id);
             pstmt.executeUpdate();
         }     
          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的内存参数](http://www.cnblogs.com/tommy-huang/p/4535116.html)

               if(i>0 && i%500==0){
                    pstmt.executeBatch();

                    //如果不想出错后，完全没保留数据，则可以没执行一次提交一次，但得保证数据不会重复

                    conn.commit();

                }

         }
          pstmt.executeBatch();//执行最后剩下不够500条的
          pstmt.close();

          conn.commit();//执行完后，手动提交事务
          conn.setAutoCommit(true);//在把自动提交打开
          conn.close();

较完整的代码：

 1  public class ExecuteBatchTest {
 2     private Connection conn;
 3     private PreparedStatement pstmt;
 4     private PreparedStatement pstmt2;
 5     private ResultSet rs;
 6     private String user = "root";
 7     private String password = "123456";
 8     private String dbUrl = "jdbc:mysql://localhost:3306/user?rewriteBatchedStatements=true";
 9     private int limitNum = 10000;
10 
11     public void changeData() {
12         try {
13             Class.forName("com.mysql.jdbc.Driver");
14             conn = DriverManager.getConnection(dbUrl, user, password);
15             
16             //既不用batch,也不用事务
17             testBatch(false,false);
18             //只用batch, 不用事务
19             testBatch(false,true);
20             //只用事务，不用batch
21             testBatch(true,false);
22             //不仅用事务，还用batch
23             testBatch(true,true);
24             
25             pstmt.close();
26             conn.close();
27         } catch (ClassNotFoundException e) {
28             e.printStackTrace();
29         } catch (SQLException e) {
30             e.printStackTrace();
31         }
32     }
33     
34     public void testBatch(Boolean openTransaction, Boolean useBatch) throws SQLException{
35         if(openTransaction)
36             conn.setAutoCommit(false);
37         
38         if(pstmt!=null){
39             pstmt.clearParameters();
40             pstmt.clearBatch();
41         }
42         
43         pstmt = conn.prepareStatement("insert into person (name) values (?)");
44         long start = System.currentTimeMillis();
45         for(int a = 0;a<limitNum;a++){
46             String name = "tommy"+a;
47             pstmt.setString(1, name);
48             if(useBatch)
49                 pstmt.addBatch();
50             else
51                 pstmt.executeUpdate();
52         }
53         
54         if(useBatch)
55            pstmt.executeBatch();
56          
57         if(openTransaction){
58             conn.commit();
59             conn.setAutoCommit(true);
60         }
61         long end = System.currentTimeMillis();
62         System.out.println("use time:"+(end-start)+" ms");
63         
64     }
65     
66     //main method
67     publi static void main(String[] args){
68         ExecuteBatchTest ebt = new ExecuteBatchTest();
69         ebt.changeData();
70     }
71        
72 }

运行结果：

image.png

分别是:
不用批处理，不用事务；
只用批处理，不用事务；
只用事务，不用批处理；
既用事务，也用批处理；（很明显，这个最快，所以建议在处理大批量的数据时，同时使用批处理和事务）

作者：zjk_00
链接：https://www.jianshu.com/p/b455d5d7d659
来源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。