Posted inMySQL

LOAD DATA INFILEで大量データをMySQLに高速インポートする方法：数百万件のレコードを処理速度を最適化する

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

問題：なぜ数百万件のINSERTはこれほど遅いのか？

ターミナルの画面を何時間も眺めながら、INSERTコマンドがまだ終わらないという状況に陥ったことはないだろうか？以前、旧システムから約5000万行のログデータをMySQLに移行してレポートを作成するタスクを受けたことがある。最初はシンプルなPythonスクリプトを書いて、CSVファイルを読み込み、INSERT INTO...を1行ずつ実行した。結果は2時間後、データの約3%しか処理できていなかった。上司がデータを使う会議が迫る中、あまりの遅さに冷や汗をかいた。

なぜこれほど遅いのか？単一のINSERT文には多くの隠れたコストが伴う。MySQLはSQL文の解析（parse）、権限チェック、テーブルロック、トランザクションログ（redoログ）の書き込み、そして最も重要なこととして、各行ごとのインデックス更新を行わなければならない。100万行あれば、MySQLはこれらの処理を100万回繰り返す。さらに、アプリケーションとデータベースサーバー間のネットワーク遅延も時間を押し上げる要因となる。

この問題を解決するため、MySQLは強力な武器を提供している。それがLOAD DATA INFILEだ。1行ずつ処理するのではなく、大きなブロック単位でデータをストレージエンジンに直接投入し、不要な中間処理のほとんどをスキップする。

LOAD DATA INFILEとは何か、なぜ速いのか？

LOAD DATA INFILEはテキストファイル（通常はCSVまたはTXT）からデータを読み込み、MySQLのテーブルに直接ロードするSQL文だ。公式ドキュメントと実際の経験によれば、この方法は通常のINSERTより10〜20倍高速だ。

主に3つの理由で大幅に速い：

  • 大きなブロック単位でデータを読み込み、システムコールの回数を最小化する。
  • 各データ行のSQL解析ステップをスキップする。
  • データのロード完了後にインデックスの構築を最適化でき、1行ごとの継続的な更新が不要になる。

開始前の設定（セキュリティの壁を乗り越える）

すぐに実行しようとしないこと。MySQLはデフォルトでシステムファイルの読み取りによるSQLインジェクションを防ぐためにこの機能を無効にしている。以下の設定を先に行う必要がある。なお、MySQLサーバーのセキュリティ設定を事前に把握しておくと、この壁を安全に乗り越えやすい。

1. secure_file_priv変数を確認する

この変数はMySQLがファイルを読み込めるディレクトリを指定する。次のコマンドで確認できる：

SHOW VARIABLES LIKE 'secure_file_priv';
  • 結果がパス（例：/var/lib/mysql-files/）の場合、CSVファイルをそのディレクトリにコピーする必要がある。
  • 結果がNULLの場合、この機能は完全に無効になっている。
  • 結果が空（empty）の場合、任意の場所からファイルを読み込める。

変更するにはmy.cnfまたはmy.iniを編集する：

[mysqld]
secure_file_priv = ""

その後、MySQLサーバーを再起動する。

2. LOAD DATA LOCALを許可する

ファイルがMySQLサーバー上ではなくローカルマシンにある場合、LOCALキーワードを使う必要がある。サーバーとクライアントの両方でこれを許可する必要がある：

SET GLOBAL local_infile = 1;

実践：構文と実際の使用例

customers.csvというファイルがあり、構造はid, name, email, created_atとする。ファイルの内容は次のようなものだ：

1,"Nguyen Van A","[email protected]","2023-10-01 10:00:00"
2,"Tran Thi B","[email protected]","2023-10-01 10:05:00"

インポートのSQL文はこのようになる：

LOAD DATA INFILE '/var/lib/mysql-files/customers.csv'
INTO TABLE customers
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, name, email, created_at);

