MySQLチューニング

以前調べた時のメモ書き程度ですが。。

はじめに

ソフトウェアを動かす時にとにかく遅い、動かない、といった問題にここ最近は遭遇されているので調査。
アプリケーションの待ち時間のほとんどがI/Oにかかる時間なので、そこはどう解決していくか方針レベルでもまとめられたら良いと思い調べていく。
MySQLを多用しているので、一般論としてRDBについて調べていく。
チューニングの目的はどこらへんがボトルネック担っているかのあたりを早くつけて解消につなげることだと思っているので、早く解決案につなげられるような
指針を掲示できればいいと。。

DBチューニング

DBレベルでのチューニング項目について。
パラメータチューニングについては効果が最大でも数倍と効果が薄い。対してSQLチューニングに対してはやりようによっては数100倍の効果をもたらすこともある。
パラメータチューニングは「パラメーターが悪いことが明らかな場合」や「どうしてもクエリーチューニングで辿り着いた以上にレイテンシーを低くする必要がある」場合に行う。
パラメータは相互に関係し合う関係になっているので設定値を変更する場合には2つを一気に変える！みたいなことはしない。（原因が全くわからなくなってしまうため）
主にパラメータチューニングで利用されるのはバッファサイズについて。設定値については後述。

設定値

innodb_buffer_pool_size
InnoDBだけを利用する場合は空きメモリの7〜8割程度を割り当てる最も重要なバッファである。余談だが、実際にはここで割り当てた値の5〜10%ぐらいを多めにメモリを使うので注意が必要。
key_buffer_size
MyISAMだけを利用する場合は、空きメモリの3割程度を割り当てるといい。残りはファイルシステムのキャッシュ用に残しておこう。
sort_buffer_size
ソート処理に利用するバッファである。OLTPでは256K〜1Mぐらいを割り当てると良い。これがあまり大きすぎると、メモリの割り当てのオーバーヘッドが大きくなるので注意しよう。DWH系の処理などで大きなソートが必要な場合、セッションごとに動的に調整すると良い。
read_buffer_size
全件スキャンをするときに利用するバッファ。OLTPでは128K〜512Kぐらいを割り当てると良い。
read_rnd_buffer_size
ソート処理でインデックスを利用する場合に利用するバッファ。OLTPでは256K〜1MぐらいをDWH系の処理などで大きなソートが必要な場合、セッションごとに動的に調整すると良い。

高速なディスクを使用する

メモリサイズで読み書きの量や回数は多少減らせるが、どうしてもというときにはSSDなどの利用でディスクのシーク時間に対しては有効かもしれない。

テーブルの最適化

カラム定義などが要領を食うような設定になっていた場合。特に文字列などが最適なカラムサイズになっているかどうか？などは有効かもしれない。
数値(Int,Bigint)を文字列(varchar)型で格納して場合はよく見かけるパターンらしい。

SQLチューニング

SQLレベルでのチューニング項目について
これは特にINDEXについて、参照したサイトからつけたほうがいいものとつけないほうがいいものについて抜き出し。
あとはEXPLAINという実行計画について着眼点とか諸々。

INDEX はるパターン

テーブル内のデータ量が多く少量のデータを検索する場合
まあそりゃそうだわ
WHERE,ORDERBY,結合の条件に指定されるカラムとして頻繁に利用されている場合
まあそりゃそうだわ
NULL値が多いデータからNULL以外のデータを検索する場合
INDEXはNULLを除外するので早い

INDEX はらないパターン

表の規模が小さい場合
そもそも表の規模が小さいなら早いよねって話
表のほとんどのデータを検索する場合
INDEXはる場合は指定する検索条件に該当するカラムのカーディナリティが高い方がいいんですよね
列の値が頻繁に挿入、更新、削除される場合
INDEXのメンテナンス作業によってコストが生じる

INDEX使われる時

フィールド値を定数と比較するとき (WHERE name = "hogehoge" )
フィールド値全体でJOINするとき ( WHERE a.name = b.name )
フィールド値の範囲を求める時 >,
LIKEで文字列の先頭が固定な時
MIN(), MAX() (複数要素indexの同一first fieldでsecond fieldのmin,max でも有効)
文字列のプレフィックスをもとにしたORDER BY, GROUP BY
WHEREのすべてのフィールドがindexの一部の場合 (DBまったく参照されず)

INDEX使われない時

LIKEがワイルドカードで始まる時
DB全体を読んだ方が早いとMySQLが判断した時
通常はindexはORDER BYには使われない
WHERE と ORDER BYのフィールドが違う時にはどちらかしか使われない

EXPLAIN

MySQLにチューニングで重要なのはクエリとスキーマの最適化であるが、スキーマの変更は1度決めてしまうと中々難しい。
でもSQLだったら書き換えるだけなので比較的影響が少なく実施できる。
遅いクエリが遅延の原因になっているのだったらそこを直せばすむべきなのだからアプリケーションの速度を向上させたい！っていうところだったらSQLのチューニングを第一に検討するべきである。
EXPLAINを見るには少々コツがいる。SQLは内部的にはツリー状に実行されているのにもかかわらず実行計画を表示した場合は表形式で出力されるためである。

EXPLAIN表示項目と概要説明

id/select type クエリの種類について表している。この2つはセットにして考える。

joinの場合

select_typeはSIMPLEと表示される。如何に複雑なJOINであってもCOMPLEXとはならずにSIMPLEなのである。従って「これはシンプルなクエリを示すのだ」などと誤解をしてはならない。SIMPLEではidが全て同じ値になる。これはそのクエリが一つのNLJで処理されることを示すからである。NLJではどのテーブルから処理するのかということが最も重要になるが、EXPLAINの出力の順序がどのテーブルから処理するかということを反映している。

