MySQLパフォーマンスチューニングのためのクエリの基礎知識

前回書いたMySQLパフォーマンスチューニングのためのインデックスの基礎知識に引き続き、MySQLのパフォーマンスチューニングについて学んだことをまとめ。
MySQLを使っていると、クエリが遅い理由をつきとめる必要が出てくる。
どうやって遅いクエリをつきとめ、改善すればよいかについて学んだのでまとめた。
下記のような基礎知識があればパフォーマンスチューニングをうまくやれる、と思う。

クエリ処理の基礎

MySQLがクエリを処理する手順

まずはMySQLがクエリを処理する手順を知っておく必要がある。
処理は以下のような流れで進む。

クエリキャッシュの中からクエリの結果を探す。見つかればそれを返す。
クエリを解析して構成要素に分解する。
- クエリの構文が正しいことを確認
- クエリについて基本情報を収集する。
クエリを基本的な要素に分解した後、何を実行すべきかを判断する。
クエリオプティマイザが動き始め、最も効率的なクエリの実行方法を見つけ出す。
クエリを実行する。

クエリキャッシュ

MySQLは、SELECTクエリを実際に分析または実行する前に、クエリキャッシュの中からクエリの結果を見つけようと試みる。MySQLはクエリにハッシュ関数を実行し、キャッシュの中からハッシュ値に該当する結果をまず調べる。キャッシュの中でクエリのハッシュ値が見つかれば、MySQLは期待通りに余分な労力を回避し、キャッシュから結果を取り出してクライアントに送り返す。

クエリキャッシュを使うには、my.cnfでquery_cache_typeに適切な値を与え、query_cache_sizeを設定する。

query_cache_typeについて

query_cache_typeの値	挙動
0またはOFF	キャッシュを行わない。またはキャッシュした結果の読み出しを行わない。
1またはON	SELECT SQL _NO_CACHEで始まるステートメント以外のキャッシュになる。
2またはDEMAND	SELECT SQL_CACHEで始まるステートメントのみのキャッシュになる。

特定のクエリをキャッシュに入れないようにするためにMySQLに指示することができる。これは、キャッシュの汚染を防止するのに有効。例えば、一日に一度しか実行しないようなクエリの場合、そのクエリをキャッシュに入れても意味はない。そのクエリをキャッシュに入れないようにすれば、一日に何度も繰り返すキャッシュのために容量を確保できる。

SELECT SQL_NO_CACHE * FROM mytable

query_cache_sizeについて

query_cache_size の値がゼロではない場合は、ストラクチャのアロケートにおよそ 40 KB を必要とするため、クエリキャッシュのサイズを最低 40 KB でセットする必要がある。
正確なサイズは、システムのアーキテクチャによる。サイズが小さすぎると、警告がでる。

参照
- http://dev.mysql.com/doc/refman/5.1/ja/query-cache-configuration.html

構文解析と分析の最適化

MySQLは、キャッシュに入っていないクエリに対して目的の処理を実行する前に、クエリを解析して構成要素に分解する。
クエリの構文が正しいかを確認し、以下の基本情報を収集する。
- どのような種類のクエリか。SELECT、INSERT、UPDATE、DELETEのどれか。あるいはSETやGRANTなどの管理コマンドか。
- どのようなテーブルが関係するか。エイリアスを使用するか。
- どのようなWHERE句を使用するか。
- 他にヒントや修飾子が関係するか

要素分解した後、何を実行すべきかを判断する。そしてクエリオプティマイザにより、取得した情報を元に最も効率的なクエリの実行方法(調査すべきレコードの数を限定できる方法)を見つけ出す。
これは、ディスクI/Oに要する時間が、クエリの所要時間を支配する要因になることが多いためそうしている。
※インデックスが役に立つ理由も、この原理で説明できる。

