sidebarちょっと硬派なコンピュータフリークのBlogです。カスタム検索2009-03-31MySQLのEXPLAINを徹底解説!!以前、MySQLを高速化する10の方法という投稿で「EXPLAINの見方についてはいずれ解説しようと思う」と書いてしまったので、今日はその公約?を果たそうと思う。MySQLのチューニングで最も大切なのは、クエリとスキーマの最適化である。スキーマの設計は一度決めてしまうとそのテーブルを利用する全てのクエリに影響してしまうためなかなか変更することは出来ないが、クエリはそのクエリだけを書き直せば良いので変更の敷居は低い。遅いクエリをなくすことは、性能を大幅に向上させるための最も有効な手段である。従って、アプリケーションの性能を向上させたいなら、まず最初にクエリのチューニングを検討するべきなのである。最適化するべきクエリはスロークエリログやクエリアナライザで見付けられるが、ではそのようなクエリが見つかった場合にはどのように最適化すればいいのか?そのためにはまず現在どのようにクエリが実行されているかということを知らなくてはならない。...の出力を理解することが初めの第一歩となる。書き換える前と後でクエリの実行結果が同じになるEXPLAINがよりよい実行計画を表示するということであると言える。クエリを書き換えてはEXPLAINを実行して、結果が思わしくなければまた書き換えて・・・ということ繰り返して最適化するわけである。EXPLAINの出力を理解し、どのようなクエリならMySQLが高速に実行出来るかということを知っていればこの作業は比較的簡単である。今日は前者、つまりEXPLAINの出力が何を意味するのか?ということについて解説しよう。以下に順を追って各フィールドの詳細を説明する。id/select_typeMySQLのEXPLAINの見方には少々コツが要る。クエリの実行計画は内部的にはツリーで表現されているが、EXPLAINの出力は表になっているからである。EXPLAINを見ることによってツリー構造を理解しないといけないわけで、これが第一の関門となる。そのツリー構造を理解すれば、各テーブルがどのようにアクセスされるかということが容易に理解出来るだろう。idとselect_typeはEXPLAINの最初の2つのフィールドであるが、これらはセットにして考えると良い。select_typeはクエリの種類を表すものであり、ズバリツリーの構造にそのまま反映される。クエリの種類とはJOIN、サブクエリ、UNIONおよびそれらの組み合わせで、select_typeの内容もその組み合わせから導き出されたものなのである。Join(NLJ)の一種類しかない。NLJとは例えばA、B、Cという3つのテーブルをJOINする際、最初にテーブルAから条件にマッチする行を全てフェッチして、次にBから条件にマッチする行をフェッチしてJOINし、次にCから条件にマッチする行をフェッチしてJOINする・・・というように、テーブルを一つずつ順に処理していく方式である。JOINというのが追加されるが、これもNLJの発展系である。JOINなどがあるが、MySQLには実装されていない。)クエリがJOINだけから構成される場合、select_typeはSIMPLEと表示される。複雑なJOINであってもCOMPLEXとはならずにSIMPLEなのである。従って「これはシンプルなクエリを示すのだ」などと誤解をしてはならない。SIMPLEではidが全て同じ値になる。これはそのクエリが一つのNLJで処理されることを示すからである。NLJではどのテーブルから処理するのかということが最も重要になるが、EXPLAINの出力の順序がどのテーブルから処理するかということを反映している。サブクエリの場合サブクエリが絡むと次のselect_typeには次の5種類のうちいずれかが表示される。PRIMARY・・・外部クエリを示す。SUBQUERY・・・相関関係のないサブクエリ。SUBQUERY・・・相関関係のあるサブクエリ。SUBQUERY・・・実行する度に結果が変わる可能性のあるサブクエリ。DERIVED・・・FROM句で用いられているサブクエリ。サブクエリの場合は実行順序に気をつける必要がある。DERIVEDの場合、サブクエリ→外部クエリの順番でクエリが実行される。次のような場合はCityテーブルから最初に行がフェッチされて<derived2>テーブルとなり、その次にCountryテーブルとのJOINが実行される。sec)それ以外の場合は外部クエリ→サブクエリの順番でクエリが実行される。SUBQUERYの場合はサブクエリが本当に実行されるのは最初の一回だけで、それ以降はキャッシュされた実行結果が利用される。SUBQUERYの場合はサブクエリが行の評価の度に実行されることになる。サブクエリの場合、外部クエリとサブクエリでは別々のidがつけられる。UNIONの場合クエリにUNIONが含まれる場合、次の5種類のいずれかがselect_typeに表示される。SUBQUERYがUNIONになっている場合<derived2>UNIONは前から順番に処理されていくだけなので、テーブルが処理される順序という観点ではわかり易いと言えるだろう。select_typeについては次のページにまとめてあるので参考にして欲しい。http://www.mysqlpracticewiki.com/index.php/Select_typetableアクセスする対象のテーブル。読んで字のごとくである。typeselect_typeの次に意識しなければいけないのは、typeフィールドである。このフィールドはレコードアクセスタイプとも呼ばれ、対象のテーブルに対してどのような方法でアクセスするかを示す。致命的なクエリはこのフィールドを見れば一目で分かるのでとても重要なフィールドである。見かけるものは次の通り。KEYまたはUNIQUEインデックスのルックアップによるアクセス。KEYが利用される時のアクセスタイプ。constと似ているがJOINで用いられるところが違う。value)を行った時に使われるアクセスタイプ。range・・・インデックスを用いた範囲検索。index・・・フルインデックススキャン。インデックス全体をスキャンする必要があるのでとても遅い。ALL・・・フルテーブルスキャン。インデックスがまったく利用されていないことを示す。OLTP系の処理では改善必須。<derived2>indexまたはALLを見かけたらすかさずクエリをチューニングしよう。typeフィールドの詳細については次のページにまとめてあるので参照して欲しい。http://www.mysqlpracticewiki.com/index.php/Record_access_typespossible_keysオプティマイザがテーブルのアクセスに利用可能なインデックスの候補として挙げたキーの一覧。keyオプティマイザによって選択されたキー。key_len選択されたキーの長さ。インデックスの走査は、キー長が短い方が高速である。インデックスをつけるカラムを選ぶ時にはそのことを念頭に置いて欲しい。ref検索条件で、keyと比較されている値やカラムの種類。定数が指定されている場合はconstと表示される。JOINが実行されている時には、結合する相手側のテーブルで検索条件として利用されているカラムが表示される。次の例では、CountryテーブルはCityテーブルとCity.CountryCodeカラムでJOINされるということを示している。sec)rowsそのテーブルからフェッチされる行数の見積もりである。このフィールドはあくまでもテーブル全体の行数やインデックスの分散具合から導き出された大まかな見積もりなので、実際にフェッチされる正確な行数ではないので注意が必要されたい。DERIVEDテーブルである。DERIVEDテーブルは実際に実行してみないと行数の見積もりができないので、オプティマイザはEXPLAINの際にもサブクエリを実行する。そのため、DERIVEDテーブルだけは常に正確な行数を見積もることができるわけである。DERIVEDテーブルを扱う時は、サブクエリが最適化されていない場合はEXPLAINにも時間が掛かってしまうため注意が必要だ。フェッチされた全ての行がそのまま結果として返されるわけではないという点にも注意したい。whereがExtraフィールドに表示されている場合は、フェッチした行に対してさらにWHERE句の検索条件が適用されて行の絞り込みが行われるので、クライアントへ返される結果行は少なくなる可能性がある。JOINを処理する場合、WHERE句により行の絞り込みがなければ最終的な結果行数の見積もりはJOINする全てのテーブルのrowsフィールドの積として考えることが出来る。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フィールドに表示される代表的な追加情報を以下に挙げる。これらの情報は同時に複数表示される場合がある。where・・・頻繁に出力される追加情報である。WHERE句に検索条件が指定されており、なおかつインデックスを見ただけではWHERE句の条件を全て適用することが出来ない場合に表示される。index・・・クエリがインデックスだけを用いて解決できることを示す。Indexを利用している場合などに表示される。filesort・・・filesort(クイックソート)でソートを行っていることを示す。filesortについては先日詳しく説明したので参照されたい。temporary・・・JOINの結果をソートしたり、DISTINCTによる重複の排除を行う場合など、クエリの実行にテンポラリテーブルが必要なことを示す。BY句と併用されているとき、クエリがインデックスだけを用いて解決できることを示す。N)・・・JOINにおいてrangeまたはindex_mergeが利用される場合に表示される。NULLとして定義されたフィールドでJOINされている場合にはマッチしない行を探せば良い・・・ということを示す。Extraフィールドにはまだまだバリエーションが存在するが、下記のページにまとめてあるので参考にして欲しい。6.0で追加される予定のものについては書きかけだけど気にしてはいけない。http://www.mysqlpracticewiki.com/index.php/Extra_field注意点EXPLAINを実行する場合、実際のデータを利用することが非常に大切である。テーブルの行数やインデックスの分散具合によって実行計画に違いが生じるからである。本番ではテーブルに100万行のデータが存在するのに、100行しかないテストテーブルを使ってクエリの最適化を行ってもあまり意味がない。テーブルに対して大量のデータ追加、削除、変更などを行った場合、インデックスの統計情報が正確ではなくなる場合がある。TABLEコマンドを利用すると良いだろう。TABLEはテーブルの統計情報を更新してくれるコマンドである。クエリを書き換えた場合にはEXPLAINの出力を見るだけでなく、正しい結果が得られるかどうかを確認しよう。高速なクエリでも、正しい結果が得られなければ意味がない。まとめEXPLAINコマンドの各フィールドの詳細を説明したが、実際にEXPLAINコマンドを使ってクエリの実行計画を見る際には次のようなステップを踏むといいだろう。id/select_type/tableフィールドを見て、どのテーブルがどの順序でアクセスされるのかを知る。これらはクエリの構造を示すフィールドであると言える。サブクエリが含まれている場合にはEXPLAINの表示順とアクセスされる順序が異なる場合があるので気をつける必要がある。type/key/ref/rowsフィールドを見て、各テーブルから行がどのようにフェッチされるのかを知る。どのテーブルへのアクセスが最も重いか(クエリの性能の足を引っ張っているのか)を、これらのフィールドから判断することが出来る。Extraフィールドを見て、オプティマイザがどのように判断して、各々のテーブルへのアクセスにおいて何を実行しているのかを知る。Extraフィールドはオプティマイザの挙動を示すものであり、クエリの全体像を把握するのに役立つ。EXPLAINコマンドはクエリの実行計画を知るためのツールである。どのようにクエリを書き換えれば良いか?というのはまた別の問題であり、EXPLAINコマンドはどのようにクエリを書き換えればいいかについては何も教えてくれない。一つ言えることはクエリにおいてテーブルからフェッチされる行数を減らすことが重要だと言うことである。同じ結果が得られるなら、ひとつのクエリにおいてテーブルからフェッチされる行数が減れば減るほど良い。ストレージエンジンの性能には上限があるからである。あるストレージエンジンが1秒間に100万行のフェッチが出来る性能を持っているとすると、一つのクエリが平均1000行のフェッチを行うならば1秒間に最大1000クエリ、一つのクエリが平均100行のフェッチを行うならば1秒間に最大1万クエリが可能であるという計算になる。ひとつのクエリがフェッチする行数が減れば、クエリのレスポンスだけでなく全体のスループットも向上するのである。そんなわけで、クエリを最適化したいならば、クエリを書き換えてはEXPLAINの出力と格闘しよう。その際にはこの記事を参考にしてくれると嬉しい限りである。Okuno私は所謂ギークです。普段はMySQL関係の仕事をしています。今の時代、フリーソフトウェア(自由なソフトウェア)こそギークの歩く道だと信じています。コンピュータに手を染めてはや二十年。コンピュータ道はいくら進んでも終わりが見えない果てしない世界。コンピュータにはロマンがある。(18)MySQLのEXPLAINを徹底解説!!なぜMySQLのサブクエリは遅いのか。filesortMySQLのプロンプトを変更する。高速SSDの落とし穴。本ブログにおいて示されている見解は、私自身の見解であって、オラクルの見解を必ずしも反映したものではありません。