重要なパラメータの説明：

  • FIELDS TERMINATED BY：カラム間の区切り文字（通常はカンマまたはタブ\t）。
  • ENCLOSED BY：文字列を囲む文字。データにカンマが含まれる場合に対処するためダブルクォートを使用する。
  • LINES TERMINATED BY：行末文字。Linuxは\n、Windowsは\r\nを使用。これはよく間違える。
  • IGNORE 1 LINES：ファイルにヘッダー行がある場合、最初の行をスキップする。

速度を極限まで高める最適化テクニック（実戦経験）

上記のコマンドを実行するだけでもかなり速い。しかし、数千万件のレコードを扱う場合、さらに以下のテクニックを組み合わせて速度を最大化することが多い。大規模データの管理という観点では、テーブルパーティショニングとの組み合わせも長期的なパフォーマンス維持に効果的だ。

1. 制約チェックを一時的に無効にする

行を挿入するたびに、MySQLは外部キー（Foreign Key）や一意性（Unique Index）の制約違反がないかチェックする。クリーン済みのデータを大量インポートする場合、このチェックは不要だ。

SET foreign_key_checks = 0;
SET unique_checks = 0;
SET autocommit = 0;

インポート完了後は必ず元に戻し、COMMITを実行すること。

2. インデックスを無効化する（MyISAMテーブルまたは新規テーブルのみ）

MyISAMテーブルの場合はALTER TABLE table_name DISABLE KEYS;コマンドが使える。InnoDBの場合、最善の方法はCSVファイルをインポート前にプライマリキーの順序でソートすることだ。これによりMySQLがディスクにデータを順番に書き込めるため、ディスクのランダムアクセス（ディスクスラッシング）を避けられる。

3. バッファサイズを増やす

LOAD DATAを実行する前に、MySQLが大量データを処理するためのメモリバッファを増やす：

SET GLOBAL innodb_buffer_pool_size = 4G; -- RAMの容量に応じて調整する
SET GLOBAL innodb_log_file_size = 1G;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

注意：innodb_flush_log_at_trx_commit = 2はトランザクションごとのディスクへのログ同期をスキップするため、速度が顕著に向上する。トレードオフとして、インポート中にサーバーが突然停止した場合、最後の数秒分のデータが失われる可能性がある。これは1回限りのバッチインポートとして完全に許容できる範囲だ。

よくあるエラーの対処法

このコマンドを実行する際に最もよく遭遇する3つのエラーと、その迅速な対処法を紹介する：

  • 日付フォーマットエラー：MySQLはYYYY-MM-DDフォーマットに厳格だ。CSVファイルがDD/MM/YYYYフォーマットを使用している場合、一時変数を使って変換する： 
    LOAD DATA INFILE 'data.csv'
INTO TABLE orders
FIELDS TERMINATED BY ','
(id, @temp_date, amount)
SET order_date = STR_TO_DATE(@temp_date, '%d/%m/%Y');
  • Data too longエラー：多くの場合、行末文字の誤りが原因だ。WindowsでCSVを作成してLinuxで実行すると即座に問題が発生する。\r\nと\nをしっかり確認すること。
  • 接続切断エラー：ファイルが大きすぎて接続がタイムアウトする。1〜2GBほどの小さなチャンクに分割するか、MySQLの設定でwait_timeoutとmax_allowed_packetを増やす。

まとめ

冒頭の話に戻ると、LOAD DATA INFILEと外部キーチェックの無効化、プライマリキー順のファイルソートを組み合わせた結果、5000万行のデータを45分以内に処理し終えた。最初のINSERTスクリプトと比較して20時間以上の節約となり、上司は会議に間に合ってデータを入手できた。

1000万行を投入する前に、まず1,000行でテストしよう。TERMINATED BYとカラムのマッピングがすべて正しいことを確認してから全体を実行する。そして事前のバックアップは言うまでもない。10分のバックアップは、1日がかりのリカバリよりずっとましだ。1000万行を処理し終えてからカラムのずれに気づいても、その後の後片付けは本当に大変だ！

Share:
Tags: