今すぐ使えるMySQLのコマンド集 - スロークエリの調査

はじめに

• 業務をする上で良く忘れる場合があるため、まとまったメモとして残してます
• 周囲に教える際の参考資料のためでもあります
• 前提条件など結構すっ飛ばしております

スロークエリとは

• SQLを実行する際に予めMySQLにて設定された実行結果までのタイムを越えたクエリ
• MySQLの設定でログとして出力することができる

mysql-slow.log

# Time: 110723 13:22:02
# User@Host: root[root] @ localhost []
# Query_time: 7.677186  Lock_time: 0.156390 Rows_sent: 1  Rows_examined: 1386155
use hoge_db;
SET timestamp=1311394922;
SELECT count(*) FROM `hoge_table` WHERE (hoge_time between '2011-07-19 18:00:00' AND '2011-07-19 19:00:00') AND (hoge_id = 1) AND (hage_id = '1');

• 1行目 記録日時
• 2行目 ユーザーIDとリクエストした端末
• 3行目 Query_time（実行時間）　Lock_time(ロック時間)　Rows_sent（送信行数）
  Rows_examined（処理対象となった行数）
• 4行目 SQL文

EXPLAINとは

• スロークエリを最適化する際に、どのように最適化すれば良いかをMySQLが教えてくれるコマンド
• 主に見るべきところとしてはtype、rows、

mysql> EXPLAIN SELECT * FROM hoge;
+----+-------------+----------+------+---------------+------+---------+------+-------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------+
|  1 | SIMPLE      | hoge  | ALL  | NULL          | NULL | NULL    | NULL | 46497 |       |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------+

type

|#|ステータス名|説明|
|:---|:---|:---|:---|:---|
|1|system|テーブルに1レコードしかない状態|
|2|const|マッチするレコードが1件しかない状態。PRIMARY KEYまたはUNIQUEインデックスを利用して検索される|
|3|eq_ref|JOINにおいてPRIMARY KEYまたはUNIQUEインデックスを利用して1つのレコードを検索している状態|
|4|index|すべてのレコードのインデックスをスキャンする必要がある状態(フルインデックススキャン)|
|5|ALL|すべてのレコードを走査する必要がある状態(フルテーブルスキャン)|

最適化ポイント

• const、eq_ref、rengeは、インデックスを有効に利用できている
• indexはインデックスをフルスキャンしているので遅い。インデックスの見直しが必要
• ALLの場合はテーブルフルスキャン。インデックスもしくはテーブル構成の見直しが必要

row

• クエリによって取得が想定される行数
• 実際に取得された数ではないところに注意が必要

Extra

|#|ステータス名|説明|
|:---|:---|:---|:---|:---|
|1|Using where|WHERE句に検索条件が指定されており、なおかつインデックスを見ただけではWHERE句の条件を全て適用することが出来ない場合に表示される|
|2|Using index|クエリがインデックスだけを用いて解決できることを示す。Covering Indexを利用している場合に表示される|
|3|Using filesort|メモリ上のバッファでソート処理ができず、テンポラリファイルが作成されてソート処理がされている場合に出力される。SQL文にORDER BY句を指定している場合に表示される|
|4|Using temporary|クエリの実行にテンポラリテーブルが必要な場合に表示される。SQL文がJOINかつORDER BY句を利用している場合やGROUP BY句とORDER BY句で指定しているカラムが異なる場合などで発生することがある|
|5|Using index for group-by|MIN()/MAX()がGROUP BY句と併用されているとき、クエリがインデックスだけを用いて解決できることを示す|
|6|Range checked for each record (index map: N)|JOINにおいてrangeまたはindex_mergeが利用される場合に表示される|
|7|Not exists|LEFT JOINにおいて、左側のテーブルからフェッチされた行にマッチする行が右側のテーブルに存在しない場合、右側のテーブルはNULLとなるが、右側のテーブルがNOT NULLとして定義されたフィールドでJOINされている場合にはマッチしない行を探せば良い・・・ということを示す|

全ページリンク

• 今すぐ使えるMySQLのコマンド集 - スロークエリの調査
• 今すぐ使えるMySQLのコマンド集 - INDEXの作成