MySQL 数十億のデータのインポート、エクスポート、移行に関するメモ

最近はMySQLのメモをたくさん取っていますが、それは主に会社のOracleが比較的安定していてメンテナンスも少ないからです。先週、数十億のMySQLデータの移行を任されたので、その機会を利用して勉強メモを記録しました。

データ移行は、動作原理や技術サポートの面では、データエクスポート、BI レポートなどに似ています。最大の違いは、インポートおよびエクスポートされるデータの量にあります。通常、レポート データの量は数百万を超えることはありません。ただし、インターネット企業のデータ移行では、数千万または数億のデータが関係することがよくあります。

インポートとエクスポートは 2 つのプロセスです。データ移行を行う場合も、これらを別々に検討する必要があります。同時に、インポート/エクスポートの方法は次のように分けられます。

1. MySQLにはインポート/エクスポートメソッドが付属しています

2. さまざまなクライアントのインポート/エクスポート方法

まずエクスポートを要約します。

1. フィールド数が少ないデータやフィールド内容が少ないデータの場合は、Navicatなどのツールを使用してクライアント経由でエクスポートできます。ここでは、すべて11桁以内の値である3つのフィールドをエクスポートします。Navicatを使用して1分間に約250万のデータをエクスポートすると、

2. MySQL 独自のエクスポート ステートメント: select into outfile ステートメント。

SELECT ... FROM TABLE_A --where 条件を追加できます INTO OUTFILE "/path/to/file" --エクスポート ファイルの場所 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' --フィールド区切り文字と包含文字 LINES TERMINATED BY '\n';--改行文字

前のフィールドは非常にシンプルでわかりやすいので、ここでは説明しません。後のフィールドについて説明しましょう。

FIELDS TERMINATED BY ',' は、フィールドがコンマで区切られていることを意味します。例: フィールド A フィールド B、エクスポート時の表示形式は A, B です。

オプションで「"」で囲むと、フィールドの内容が二重引用符で囲まれます。エクスポート形式は「A」、「B」です。

行は '\n' で終了します。各データ行は改行で区切られます。エクスポート形式は次のとおりです。

「A」、「B」

「A1」、「B1」

もちろん、フィールドの区別と包含のシンボルを次のように自分で定義することもできます: '#

MySQL の組み込みエクスポート/インポートを使用する利点は、非常に高速であることです。ただし、欠点は、サーバー ホストのローカル アドレスにしかファイルをエクスポートできないことです。データベース ホスト権限を持たない bi のような同僚にとっては、この方法は贅沢かもしれません。幸いなことに、フィールドやコンテンツが少ないレポートの場合、サードパーティのクライアント ツールのエクスポート速度は特に遅くはありません。

輸入：

鍵となるのはレコードのインポートです。インポートは主に DBA がデータを移行するために行います。方法はクライアントと MySQL 組み込みの 2 つに分かれています。

以前 1 億 3000 万件のデータを移行する必要があったため、ここでは MySQL インポートを使用することを強くお勧めします。Navicat クライアントを使用してデータをインポートするには 22 時間かかり、時間がかかりすぎて不確実でした。Navicat のようなツールには擬似死のリスクがあります。したがって、Navicat を介して 10,000 件を超えるデータをインポートすることはお勧めしません。

MySQL 組み込みインポート方法:

--公式文書の定義は以下の通りで、コメントは私自身の理解に基づいて追加されています。

