はじめに

これまで、SQLは取得できればオッケー！パフォーマンスは悪かったら考えよう
くらいのスタンスで実装してきてしまい、
現在、運用保守を任されパフォーマンスがいかに大事かを痛いほど感じました。
割と痛いです。

パフォーマンスについてもっとちゃんと考えておけばよかった、、ということで
MySQLにおけるSQLチューニングについて備忘も含めてまとめました。

そもそもパフォーマンスが悪かったときは何をすればいいの？

　チューニングの方法は大きく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が使われている。

おわりに

クエリを実行した時に、どういった処理が動いて結果が取得されているかがイメージ出来るようになった。
実行イメージしながら実装することで、後々頭を抱えることもなくなるはず。。。