Java-100万レベルのデータファイルをすばやく読み込み、データベースを挿入
7783 ワード
いくつかのTxtのテキストデータをデータベースに挿入して、データ量は特に大きくて、単一のtxtファイルはすべて300余りMがあって、データは約200 w余りあって、ss dディスクの上でNotepad++を通じて開けてすべて5分近くロードしなければなりません(私のssdハードディスクは比較的に腐っているかもしれません).
関連資料参考リンク:1.java接続mysqlデータベースは単一の挿入と一括挿入を実現する2.executeBatch()一括実行Sql文
一、大体の考えからPHPでやることを選んだが、不便で直感的ではないことに気づいた.その後、Javaで書くようになった.1.まず、Txtファイルの内容をすべてメモリに読み込みます(後で操作を速くします).2.Javaデータベース操作を使用したPreparedStatement.executeBatch()の一括書込み.3.Txtファイルのデータはすべて規則的に保存されて、大体各行は基本的に以下の通りです
二、具体的な実装1.データベース接続クラスの作成:sqllink.java
2.Txtファイルの内容特徴に基づいて、ファイル情報クラスを作成する:UserInfo.java
3.Txtファイルの内容を読み込み、データベースに書き込み、Tinput.java
4.実測を経て、200万件以上のデータをメモリに読み取るのに30 sもかからない.目標:200 wのデータをメモリからデータベースに書き込み、実際に170 wを書き込むのに8分程度かかり、一部のデータの書き込みに失敗した.
機械の配置:全体の過程はssdハードディスクの上で、i 5-6300 HQ CPU 2.30 Ghz、4コア8 Gメモリ、正常な状態の下でcpuの使用率は55%で、動作状態は80%近くまで急上昇します
三、注意事项1).ファイルが大きいので、データの読み取り方をよくしなければならない.そうしないと、データの読み取りが遅くなる.2).最初はファイルから1つのデータを読んでデータベースに書き込もうとしたが,1つ1つの挿入データが遅く,テストした後,40 wデータを挿入するのに4時間以上かかった...3).一括送信時にsettoCommit(false)をfalseに設定することが重要であり,SQL文を1つ実行するたびにトランザクションとしてコミットする機能を持つ.しかし、通常、プロジェクトではトランザクションとして複数のSQL文を実行する必要がある可能性があります.実行が失敗するとrollback()になります.trueの場合は自動コミットモードを有効にし、falseはこのモードを無効にします.
簡単に言えばfalseに設定しないと、sql文の実行エラー後、今回コミットしたデータが挿入されず、今回の操作が無駄に実行されたことに相当します.
settAutoCommit(false)を設定する場合、データベース接続を閉じるにはcon.commit()が必要です.con.close()ではありません.
4).executeBatch()を使用してデータを一括してコミットする場合,データにプライマリ・キーが重複していると,異常を挿入して終了し,プライマリ・キーが重複していることを提示し,この方式でデータを挿入するという欠点がある.解决方法:a.データベースの主キーフィールドをインクリメント(検索した回答も派手)b.主キーをキャンセルし、すべてのデータを挿入して成功した后、データを削除します.私はb案を使っています.a案はインクリメントがどこにあるか分かりません.
関連資料参考リンク:1.java接続mysqlデータベースは単一の挿入と一括挿入を実現する2.executeBatch()一括実行Sql文
一、大体の考えからPHPでやることを選んだが、不便で直感的ではないことに気づいた.その後、Javaで書くようになった.1.まず、Txtファイルの内容をすべてメモリに読み込みます(後で操作を速くします).2.Javaデータベース操作を使用したPreparedStatement.executeBatch()の一括書込み.3.Txtファイルのデータはすべて規則的に保存されて、大体各行は基本的に以下の通りです
,,,ID,111111111111111111,M,19999999, ,-, ,,CHN,32,3201,,,,,, 1, 2,-, ,,,,,,,,0, ,88210
二、具体的な実装1.データベース接続クラスの作成:sqllink.java
package openfile;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/*
*
* mysql
*/
public class sqllink {
private static String DRIVERCLASS="com.mysql.jdbc.Driver";
private static String URL="jdbc:mysql://localhost:3306/stu?characterEncoding=utf8";
private static String USERNAME="stu"; //
private static String PASSWORD="stu"; //
public sqllink() {
}
public static Connection getConnection() throws ClassNotFoundException, SQLException{
Class.forName(DRIVERCLASS);
Connection conn =DriverManager.getConnection(URL, USERNAME, PASSWORD);
return conn;
}
}
2.Txtファイルの内容特徴に基づいて、ファイル情報クラスを作成する:UserInfo.java
package openfile;
public class UserInfo {
private String name; //
private String idcard; //id
private String sex; //
private String year; //
private String phone; //
private String mail; //
private String address; //
private String logintime; //
private String mz; //
/* get/set*/
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getIdcard() {
return idcard;
}
public void setIdcard(String idcard) {
this.idcard = idcard;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getYear() {
return year;
}
public void setYear(String year) {
this.year = year;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getMail() {
return mail;
}
public void setMail(String mail) {
this.mail = mail;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getLogintime() {
return logintime;
}
public void setLogintime(String logintime) {
this.logintime = logintime;
}
public String getMz() {
return mz;
}
public void setMz(String mz) {
this.mz = mz;
}
}
3.Txtファイルの内容を読み込み、データベースに書き込み、Tinput.java
package openfile;
import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CountDownLatch;
import com.mysql.fabric.xmlrpc.base.Data;
import sqlStudent.sqllink;
public class Tinput {
public static void main(String[] args) throws IOException {
int i = 0;
int j = 0;
String inputFile = "C:\\wwwroot\\W1.txt"; // 300M txt
List LS = new ArrayList<>();
Connection con=null;
try {
con = sqllink.getConnection();
System.out.println("sql ");
String sql = "INSERT INTO `stu`.`id_msg`(`name`, `idcard`, `year`, `sex`, `nation`, `phone`, `mail`, `address`, `longintime`) VALUES (?, ?, ?, ?, ?, ?, ?, ?,?)";
con.setAutoCommit(false); //( ) 4
java.sql.PreparedStatement ptatm = con.prepareStatement(sql);
// 2G
BufferedInputStream bis = new BufferedInputStream(new FileInputStream(new File(inputFile)));
BufferedReader in = new BufferedReader(new InputStreamReader(bis, "GBK"), 10 * 1024 * 1024);// 10M
while (in.ready()) {
String line = in.readLine();
String[] arr = line.split(","); // ，
if(arr.length<=31) continue; //arr 31
if( arr[4].length()<5) continue; //id 5，
UserInfo userInfo = new UserInfo();
userInfo.setName(arr[0]); //
userInfo.setIdcard(arr[4]); //id
userInfo.setYear(arr[6]); //
userInfo.setSex(arr[5]); //
userInfo.setMz(arr[23]); //
userInfo.setPhone(arr[19]); //
userInfo.setMail(arr[22]); //
userInfo.setAddress(arr[7]); //
userInfo.setLogintime(arr[31]); //
LS.add(userInfo); //
i++; //
System.out.println(i); //
}
in.close(); //
System.out.println("
Txt "+i+" ");
i= 0 ;
j = 0 ;
try {
// ，
for(UserInfo userInfo : LS ) {
ptatm.setString(1, userInfo.getName()); //
ptatm.setString(2, userInfo.getIdcard()); //id
ptatm.setString(3, userInfo.getYear()); //
ptatm.setString(4, userInfo.getSex()); //
ptatm.setString(5, userInfo.getMz()); //
ptatm.setString(6, userInfo.getPhone()); //
ptatm.setString(7,userInfo.getMail() ); //
ptatm.setString(8, userInfo.getAddress()); //
ptatm.setString(9, userInfo.getLogintime()); //
ptatm.addBatch(); //
if(i==100000) { // 10w
i=0; // i
System.out.println(" :"+j +"=============================================================");
ptatm.executeBatch(); // SQL ，
ptatm.clearBatch(); // ,
}
i++;
j++;
System.out.println(" "+i);
}
ptatm.close();
//con.close();
con.commit(); //
}
catch (Exception e) {
ptatm.close();
con.commit();
e.printStackTrace();
}
} catch (IOException ex) {
ex.printStackTrace();
} catch (SQLException e1) {
e1.printStackTrace();
}catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}
4.実測を経て、200万件以上のデータをメモリに読み取るのに30 sもかからない.目標:200 wのデータをメモリからデータベースに書き込み、実際に170 wを書き込むのに8分程度かかり、一部のデータの書き込みに失敗した.
機械の配置:全体の過程はssdハードディスクの上で、i 5-6300 HQ CPU 2.30 Ghz、4コア8 Gメモリ、正常な状態の下でcpuの使用率は55%で、動作状態は80%近くまで急上昇します
三、注意事项1).ファイルが大きいので、データの読み取り方をよくしなければならない.そうしないと、データの読み取りが遅くなる.2).最初はファイルから1つのデータを読んでデータベースに書き込もうとしたが,1つ1つの挿入データが遅く,テストした後,40 wデータを挿入するのに4時間以上かかった...3).一括送信時にsettoCommit(false)をfalseに設定することが重要であり,SQL文を1つ実行するたびにトランザクションとしてコミットする機能を持つ.しかし、通常、プロジェクトではトランザクションとして複数のSQL文を実行する必要がある可能性があります.実行が失敗するとrollback()になります.trueの場合は自動コミットモードを有効にし、falseはこのモードを無効にします.
簡単に言えばfalseに設定しないと、sql文の実行エラー後、今回コミットしたデータが挿入されず、今回の操作が無駄に実行されたことに相当します.
settAutoCommit(false)を設定する場合、データベース接続を閉じるにはcon.commit()が必要です.con.close()ではありません.
4).executeBatch()を使用してデータを一括してコミットする場合,データにプライマリ・キーが重複していると,異常を挿入して終了し,プライマリ・キーが重複していることを提示し,この方式でデータを挿入するという欠点がある.解决方法:a.データベースの主キーフィールドをインクリメント(検索した回答も派手)b.主キーをキャンセルし、すべてのデータを挿入して成功した后、データを削除します.私はb案を使っています.a案はインクリメントがどこにあるか分かりません.