mysqlTweetmysqlのサブクエリに問題があることはなんとなく見聞きしていたが今日はこれについて深く追求せざるを得ない機会があったので調べてみました。自分なりに理解したので簡単にまとめてみるが、以下のブログの記事が素晴らしいので詳しくはそちらもあわせて読むと良いでしょう。MySQLのサブクエリは遅いのか。trueにすると、コメントの書いてないブクマを表示しない。"このページはまだブックマークされていません。MySQLのサブクエリは遅いのか。content='ちょっと硬派なコンピュータフリークのBlogです。/></a></div><h4>ちょっと硬派なコンピュータフリークのBlogです。href='http://nippondanji.blogspot.com/2009/03/mysql_25.html'>なぜMySQLのサブクエリは遅いのか。entry-content'>よくMySQLはサブクエリが弱いと言われるが、これは本当だろうか?半分は本当で半分は嘘である。MySQLのサブクエリだってなんでもかんでも遅いわけではない。落とし穴をしっかり避け、使いどころを間違えなければサブクエリも高速に実行できるのである。今日はMySQLがどんな風にサブクエリを実行し、どのような場合に遅いのかということについて説明しよう。/>EXPLAINで実行計画を調べた際に、select_typeにはクエリの種類が表示されるのだが、代表的なサブクエリには次の3つのパターンがある。SUBQUERYである。SUBQUERYとはいわゆる相関サブクエリに相当するもので、サブクエリにおいて外部クエリのカラムを参照しているサブクエリのことである。SUBQUERYであると判断してしまう。そのため、多くの場合においてサブクエリが遅くなってしまうのである。SUBQUERYが何故遅いか?それはクエリの評価方法にある。/>現時点でのMySQL(バージョン5.1)では、サブクエリはまず外部クエリの条件から評価される。外部クエリの条件に合致する行が見つかると、その行がサブクエリの条件に合致するかどうかが評価されるわけである。サブクエリにおいてフェッチしなければいけない行数が平均N行、外部クエリでフェッチされる行数がM行のとき、サブクエリにおいてM×N行の評価が行われることになる。これは膨大な計算量である。/>以下に具体例を挙げよう。href="http://dev.mysql.com/doc/#sampledb">MySQL公式のサンプルデータベース</a>であるworldデータベースのCountryおよびCityテーブルである。このクエリは「人口が特定の都市に半分以上が密集しているアジアの国」を探し出す。'Asia'にマッチする行がフェッチされる。City...)が評価され、人口が国全体の半数以上密集している都市が存在するかどうかを調べる。'Asia'にマッチする行が51行あるので、Cityテーブルは51×4079=208,029回の評価が行われることになる。ちなみに、このクエリの実行には1秒程度かかってしまう。SUBQUERYが常に遅いかというと、そういうわけでもない。サブクエリが評価される際にインデックスを使用することが出来ればこのサブクエリの性能は改善される。/>インデックスが使われている様子は、EXPLAINでも見て取れる。bold;">なぜインデックスをつけるのがCountryCodeカラムなの?</span>と思われるかも知れない。それ以前に、そもそもこのサブクエリには相関関係などないように見える。SUBQUERYになるのだろうか?</span>それは、MySQLがこのクエリを次のように書き換えて実行するからだ。bold;">MySQLは内部的にINを直接処理することができないので、EXISTSに変換することでSQL的には相関のないサブクエリも相関サブクエリになってしまうのである。</span>これがまさにMySQLのサブクエリが遅い!と言われている原因だろう。/>この例では外部クエリのWHERE句の条件にマッチする行数が51行しかないので高速化が可能だが、マッチする行数が多い場合、たとえば外部クエリのWHERE句にサブクエリ以外の検索条件がない場合にはテーブルスキャンが発生してしまうことになり、外部クエリで用いられているテーブルが大きい場合には時間が掛かってしまうのである。SUBQUERYとなっていないかどうか、外部クエリからフェッチされる行数が膨大でないかどうか、サブクエリでインデックスが利用されているかどうかをチェックすると良い。サブクエリでインデックスをつけるべきカラムは、サブクエリが結果を返すカラム、上記の例で言えばCountryCodeである。サブクエリ内のWHERE句で指定されているカラムであるPopulationと複合インデックス(CountryCode,Population)を作成すればこのクエリはもっと高速になるだろう。SUBQUERY以外のサブクエリはとても高速である。EXPLAINでSUBQUERYと表示されるパターンについて見てみよう。/>この例では敢えてインデックスが利用されないようなクエリを選んでいる。それでもこのクエリはとても速い。(実行時間は0.01秒とか)なぜならSUBQUERYの部分は一度だけしか評価されないからである。'Handler%'でストレージエンジンへのアクセス回数を見てみよう。/>ちなみに、このクエリをMySQLは内部的に次のように書き換えて実行する。/>どの行(ANY)より大きいということは最大値(MAX)より大きいということになるので、上記のように書き換えても結果は同じになるというわけである。このクエリではインデックスがあればさらに高速になる。インデックスをつけるべきカラムはもちろんCity.Populationである。その場合、EXPLAINの表示は次のようになる。/>Extraフィールドに面白い表示がされている。awayは、テーブルがMyISAMである場合にMAX()/MIN()によってサブクエリの最適化が行われた事を意味するメッセージである。これが現れたらサブクエリは最強に最適化されまくってるので、これ以上手の入れようがないということである。(外部クエリは最適化の余地があるかも知れないが。最後に見るのはDERIVEDである。clause」と呼ばれる。そのままやんけ!と叫びたくなる呼び名であるが、ここは気にせず話を進めよう。DERIVEDが利用されるのは例えば次のようなクエリである。/>id=1の項目では、テーブル名が<derived2>となっているが、id=2のテーブルからDerive(由来)するものであるということを示す。DERIVEDでは、サブクエリは前もって実行されてその結果がテンポラリテーブルに格納される。外部クエリはテンポラリテーブルを使ってクエリを実行することになる。DERIVEDで注意しないといけないのは、テンポラリテーブルのサイズが大きくなってしまうような場合である。MySQLのテンポラリテーブルは最初MEMORYストレージエンジンを利用して作成されるが、テーブルのサイズがmax_heap_table_sizeまたはtmp_table_sizeを超えてしまうとMyISAMに変換される。DERIVEDを利用する場合には、テンポラリテーブルがメモリ内に収まるようにするというのが一つの鍵である。テンポラリテーブルが大きくなってしまう場合は、max_heap_table_sizeおよびtmp_table_sizeを増やすといいだろう。メモリに収まりきらないぐらい大きなテンポラリテーブルが必要な場合には、BIG_TABLESシステム変数を使うと良い。テンポラリテーブルが大きくなってしまった場合、MEMORYからMyISAMへの変換作業自体に時間が掛かってしまうことが問題になる場合がある。そんな時は最初からMyISAMを使えば良い。BIG_TABLES=1を設定すると、テンポラリテーブルは最初からMyISAMを利用して作成される。僅かではあるが無駄な変換が行われなくなるので効率化が期待出来る。bold;">まとめ。いうわけでMySQLによるサブクエリの処理について見てきたが、きちんと気をつけて使えばサブクエリも高速に実行される。JOINに書き換えた方が速いのは言うまでもないが、SQL文のメンテナンスし易さなどを考えるとサブクエリで処理を書きたい!という人も居るのではないだろうか。そんな方は次の事に気をつけてサブクエリを使って頂きたい。/></li><li>サブクエリで利用されるインデックス</li><li>テンポラリテーブルのサイズ</li></ul>実はMySQLのEXPLAINが表示するサブクエリにはもう一つ種類がある。SUBQUERYというもので、サブクエリ内でRAND()などが利用されている時に表示される。SUBQUERYよりも重い。サブクエリ内でRAND()などを利用しないように心がけよう。SUBQUERYを見かけたらクエリを書き換えるべし。6.0では、サブクエリの最適化が加わる予定である。Flattening最適化手法が盛り込まれる予定である。6.0では「MySQLのサブクエリは遅い!」と言われることはなくなるだろう。Flattening以外の最適化が使えるので、リリースされた暁にはブログで紹介したいと思う。6.0の開発は中止され、MySQLのバージョンは5.4、5.5、5.6...と続くようになりました。