MySQLで連続挿入処理を高速化する方法

こんにちは!ぐちです。

INSERT

マスタデータやテストデータなど連続して同じテーブルにデータを流し込む作業って必ず発生しますよね。しかもそういう作業って決まって10万件とか100万件とか大量なんですよね。。何個もあるうちのカラムの1つとか2つだけが違うだけのSQLが呪文のように並んでいることでしょう。

INSERT INTO TBL_HOGE VALUES ('100','10001101');
INSERT INTO TBL_HOGE VALUES ('101','10013101');
INSERT INTO TBL_HOGE VALUES ('102','10005541');
   ・
   ・
   ・

上記のような感じですね。

プロジェクトごとにINSERT文を生成するようなエクセルのマクロとかツールを使っておられるとは思いますが実際にSQLを実行する場合に上記のSQLをそのまま実行するのはイケてないですよ〜。笑

バルクインサート

上記の例にある1文ずつのINSERT文はレコード数分SQLを発行していることになります。皆さんご存知の通りですよね。正常に終わればコミットまで処理されます。ということはそれだけI/Oのボトルネックが発生することになります。

そこでバルクインサートという方法があります。SQLを見て頂いたほうが早いと思うので前述のINSERT文を変更してみます。

INSERT INTO TBL_HOGE VALUES ('100','10001101'), ('101','10013101'), ('102','10005541');

INSERT INTO TBL_HOGE VALUESの部分が1つになり、挿入する実データの部分が,でつながりました。なんとなくご想像がつくかと思いますがこのSQLであれば実行回数としては1回となります。したがってI/Oのボトルネックの発生を最小限まで抑えることができます。

実行速度

ちゃんと測ったわけではないので感覚値ということでご容赦頂きたいのですが10万件程度のレコード挿入であればだいたい100〜200分の1程度の処理速後が期待できます。

ネットワーク状態やサーバマシンの状態にも依存すると思うのでほんとに僕個人の感覚値になってしまいますがレコード数が多くなればなるほど高速処理を体感できると思います。

注意点

完璧なSQLに感じ取られたかもしれませんが唯一の注意点があります。挿入する対象のカラムは全て同一である必要があります。カラム名の指定は通常のINSERT文と同様に省略すれば全カラムに対して、指定すれば特定のカラムに対して実行することが可能ですが全てのVALUESで同じカラムに対しての挿入である必要があります。

冒頭でご説明した通り同じようなテストデータやマスタデータなどの登録に威力を発揮すると思うのでぜひ試してみてください。

では今回はこの辺で。