MySQLにデータを素早くインポートする方法

序文：

日々の勉強や仕事の中で、データをエクスポートする必要に迫られることがよくあります。たとえば、データの移行、データの回復、新しいスレーブデータベースの作成などです。これらの操作には、大量のデータのインポートが伴う場合があります。インポートの進行が遅く、コンピューターのファンが激しく回転しているときがあり、非常にイライラします。実際、インポートを高速化できるヒントがいくつかあります。この記事では、データをすばやくインポートする方法について説明します。

注: この記事では、論理バックアップによって生成された SQL スクリプトをすばやくインポートする方法のみを説明し、他のファイル形式については現時点では説明しません。

1. インポートするファイルのサイズを小さくしてみてください

まず、提案させていただきたいのですが、データのエクスポートやインポートを行う際は、MySQL に付属しているコマンドラインツールを使用するようにし、Navicat や workbench などのグラフィカルツールは使用しないでください。特に大量のデータを扱う場合、MySQL の組み込みコマンドラインツールを使用したエクスポートとインポートは、Navicat などのグラフィカルツールを使用するよりも数倍高速です。さらに、Navicat などのグラフィカルツールは、大量のデータに対して操作を実行すると簡単に停止する可能性があります。以下は、MySQL に付属するコマンドラインツールを使用してインポートおよびエクスポートを行う方法の簡単な紹介です。

# インスタンス全体をエクスポートします。mysqldump -uroot -pxxxxxx --all-databases > all_database.sql

# 指定されたライブラリをエクスポートします mysqldump -uroot -pxxxxxx --databases testdb > testdb.sql

# 指定されたテーブルをエクスポートします mysqldump -uroot -pxxxxxx testdb test_tb > test_tb.sql

#指定されたSQLファイルをインポートします（testdbライブラリへのインポートを指定します）
mysql -uroot -pxxxxxx testdb < testdb.sql

インポートされた SQL スクリプトのほとんどは、最初にデータベースとテーブルを作成し、次にデータを挿入します。最も時間のかかる部分は、おそらくデータの挿入です。ファイルサイズを縮小するには、拡張挿入メソッド、つまり insert into table_name values (),(),(),...,(); のように複数の行をまとめてバッチ挿入する方法を使用することをお勧めします。拡張挿入を使用すると、ファイルサイズが大幅に小さくなり、1 つずつ挿入する場合よりも挿入速度が数倍速くなります。 mysqldump を使用してエクスポートされたファイルは、デフォルトでバッチ挿入方式を使用します。エクスポート時に、--skip-extended-insert パラメータを使用して、1 つずつ挿入するように変更できます。以下では、1,000 万のデータポイントを含むデータテーブルを例に、さまざまな方法でエクスポートされたファイルを挿入する速度をテストします。

上図からわかるように、拡張挿入を使用して SQL スクリプトをインポートするには約 10 分かかりますが、SQL スクリプトを 1 つずつインポートすると時間がかかりすぎて、約 1 時間経ってもまだ完了しません。2G を超えるテキストのインポートが 1 時間以上完了せず、作成者は待ちきれず手動でキャンセルしますか? ただし、複数のエントリをまとめて挿入すると、データを 1 つずつ挿入するよりも数倍の時間を節約できることがわかります。

2. インポートを高速化するためにパラメータを変更してみる

MySQL には、innodb_flush_log_at_trx_commit と sync_binlog という有名な「ダブルワン」パラメータのペアがあります。セキュリティ上の理由から、これら 2 つのパラメータのデフォルト値は 1 です。スクリプトをすばやくインポートするために、これら 2 つのパラメータを一時的に変更できます。以下は、これら 2 つのパラメータの簡単な紹介です。

innodb_flush_log_at_trx_commitのデフォルト値は1で、0、1、または2に設定できます。
innodb_flush_log_at_trx_commit が 0 に設定されている場合、ログバッファは 1 秒ごとにログファイルに書き込まれ、同時にログファイルのフラッシュ操作が実行されます。このモードでは、トランザクションがコミットされると、ディスクへの書き込み操作はアクティブにトリガーされません。
innodb_flush_log_at_trx_commit が 1 に設定されている場合、MySQL はトランザクションがコミットされるたびにログバッファー内のデータをログファイルに書き込み、ディスクにフラッシュします。
innodb_flush_log_at_trx_commit が 2 に設定されている場合、MySQL はトランザクションがコミットされるたびにログバッファー内のデータをログファイルに書き込みます。ただし、フラッシュ操作は同時に実行されません。このモードでは、MySQL は 1 秒ごとにフラッシュ操作を実行します。
sync_binlogのデフォルト値は1で、[0,N)に設定できます。
sync_binlog = 0 の場合、オペレーティングシステムのその他のファイルをフラッシュするメカニズムと同様に、MySQL はディスクに同期せず、オペレーティングシステムに依存してバイナリログを更新します。
sync_binlog = N (N>0) の場合、MySQL はバイナリログを N 回書き込むたびに fdatasync() 関数を使用してバイナリログをディスクに同期します。

これら 2 つのパラメータはオンラインで変更できます。すばやくインポートしたい場合は、次の手順に従ってください。

# 1. MySQL コマンドラインを入力して、これら 2 つのパラメータを一時的に変更します。set global innodb_flush_log_at_trx_commit = 2;
グローバルsync_binlogを2000に設定します。

# 2. SQLスクリプトを実行してmysqlをインポートします -uroot -pxxxxxx testdb < testdb.sql

# 3. インポートが完了したら、パラメータを元に戻して、global innodb_flush_log_at_trx_commit = 1 に設定します。
グローバルsync_binlogを1に設定します。

新しいスレーブデータベースを作成する必要がある、または binlog を生成する必要がないという別のシナリオもあります。この場合、SQL スクリプトをインポートするときに一時的に binlog を記録しないように設定できます。スクリプトの先頭に set sql_log_bin=0; を追加してからインポートを実行すると、プロセスがさらに高速化されます。 MySQL インスタンスで binlog が有効になっていない場合は、このステートメントを実行する必要はありません。

要約:

この記事では主にデータを素早くインポートする方法を紹介します。データのロードやマルチスレッド挿入用のプログラムの作成など、データを素早くインポートする方法は他にもあるかもしれません。この記事で説明する方法は、SQL スクリプトを手動でインポートする場合にのみ適しています。以下は、この記事で説明した方法の概要です。

エクスポートとインポートには、MySQL に付属するコマンドラインツールを使用します。
拡張挿入メソッドを使用して、複数の値を一度に挿入します。
innodb_flush_log_at_trx_commit および sync_binlog パラメータを一時的に変更します。
binlog を閉じるか、一時的に binlog の記録を停止します。

実際には、最初にインデックスを作成せず、データを挿入した後にインデックスを追加する、最初にテーブルを MyISAM または MEMORY エンジンに変更し、インポートが完了した後に InnoDB エンジンに変更するなど、他の解決策もいくつかあります。しかし、これら 2 つの方法は実装が面倒であり、その効果も不明です。上記の方法は、著者の個人的な経験に基づく要約に過ぎず、包括的ではない可能性があります。追加していただいても結構です。

上記は、MySQL にデータを素早くインポートする方法の詳細です。MySQL へのデータのインポートの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

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