java大批量資料匯入（MySQL）

標籤：back   end   trace   max   cep   indexof   取數   length   type   

? 著作權聲明：本文為博主原創文章，轉載請註明出處

最近同事碰到大批量資料匯入問題，因此也關注了一下。大批量資料匯入主要存在兩點問題：記憶體溢出和匯入速率慢。

記憶體溢出：將檔案中的資料全部取出放在集合中，當資料過多時就出現Java記憶體溢出，此時可通過調大JVM的最大可用記憶體（Xmx）解決，

　　　　　　　　但終究不是王道。

　　　　    MySQL支援一條SQL語句插入多條記錄的操作，並且效率比單條插入快的不是一點點；但是MySQL一次可接受的資料包大小

　　　　　　　　也是有限制的，當一次插入過多時也可能造成資料包記憶體溢出，此時可通過調大MySQL的max_allowed_packet 解決，

　　　　　　　　但也不是王道。

匯入速率慢：單條插入就不用考慮了，因此考慮一條SQL語句插入多條記錄，

　　　　　　　　根據上述所說還應控制好一條插入的資料大小不能超過max_allowed_packet 的配置。

下面比較了用PreparedStatement和直接拼接SQL兩種批量插入的方式的速率（一次插入1w條）

 

package org.javaio.CSV;import java.io.BufferedReader;import java.io.FileInputStream;import java.io.InputStreamReader;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.text.SimpleDateFormat;import java.util.Date;import com.mysql.jdbc.Connection;/** * 匯入大批量CSV檔案 * */public class Test {/** * jdbc所屬，暫不使用 */private final static String url = "jdbc:mysql://localhost:3306/demo_test?useSSL=true&characterEncoding=utf8";private final static String name = "root";private final static String pwd = "20121221";private static Connection conn;private static PreparedStatement ps;    /** * 解析csv檔案並插入到資料庫中，暫不使用(jdbc) *  * @param args *  * @throws Exception */public static void main(String[] args) throws Exception {Test test = new Test();// psBatch 時間統計 - 開始SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");String startTime = sdf.format(new Date());System.out.println("psBatch 開始時間為：" + startTime);System.out.println("psBatch 開始執行...");// 使用PreparedStatement批量插入int idx = test.psBatch();// 統計時間 - 結束System.out.println("psBatch 執行完成，共插入" + idx + "條資料");String endTime = sdf.format(new Date());System.out.println("psBatch 結束時間為：" + endTime);System.out.println();// 時間統計 - 開始startTime = sdf.format(new Date());System.out.println("sqlBatch 開始時間為：" + startTime);System.out.println("sqlBatch 開始執行...");// 使用SQL語句批量插入idx = test.sqlBatch();// 統計時間 - 結束System.out.println("sqlBatch 執行完成，共插入" + idx + "條資料");endTime = sdf.format(new Date());System.out.println("sqlBatch 結束時間為：" + endTime);}/** * 使用PreparedStatement批量插入 *  * @return *  * @throws Exception */private int psBatch() throws Exception {int idx = 0;// 行數try {// 讀取CSV檔案FileInputStream fis = new FileInputStream("C:/Users/chen/Desktop/data/ceshi .csv");InputStreamReader isr = new InputStreamReader(fis, "UTF-8");BufferedReader br = new BufferedReader(isr);String line;// 行資料String[] column = new String[4];// 列資料// 擷取資料庫連接conn = getConnection();// 設定不自動認可conn.setAutoCommit(false);// SQLString sql = "insert into test (name, `desc`, column1, column2, column3, column4) "+ "values (?, ?, ?, ?, ?, ?)";ps = conn.prepareStatement(sql);while ((line = br.readLine()) != null) {// 迴圈讀取每一行idx++;// 計數column = line.split(",");ps.setString(1, column[0]);if (column.length >= 2 && column[1] != null) {ps.setString(2, column[1]);} else {ps.setString(2, "");}if (column.length >= 3 && column[2] != null) {ps.setString(3, column[2]);} else {ps.setString(3, "");}if (column.length >= 4 && column[3] != null) {ps.setString(4, column[3]);} else {ps.setString(4, "");}ps.setString(5, "type");ps.setString(6, "1");ps.addBatch();if (idx % 10000 == 0) {ps.executeBatch();conn.commit();ps.clearBatch();}}if (idx % 10000 != 0) {ps.executeBatch();conn.commit();ps.clearBatch();}} catch (Exception e) {System.out.println("第" + idx + "前一萬條資料插入出錯...");} finally {try {if (ps != null) {// 關閉串連ps.close();}if (conn != null) {conn.close();}} catch (Exception e2) {e2.printStackTrace();}}return idx;}/** * 使用sql語句批量插入 *  * @return *  * @throws Exception */private int sqlBatch() {int idx = 0;// 行數try {// 讀取CSV檔案FileInputStream fis = new FileInputStream("C:/Users/chen/Desktop/data/ceshi .csv");InputStreamReader isr = new InputStreamReader(fis, "UTF-8");BufferedReader br = new BufferedReader(isr);String line;// 行資料String[] column = new String[4];// 列資料// 擷取資料庫連接conn = getConnection();// SQLStringBuffer sql = new StringBuffer("insert into test (name, `desc`, column1, column2, column3, column4) "+ "values ");while ((line = br.readLine()) != null) {// 迴圈讀取每一行idx++;// 計數column = line.split(",");sql.append("(‘" + column[0] + "‘, ‘");if (column.length >= 2 && column[1] != null) {sql.append(column[1] + "‘, ‘");} else {sql.append("‘, ‘");}if (column.length >= 3 && column[2] != null) {sql.append(column[2] + "‘, ‘");} else {sql.append("‘, ‘");}if (column.length >= 4 && column[3] != null) {sql.append(column[3] + "‘, ‘");} else {sql.append("‘, ‘");}sql.append("type‘, ‘1‘),");if (idx % 10000 == 0) {String executeSql = sql.toString().substring(0, sql.toString().lastIndexOf(","));ps = conn.prepareStatement(executeSql);ps.executeUpdate();sql = new StringBuffer("insert into test (name, `desc`, column1, column2, column3, column4) "+ "values ");}}if (idx % 10000 != 0) {String executeSql = sql.toString().substring(0, sql.toString().lastIndexOf(","));ps = conn.prepareStatement(executeSql);ps.executeUpdate();}} catch (Exception e) {System.out.println("第" + idx + "前一萬條資料插入出錯...");} finally {try {if (ps != null) {// 關閉串連ps.close();}if (conn != null) {conn.close();}} catch (Exception e2) {e2.printStackTrace();}}return idx;}/** * 擷取資料庫連接 *  * @param sql * SQL語句 */private Connection getConnection() throws Exception {Class.forName("com.mysql.jdbc.Driver");conn = (Connection) DriverManager.getConnection(url, name, pwd);return conn;}}

 

速率比較：為了排除其他影響，兩次次都是在空表的情況下進行匯入的

　　用SQL拼接批量插入用時大概3-4分鐘

 

　　用PreparedStatement批量插入用時大概10分鐘

 

java大批量資料匯入（MySQL）