ダメプログラマの技術メモ

プログラミングの技術メモや駄文など

CakePHP 2.x系の更新時のSQL発行回数を減らしたい

CakePHP

今年7月に入ってから業務でCakePHPを使用しており、外国人プログラマ達によって書かれたソースの改修とパフォーマンス・チューニングを行っています。

その際、DB更新処理でSQLの発行回数を減らす必要が生じたので、その対応方法を述べたいと思います（需要はあまりないかもなぁ）
 

saveメソッドのSQL発行回数は多い？

DBの更新処理にはsaveメソッドを使用することが多いと思いますが、実はこいつ、かなりの曲者です。

百聞は一見に如かずです。

まず下のメソッドを見てください。

public function addPoint($user_id, $point) {
    $user = $this->findById($user_id);
    $user['User']['point'] += $point;
    $this->save($user);
}

実際に今の案件で散見されるsaveメソッドの使い方です。

上記のメソッドはUser.phpというモデルに書かれているとします。

ユーザが保持しているポイントを加算するだけです。

ちなみに、usersテーブルのフォーマットは次のとおりです。

usersテーブル

カラム値
id 1
name 初音ミク
point 1000
created 2012-08-18 21:24:44
modified 2012-08-18 21:24:44

saveする前に何故かfindしているのが気になりますが（後述）、特に問題がなさそうなプログラムである、と最初はそう思っていました。

このメソッドをコントロール側で「$this->User->addPoint(1, 50);」のように呼び出すとミクちゃんのポイントが50加算されます。

しかし、これを実現するためにCakePHPが発行するSQLを見て驚きました。

SELECT `User`.`id`, `User`.`name`, `User`.`point`, `User`.`created`, `User`.`modified` FROM `LAA0200849-cakephp`.`users` AS `User` WHERE `User`.`id` = 1 LIMIT 1
SELECT COUNT(*) AS `count` FROM `LAA0200849-cakephp`.`users` AS `User` WHERE `User`.`id` = 1
SELECT COUNT(*) AS `count` FROM `LAA0200849-cakephp`.`users` AS `User` WHERE `User`.`id` = 1
SELECT COUNT(*) AS `count` FROM `LAA0200849-cakephp`.`users` AS `User` WHERE `User`.`id` = 1
UPDATE `LAA0200849-cakephp`.`users` SET `id` = 1, `name` = '初音ミク', `point` = 1050, `created` = '2012-08-18 21:24:44', `modified` = '2012-08-18 21:24:44' WHERE `LAA0200849-cakephp`.`users`.`id` = '1'

(°д°)ｴｯ?

SQLが5回も発行されており、しかもそのうち3個は同じSQLです。

というわけで、何故このようなSQLが発行されるのか調査しました。

 

どこでSQLが発行されているのか？

1つ目のSQL

SELECT `User`.`id`, `User`.`name`, `User`.`point`, `User`.`created`, `User`.`modified` FROM `LAA0200849-cakephp`.`users` AS `User` WHERE `User`.`id` = 1 LIMIT 1

これは例で提示したメソッドの１行目「$user = $this->findById($user_id);」で発行されています。

「（ ´_ゝ｀）ふーん、だったらいいじゃん」と思われるかもしれませんが、よ～く見てください。

usersテーブルの全カラムを取得する必要があるでしょうか？

更に言うと、このSQLそのものが不要です。

何故ならupdateする前に、わざわざselectで更新カラムの値を取得することはないでしょう。

この部分は改善の余地がありそうです。

 

2つ目のSQL

SELECT COUNT(*) AS `count` FROM `LAA0200849-cakephp`.`users` AS `User` WHERE `User`.`id` = 1

これは/cakephpのルート/lib/Cake/Model/Model.phpの1634行目で発行されています。

$exists = $this->exists();

レコードの存在チェックをして、insertとupdateのどちらを行うか判断しています。

あっ、それと言い忘れていましたがCakePHP 2.2.1の場合で説明しています。

バージョンが違うと行番号がズレる可能性がありますのでご注意を。

 

3つ目のSQL

SELECT COUNT(*) AS `count` FROM `LAA0200849-cakephp`.`users` AS `User` WHERE `User`.`id` = 1

