技術者派遣の技術日誌ブログ

MySQLのチューニングで最も大切なのは、クエリとスキーマの最適化である。スキーマの設計は一度決めてしまうとそのテーブルを利用する全てのクエリに影響してしまうためなかなか変更することは出来ないが、クエリはそのクエリだけを書き直せば良いので変更の敷居は低い。そして遅いクエリをなくすことは、性能を大幅に向上させるための最も有効な手段である。従って、アプリケーションの性能を向上させたいなら、まず最初にクエリのチューニングを検討するべきなのである。

最適化するべきクエリはスロークエリログやクエリアナライザで見付けられるが、ではそのようなクエリが見つかった場合にはどのように最適化すればいいのか？そのためにはまず現在どのようにクエリが実行されているかということを知らなくてはならない。つまり、EXPLAIN SELECT …の出力を理解することが初めの第一歩となる。クエリを最適化するということは、

• 書き換える前と後でクエリの実行結果が同じになる
• EXPLAINがよりよい実行計画を表示する

ということであると言える。クエリを書き換えてはEXPLAINを実行して、結果が思わしくなければまた書き換えて・・・ということ繰り返して最適化するわけである。EXPLAINの出力を理解し、どのようなクエリならMySQLが高速に実行出来るかということを知っていればこの作業は比較的簡単である。今日は前者、つまりEXPLAINの出力が何を意味するのか？ということについて解説しよう。

では以下に順を追って各フィールドの詳細を説明する。

id/select_type

MySQLのEXPLAINの見方には少々コツが要る。クエリの実行計画は内部的にはツリーで表現されているが、EXPLAINの出力は表になっているからである。つまり、EXPLAINを見ることによってツリー構造を理解しないといけないわけで、これが第一の関門となる。そのツリー構造を理解すれば、各テーブルがどのようにアクセスされるかということが容易に理解出来るだろう。

idとselect_typeはEXPLAINの最初の2つのフィールドであるが、これらはセットにして考えると良い。select_typeはクエリの種類を表すものであり、ズバリツリーの構造にそのまま反映される。クエリの種類とはJOIN、サブクエリ、UNIONおよびそれらの組み合わせで、select_typeの内容もその組み合わせから導き出されたものなのである。

JOINの場合

MySQLが実行出来るJOINの種類はNested Look Join（NLJ）の一種類しかない。NLJとは例えばA、B、Cという3つのテーブルをJOINする際、最初にテーブルAから条件にマッチする行を全てフェッチして、次にBから条件にマッチする行をフェッチしてJOINし、次にCから条件にマッチする行をフェッチしてJOINする・・・というように、テーブルを一つずつ順に処理していく方式である。MySQL 6.0ではBKA JOINというのが追加されるが、これもNLJの発展系である。（JOINの方式にはソートマージやHASH JOINなどがあるが、MySQLには実装されていない。）

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

サブクエリの場合

サブクエリが絡むと次のselect_typeには次の5種類のうちいずれかが表示される。

• PRIMARY・・・外部クエリを示す。
• SUBQUERY・・・相関関係のないサブクエリ。
• DEPENDENT SUBQUERY・・・相関関係のあるサブクエリ。
• UNCACHEABLE SUBQUERY・・・実行する度に結果が変わる可能性のあるサブクエリ。
• DERIVED・・・FROM句で用いられているサブクエリ。

サブクエリの場合は実行順序に気をつける必要がある。DERIVEDの場合、サブクエリ→外部クエリの順番でクエリが実行される。例えば次のような場合はCityテーブルから最初に行がフェッチされてテーブルとなり、その次にCountryテーブルとのJOINが実行される。

mysql> EXPLAIN SELECT * FROM Country, (SELECT * FROM City WHERE Population > 1000000) AS C1 WHERE Country.Code = C1.CountryCode;
+----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref            | rows | Extra       |
+----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
|  1 | PRIMARY     |  | ALL    | NULL          | NULL    | NULL    | NULL           |  237 |             |
|  1 | PRIMARY     | Country    | eq_ref | PRIMARY       | PRIMARY | 3       | C1.CountryCode |    1 |             |
|  2 | DERIVED     | City       | ALL    | NULL          | NULL    | NULL    | NULL           | 4079 | Using where |
+----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
3 rows in set (0.00 sec)

それ以外の場合は外部クエリ→サブクエリの順番でクエリが実行される。ただしSUBQUERYの場合はサブクエリが本当に実行されるのは最初の一回だけで、それ以降はキャッシュされた実行結果が利用される。DEPENDENT SUBQUERYおよびUNCACHEABLE SUBQUERYの場合はサブクエリが行の評価の度に実行されることになる。

サブクエリの場合、外部クエリとサブクエリでは別々のidがつけられる。

UNIONの場合

クエリにUNIONが含まれる場合、次の5種類のいずれかがselect_typeに表示される。

• PRIMARY・・・UNIONにおいて最初にフェッチされるテーブル
• UNION・・・2番目以降にフェッチされるテーブル
• UNION RESULT・・・UNIONの実行結果
• DEPENDENT UNION・・・DEPENDENT SUBQUERYがUNIONになっている場合
• UNCACHEABLE UNION・・・UNCACHEABLE SUBQUERYがUNIONになっている場合

UNIONは前から順番に処理されていくだけなので、テーブルが処理される順序という観点ではわかり易いと言えるだろう。

select_typeについては次のページにまとめてあるので参考にして欲しい。
http://www.mysqlpracticewiki.com/index.php/Select_type

table

アクセスする対象のテーブル。読んで字のごとくである。

type

select_typeの次に意識しなければいけないのは、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系の処理では改善必須。

indexまたはALLを見かけたらすかさずクエリをチューニングしよう。

typeフィールドの詳細については次のページにまとめてあるので参照して欲しい。
http://www.mysqlpracticewiki.com/index.php/Record_access_types

possible_keys

オプティマイザがテーブルのアクセスに利用可能なインデックスの候補として挙げたキーの一覧。

key

オプティマイザによって選択されたキー。

key_len

選択されたキーの長さ。インデックスの走査は、キー長が短い方が高速である。インデックスをつけるカラムを選ぶ時にはそのことを念頭に置いて欲しい。

ref

検索条件で、keyと比較されている値やカラムの種類。定数が指定されている場合はconstと表示される。JOINが実行されている時には、結合する相手側のテーブルで検索条件として利用されているカラムが表示される。例えば次の例では、CountryテーブルはCityテーブルとCity.CountryCodeカラムでJOINされるということを示している。。

mysql> EXPLAIN SELECT * FROM Country,City WHERE Country.Code=City.CountryCode AND Country.Name LIKE 'A%';
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------------+
| id | select_type | table   | type   | possible_keys | key     | key_len | ref                    | rows | Extra       |
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------------+
|  1 | SIMPLE      | City    | ALL    | NULL          | NULL    | NULL    | NULL                   | 4079 |             |
|  1 | SIMPLE      | Country | eq_ref | PRIMARY       | PRIMARY | 3       | world.City.CountryCode |    1 | Using where |
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------------+
2 rows in set (0.00 sec)

rows

そのテーブルからフェッチされる行数の見積もりである。このフィールドはあくまでもテーブル全体の行数やインデックスの分散具合から導き出された大まかな見積もりなので、実際にフェッチされる正確な行数ではないので注意が必要されたい。ただし一つだけ例外がある。DERIVEDテーブルである。DERIVEDテーブルは実際に実行してみないと行数の見積もりができないので、オプティマイザはEXPLAINの際にもサブクエリを実行する。そのため、DERIVEDテーブルだけは常に正確な行数を見積もることができるわけである。ただし、DERIVEDテーブルを扱う時は、サブクエリが最適化されていない場合はEXPLAINにも時間が掛かってしまうため注意が必要だ。

また、フェッチされた全ての行がそのまま結果として返されるわけではないという点にも注意したい。後述するUsing whereがExtraフィールドに表示されている場合は、フェッチした行に対してさらにWHERE句の検索条件が適用されて行の絞り込みが行われるので、クライアントへ返される結果行は少なくなる可能性がある。

