後端開發：【批量插入海量資料之Java插入MySql】解決方案

Breaker93發表於2017-09-29
後端JavaMySql

一、解析問題。

Java向MySql資料庫插入萬級記錄時，採用的方案不同時執行速度會有所不同，資料量越大則優劣越明顯。所以選取最優方案尤其重要，本文目前提供如下兩種解決方案（不借用第三方框架或工具）。

二、解決問題。

1、方案一：迴圈逐條插入。

關鍵程式碼：

//DataModel 為自定義的資料模型類，dataList 即傳入的即將要插入的資料集合；
public int insertData(List<DataModel> dataList) throws ClassNotFoundException, SQLException{

//開始計時；
Long begin = new Date().getTime();

//建立要執行的sql語句；
String sql = "insert into tb_ncdc values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

/* 建立並獲取JDBC連線類"Connection"的例項物件。（DBUtil類內為資料庫訪問的配置資訊，需要自定義）*/
Connection connection = new DBUtil().getDbCon();

//PrepareStatement類存放每條記錄對應的欄位值；
PreparedStatement preparedStatement= connection.prepareStatement(sql);

    for (int i = 0; i < dataList.size(); i ++) {

        preparedStatement.clearParameters();

        preparedStatement.setString(1, dataList.get(i).getSTN());

        preparedStatement.setString(2, dataList.get(i).getWBAN());

        preparedStatement.setString(3, dataList.get(i).getYEARMODA());

        preparedStatement.setString(4, dataList.get(i).getTEMP());

        preparedStatement.setString(5, dataList.get(i).getDEWP());

        preparedStatement.setString(6, dataList.get(i).getSLP());

        preparedStatement.setString(7, dataList.get(i).getSTP());

        preparedStatement.setString(8, dataList.get(i).getVISIB());

        preparedStatement.setString(9, dataList.get(i).getWDSP());

        preparedStatement.setString(10, dataList.get(i).getMXSPD());

        preparedStatement.setString(11, dataList.get(i).getGUST());

        preparedStatement.setString(12, dataList.get(i).getMAX());

        preparedStatement.setString(13, dataList.get(i).getMIN());

        preparedStatement.setString(14, dataList.get(i).getPRCP());

        preparedStatement.setString(15, dataList.get(i).getSNDP());

        preparedStatement.setString(16, dataList.get(i).getFRSHTT());

        preparedStatement.execute();
     }

    /*如果autocommit=false時（預設為true，即自動提交事務）記得將本次事務提交，否則資料庫裡沒有資料的；*/
    //connection.commit();

    //所有資料庫操作結束後記得關閉連線，減少記憶體的佔用；
    preparedStatement.close();
    connection.close();

    // 結束時間
    Long end = new Date().getTime();

    //總耗時
    System.out.println("插入"+dataList.size()+"條資料的總時間為 : " + (end - begin)  + " ms");

    return 1;

}複製程式碼

2、方案二：分批事務插入。

//DataModel 為自定義的資料模型類，dataList 即傳入的即將要插入的資料集合；
public int insertData(List<DataModel> dataList) throws ClassNotFoundException, SQLException {

        //設定每批、每次事務插入多少條資料；
        int itemNum = 1000;

        //開始時間；
        Long begin = new Date().getTime();

        // 建立sql字首
        String prefix = "INSERT INTO tb_ncdc VALUES ";

        /* 建立並獲取JDBC連線類"Connection"的例項物件。（DBUtil類內為資料庫訪問的配置資訊，需要自定義） */
        Connection connection = new DBUtil().getDbCon();

        // PrepareStatement類存放每條記錄對應的欄位值；
        PreparedStatement preparedStatement= connection.prepareStatement("");

        // 建立sql字尾
        StringBuffer suffix = new StringBuffer();

        // 設定事務為非自動提交
        connection.setAutoCommit(false);

        //根據總的資料量計算需要分多少次事務插入；
        int numTrans = dataList.size() / itemNum + 1;

        //設定首次事務中的資料在集合中的索引為0；
        int numData = 0;

        //  外層迴圈，j代表提交事務次序；
        for (int j = 1; j <= numTrans; j++) {

        // 從索引numData開始查詢總數為itemNum個資料，即為本批要插入的資料量；
        for (int i = numData; i < numData + itemNum; i++) {

            //判定如果是最後一批，可能會不足itemNum數量，則夠數結束，防止陣列越界；
            if (i == dataList.size()) {
                break;
            }

            // 構建sql字尾
            suffix.append("('" + dataList.get(i).getSTN() + "','" + dataList.get(i).getWBAN() + "','"
            + dataList.get(i).getYEARMODA() + "','" + dataList.get(i).getTEMP() + "','"
            + dataList.get(i).getDEWP() + "','" + dataList.get(i).getSLP() + "','"
            + dataList.get(i).getSTP() + "','" + dataList.get(i).getVISIB() + "','"
            + dataList.get(i).getWDSP() + "','" + dataList.get(i).getMXSPD() + "','"
            + dataList.get(i).getGUST() + "','" + dataList.get(i).getMAX() + "','"
            + dataList.get(i).getMIN() + "','" + dataList.get(i).getPRCP() + "','"
            + dataList.get(i).getSNDP() + "','" + dataList.get(i).getFRSHTT() + "'),");
        }

        // 構建完整sql
        String sql = prefix + suffix.substring(0, suffix.length() - 1);

        // 新增sql批；
        preparedStatement.addBatch(sql);

        // 執行sql批；
        preparedStatement.executeBatch();

        // 提交本次事務
        connection.commit();

        // 清空上一次的sql字尾；
        suffix = new StringBuffer();

        numData += itemNum;

        }

        // 所有資料庫操作結束後記得關閉連線，減少記憶體的佔用；
        preparedStatement.close();
        connection.close();

        // 結束時間
        Long end = new Date().getTime();

        // 耗時
        System.out.println("插入" + dataList.size() + "條資料的總時間為 : "+ (end - begin) + " ms");

        return 1;

}複製程式碼

三 、總結問題。

1.兩種方案的主要區別在於，sql語句的不同、batch批和事務的使用。

單條插入sql語句：insert into Table (col1,col2...) values (val11,val12...);

多條批插入sql語句：insert intoTable (col1,col2...) values (val11,val12...),(val11,val12...),...;

2.本次測試的例項中，插入69萬條資料左右，方案二要比方案一的速度快上10倍左右。具體測試得到的具體毫秒數可能不同。影響因素個人認為有如下幾條：

（1）資料模型，每條資料記錄的欄位越多，就需要調整itemNum(每批插入的資料量，可以採用二分法找到最合適的數值)，或者調整MySql資料庫對每次執行sql語句的位元組長度限制（網上自行搜尋）。itemNum值找到最合適的，速度才可能在其他條件同等的條件下是最快的；

（2）主機配置。包括處理器效能、硬碟效能 ，mysql資料庫可能也會影響到速度；

本節Demo原始碼附帶測試資料包及資料庫指令碼GitHub下載地址：

github.com/Breaker-93/…

若有任何疑問，請留言。

相關文章