使用JDBC向MySQL資料庫批次插入10W條資料測試效率

zyj8170發表於2020-04-04
JDBCMySql資料庫
使用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();
        }
    }
}

一共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";
再次測試結果如下：

相關文章