MySQLは適切な判断を下すため、以下のような質問の答えを見つけ出そうとする。
- 行を迅速に見つけ出すために、適切な候補となるインデックスが存在するか
- どのインデックスが最も適切か。複数のテーブルが関係する場合、各テーブルについて、どのインデックスが最も適切か
- テーブルを結合している場合、どのテーブルがどのテーブルに依存するか
- テーブルにとって、どの結合順序が最も適切か

MySQLはすべての選択肢を実際にテストせず、非常に迅速に判断を下す。
- そうしないと実際にクエリを実行する時間よりも、実行方法を判断するのに時間がかかってしまうため。
MySQLがSELECTクエリをどのように扱うかを知るには、そのクエリに対するEXPLAINの出力を見るとよい。

EXPLAINによる調査方法

テーブル構造は、describeコマンドで表示できる。

describe Hoge;

MySQLの動作を調べるには、EXPLAINコマンドを使用する。
ここで得られた情報を元に、インデックスを追加したり、クエリを書き直すことにより、性能を大幅に改善できることがある。

EXPLAIN SELECT questionnaire_records where id=1;

すると、下記のようなデータが得られる。

+--+-----------+---------------------+-----+-------------+-------+-------+-----+----+-----+
|id|select_type|table                |type |possible_keys|key    |key_len|ref  |rows|Extra|
+--+-----------+---------------------+-----+-------------+-------+-------+-----+----+-----+
| 1|SIMPLE     |questionnaire_records|const|PRIMARY      |PRIMARY|      4|const|   1|     |
+--+-----------+---------------------+-----+-------------+-------+-------+-----+----+-----+

各項目の意味は以下。

カラム名	意味
id	クエリにおけるテーブルのID。EXPLAINは、クエリの各テーブルごとに１つのレコードを出力する。
select_type	より大きなクエリの中で、このテーブルがどのような役割をはたすか。 SIMPLE, PRIMARY, UNION, DEPENDENT UNION, SUBSELECT, DERIVEDがある。
table	MySQLがレコードを読み出すテーブルの名前
type	MySQLは、どのような型の結合を使用するか。あり得る値は、const, system, eq_ref, ref, range, index, ALL。上記の例では、クエリで定数を使用しているので、typeはconstとなっている。検索条件に値の範囲を指定するとtypeがrangeになる。
possible_keys	MySQLがテーブルから行を見つけ出すために使用できるインデックスのリスト。ない場合はNULL。これがないと、使用できるインデックスがまったく存在しなかったことを意味する。複数のインデックスがある場合、MySQLは調査すべき行数の少ないインデックスを選択しようとする。
key	possible_keysの中から、MySQLが使用すべきだと判断したインデックスの名前。ここがNULLだとMySQLがインデックスを使用していないということ。
key_len	キーの値のサイズ(バイト数)
ref	キーを照合するときに使用するカラムまたは値
rows	クエリを満たすかどうかを調査する必要があるとMySQLが推定した行の数。レコードの追加や削除を頻繁に行うと、推定がずれる。そういうとき、ANALYZE TABLEを実行すれば、MySQLはインデックス統計情報を更新し、推定精度を改善できる。
Extra	このクエリの実行についてMySQLが伝えたい追加情報。 Using whereは、選択するレコードをWHERE句で制限したことを意味する。 Using indexだと、インデックスだけのクエリを実行したことを意味する。

同じカラムに対して複数のレコードを取り出す場合、ORとIN()のどちらを使うべきか。数が増えるに連れ、IN()を使用した方が明らかにクエリ文字列は小さくなる。クエリ文字列が小さければ、構文解析のオーバーヘッドが少なくなり、性能の改善につながる。

クエリの実行
- MySQLはクエリの実行中に以下のような情報を収集する
  - クエリを実行したユーザ
  - 処理にかかった時間
  - 返した行数

※ログの使用を設定している場合、クエリ時間がサーバの設定値を超えると、サーバは上記の情報をスロークエリログに書き込む。

オプティマイザの機能と特徴

