CakePHP2.x系でバルクインサートを使用して高速なインサート処理を実現する
CakePHPで複数のレコードをDBに投入する時に、forループでsaveメソッドを何度も呼び出していませんか?
今のプロジェクトでもそういったソースをよく見かけるのですが、とてつもなく遅い(;´Д`)
ということで、今回はCakePHPでバルクインサートを使用してインサート処理を高速に行う方法を説明します。
今回使用するテーブルは?
musicsテーブル
id | singer | song_title | created | modified |
---|---|---|---|---|
1 | 初音ミク | Tell Your World | 2012-09-29 20:18:57 | 2012-09-29 20:18:57 |
2 | 鏡音リン | 炉心融解 | 2012-09-29 20:18:57 | 2012-09-29 20:18:57 |
3 | 鏡音レン | Fire◎Flower | 2012-09-29 20:18:57 | 2012-09-29 20:18:57 |
4 | 巡音ルカ | Just Be Friends | 2012-09-29 20:18:57 | 2012-09-29 20:18:57 |
前2コの記事でも同じようなテーブルを使用しており、特に言及していませんでしたが、idはAUTO_INCREMENT、modifiedはON UPDATE CURRENT_TIMESTAMPを指定しています(MySQL独自の属性です)
ちなみに、CREATE文は次のとおりです。
CREATE TABLE IF NOT EXISTS `musics` ( `id` INT NOT NULL AUTO_INCREMENT COMMENT 'ID', `singer` VARCHAR(256) NOT NULL COMMENT '歌手', `song_title` VARCHAR(256) NOT NULL COMMENT '曲名', `created` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '作成日時', `modified` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日時', PRIMARY KEY (`id`) ) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;
次回以降は特に断りがない限り、同じようなテーブル構成であれば今回のような属性が付加されているとします。
まずはsaveAllメソッドを使ってみます
CakePHPを使用していれば、バルクインサート ⇒ 一括挿入 ⇒ Model::saveAll(あるいはModel::saveMany)が思い浮かぶと思います。
そこでsaveAllメソッドを使用して、複数のレコードをテーブルに投入するサンプルプログラムを書いてみました。
public function insertMultiMusic($data) { $ret = $this->saveAll($data['Music'], array('atomic' => false)); if (in_array(false, $ret, true)) { // エラー処理 } }
このメソッドはMusic.phpにあるとします。
insertMultiMusicメソッドの引数には、次のような連想配列を指定します。
$data = array( 'Music' => array( array( 'singer' => '初音ミク', 'song_title' => 'Tell Your World', ), array( 'singer' => '鏡音リン', 'song_title' => '炉心融解', ), array( 'singer' => '鏡音レン', 'song_title' => 'Fire◎Flower', ), array( 'singer' => '巡音ルカ', 'song_title' => 'Just Be Friends', ), ), );
saveAllメソッドは直感的で使いやすいメソッドですが、注意すべき点が2つあります。
1つ目は第2引数の指定です。
もし第2引数に何も指定しなかった場合は、saveAllメソッドの内部でトランザクション処理が行われて勝手にコミットしてしまいます。
それを避けるためにサンプルプログラムでは、「array('atomic' => false)」を指定しています。
この指定によって、トランザクション処理が行われなくなります。
恐らく、自前でトランザクション制御をすることの方が多いと思うので、常に「array('atomic' => false)」を指定する癖を付けておいた方がよいかもしれません。
2つ目は戻り値です。
もし全てのレコードのINSERTが成功した場合は、投入レコード数分の要素を持った次のような配列が返却されます。
array(true, true, true, true);
この流れでいくと、3レコード目だけINSERTが失敗した場合は、次のような配列が返りそうですが、
array(true, true, false, true);
にはならず、PDOExceptionがスローされます(not null項目にnullを設定した場合)
う~ん(汗
saveAllメソッドのロジックを見る限りでは、INSERTに失敗したレコード(戻り値の配列要素)にはfalseが設定されそうなのですが。。。
かなり粘ったのですが、falseを返すデータを作ることができませんでした(´・ω・`)ゴメンネ
まぁ、エラー処理に関しては戻り値の配列要素のfalseチェックと、呼び出し元でPDOExceptionをキャッチして、ロールバックするロジックを入れるようにすれば十分だと思います。
saveAllメソッドが発行するSQLは?
INSERT INTO `LAA0200849-cakephp`.`musics` (`singer`, `song_title`, `modified`, `created`) VALUES ('初音ミク', 'Tell Your World', '2012-09-29 20:18:57', '2012-09-29 20:18:57') INSERT INTO `LAA0200849-cakephp`.`musics` (`singer`, `song_title`, `modified`, `created`) VALUES ('鏡音リン', '炉心融解', '2012-09-29 20:18:57', '2012-09-29 20:18:57') INSERT INTO `LAA0200849-cakephp`.`musics` (`singer`, `song_title`, `modified`, `created`) VALUES ('鏡音レン', 'Fire◎Flower', '2012-09-29 20:18:57', '2012-09-29 20:18:57') INSERT INTO `LAA0200849-cakephp`.`musics` (`singer`, `song_title`, `modified`, `created`) VALUES ('巡音ルカ', 'Just Be Friends', '2012-09-29 20:18:57', '2012-09-29 20:18:57')
・・・
(;゚∀゚)=3ハァハァ
投入レコード数分だけINSERT文を発行しています。
これではバルクインサートになっていないですねw
次にqueryメソッドを使ってみた
調べてみたらCakePHPではバルクインサート用のメソッドが用意されておらず、自前でバルクインサートのSQLを書かなければならないようです。
仕方がないので、サクっと書いてみました。
public function insertMultiMusic($data) { $fields = array('singer', 'song_title', 'created'); $holder = '(' . implode(',', array_fill(0, count($fields), '?')) . ')'; $holders = implode(',', array_fill(0, count($data), $holder)); $params = array(); foreach ($data as $val) { foreach ($fields as $field) { if ($field == 'created') { $params[] = date('Y-m-d H:i:s'); } else { $params[] = $val[$field]; } } } $fields = implode(',', $fields); $sql = "INSERT INTO musics ({$fields}) VALUES {$holders}"; $ret = $this->query($sql, $params); if ($ret === false) { // エラー処理 } if ($this->getAffectedRows() != count($data)) { // エラー処理 } }
処理内容はsaveAllメソッドを使用したサンプルプログラムと同じです。
insertMultiMusicメソッドの引数は、saveAllメソッドの第1引数と同じにしています。
微妙に汎用的な作りにしました(汗
$fieldsとSQL文のテーブル名をパラメータ化すれば、多分どのモデルでも使用できるようになります。
DboSource::insertMultiに近い感じかなぁ。
実は、このプログラムもおかしなデータをqueryメソッドに渡すとPDOExceptionが発生します。
なので、状況によってはPDOExceptionをキャッチした後にロールバックするロジックが必要になります。
あと、MySQLの使用を想定しているので、MySQL以外を使用する場合は、DBに応じてバルクインサートSQLを構築する部分を書き換えて下さい。
また、DB毎のバルクインサートの使用制限(SQL文の最大長、投入レコード数など)には注意を払って下さい。
場合によっては、一定件数毎に分けてバルクインサートする必要があるかもしれません。
queryメソッドが発行するSQLは?
INSERT INTO musics (singer,song_title,created) VALUES (?,?,?),(?,?,?),(?,?,?),(?,?,?) , params[ 初音ミク, Tell Your World, 2012-09-30 05:13:19, 鏡音リン, 炉心融解, 2012-09-30 05:13:19, 鏡音レン, Fire◎Flower, 2012-09-30 05:13:19, 巡音ルカ, Just Be Friends, 2012-09-30 05:13:19 ]
複数レコードの投入がバルクインサートの1クエリだけで済んでいますね^^
ベンチマークは如何ほど?
1万件のレコードを投入した場合のベンチマークを取ってみました。
saveメソッド+forループ | saveAllメソッド | queryメソッド | |
---|---|---|---|
1回目 | 14.3828558922 | 15.3304040432 | 0.311769008636 |
2回目 | 14.4124500751 | 15.4717850685 | 0.304137945175 |
3回目 | 14.5008730888 | 15.3734378815 | 0.319990158081 |
【単位:sec】
queryメソッドを使用したバルクインサートが桁違いに速いことがわかります(その差は約50倍!)
CakePHPに限ったことではないですが、インサート処理においてパフォーマンスを求める場合はバルクインサートの使用をオススメします。