java使用JDBC向MySQL数据库批次插入10W条数据测试效率

红太狼 2022-04-23 11:04 324阅读 0赞

使用JDBC连接MySQL数据库进行数据插入的时候，特别是大批量数据连续插入（100000），如何提高效率呢？  
在JDBC编程接口中Statement 有两个方法特别值得注意：  
通过使用addBatch（）和executeBatch（）这一对方法可以实现批量处理数据。  
不过值得注意的是，首先需要在数据库链接中设置手动提交，connection.setAutoCommit(false)，然后在执行Statement之后执行connection.commit()。

import java.io.BufferedReader;  
import java.io.IOException;  
import java.sql.DriverManager;  
import java.sql.PreparedStatement;  
import java.sql.SQLException;  
import java.util.Date;  
import com.mysql.jdbc.Connection;  
public class MysqlBatchUtil \{  
    private String sql="INSERT INTO db\_test (param1,param2,param3,param4,param5) VALUES (?,?,?,?,?)";    
    private String charset="utf-8";    
    private String connectStr="jdbc:mysql://localhost:3306/test";  
    private String username="root";    
    private String password="123456";    
    private void doStore() throws ClassNotFoundException, SQLException, IOException \{    
        Class.forName("com.mysql.jdbc.Driver");    
        connectStr += "?useServerPrepStmts=false&rewriteBatchedStatements=true";//此处是测试高效批次插入，去掉之后执行时普通批次插入  
        Connection conn = (Connection) DriverManager.getConnection(connectStr, username,password);    
        conn.setAutoCommit(false); // 设置手动提交    
        int count = 0;    
        PreparedStatement psts = conn.prepareStatement(sql);    
        String line = null;    
        Date begin=new Date();  
        for(int i=0;i<=100000;i++)\{  
            psts.setString(1, i+"param1");    
            psts.setString(2, i+"param2");    
            psts.setString(3, i+"param3");    
            psts.setString(4, i+"param4");    
            psts.setString(5, i+"param5");    
            psts.addBatch();          // 加入批量处理    
            count++;        
        \}    
        psts.executeBatch(); // 执行批量处理    
        conn.commit();  // 提交    
        Date end=new Date();  
        System.out.println("数量="+count);    
        System.out.println("运行时间="+(end.getTime()-begin.getTime()));  
        conn.close();    
    \}    
    public static void main(String\[\] args) \{  
        try \{  
            new MysqlBatchUtil().doStore();  
        \} catch (ClassNotFoundException e) \{  
            e.printStackTrace();  
        \} catch (SQLException e) \{  
            e.printStackTrace();  
        \} catch (IOException e) \{  
            e.printStackTrace();  
        \}  
    \}  
\}

测试结果：

数量=100001
    运行时间=4725

一共10W，执行时间一共花费 47 秒.  
这个效率仍然不高，似乎没有达到想要的效果，需要进一步改进。  
在MySQL JDBC连接字符串中还可以加入参数，  
rewriteBatchedStatements=true  
mysql默认关闭了batch处理，通过此参数进行打开，这个参数可以重写向数据库提交的SQL语句  
useServerPrepStmts=false  
如果不开启(useServerPrepStmts=false)，使用com.mysql.jdbc.PreparedStatement进行本地SQL拼装，最后送到db上就是已经替换了?后的最终SQL.  
在此稍加改进，连接字符串中加入下面语句（代码构造方法中去掉注释）：  
connectStr += "?useServerPrepStmts=false&rewriteBatchedStatements=true";  
再次测试结果如下：

数量=100001
    运行时间=1213

同样的数据量，这次执行只花费了12秒 ，由此可见处理效率大大提高

转载自：[https://www.cnblogs.com/wangyayun/archive/2016/12/29/6231832.html][https_www.cnblogs.com_wangyayun_archive_2016_12_29_6231832.html]

[https_www.cnblogs.com_wangyayun_archive_2016_12_29_6231832.html]: https://www.cnblogs.com/wangyayun/archive/2016/12/29/6231832.html