サブクエリの場合

サブクエリが絡むと次のselect_typeには次の5種類のうちいずれかが表示される。
PRIMARY・・・外部クエリを示す。
SUBQUERY・・・相関関係のないサブクエリ。
DEPENDENT SUBQUERY・・・相関関係のあるサブクエリ。
UNCACHEABLE SUBQUERY・・・実行する度に結果が変わる可能性のあるサブクエリ。
DERIVED・・・FROM句で用いられているサブクエリ。

unionの場合

クエリにUNIONが含まれる場合、次の5種類のいずれかがselect_typeに表示される。
PRIMARY・・・UNIONにおいて最初にフェッチされるテーブル
UNION・・・2番目以降にフェッチされるテーブル
UNION RESULT・・・UNIONの実行結果
DEPENDENT UNION・・・DEPENDENT SUBQUERYがUNIONになっている場合
UNCACHEABLE UNION・・・UNCACHEABLE SUBQUERYがUNIONになっている場合

table
アクセスする多少のテーブルが出力される1行につき1テーブルが表示される。
type
レコードアクセスタイプと呼ばれる。対象のテーブルに対してどのような方法でアクセスするかを表している。
致命的なクエリに関してはこのレコードを見ることによって一目瞭然。
- const・・・PRIMARY KEYまたはUNIQUEインデックスのルックアップによるアクセス。最速。
- eq_ref・・・JOINにおいてPRIARY KEYまたはUNIQUE KEYが利用される時のアクセスタイプ。constと似ているがJOINで用いられるところが違う。
- ref・・・ユニーク（PRIMARY or UNIQUE）でないインデックスを使って等価検索（WHERE key = value）を行った時に使われるアクセスタイプ。
- range・・・インデックスを用いた範囲検索。
- index・・・フルインデックススキャン。インデックス全体をスキャンする必要があるのでとても遅い。
- ALL・・・フルテーブルスキャン。インデックスがまったく利用されていないことを示す。OLTP系の処理では改善必須。
possible_keys
オプティマイザがテーブルのアクセスに利用可能なインデックスの候補として挙げたキーの一覧。
key
オプティマイザによって選択されたキー。
key_len
選択されたキーの長さ。インデックスの走査は、キー長が短い方が高速である。インデックスをつけるカラムを選ぶ時にはそのことを念頭に置いて欲しい。
ref
検索条件で、keyと比較されている値やカラムの種類。定数が指定されている場合はconstと表示される。JOINが実行されている時には、結合する相手側のテーブルで検索条件として利用されているカラムが表示される。
rows
そのテーブルからフェッチされる行数の見積もりである。このフィールドはあくまでもテーブル全体の行数やインデックスの分散具合から導き出された大まかな見積もりなので、実際にフェッチされる正確な行数ではないので注意が必要されたい。
Extra
そのクエリを実行するためにオプティマイザがどのような戦略を選択したかということを示すフィールドである
- Using where・・・頻繁に出力される追加情報である。WHERE句に検索条件が指定されており、なおかつインデックスを見ただけではWHERE句の条件を全て適用することが出来ない場合に表示される。
- Using index・・・クエリがインデックスだけを用いて解決できることを示す。Covering Indexを利用している場合などに表示される。
- Using filesort・・・filesort（クイックソート）でソートを行っていることを示す。Using filesortについては先日詳しく説明したので参照されたい。
- Using temporary・・・JOINの結果をソートしたり、DISTINCTによる重複の排除を行う場合など、クエリの実行にテンポラリテーブルが必要なことを示す。
- Using index for group-by・・・MIN()/MAX()がGROUP BY句と併用されているとき、クエリがインデックスだけを用いて解決できることを示す。
- Range checked for each record (index map: N)・・・JOINにおいてrangeまたはindex_mergeが利用される場合に表示される。
- Not exists・・・LEFT JOINにおいて、左側のテーブルからフェッチされた行にマッチする行が右側のテーブルに存在しない場合、右側のテーブルはNULLとなるが、右側のテーブルがNOT NULLとして定義されたフィールドでJOINされている場合にはマッチしない行を探せば良い・・・ということを示す。

ORDER BY使用時に
using filesort
using temporary
は良くない（改善する必要あり)
→記事では複合キーで対応してExtraから削除している

実際には

EXPLAINコマンドを使ってクエリの実行計画を見る際には、次のようなステップを踏む。
1. id/select_type/tableフィールドを見て、どのテーブルがどの順序でアクセスされるのかを知る。これらはクエリの構造を示すフィールドであると言える。サブクエリが含まれている場合にはEXPLAINの表示順とアクセスされる順序が異なる場合があるので気をつける必要がある。
2. type/key/ref/rowsフィールドを見て、各テーブルから行がどのようにフェッチされるのかを知る。どのテーブルへのアクセスが最も重いか（クエリの性能の足を引っ張っているのか）を、これらのフィールドから判断することが出来る。
3. Extraフィールドを見て、オプティマイザがどのように判断して、各々のテーブルへのアクセスにおいて何を実行しているのかを知る。Extraフィールドはオプティマイザの挙動を示すものであり、クエリの全体像を把握するのに役立つ。

どうクエリを改善していくかは教えてくれないが
テーブルからフェッチされる行を減らすことが重要であり、EXPLAINコマンドを使用しながら試行錯誤していくしかない。

参考

http://qiita.com/katsukii/items/3409e3c3c96580d37c2b
http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
http://tech.aainc.co.jp/archives/4634

MySQL チューニング