大量のデータをMySQLにインポートする際に発生する問題と解決策の分析

プロジェクトでは、SQL を使用してデータ分析を実行するために、大量のデータをデータベースにインポートするという問題に頻繁に遭遇します。データをインポートする過程で、解決しなければならない問題がいくつか発生します。ここでは、約 4G の txt データをインポートする実践と合わせて、発生した問題とその解決策を示します。一方では、自分自身のための要約記録を作成し、他方では、同じ問題に遭遇した友人の参考になることを願っています。

インポートしたデータは百科事典のtxtファイルで、ファイルサイズは4G以上、データは6500万以上あり、各データは改行で区切られています。各データにはタブで区切られた 3 つのフィールドが含まれます。データを取得するために使用する方法は、TripleData クラスを使用してこれら 3 つのフィールドを保存することです。すべてのフィールドで String を使用し、複数のデータを List<TripleData> に保存してから、List<TripleData> を MySQL データベースに保存し、すべてのデータをバッチで MySQL データベースに保存します。

上記は一般的な考え方です。以下は、特定のインポートプロセス中に発生する問題です。

1. データベース接続で文字化けが発生し、互換性の問題が発生しました。

データに中国語が含まれる場合は、データベースにリンクする URL のエンコードパラメータを必ず設定してください。URL は次のように設定する必要があります。

URL="jdbc:mysql://"+IP+":"+PORT+"/"+DB_NAME+"?useSSL=false&useUnicode=true&characterEncoding=utf-8";

エンコーディングを UTF-8 に設定すると文字化けの問題が解決され、useSSL を設定すると JDBC と MySQL 間の互換性の問題が解決されます。 useSSL が設定されていない場合は、エラーが報告されます。類似

サーバーの ID 検証なしで SSL 接続を確立することは推奨されません。MySQL 5.5.45+、5.6.26+、および 5.7.6+ の要件によると、明示的なオプションが設定されていない場合は、デフォルトで SSL 接続を確立する必要があります。SSL を使用していない既存のアプリケーションに準拠するには、verifyServerCertificate プロパティを 'false' に設定します。useSSL=false を設定して SSL を明示的に無効にするか、useSSL=true を設定してサーバー証明書の検証用のトラストストアを提供する必要があります。

このようなエラーメッセージ。主な理由は、MySQL のバージョンが比較的高く、JDBC のバージョンが比較的低いため、互換性が求められるためです。

2 utf8mb4 エンコーディングの問題

データをインポートする過程で、同様の問題に遭遇するでしょう。

SQLException: 列 'name' の文字列値が正しくありません: '\xF0\xA1\x8B\xBE\xE5\xA2...'

このエラーメッセージは、MySQL で設定されている UTF-8 がデフォルトで 3 バイトであるため、一般的なデータでは問題ありません。ただし、データ量が多い場合は、必然的に一部の WeChat 絵文字や特殊文字が含まれ、これらは 4 バイトを占め、UTF-8 では処理できないため、エラーが報告されます。解決策は、MySQL が 5.5.3 以降のバージョンで 4 バイトの UTF-8 エンコーディング、つまり utf8mb4 を導入し、MySQL エンコーディングをリセットする必要があることです。

以下の手順に従ってください。まず、変更するデータベースをバックアップします。utf8mb4 は utf8 と下位互換性がありますが、不適切な操作を防ぐために、予防措置を講じてバックアップを取る必要があります。 2 つ目は、データベースの文字セットエンコーディングを utf8mb4 (UTF-8 Unicode) に変更し、ソート規則を utf8mb4_general_ci に変更することです。上記の変更は navicat を使用して行いました。コマンドラインを使用して変更する方法については、こちらをご覧ください。 3 番目は、MySQL インストールのルートディレクトリにある構成ファイル my.ini を変更することです。以下の設定を追加します。

[クライアント]
デフォルトの文字セット = utf8mb4
[mysqld]
文字セットサーバー=utf8mb4
照合サーバー=utf8mb4_general_ci
[mysql]
デフォルトの文字セット = utf8mb4

変更が完了したら、変更を有効にするために MySQL を再起動する必要があります。

その後、データをインポートすると、正常にインポートされるはずです。

3 大規模輸入における時間効率の問題

データ量が比較的多いため、データをセグメント化しました。6,500 万のデータを 500 のファイルに分割し、各ファイルに約 110,000 のデータ項目を含めました。これらの 110,000 のデータ項目を ArrayList<TripleObject> に入れて、バッチでインポートしました。一般的な考え方としては、「insert into tb (...) values(...),(...)...;」メソッドを使用し、挿入を使用して一度に挿入することで、多くの時間を節約できます。方法例は以下のとおりです。

パブリック静的 void insertSQL(String sql,List<TripleObject> tripleObjectList) は SQLException をスローします{
    接続 conn=null;
    PreparedStatement psts=null;
    試す {
      conn = DriverManager.getConnection(Common.URL、Common.DB_USERNAME、Common.DB_PASSWORD);
      conn.setAutoCommit(false); // 手動コミットを設定する // SQL サフィックスを保存する StringBuffer suffix = new StringBuffer();
      整数カウント = 0; 
      psts=conn.prepareStatement("");
      文字列 s="";
      文字列 p="";
      文字列 o="";
      (count<tripleObjectList.size()) の場合 {
        s=tripleObjectList.get(count).getSubject().replaceAll(",", ".").replaceAll("\\(", "").replaceAll("\\)", "").replaceAll("\'", "").replaceAll("\\\\", "");
        p=tripleObjectList.get(count).getPredicate().replaceAll(",", ".").replaceAll("\\(", "").replaceAll("\\)", "").replaceAll("\'", "").replaceAll("\\\\", "");
        o=tripleObjectList.get(count).getObject().replaceAll(",", ".").replaceAll("\\(", "").replaceAll("\\)", "").replaceAll("\'", "").replaceAll("\\\\", "");
        接尾辞を追加します("('" +s +"','"+p+"','"+ o+"'),");
        カウント++;
      }
      // 完全なSQLを構築
      文字列 allsql = sql + suffix.substring(0, suffix.length() - 1);
      // 実行SQLを追加
      psts.addBatch(allsql);
      psts.executeBatch(); // バッチ処理を実行 conn.commit(); // コミット } catch (Exception e) {
      e.printStackTrace();
    }ついに{
      if(psts!=null){
        psts.close();
      }
      if(conn!=null){
        接続を閉じる();
      }
    }
  }

この方法の利点は、データのインポートにほとんど時間がかからないことです。6,500 万個のデータをインポートするのにちょうど 1 時間かかりました。欠点は、データ内に長い文がある場合、その中のカンマ、括弧、バックスラッシュなどを処理する必要があることです。ここで、この方法を使用するかどうかを検討する必要があります。

通常通り、つまり「insert into tb (...) values(...);insert into tb (...) values(...);...」という形式でデータを挿入すると、特別な記号を扱う必要はありませんが、時間がかかります。私がテストしたところ、11万件のレコードをインポートするのに約12分、6500万件のレコードをインポートするのに約100時間かかりました。

私たちは最初の方法を使用します。この方法では、データを大まかに確認するだけでよく、データに対する厳しい要件がないため、時間を節約できます。

以上が、MySQLに大量のデータをインポートする際に遭遇した問題と、私が考えた解決策です。もっと良い解決策があったり、別の問題に遭遇したりした場合は、一緒に議論できればと思います。

以下もご興味があるかもしれません: