うっかりエンジニアのメモ

未来の自分に宛てたメモ

CakePHPでやたら遅いSELECTクエリの改善

プライベートで作ったWebアプリで、一画面だけブラウザに表示されるまで3秒かかる激重画面があります。 この画面ではCakePHPが自動的にいろんなテーブルをjoinしたSQLを生成しているので、その辺りが原因だろうとは感づきました。 それにしても、たかだか20行程度のselectなので、なんか変だ。。。 ちょっとだけ分析と改善をしました。(はじめてのパフォーマンスチューニング…ドキドキ)

結論としては、1000倍早くなりました。 CakePHPのクエリ自動生成は楽ですが、パフォーマンス上の問題が発生した時にはやはりSQLを知らないとダメだなぁ…

環境

VirtualBoxVM(メモリ613MB)上に下記の環境があります。

テーブル構造

テーブル 内容 外部キー
orders 注文データ shipping_method_id, payment_method_id, prefecture_id, tracking_code_id
shipping_methods 発送手段データ なし
payment_methods 入金方法データ なし
prefectures 都道府県データ なし
tracking_codes 追跡番号データ order_id(この追跡番号に対応した注文のid)

全てのテーブルはid列を主キーとして持つ。

恐ろしく時間がかかるクエリは、ordersテーブルを基点に他の4つのテーブルをleft joinして、注文が新しい順にソートして上位20件だけselectするようなクエリです。

改善前

CakePHPが発行したクエリ

SELECT *
FROM
  orders 
LEFT JOIN shipping_methods ON orders.shipping_method_id = shipping_methods.id
LEFT JOIN payment_methods ON orders.payment_method_id = payment_methods.id
LEFT JOIN prefectures ON orders.prefecture_id = prefectures.id
LEFT JOIN tracking_codes ON orders.id = tracking_codes.order_id
  WHERE 1 = 1
  ORDER BY orders.created DESC
  LIMIT 20;

※見やすさのために手を加えています

WHERE 1 = 1が気になりますが速度に影響しません。
CakePHPが挿入するこのWHERE 1 = 1の意味はこちらの解説がわかりやすいです。
CROSSOVER SEplus : where 1 = 1

実行速度(5回実行平均)

1826ms
くっそ遅い

クエリのexplain結果

id select_type table type possible_keys key key_len ref row Extra
1 SIMPLE orders ALL NULL NULL NULL NULL 2604 Using temporary; Using filesort
1 SIMPLE shipping_methods eq_ref PRIMARY PRIMARY 4 ordersys.orders.shipping_method_id 1
1 SIMPLE payment_methods eq_ref PRIMARY PRIMARY 4 ordersys.orders.payment_method_id 1
1 SIMPLE prefectures eq_ref PRIMARY PRIMARY 1 ordersys.orders.prefecture_id 1
1 SIMPLE tracking_codes ALL NULL NULL NULL NULL 2233

で、でた〜ww
Using temporary; Using filesortはアカン。
なぜアカンのかはイケメンな漢さんの記事が大変参考になります。
漢(オトコ)のコンピュータ道: Using filesort

分析

怪しいのは2箇所。

  • LEFT JOIN tracking_codes ON orders.id = tracking_codes.order_idだけ、join先の列がidではなくorder_id
  • ORDER BY orders.created DESCでは、indexが生成されていないcreated列を使ってソートしている

それぞれ修正していきます。

CakePHPのテーブル関係定義

生成されるSQLの違いはCakePHPでの設定の違いから生まれるのは間違いないので、Orderモデルの定義をみていきます。

<?
class Order extends AppModel {
    public $hasMany = array(
        'ItemOrder'
    );
    
    public $hasOne = array(
        'TrackingCode',
    );

    public $belongsTo = array(
        'ShippingMethod',
        'PaymentMethod',
        'Prefecture'
    );
    ?>

SQLが怪しいTrackingCodeモデルだけ、関係がhasOneなのが気になります。

設計時に「OrderはひとつのTrackingCodeを持つ」という考えだったけれど、エンティティの主従関係を踏まえると、OrderがあろうがなかろうがTrackingCodeは存在するのだから、「OrderTrackingCodeに紐づく」(つまりOrder belongs to TrackingCode)としたほうが自然。

したがってOrderモデルを以下のように修正しました。

<?
class Order extends AppModel {
    public $hasMany = array(
        'ItemOrder'
    );

    public $belongsTo = array(
        'ShippingMethod',
        'PaymentMethod',
        'Prefecture',
        'TrackingCode'
    );

    // 中略
    ?>

ソート

これまで、注文日時が新しい順にデータが欲しかったのでorders.created(レコードの挿入日時)をもとにソートしていましたが

  • orders.created列にはindexが作成されていない
  • orders.id列で降順ソートしても注文日時順にデータが取得できる

ということで、orders.idをもとにソートするように修正します。

ORDER BY orders.id DESC

改善後

CakePHPが発行したクエリ

SELECT *
FROM
  orders 
LEFT JOIN shipping_methods ON orders.shipping_method_id = shipping_methods.id
LEFT JOIN payment_methods ON orders.payment_method_id = payment_methods.id
LEFT JOIN prefectures ON orders.prefecture_id = prefectures.id
LEFT JOIN tracking_codes ON orders.tracking_code_id = tracking_codes.id
  WHERE 1 = 1
  ORDER BY orders.id DESC
  LIMIT 20;

実行速度(5回実行平均)

1.3ms!!
当社比1400倍。くっそ速い(前が遅すぎた)

クエリのexplain結果

id select_type table type possible_keys key key_len ref row Extra
1 SIMPLE Order index NULL PRIMARY 4 NULL 20
1 SIMPLE ShippingMethod eq_ref PRIMARY PRIMARY 4 ordersys.Order.shipping_method_id 1
1 SIMPLE PaymentMethod eq_ref PRIMARY PRIMARY 4 ordersys.Order.payment_method_id 1
1 SIMPLE Prefecture eq_ref PRIMARY PRIMARY 1 ordersys.Order.prefecture_id 1
1 SIMPLE TrackingCode eq_ref PRIMARY PRIMARY 4 ordersys.Order.tracking_code_id 1

Using temporary; Using filesortが消えてる

まとめ

  • CakePHPhasOneアソシエーションを設定する場合は生成されるクエリに注意
  • ソート列にindexが作成されているか注意
  • クエリが遅かったらexplainで手がかりをつかむ