mysql批量資料匯入探究

標籤：

最近工作碰到一個問題，如何將大量資料（100MB+）匯入到遠端mysql server上。

 

 

嘗試1：　　

　　Statement執行executeBatch的方法。每次匯入1000條記錄。時間為12s/1000條。比較慢。

　　對於1M次的插入這意味著需要4個多小時，期間還會因為網路狀況，資料庫負載等因素而把載入延遲提升到85s/1000條甚至更高。　　

　　效果較差。

 

 嘗試2：

　　使用PreparedStatement，該方法需要預先給定insert操作的“格式”。

　　實測用這種方式插入的效率為每秒鐘數十行。

　　注意，將rewriteBatchedStatements設為true之後，在不到一分鐘時間裡面就將78萬條資料全部匯入資料庫了。這是一個行之有效方法。

　　代碼：

 1 import java.io.BufferedReader; 2 import java.io.FileReader; 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.PreparedStatement; 6  7 /** 8  * 9  */10 public class PreparedStatementTestMain {11     private static PreparedStatement ps;12     public static void main(String[] args) {13         try{14             Class.forName("com.mysql.jdbc.Driver");15             Connection conn = DriverManager.getConnection("jdbc:mysql://remote-host/test?user=xxx&password=xxx");16             String sql = "insert into test values(?,?,?,?,?,?,?,?,?,?,?)";17             ps = conn.prepareStatement(sql);18 19             BufferedReader in = new BufferedReader(new FileReader("xxxx"));20             String line;21             int count =0;22             while((line = in.readLine())!=null){23                 count+=1;24                 String[] values = line.split("\t",-1);25                 //ps.setInt(1,count);26                 for(int i =1;i<values.length;i++) {27 //                    if(i==6){28 //                        ps.setInt(i+1,Integer.parseInt(values[i]));29 //                    }else{30 //                    if(values[i]==null){31 //                        ps.setString(i," ");32 //                    }else {33                         ps.setString(i, values[i]);34 //                    }35 //                    }36                 }37                 ps.addBatch();38                 System.out.println("Line "+count);39             }40             ps.executeBatch();41             ps.close();42         }catch(Exception e){43             e.printStackTrace();44         }45     }46 }

 

嘗試3：

　　使用mysqlimport工具。經過實測，速度接近於嘗試2中加上rewriteBatchedStatements之後的速度。不過前提是資料必須要儲存為檔案。

　　

　　

 

rewriteBatchedStatements到底為什麼對速度最佳化這個多？

　　一種說法：這樣做的目的是為了讓mysql能夠將多個mysql insert語句打包成一個packet和mysql伺服器通訊。這樣可以極大降低網路開銷。

　　另一種說法：

　　Rewriting Batches

　　? “rewriteBatchedStatements=true”

    　? Affects (Prepared)Statement.add/executeBatch()

　　? Core concept - remove latency

　　? Special treatment for prepared INSERT statements

　　——Mark Matthews - Sun Microsystems

 

PreparedStatement VS Statement

　　資料庫系統會對sql語句進行先行編譯處理（如果JDBC驅動支援的話），預先處理語句將被預先編譯好，這條先行編譯的sql查詢語句能在將來的使用中重用。

 

mysql批量資料匯入探究