これは /cakephpのルート/lib/Cake/Model/Model.phpの1643行目のvalidateメソッドで発行されています。

if ($options['validate'] && !$this->validates($options)) {

おそらくsaveメソッドの第2引数（$validate = true）が無指定なので、$options['validate']がtrueになって、validatesメソッドが呼ばれているのだと思います。

試しに最初に提示したプログラムのsaveメソッドの第2引数にfalseを指定したら、$options['validate']もfalseになってvalidatesメソッドが呼ばれなくなりました。

 

4つ目のSQL

SELECT COUNT(*) AS `count` FROM `LAA0200849-cakephp`.`users` AS `User` WHERE `User`.`id` = 1

これは/cakephpのルート/lib/Cake/Model/Model.phpの1715行目で発行されています。

$success = (bool)$db->update($this, $fields, $values);

updateメソッドの本体は/cakephpのルート/lib/Cake/Model/Datasource/DboSource.phpにあります。

その中でdefaultConditionsメソッドをコールしています。

defaultConditionsメソッドには「$exists = $model->exists();」という処理があり、この部分でクエリを発行しています。

ちなみに、defaultConditionsメソッドの役割はWHERE条件句の一部を作成することです（たぶん）

 

5つ目のSQL

UPDATE `LAA0200849-cakephp`.`users` SET `id` = 1, `name` = '初音ミク', `point` = 1050, `created` = '2012-08-18 21:24:44', `modified` = '2012-08-18 21:24:44' WHERE `LAA0200849-cakephp`.`users`.`id` = '1'

長かった。

これが実行したかったupdate文です。

ただし、1つ目のSQLと同様に不要な項目が混入しています。

しかもcreatedやmodifiedをfindの取得結果で更新しています。

バグっていますね(汗

 

SQLの発行回数を減らしてみます！

以上の調査から、saveメソッドを使用して、かつ、SQLの発行回数を減らそうと思ったらModel.phpなどのコアライブラリを修正する必要があります。

しかし、そのようなことをダメプログラマの私はガクブル怖くてできないです。

というわけで実際には、最初に提示したメソッドを次のように修正しました。

public function addPoint($user_id, $point) {
    $ret = $this->query('UPDATE users SET point = point + ? WHERE id = ?', array($point, $user_id));
    if ($ret === false) {
        // エラー処理
    }
    if ($this->getAffectedRows() == 0) {
        // エラー処理
    }
}

 

修正のポイントは2つあります。

 

1つ目はqueryメソッドでupdate文を発行すること。

もともとsaveメソッドはSQLを知らない人でも簡単にDBアクセスできるようにするために導入されているので（違うかも、適当に言っちゃいましたｗ）、SQLが得意な人はわざわざ使用する必要はありません。

むしろ直接SQLを書いたほうが速いでしょう。

念のため戻り値のチェックもしています。

あとqueryメソッドはバインド変数が使用できるので積極的に利用したいところです。

 

2つ目はgetAffectedRowsメソッドを使用してSQLで影響を受けた行数をチェックすること。

今回は必ず更新処理が行われることを前提としているので更新が行われなかった（影響行数が0だった）場合、エラーにしています。

 

さてさて、修正版メソッドが発行するSQLを調べたら次のようになりました。

UPDATE users SET point = point + ? WHERE id = ? , params[ 50, 1 ]

1回のクエリですみ、SQLの発行回数を削減するという目的が達成できました^^

 

総括すると

saveメソッドによりデータベースの種類に依存せず、追加・更新が簡単にできるのはスゴイことですが、その利便性ゆえにパフォーマンスが犠牲となっているのは否めないです。

これらの関係はトレードオフでしょうね。

saveメソッドは非常に便利なので、わざわざqueryメソッドで全て書き換える必要はありませんが（最初のメソッドの例は酷すぎますが）、パフォーマンスを要求する部分はquery＋getAffectedRowsの組み合わせがおススメだと思います。

 

（パフォーマンス云々言うなら、そもそもフレームワークを使うなと言われそうですが、立場上そういう発言ができないのがツライところです）