膨大なサイズのCSVファイルから爆速でMySQLへインポート

　CSVファイルからDB(MySQL)にインポートする機能はよくあるし、情報もゴロゴロとある。前回の記事では大量のデータをCSVファイルとして素早く書き込む方法を書いたが、今回はその逆と言っても良い。

　ただ、CSV→DBは色々な制約を受けるので最も早く確実なただ一つの答えがあるわけではなさそう。今回紹介するのはサーバーのupload_max_filesizeや利用可能メモリについては度外視している。

CSVのMySQLインポートの手段

　先に方法を列挙すると以下のようになる。

１レコードずつインサート（激遅）
バルクインサート（速い）
バッチでインサート（ちょっと遅い）
LOAD DATA LOCAL INFILEインサート（最速）

　全てのやり方ができる環境かどうかも考慮に入れる必要がある。

　また今回はフレームワークで使用されることを前提としてPHP(Laravel)で検証しているが、Insertの処理速度という意味ではDBの問題なのでどのフレームワークであろうが、どの言語であろうが同じである。

激遅！1レコードずつインサート

　この方法は当然一番遅い方法。CSVのデータを取得して一行ごとにINSERT文を発行しているのでとてつもなく時間がかかる。

$file = new \SplFileObject($path);
$file->setFlags(\SplFileObject::READ_CSV);
 
foreach ($file as $row => $line) {
	Hoge::create([
        'huga'=> $line[0],
        ...
    ]);
}

　LaravelのModel::createでやっている分、普通のInsertより処理が重いのだろうが、そもそもこのような1行ずつInsertは問題がある。まさにコンピュータリソースの無駄遣い。

　今回検証したデータ（30万行のCSVファイル）をこの方法でimportしようとしたが5分以上かかってリクエストタイムアウトになった。

速い！BULK INSERT

　BULK INSERTは１度のクエリで複数のデータをInsertする方法だ。

　これは当然速い。また、１クエリの処理なのでトランザクションを貼る必要もない。

$csvData = [
['hoge','foo',...]
...
]
DB::table('hoge').insert($csvData);

　これで処理すれば最強じゃんって思ったが、Bulk（バルク）にも問題は存在する。

　MySQLの場合、「max_allowed_packet」というクエリのデータサイズの上限が存在する。なんでも16MBが最大値なのだとか。

https://qiita.com/_am_/items/91824da643256d46b847

　私が検証した30万行のCSVファイルを１クエリでINSERTしようとした場合、SQL文のデータサイズは16MBなど軽く超えてしまう。

　膨大なデータをINSERTする場合1クエリでINSERTは不可能ってことだ。データ量が不定の場合は次のバッチ処理が一般的で王道だろう。

バッチINSERT処理で妥協するか

　1件ずつINSERTは遅すぎるし、1度のクエリでのINSERTはデータサイズが問題になる。一般的にはこれら2つを組み合わせたバッチで処理すればよいだろう。（2000件に1回INSERTなど）

$file = new \SplFileObject($path);
$file->setFlags(\SplFileObject::READ_CSV);
$cnt = 0;
$insertData = [];
foreach ($file as $row => $line) {
	$cnt++;
	$insertData[] = [
       'huga'=> $line[0],
        ...
    ];
	if ($cnt >= 2000) {
		DB::table('hoge').insert($insertData);
		$cnt = 0;
		$insertData = [];
	}
}

　これならどれだけデカイサイズのCSVでもほとんどの場合正常にINSERTできるし、現実的な処理時間を達成できるので、多くのケースではバッチ処理で行われる。

　ただし、今回の検証データ（30万行データ）では全てのクエリの処理完了まで2分程度かかった。ちなみに上記はトランザクションとか処理してないので注意。

爆速処理！LOAD DATA LOCAL INFILEでCSVファイルごと実行

　バッチ処理の実行時間では納得できない人は、CSVファイルからINSERT実行を検討してみると良い。

　これはバルクインサートよりも速く、max_allowed_packetの制限もない、最速のINSERT方法と思われる。

$path = "test.csv";
$sql = <<< EOM
LOAD DATA LOCAL INFILE '$path' REPLACE INTO TABLE hoge 
CHARACTER SET utf8 FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
IGNORE 1 LINES;
EOM;
DB::insert($sql, []);

　UTF-8でのファイルで、csvの1行目は無視している。

　これだとなんと2秒台で30万行のデータをINSERTできた。とてつもなく早い！ファイルパスを指定していることからファイルデータそのものを送ってバルクINSERTのごとく処理している。

　当然DBサーバーが扱えないようなデカさのサイズ(100GBとか)のCSVファイルをこの方法でINSERTできるかと言ったら不可能だ。DBサーバーのメモリ量を超えると処理が落ちる。

　なので巨大なCSVファイルをインポートする必要があり、そのサイズが不定の場合はやはりバッチ処理でInsertするのが無難でしょう。それでも処理速度を追い求めたい場合はLOAD DATA LOCAL INFILEでInsertするのが良い。例えば、ユーザーが大きめのCSVファイルをアップロードしてそれをMySQLへInsertしてレスポンスを返す場合など、何分も待たせることができないときには重宝するだろう。