SQL のパフォーマンスを改善するための手法がいくつもあるようです。今回は「クエリを最適化する」方法に焦点を当てます。なお、アドバイスを頂いたのですがクエリの速度は explain や直SQLを発行して差を比較してみると良いそうです。

最適化すべきクエリ

対象のクエリを EXPLAIN に入れると、色々な値が入ったフィールドが出力されます。出力はまず解説を見た後にMySQLのドキュメントを読むと分かりやすかったです。この「出力された各フィールドの値」を参考にし、クエリのチューニングの有無を決めると良いそうです。具体的に確認したいフィールドは「id/select_type/table」、「type/key/ref/rowsフィールド」、そして「Extraフィールド」に参考したリンク先では分けられていました。

id/select_type/tableフィールド

ここを見ると全体のアクセス順序が分かります。具体的にどこが遅いということよりも、全体の流れが考えているとおりになっているか、などの確認に使えます。（nested loop とかいういかにも遅そうなのが時たま見えます。また、MySQL Workbench などのGUIを使っている場合は、explain 抜きでクエリを実行すると、右のペインから Execution Plan が選択できて視覚的に確認できます。）

type/key/ref/rowsフィールド

公式ドキュメントによると、type の出力で最も嬉しいのは system で、最も不適切なのは ALL だといいます。ランクとしては、　system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL だそうです。なので、index または ALL を見かけたらすかさずクエリをチューニングすべきです。なお、今回のチケットは、条件によってここがALLになってしまう、というものでした。

Extraフィールド

ドキュメントによれば、Extraフィールドには「MySQLがクエリーを解決する方法」に関する追加情報が含まれます。オプティマイザがどのように判断して、各々のテーブルへのアクセスにおいて何を実行しているのかを知れます。

もし type が ALL だったり index だったりした場合は、クエリのチューニングを検討したほうが良いそうです。

以下は後日編集いたします。

クエリの最適化

EXPLAINがよりよい実行計画を表示する

適切にインデックスを使う
サブクエリをJOINに書き換える
IN の代わりに EXIST を使う

テーブルから全件フェッチしてからアプリケーション側で行を絞り込むというようなロジックを実装してはいけない。必ずSQL文、つまりWHERE句で行の絞り込みができるようにしよう。
クエリを手当たり次第チューニングしていてはいくら時間があっても足りないだろう。問題のあるクエリだけをチューニングするべきであるが、そのようなクエリを見付けるにはスロークエリログや商用のクエリアナライザを用いると効果的である。

EXPLAINと実行計画

https://dev.mysql.com/doc/refman/5.6/ja/explain-output.html

219　行目あたり。

たぶん今できるのはクエリの最適化。 explain をよむ。 https://hacknote.jp/archives/29282/ パフォーマンスを良くしたいいくつか方法がある。スキーマを変える？　クエリを変える？クエリを変えるのはお手軽。（比較的）影響がないし。

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

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

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

まず explain 文で理解する。次に select 文を素で発行して、木を見る。

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

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

クエリを発行した状態なら見れるけど、explain文に対してはみれない。あたりまえといえばそうかもしれないけど。

適切にインデックスを使う

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

測定(Measure)

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

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

メモ

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