DBエンジンは、扱うデータ量が多いという性質上、適切な性能を出すには工夫が必要となることが多々あります。今回は、少しの工夫でMySQLのINSERTが速くなった、という話です。
INSERTはデータ量に連れて
データベースにデータを入れるにはINSERTを使いますが、データが多くなればそれだけ手間が増えてきます。しかも、1命令あたりで極端に重くなっているわけではないので、スロークエリにもなかなか現れず、追いかけるだけでも意外と面倒かもしれません。
一気に入れることでの効率化
もちろん、ある行数だけ入れる場合にそれだけの書き込み処理が内部で動くことは避けようがないですが、SQL文を投げた際に、内部では
- DBサーバと通信を行う
- SQL文をパースして処理内容を把握する
- 実際の書き込み処理
- インデックスの構築
などなど、いろんな処理が走ります。一気に入れることができれば、これらの処理は一度に済ますことができますし、記憶域の確保もまとめて行えるなど、高速化できるようになります。
実際の構文
INSERT INTO table_name (col_1, col_2, col_3) VALUES (1, 2, 3);
1行を挿入する場合はこのように書きますが、複数行の挿入の場合も、文法はそこまで変わらず
INSERT INTO table_name (col_1, col_2, col_3) VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9);
というように、VALUESのあとにかっこを連ねるだけです。
ON DUPLICATE KEY UPDATE
MySQLには、ON DUPLICATE KEY UPDATEといって、主キーやUNIQUEが重複するデータがすでに入っていた場合には、その行の更新を行う、という機能がありますが、これも複数列に対応させることができます。重複時のUPDATE内容を生成する上で、もともとINSERTしようとしていた内容が必要なら、VALUES(col)という形で取得できます。
長さの制限
INSERT自体に個数制限はありませんが、MySQLに投げられるSQL文の長さの最大値は、max_allowed_packetという変数で決まっています。MySQLサーバに、show variables like 'max_allowed_packet';と投げることで設定を確認することができます。動的に増やしたり、my.cnfで設定することもできます（参考）。
なお、この設定自体の最大値は1GBだそうなので、設定を上げすぎてパンクするということは、そうそうないとは思います。