たぶん今できるのはクエリの最適化。 explain をよむ。

パフォーマンスを良くしたい いくつか方法がある。 スキーマを変える？　クエリを変える？ クエリを変えるのはお手軽。（比較的）影響がないし。

そのためには、explainを理解する必要がある。（なぜ？） クエリを最適化するには、まずEXPLAINで実行計画をチェックする必要があるからさ。

クエリを最適化するということは、 * 書き換える前と後でクエリの実行結果が同じになる * EXPLAINがよりよい実行計画を表示する であるらしい。

そのためには、explainを理解する必要がある（重要なことなので２回）。 以下のような出力が得られる。

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,PRIMARY,t1,ALL,"room_id,eip_t_message_room_member_target_user_id,eip_t_message_room_member_user_id_target_user_id",NULL,NULL,NULL,6,"Using where; Using temporary; Using filesort"
1,PRIMARY,t2,ALL,PRIMARY,NULL,NULL,NULL,4,"Using where; Using join buffer (Block Nested Loop)"
1,PRIMARY,t4,eq_ref,PRIMARY,PRIMARY,4,org0000009.t1.target_user_id,1,NULL
2,"DEPENDENT SUBQUERY",t3,ref,"eip_t_message_read_index1,eip_t_message_read_index2",eip_t_message_read_index1,14,"org0000009.t2.room_id,const,const",1,"Using where; Using index"

実行計画は内部的にはツリーで表現されているらしい。どうみてもテーブルなんですがそれは…。どういうことなんでしょう。（ツリーと聞いて紙とペンを用意する）しかも、そのツリーは「見ると各テーブルへのアクセスが理解できるようになるもの」らしい。

ちなみに、ちゃんと「execution plan」というのが表示されている。ただ、下のクエリだと表示できなかった。

explain select t2.room_id, t2.name, t2.has_photo, t2.photo_modified, t4.user_id, t4.last_name, t4.first_name, t4.has_photo as user_has_photo, t4.photo_modified as user_photo_modified, t2.auto_name, t2.room_type, t2.last_message, last_update_date, (select count(*) from eip_t_message_read t3 where t3.room_id = t2.room_id and t3.user_id = 4 and t3.is_read =’F’) as unread from eip_t_message_room_member t1, eip_t_message_room t2, turbine_user t4 where t1.user_id = 4 and t1.room_id = t2.room_id and t1.target_user_id = t4.user_id order by t2.last_update_date desc;

このクエリだと表示された。 SELECT t0.USER_ID, t0.GROUP_ID, t0.ROLE_ID, t0.ID FROM turbine_user_group_role t0 WHERE t0.USER_ID = 4;

これもしっかり表示される。 確かに木だ。

image

select t1.message_id, t1.room_id, t1.user_id, t1.message, t1.create_date, t1.member_count, t2.last_name, t2.first_name, t2.has_photo, t2.photo_modified, (select count(*) from eip_t_message_read t3 where t3.message_id = t1.message_id and t3.room_id = t1.room_id and t3.is_read = ‘F’) as unread from eip_t_message t1, turbine_user t2 where t1.user_id = t2.user_id and t1.room_id IN(3) order by t1.create_date desc limit 50

SQL のパフォーマンスを改善する方法

SQL のパフォーマンスを改善したいとなると、index を作成することで実現できる場合があるようなのですが、色々と問題もあるようです（闇雲インデックス）。 取りうる手段としては、今回のチケットで行われていたように、測定(Measure)、解析(Explain)、指名(Nomiante)、テスト(Test)、最適化(Optimize)、再構築(Rebuild)、の手順を踏むと良いそうです。頭文字で MENTOR で覚えると良いとか。

測定(Measure)

まずチケットで改善されているかを確かめるために、master でのパフォーマンスを調べて、対応していただいたチケットとのパフォーマンスと比較しようと思いました。ORMを使っていて直接はクエリが見えないので、実行されたsql文を監視するや特定の箇所のSQLログを確認するためにやったこと(訂正)やAipoのログ出力レベルを変えるを参考に Cayenne で作成されたクエリを見えるようにして、RDBMS に入れて確かめてみます。

次回以降、更新していきます。 （コメントアウトしたらレイアウトが崩れてしまいました。）

メモ

ハックノートのロゴの書体かわいい