はじめに
- 業務をする上で良く忘れる場合があるため、まとまったメモとして残してます
- 周囲に教える際の参考資料のためでもあります
- 前提条件など結構すっ飛ばしております
スロークエリとは
- 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されている場合にはマッチしない行を探せば良い・・・ということを示す |