MySQLはインデックスを使用したほうが効率が向上すると十分に確信できる場合にのみインデックスを使用する。
これは、使用すべきところを使用すべきでないと誤って判断してしまうことがあることを意味する。
MySQLの判断は以下のようになされる。
- 一定の上限を超えると、行を順番に読み出すよりも、インデックスを使用して行の場所を見つけるほうが遅くなる。
- MySQLの場合、分岐点はおよそ30%と予想される。
- 同じクエリを発行してもデータ数によって使われるインデックスが異なる。
したがって、テストするときは、必ず現実的なデータを使用する必要がある。

遅いクエリを特定する方法

遅いクエリを突き止めるためのもっとも簡単な方法は、MySQLに実際にクエリの処理を行わせ、スロークエリログを出させること。
- スロークエリログとは、一定の秒数よりも長い時間がかかったクエリをログに記録することができる。MySQLは、クエリに加えて、ほかのメタデータ(データに関する情報を記述したデータ)もログに記録する。
- ただし、本当の原因がそのクエリである保障はない。そのときたまたま遅く、次にやってみると速い場合もある。その原因は以下のようなものが考えられる。
  - テーブルがロックされていたので、クエリが待機した。Lock_timeはロックが解除されるまで待機していた時間を示す。
  - データもインデックスも、まだメモリ内のキャッシュに入っていなかった。MySQLを初めて起動した場合や、適切なチューニングを施していない場合、起こりやすい。
  - たまたまバックアップなどが動いていたので、あらゆるディスクI/Oが大幅に遅くなっていた
  - 同じ時間に、サーバがほかの何百もの無関係なクエリを処理していたので、効率的に作業を実行できるだけのCPUパワーが足りなかった。
- ログに同じクエリが何度も現れていれば、そのクエリが遅い可能性は高い。

遅いクエリを改善する方法

一般的な方法

無駄なクエリを減らす
無駄な結合を減らす/結合させるべきものは結合させる
インデックスを適切に貼る
クエリキャッシュをうまく利用する

特殊な方法

結合順序を指定する。
- EXPLAINクエリを調べて、もしもMySQLが実行した結合よりも最適な結合順序を見つけたとしたら、カンマまたはJOINキーワードの代わりにSTRAIGHT_JOINを使用すればいい。

SELECT * FROM table1 STRAIGHT_JOIN table2 WHERE ...

インデックスの使用
- MySQLに検討してほしいインデックスのリストを提供し、ほかのインデックスをすべて無視させるには、テーブル名の後にUSE INDEXを追加すればいい。

SELECT * FROM mytable USE INDEX (name, address) ...

- MySQLにインデックスを無視させたい場合は、IGNORE INDEXを使用する。

SELECT * FROM mytable IGNORE INDEX (priority) ...

- 特定のインデックスを使用することをMySQLに強制するには、FORCE INDEXを使用する

SELECT * FROM mytable FORCE INDEX (name) ...

クエリキャッシュ
- 頻繁に実行するSELECTクエリの結果をメモリ内のクエリキャッシュに格納しておけば、その結果を迅速に取り出すことができる。
- SQL_CACHE
  - query_cache_typeを1にしたとき、すべてのSELECTクエリがデフォルトでキャッシュに入るので、このヒントは使用しない。
  - query_cache_typeを2に設定した場合、キャッシュは使用可能になるが、要求したクエリのみがキャッシュに入る。
- SQL_NO_CACHE
  - クエリの結果をキャッシュに入れないようにMySQLに依頼する。
  - query_cache_typeが1の場合でも2の場合でも作用する。

実際やってみた

表示させるのに10秒近くかかっていたページを、1秒で表示させることができた。
まずはどこが遅いかをスロークエリログなどを利用して突き止め、そのクエリが遅い理由をEXPLAINを使ったり、クエリを見て考えた。
なぜ遅いか仮説を立て、それを解消するようにコードを変更した。ちなみに、主な原因は無駄な情報の取得(結合しなくていいテーブルまで結合していた)があったことと、インデックスのはりかたが不適切なことだった。
原因を追求して問題を見つけて解決するって作業は面白いなぁと思った。