【MySQL】大量のデータを登録する際に処理時間を大幅に削減する方法

DB&サーバ

2019.04.09

みなさんこんにちは。
最近散歩にはまっているyamashitaです。あまりにも不健康な生活をしているので始めてみましたが楽しいです。
休みの日に会社の近くに行くのは少し抵抗ありますが、馬車道から元町辺りまで歩いて帰ってくるのオススメです。
まあそれも桜の季節と共に終わりを迎えそうですが。
次は5月頃に港の見える丘公園のバラが見頃になるらしいのでフラッと言ってみたいですね。

さて今回は大量のデータをデータベースに登録する際にかかる時間を大幅に削減する方法について紹介します。
状況としてはお客さんから預かったCSVファイルを読み込み、それを登録するという物でした。

LOAD DATA INFILE 構文

大量のデータを登録する際に一つずつINSERTするのではなく、一つのファイルを読み込んでその中身を一気に登録します。
この時お客さんからもらえるCSVファイルとデータベースの構成が全く一緒なら楽だったのですが、そんな都合のいい事は無く処理の中で入れ替えて書き替えました。
~~その方法については後日別の記事で書きたいと思います。~~
書きました。
Dockerのコンテナ上で作成したファイルを別コンテナに共有する方法

クエリ例

LOAD DATA INFILE '/tmp/test.csv' INTO TABLE users FIELDS TERMINATED BY ',' ENCLOSED BY '"' (name, age, phone, address);

指定することは以下の通りです。

LOAD DATA INFILE 'ファイルパス' INTO TABLE テーブル名 FIELDS TERMINATED BY 'ファイル上の区切り文字' ENCLOSED BY 'ファイル内の囲い文字' (挿入したい列名1,列名2,列名3・・・・);

他の設定については以下のリファレンスマニュアルを参照してください。
LOAD DATA INFILE 構文

処理時間ビフォーアフター

今回の処理の変更でおおよそにかかった時間の比較を載せたいと思いますが、ビフォーアフターのビフォーが特殊なので参考にならないかもしれないです。

条件

ユーザー数15000件
1件につき関連テーブルに5レコードずつ登録
変更前はWordPressにPHPで登録、且つカスタムフィールドゴリゴリに使ってる

以上の条件で変更前：約90分、変更後：約1分程度まで早くなりました。
リファレンスによるとINSERTより20倍速くなるそうです。
INSERT ステートメントの速度

まとめ

データベースの登録処理に時間かかる時にはINSERTではなく、LOAD DATA INFILEがいいというお話でした。
WordPress以外でも処理時間の短縮につなげられるのではないかと思います。
ただし、ファイルのフォーマットがおかしかったりすると想定外のデータが作成されてしまうので気を付けてください
また、CMSやフレームワークを使ってる時には本来の形式とは合致しないデータが登録されてしまったり、
フレームワークのバリデーションを通過せずに処理が行われてしまうことがありそうだと感じたので使用する際の環境に応じて適宜条件設定をしてください。
以上です。