複数の .sql ファイルを MySQL に効率的にインポートする方法の詳細な説明

MySQL には、複数の .sql ファイル (SQL ステートメントを含む) をインポートする方法が複数あります。よく使用されるコマンドは、mysql と source の 2 つです。

ただし、これら 2 つのコマンドのインポート効率は大きく異なります。詳細については、最終的な比較を参照してください。

(sqlimportやLOAD DATA INFILEなどのインポート方法もありますが、これらは主に.sqlファイルではなく.csvや.xmlファイルのデータをインポートするために使用されます)

大きなファイル users.sql があるとします。便宜上、これを 3 つの独立した小さな sql ファイル、user1.sql、user2.sql、user3.sql に分割します。

1. mysqlコマンドをインポートする

複数の SQL ファイルをインポートするための mysql コマンド:

$ *.sql 内の SQL の場合; do mysql -uroot -p"123456" mydb < $SQL; 完了

2. ソースのインポートコマンド

ソース コマンドでは、まず MySQL コマンド ラインを入力する必要があります。

$ mysql -uroot -p"123456"

複数の SQL ファイルをインポートするには、まず追加のファイルを作成する必要があります。名前は任意です。ここでは、all.sql を使用します。内容は次のとおりです。

ソースuser1.sql
ソースuser2.sql
ソースuser3.sql

各行はソース コマンドで始まる必要があることに注意してください。

次に、source コマンドを使用してファイルを実行します。

mysql > mydb を使用します。
mysql > ソース /home/gary/all.sql

3. インポート速度を向上させるにはどうすればいいですか?

100 MB を超えるファイルの場合、この方法でのみインポートすると速度が非常に遅くなります。

MySQL の公式推奨事項によると、次のようにインポートの速度を大幅に向上できる対策がいくつかあります。

MyISAM の場合、システムパラメータを調整します: bulk_insert_buffer_size (少なくとも 1 つのファイルの 2 倍のサイズ)

InnoDB の場合、システム パラメータ innodb_log_buffer_size を調整します (1 つのファイルのサイズの少なくとも 2 倍。インポートが完了したら、デフォルトの 8M に戻すことができます。innodb_buffer_pool_size ではないことに注意してください)。

インポートが完了したら、主キーを除く他のインデックスを削除し、インデックスを再構築します。

自動コミットをオフにします: autocommit=0。 (set global autocommit=1; コマンドを使用してこれをオフにしないでください。そうしないと、MySQL システム全体の自動コミットが停止し、innodb ログ バッファーがすぐにいっぱいになります。項目 5 と 6 もセッションでのみ有効です。正しい方法については以下を参照してください。)

ユニークインデックスチェックをオフにします: unique_checks=0。 (これをオフにすると、重複キーの更新に影響します)

外部キーのチェックをオフにします: foreign_key_checks=0。

挿入値は、次のように 1 つのステートメントで記述されます: INSERT INTO yourtable VALUES (1,2), (5,5), ...;

自動インクリメント列がある場合は、innodb_autoinc_lock_mode の値を 2 に設定します。

このうち、項目 1 ～ 2 と 8 は my.cnf ファイルを変更し、MySQL を再起動します。

バルク挿入バッファサイズ=2G;
innodb_log_buffer_size = 2G;
innodb_autoinc_lock_mode を 2 に設定します。

第3条で使用されるコマンド:

# インデックスを削除する DROP INDEX index_name ON talbe_name
ALTER TABLE テーブル名 DROP INDEX インデックス名
ALTER TABLE テーブル名 DROP PRIMARY KEY

# インデックスを追加する ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE テーブル名 ADD UNIQUE (列リスト)
ALTER TABLE テーブル名 ADD PRIMARY KEY (column_list)

項目 4 ～ 6 は .sql で記述されており、バッチ bash スクリプトは次のようになります。

*.sql 内の SQL の場合;
する
  $SQL をエコーし​​ます。
  sed -i '1i\SET autocommit=0;\nSET unique_checks=0;\nSET foreign_key_checks=0;' $SQL
  sed -i '$a\COMMIT;\nSET autocommit=1;\nSET unique_checks=1;\nSET foreign_key_checks=1;' $SQL
終わり

上記の手順に従って調整すると、挿入速度が大幅に向上します。

4. MySQLとソースの効率の比較

SQL ファイルが小さい場合、ソースは MySQL よりも高速です。

実際のテストインポートでは、合計サイズが 25 MB の 5 つの SQL ファイルで、mysql コマンドはソースよりも 2 秒高速です (これは私自身のテストであり、一般的な結果を代表するものではありません)。

大まかに言えば、大きな SQL ファイルをインポートする場合は、mysql コマンドを使用することをお勧めします。

以上が、複数の .sql ファイルを MySQL に効率的にインポートする方法です。お役に立てれば幸いです。