JOINを処理する場合、WHERE句により行の絞り込みがなければ最終的な結果行数の見積もりはJOINする全てのテーブルのrowsフィールドの積として考えることが出来る。PRIMARY KEYやUNIQUEインデックスを利用してJOINする場合、つまりレコードアクセスタイプがeq_refの場合rowsフィールドは1になる。上記、refフィールドの説明において用いた例がそうである。eq_refは理想的なJOINの形式であると言える。しかしレコードアクセスタイプがeq_ref以外の場合、例えばrefやrangeになってしまっている場合には前のテーブルに対して多数のテーブルがJOINされてしまうことになるので注意が必要である。

Extra

さて、今日の記事を締めくくるのはExtraフィールドだ。多くの人にとって、Extraフィールドはいろいろなコメントが表示される摩訶不思議なフィールドぐらいではないかと思う。しかし、Extraフィールドはその名前（Extra＝追加情報）とは裏腹に、実はとても重要なフィールドなのである。

Extraフィールドは、そのクエリを実行するためにオプティマイザがどのような戦略を選択したかということを示すフィールドである。言ってみればオプティマイザの独り言のようなものであるが、Extraフィールドを見ればそのテーブルに対して何を行っているのかが如実に分かってしまうので、Extraフィールドを理解して初めてオプティマイザの挙動を理解したと言える。Extraフィールドを注意深く吟味することで、それぞれのテーブルがなぜこの順序でアクセスされているか？ということに対する理解に繋がるので、EXPLAINの出力結果全体を見通す際に役に立つだろう。

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されている場合にはマッチしない行を探せば良い・・・ということを示す。

Extraフィールドにはまだまだバリエーションが存在するが、下記のページにまとめてあるので参考にして欲しい。MySQL 6.0で追加される予定のものについては書きかけだけど気にしてはいけない。
http://www.mysqlpracticewiki.com/index.php/Extra_field

注意点

EXPLAINを実行する場合、実際のデータを利用することが非常に大切である。なぜなら、テーブルの行数やインデックスの分散具合によって実行計画に違いが生じるからである。本番ではテーブルに100万行のデータが存在するのに、100行しかないテストテーブルを使ってクエリの最適化を行ってもあまり意味がない。

テーブルに対して大量のデータ追加、削除、変更などを行った場合、インデックスの統計情報が正確ではなくなる場合がある。そのような場合、ANALYZE TABLEコマンドを利用すると良いだろう。ANALYZE TABLEはテーブルの統計情報を更新してくれるコマンドである。

クエリを書き換えた場合にはEXPLAINの出力を見るだけでなく、正しい結果が得られるかどうかを確認しよう。いくら高速なクエリでも、正しい結果が得られなければ意味がない。

まとめ

EXPLAINコマンドの各フィールドの詳細を説明したが、実際にEXPLAINコマンドを使ってクエリの実行計画を見る際には次のようなステップを踏むといいだろう。

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

EXPLAINコマンドはクエリの実行計画を知るためのツールである。どのようにクエリを書き換えれば良いか？というのはまた別の問題であり、EXPLAINコマンドはどのようにクエリを書き換えればいいかについては何も教えてくれない。しかし、ただ一つ言えることはクエリにおいてテーブルからフェッチされる行数を減らすことが重要だと言うことである。

同じ結果が得られるなら、ひとつのクエリにおいてテーブルからフェッチされる行数が減れば減るほど良い。なぜなら、ストレージエンジンの性能には上限があるからである。例えば、あるストレージエンジンが1秒間に100万行のフェッチが出来る性能を持っているとすると、一つのクエリが平均1000行のフェッチを行うならば1秒間に最大1000クエリ、一つのクエリが平均100行のフェッチを行うならば1秒間に最大1万クエリが可能であるという計算になる。ひとつのクエリがフェッチする行数が減れば、クエリのレスポンスだけでなく全体のスループットも向上するのである。

そんなわけで、クエリを最適化したいならば、クエリを書き換えてはEXPLAINの出力と格闘しよう。その際にはこの記事を参考にしてくれると嬉しい限りである。