« メッセージキュー事始め with Q4M | メイン | フレンド・タイムライン処理の原理と実践 »
2008年06月09日
MySQL のクエリ最適化における、もうひとつの検証方法
EXPLAIN を使用して MySQL の SQL を最適化するというのは、良く知られた手法だと思います。しかし、EXPLAIN の返す結果が、かならずしもアテになるわけではありません。たとえば、以下のような EXPLAIN を見て、このクエリが最適かどうか、判断ができるでしょうか。私には分かりません。
mysql> EXPLAIN SELECT message.id,message.user_id,message.body FROM message INNER JOIN mailbox ON message.id=mailbox.message_id WHERE mailbox.user_id=2 ORDER BY mailbox.message_id DESC LIMIT 20; +----+-------------+---------+--------+---------------+---------+---------+-------------------------+---------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+--------+---------------+---------+---------+-------------------------+---------+--------------------------+ | 1 | SIMPLE | mailbox | ref | PRIMARY | PRIMARY | 4 | const | 1070015 | Using where; Using index | | 1 | SIMPLE | message | eq_ref | PRIMARY | PRIMARY | 4 | test.mailbox.message_id | 1 | | +----+-------------+---------+--------+---------------+---------+---------+-------------------------+---------+--------------------------+ 2 rows in set (0.00 sec)
こういう場合に役立つのが、MySQL が収集している統計情報を表示する SHOW STATUS 命令です。統計情報の中にある Handler_read_ で始まる一連のプロパティは、MyISAM や InnoDB といったストレージエンジンからデータを読み込むための API が、何回呼び出されたかという情報を保存しています。この値を参照することで、クエリが最適化できているか、正確に判断することができるわけです。以下の例では、2つのテーブルを結合して 20 行取り出すのにあたって、読み込みが約 40 回発生しています。つまり、1テーブルあたり 20 回の読み込みということで、このクエリが最適解である、ということが判断できます。
mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql> SELECT message.id,message.user_id,message.body FROM message INNER JOIN mailbox ON message.id=mailbox.message_id WHERE mailbox.user_id=2 ORDER BY mailbox.message_id DESC LIMIT 20; (中略) 20 rows in set (0.00 sec) mysql> show status like 'handler_read_%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 24 | | Handler_read_next | 0 | | Handler_read_prev | 19 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ 6 rows in set (0.02 sec)
EXPLAIN だけじゃよくわからないよ、という人には、お勧めできる手法だと思います。また、より詳しく解析したい方は、Handler_read_ の各項目の意味について MySQL Internals Custom Engine - MySQL Forge Wiki 等を参考に勉強するといいと思います。
投稿者 kazuho : 2008年06月09日 11:38
トラックバック
このエントリーのトラックバックURL:
http://labs.cybozu.co.jp/cgi-bin/mt-admin/mt-tbp.cgi/1929