mysql資料庫批量快速插入，mysql資料庫

mysql資料庫批量快速插入，mysql資料庫

最近在處理一批資料，需要從庫中表裡的欄位進行處理然後匯出到一個新表中。不過這個表的資料量有近500w條。這資料量出現的的問題是需要處理的時間好長。
首先想到，一句一句的插入，大資料量處理時間好長，忽略。
其次想到，多線程插入，想到資料庫連接是需要同步的所以感覺用處不大。
最後想到，使用 PreparedStatement 先行編譯sql 進行批量插入 batch 處理。
好吧，現在就進行批處理插入測試。
1、使用簡單的 batch

public static void main(String[] args) {        Connection conn = getConn(lsqlurl, luser, lpassword);        long startTime = System.currentTimeMillis();        try {            PreparedStatement pst = conn.prepareStatement("insert into testmy (id,name,age) values (?,?,?)");            for (int i = 0; i < 2000; i++) {                pst.setInt(1, 3);                pst.setString(2, "xx");                pst.setInt(3, 10);                pst.addBatch();            }            pst.executeBatch();            long endTime = System.currentTimeMillis();            System.out.println((endTime - startTime)/1000+"s");            System.out.println("test sql batch--->2000.....");        } catch (SQLException e) {            e.printStackTrace();        }finally {            if(conn!=null) {                try {                    conn.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }        }    }

你會發現時間會是30s 左右。
2k行的資料插入就30秒 。
2w行資料插入時間為940秒（約16min）。

2、修改自動認可的 batch

public static void main(String[] args) {        Connection conn = getConn(lsqlurl, luser, lpassword);        long startTime = System.nanoTime();        try {            conn.setAutoCommit(false);            PreparedStatement pst = conn.prepareStatement("insert into test (id,name,age) values (?,?,?)");            for (int i = 0; i < 2000; i++) {                pst.setInt(1, 3);                pst.setString(2, "xx");                pst.setInt(3, 10);                pst.addBatch();            }            pst.executeBatch();            conn.commit();            long endTime = System.nanoTime();            System.out.println((endTime - startTime)/1000000+"ms");            System.out.println("test sql batch--->2000.....");        } catch (SQLException e) {             try {                conn.rollback();            } catch (SQLException e1) {                e1.printStackTrace();            }            e.printStackTrace();        }finally {            if(conn!=null) {                try {                    conn.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }        }    }

2k行插入耗時大概是260ms。
2w行資料插入大概是1.4s。
其實結果很明顯的。

因為在使用batch時資料量達到一定的值後資料庫會自動認可。而不是你執行executeBatch時再執行。所以我們需要修改自動認可變成手動提交。
這裡還有一個問題是：當你實在執行事務時，一旦出錯的時候，自動認可會幫你rollback，手動提交時就應該自己進行回退。
所以在catch裡需要添加 rollback 。

好了，綜上我們可以使用自動認可的batch進行大量資料的插入。

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。