はじめに
これまで、SQLは取得できればオッケー！パフォーマンスは悪かったら考えよう
くらいのスタンスで実装してきてしまい、
現在、運用保守を任されパフォーマンスがいかに大事かを痛いほど感じました。
割と痛いです。
パフォーマンスについてもっとちゃんと考えておけばよかった、、ということで
MySQLにおけるSQLチューニングについて備忘も含めてまとめました。
これはトラストアドベントカレンダー12日目の記事です。
そもそもパフォーマンスが悪かったときは何をすればいいの？
チューニングの方法は大きく2つ。
1. DBチューニング(全体最適化)
スループットの向上を目的としたチューニング。
MySQLの設定ファイルやパラメータの最適化によって、パフォーマンス改善を図る。
2. SQLチューニング(個別最適化)
レスポンスタイムの向上を目的としたチューニング。
テーブル構成やクエリの最適化によって、パフォーマンス改善を図る。
⇒ 大半の場合、SQLチューニングで解決することが多い。今回はこちらを紹介。
SQLチューニングをやる前に
NLJ(Nested Loop Join)について
MySQLが実装しているJOINのアルゴリズムは、Nested Loop Join(以下NLJ)。
すごく平たく言うと、JOINしたテーブルの分だけループ処理が動くというもの。
例えば、t1、t2、t3というテーブルをJOINする場合の実行イメージは以下の通り。
1. t1から条件にマッチする行を全てフェッチ
2. t2から条件にマッチする行を全てフェッチ
3. t3から条件にマッチする行を全てフェッチ
JOINするときに大事なこと
・どのインデックスを用いてJOINするか
フェッチする行数が極力最小になるようなインデックスを選択する。
テーブルスキャンは避ける(テーブルサイズが小さい場合はOK)。
・どのテーブルからJOINするか
JOINするテーブルからフェッチする行数が少なければ少ないほど、
ループの回数が少なくなる。
EXPLAINで実行計画を取得する
EXPLAINは、クエリの実行計画(※)に関する情報を取得するためのステートメント。
※ MySQLがクエリーをどのように実行するかの説明
実行計画を取得したいSELECT文の頭に、EXPLAINを付けるだけ。
EXPLAIN SELECT * FROM ...
各結果の見方
EXPLAINを実行すると、このように出力される。
mysql > EXPLAIN select * from sampledb.sampletable ;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | table | ALL | sample_id | NULL | NULL | NULL | 10 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
id
各SELECT文を識別するための通番で、実行順番を表す。
idが同じだった場合、複数のクエリが1つのクエリとして実行されたということ。
select_type
クエリの種類を表す。
| select_typeの結果 | 詳細 |
|---|---|
| SIMPLE | 単一のテーブル ※クエリがシンプルってことじゃない |
| PRIMARY | 外部クエリ |
| SUBQUERY | 相関関係の無いサブクエリ |
| DEPENDENT SUBQUERY | 相関関係のあるサブクエリ |
| UNCACHEABLE SUBQUERY | 実行する度に結果が変わる可能性のあるサブクエリ |
| DERIVED | FROM句で用いられているサブクエリ |
table
アクセス対象のテーブル
JOINを使用している場合、結合順に出力される。
type
レコードアクセスタイプ。重要なフィールド。
MySQLがテーブル内の行を検索する方法。下に行くほど遅い。
const
ユニーク（PRIMARY or UNIQUE）なインデックスのルックアップによるアクセス。
一意検索。いっちゃん速い。
eq_ref
JOINにおいてPRIARY KEYまたはUNIQUE KEYが利用される時のアクセスタイプ。
JOINした場合のconst。理想的なJOINの形。
ref
ユニーク（PRIMARY or UNIQUE）でないインデックスを使って等価検索（WHERE key = value）を行った時に使われるアクセスタイプ。
range
インデックスを用いた範囲検索。
WHERE句にBETWEENまたは不等号を使用したときのアクセス対応。
index
フルインデックススキャン。
インデックス全体をスキャンする必要があるのでとても遅い。
ALL
フルテーブルスキャン。
インデックスがまったく効いていない状態なため、要改善。いっちゃん遅い。
possible_keys
利用可能なインデックスの候補。
インデックスが効いていない場合に参考にすると良い。
key
実際に利用されたキー。
key_len
選択されたキーの長さ。キー長が短いほうが高速。
インデックスをつけるカラムを選ぶ時は意識すると良い。
ref
検索条件でキーと比較されている値やカラムの種類。定数の場合はconst。
JOINの場合、結合する相手側のテーブルで検索条件として利用されているカラム。
rows
フェッチされる行数の見積り。あくまで見積りなので正確な行数ではない。
また、フェッチされた全ての行がそのまま結果として返されるわけではないので注意。
Extra
追加情報といいつつ割と大事な情報。以下は代表的なものの一部。
Using where
テーブルから行をフェッチした後に絞り込みが実行されている。
フェッチする前に極力行数を少なくするべきなので、
WHERE句でインデックスを適切に利用する等何かしらの改良の余地あり。
Using index
カバリングインデックスが使用されている時に出力される。
クエリがインデックスだけを使用して解決できることを表す。
Using temporary
GROUP BY ORDER BY DISTINCTでソートするために一時テーブルが使用されている。
Using filesort
インデックスでのソートではなく、クイックソートが使われている
Range checked for each record (index map: N)
JOINした際、rangeまたはindex_mergeが使われている。
おわりに
クエリを実行した時に、どういった処理が動いて結果が取得されているかがイメージ出来るようになった。
実行イメージしながら実装することで、後々頭を抱えることもなくなるはず。。。