データをロード、
[LOW_PRIORITY | CONCURRENT]--データベースを使用している人がいない場合に実行/即時実行します[LOCAL]--このパラメータを使用すると、サーバー側はサーバーホスト上のファイルを読み取りません。このパラメータがない場合、ファイルはデフォルトでサーバーホスト上で読み取られますINFILE 'file_name' --ファイルアドレスとファイル名を読み取ります[REPLACE | IGNORE]--重複データに遭遇した場合、置き換え/書き込みを繰り返します。繰り返し書き込みを無視することをお勧めしますINTO TABLE tbl_name --どのテーブルにインポートするか[PARTITION (partition_name [,partition_name] ...)]--このパラメータ行は省略できます。次のフィールドを使用することをお勧めします
    [CHARACTER SET charset_name]--インポートするコンテンツの文字形式を設定します。utf-8やGBKなどを指定できます。[{FIELDS | COLUMNS} --フィールド識別子 [TERMINATED BY 'string'] --システムフィールドを区別するために使用される記号 [[OPTIONALLY] ENCLOSED BY 'char']--システムフィールド自体の開始と終了を区別するために使用される記号 [ESCAPED BY 'char']--エスケープ文字。テキストファイルの場合、テキストフィールドに二重引用符などの特殊文字があります。エスケープ文字を定義することで、テキストファイルの特殊文字を無視できます。]
    [LINES --行識別子 [STARTING BY 'string'] --行の始まりを定義する文字列。行の先頭に文字識別子がない場合、通常は [TERMINATED BY 'string'] --行末文字列識別子を記述する必要はありません。行間のデータは、定義文字によって区別されます]
    [IGNORE number {LINES | ROWS}]--ファイルの最初の行数を無視します。通常は書き込まれません。次の行は、[(col_name_or_user_var
        [、列名またはユーザー変数] ...)]
    [SET col_name={expr | DEFAULT},
        [, col_name={expr | DEFAULT}] ...] 

元のテキストでは、load data を使用するとデータをデータベースに非常に速くインポートできると書かれていますが、fields およびlines パラメータを使用する場合は、パラメータ値が必要であり、fields はlines パラメータの前に来なければなりません。

今回使用した文は次の通りです。

'/data/files/T_CUST_INFO.txt' ファイルにデータをロード -- デフォルトのサーバー フォルダー
テーブル t_dq_user を無視 -- 重複レコードの挿入を許可する
',' で終わるフィールド -- フィールドがカンマで区切られているかどうかを判定します
'\n'(CustID,DeviceNo,logintype) で終了する行 - 改行識別子を介して各データを解析し、指定したフィールドに挿入します

挿入は非常に単純なステートメントです。ここでは具体的な例は示しません。私が共有したいのは、挿入の効率を向上させる方法です。

なぜなら、挿入ステートメントを初めて使用したとき、夜の 12 時に実行が開始され、翌日の 11 時までに完了していなかったからです。したがって、他のものを構成せずに load を使用すると必ず高速になるというわけではありません。

今回挿入したデータ形式は以下のとおりです。

テキストの形式は次のとおりです。

合計 1 億 4000 万件のデータ レコードがあり、サイズが 4.3G のテキスト ドキュメント形式でエクスポートされ、FTP ソフトウェア経由で server/data/files フォルダーにアップロードされます。

苦情1:

プロジェクトでは 3 つのフィールドすべてにインデックスが必要だったため、テーブルの作成時にインデックスを追加しましたが、その結果、待機時間が無制限に長くなりました。

理由：

インデックスはスペースを占有します。3 つのフィールドをインポートするためにインデックスを追加する必要がある場合、フィールドごとに 1 回ずつインデックスを書き込む必要があるため、インデックスを追加しない場合よりも数倍の時間がかかります。

最適化方法:

インポートする前にテーブルインデックスを削除し、自動増分IDを残しておき、インポートが完了したらそれを追加します。

不満点2：

エンジン選択:

MySQL エンジンは、特にマスター/スレーブ バックアップ メカニズムにおいて、ロード書き込みを異なる方法で処理します。

MyISAM エンジンの場合:
（1）マスターサーバー上で「ロード」操作を実行します。
（２）マスター上で操作されたload.txtファイルはスレーブに同期的に転送され、tmp_dirディレクトリにload.txtファイルが生成されます。
マスター サーバーは、挿入したデータと同じ量のデータをスレーブ サーバーに渡します。
（３）マスター側のロード操作が完了すると、スレーブ側へのファイル転送も完了する。
つまり、スレーブ上で完全なload.txtファイルを生成する
この時点で、スレーブは load.txt からデータを読み取り、そのデータをローカル テーブルに挿入し始めます。

