プライベートで作ったWebアプリで、一画面だけブラウザに表示されるまで3秒かかる激重画面があります。 この画面ではCakePHPが自動的にいろんなテーブルをjoinしたSQLを生成しているので、その辺りが原因だろうとは感づきました。 それにしても、たかだか20行程度のselectなので、なんか変だ。。。 ちょっとだけ分析と改善をしました。(はじめてのパフォーマンスチューニング…ドキドキ)
結論としては、1000倍早くなりました。 CakePHPのクエリ自動生成は楽ですが、パフォーマンス上の問題が発生した時にはやはりSQLを知らないとダメだなぁ…
環境
VirtualBoxのVM(メモリ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
は存在するのだから、「Order
はTrackingCode
に紐づく」(つまり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
が消えてる
まとめ
- CakePHPで
hasOne
アソシエーションを設定する場合は生成されるクエリに注意 - ソート列にindexが作成されているか注意
- クエリが遅かったら
explain
で手がかりをつかむ