MySQLのクエリチューニングといえば、何はともあれexplainを使った分析です。

(1) Slow Query Logを仕掛ける

(2) explainで分析する

(3) インデックスを張ったりクエリを最適化したり…

この作業で、filesortがかかっていたり、期待したインデックスが使用されていないことによるパフォーマンス不足は発見、改善できます。

今回は更に、「SHOW STATUS」コマンドを使ったパフォーマンスチェックを検討してみたいと思います。
 

「SHOW STATUS」は、MySQLサーバの各種統計情報を見るコマンドです。MySQLが一定時間稼働している間に、どんなことが実行されたか、記録を確認することができます。

MySQL リファレンスマニュアル
 http://dev.mysql.com/doc/refman/5.1/ja/show-status.html

設定情報の確認コマンド「SHOW VARIABLES」と、使い勝手は同じです。

正確には、２種類あります。

SHOW GLOBAL STATUS：MySQLを起動してから今までの総計
SHOW SESSION STATUS：「FLUSH STATUS」を実行してから今までの統計
 

「GLOBAL STATUS」の方は、一定期間、例えば週次バッチ処理でログを取り貯め、各指標の増加具合を比較確認することで、全体的な傾向を把握するのに向いています。

「SESSION STATUS」の方は、特定クエリの検証に向いています。「FLUSH STATUS」で初期化し、特定クエリを実行して数値を見て、また「FLUSH STATUS」して…という風に使います。

SHOW STATUSで着目したい主な変数は次の通り。
Explainよりも根深い、クエリの構造上の問題点などが浮き彫りになるのが分かると思います。
特に、JOINの仕方に問題があるかどうかの判断に役立つケースが多いです。

Select_full_join

フルJOIN（クロスJOIN）が発生した回数。最悪。恐らくJOINの条件式が間違っている。

Select_full_range_join
片方のテーブルAで全件、もう片方のテーブルBで範囲検索を行ってJOINした回数。
これもJOINの条件式が間違っているのでは？テーブルAの抽出条件を書き忘れているのか、忘れていないなら再考で改善するチャンスかも知れません。

Select_scan
全件スキャンが発生した回数。JOINしているなら、先頭のテーブルが全件スキャンされています。

Handler_read_first

フルインデックススキャンされた回数。インデックスが有効に使用されていない。
せっかくインデックスを張ったカラムをSELECTしているのに、そのカラムを抽出条件に指定していないので、結局全レコードを見に行っている場合などにこの項目がカウントアップされます。
インデックスとデータ、両方を総なめすることになるので、余計に時間がかかります。
 

Qcache_***
クエリキャッシュ関連の履歴。Qcache_insertsがキャッシュ作成数で、Qcache_hitsがそれを使った回数。
Qcache_hitsが伸びていない場合、キャッシュがうまく使えていないということです。
致し方ないのであれば受け入れるしかないですが、クエリを分割することでキャッシュ使用率がアップし、全体の実行速度が上がることもあるので検討してみては。

Created_tmp_disk_tables
ディスク上に生成されたテンポラリテーブルの数。
複雑なクエリをMySQLに実行させると、ソートや重複排除のためにテンポラリテーブルが自動的に作成されます。
テンポラリテーブルが頻繁に生成されれば実行速度は伸びないので、意図せずこの値が伸びているようならクエリの見直しを行いましょう。