InnoDB エンジンの場合:
（１）メインデータベースが「ロード」操作を実行する
（２）マスターデータベースの操作が完了すると、load.txtファイルがスレーブに転送されます。
スレーブはファイルを受け入れ、tmp_dirディレクトリにload.txtファイルを生成します。
完全なload.txtを受け入れて生成した後、ファイルの読み取りとローカルテーブルへのデータの挿入を開始します。

したがって、極限の速度を追求し、数十億のデータがある場合は、MyISAMエンジンの選択を検討できます。MySQLのデフォルトはInnoDBであるはずです。ただし、今回はエンジンを変更しませんでした。デフォルトのInnoDBエンジンを変更することはお勧めしません。結局のところ、Oracleの公式メインエンジンが最も包括的です。特別な状況がない限り、MyISAMを使用することはお勧めしません。 MyISAM を使用する場合は、次の 2 つの点に注意してください。

MyISAM を使用する場合、いくつかのセッション値を調整して読み取りメモリを拡張し、読み取りデータを改善することができます。ステートメントは次のとおりです。

セッションBULK_INSERT_BUFFER_SIZEを256217728に設定します。
セッション MYISAM_SORT_BUFFER_SIZE を 256217728 に設定します。

MyISAM エンジンの場合、インポート前の一意のチェックを最初にオフにしてから、再度オンにすることができます。

SET UNIQUE_CHECKS=0 -- 無効 SET UNIQUE_CHECKS=1 -- 有効

不満点3：

MySQLはローカルクライアントによるファイルの読み取りをサポートしていますが、さまざまなネットワーク上の理由により、数十または数百のデータの場合は影響はほとんどありません。ただし、データ量が数億に達すると、1ミリ秒でも特に大きな影響が出るため、読み取りにはFTPを使用してサーバーに転送することをお勧めします。

不満点4：

経験を共有します。インポート後、サーバーの状態を確認します。top コマンドを使用して、ホストの CPU MySQL 使用率を確認します。理論的には、CPU をより多く占有します。非常に長い時間がかかった最初のときは、CPU が 10% を占め、非常に異常なインポートでした。正常にインポートした 2 回目は、CPU が 110% を占め、高速書き込みの状態でした。最後の 1 億 4000 万のデータはわずか 7 分以上かかったため、ステートメントを実行した後はサーバーを監視する必要があります。そうしないと、ステートメントが正常に実行されない可能性があります。

CPU使用率:

注: ロードと挿入の最大の違いは、ロードでは構文を 1 回だけ操作し、その後データがバッチで挿入されるのに対し、挿入では各データに対して 1 回操作し、フィールド インデックスを 1 回走査するため、大きなデータの場合、挿入自体が非常に遅くなることです。

要約:

この最適化における最大かつ最も明らかな変化は、インデックスを削除した後、インポート速度が非常に速くなることです。インデックス、もう一度言います:

インポート時に、最初にインデックスを削除し、インポートが完了した後に追加することができます。

2020年7月3日に更新

ビッグデータをMySQLにインポートする場合、最大トランザクション制限に注意する必要があります。数か月前、データ移行中に、MySQL 8.0 MGRクラスタで大きなトランザクション制限が発生し、インスタンスに問題が発生し、MySQLが再起動しました。デフォルトの構成では、トランザクション制限は1億5000万である必要があります。当時、インポートされたデータは比較的大きく、パラメータ拡張は実行されていませんでした。同時に、インポート時にデータのセグメント化やフロー制御が実行されなかったため、データベースがブロックされ、再起動されました。会社の7 * 24 * 365メカニズムの要件によると、これは事故と見なされました。会社の要件が高い場合は、インポート時のMySQL構成自体またはインポートトランザクションの送信制限に注意することをお勧めします。

これで、MySQL の 10 億レベルのデータのインポート、エクスポート、移行に関するメモに関するこの記事は終了です。MySQL の 10 億レベルのデータのインポート、エクスポート、移行に関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。