sidebarちょっと硬派なコンピュータフリークのBlogです。filesort去年ソートに関する記事を書いたが、今日はその続きである。filesort」という文字列。Filesortって一体なんだろう?と思ったことはないだろうか。単刀直入に言ってFilesortの正体はクイックソートである。BYが含まれる場合、MySQLはある程度の大きさまでは全てメモリ内でクイックソートを処理する。ある程度の大きさとはsort_buffer_sizeであり、これはセッションごとに変更可能である。ソートに必要なメモリがsort_buffer_sizeより大きくなると、テンポラリファイル(テンポラリテーブルではない)が作成され、メモリとファイルを併用してクイックソートが実行される。Filesortは全てのソート処理において実行されるわけではない。前回の記事ではソート処理でインデックスを利用できる場合について紹介したが、つまりインデックス順で行をフェッチすることが出来ればFilesortは必要ないわけである。filesortは現れないので、ソートではインデックスが利用されるようにクエリおよびテーブルをチューニングしよう。テーブルが一つの場合、つまりJOINを利用しない場合のソートについては前回の記事でだいたいの場合を網羅しているのだが、JOINを利用する場合にはまた別のチューニングテクニックが必要になる。Filesortは複数のテーブルを一度にソートする事は出来ない。従って複数のテーブルをJOINする場合には、ソートを行うタイミングには2つの場合が考えられる。JOINの最初のテーブルをソートしてからJOINするか、もしくはJOINをした結果をソートするかである。前者の場合はさらにソート処理をFilesortを用いるかインデックスを利用するかという2通りに分けられるので、計3通りの手法がMySQLには実装されている。(JOINした結果にはインデックスがないので、JOINした結果に対してソートするときは常にFilesortである。)このことについて、MySQLのオプティマイザの開発者であるセルゲイさんが自身のブログでわかり易く解説してくれているので、彼の画像を引用して解説したいと思う。:)まずは最初のテーブルをインデックスを用いてソートして、それからJOINするパターン。次は最初のテーブルをFilesortしてからJOINするパターン。全てのテーブルをJOINしてからFilesortをするパターンである。単純明快!図を見ればどのようにソートが行われているかが一目瞭然である。下手な解説はしないでおこう。詳細が気になる人はセルゲイさんのブログを見て欲しい。(英語だけど)どの実行計画になっているかはEXPLAINのExtraフィールドを見れば分かる。filesortの表示がない。filesortの表示。filesortの表示。後になればなるほど処理が重くなるので、出来るだけそのようなクエリはさけて1番目や2番目の実行計画になるようにすると良いわけである。1番目や2番目の実行計画になるようにするには、次のような点に注意しなければいけない。大切なことはWHERE句における検索条件とソートする対象のカラムを一つのテーブルに集中させることである。filesortを避けられる。tbl1.col2;この場合、もし(col1,col2)というインデックスがあればインデックス順に行をフェッチできるし、そのようなインデックスがない場合にはFilesortでソートが実行される。filesortになってしまうのでNGだ。BYではtbl2.col1という別のテーブルのカラムを指定している。これではJOINを行ってから最終的にソートするしかないわけである。BYで指定したテーブルからJOINされない場合がある。そのような場合には、STRAIGHT_JOINチューニングヒントを使ってJOINの順序を固定するといいだろう。tbl1.col2;複数のテーブルをJOINする際にもう一つ気をつけなければならないのはLIMIT句である。JOINでは最終的な行数がどれだけになるかということは、一番目のテーブルから行をフェッチした段階では分からない。JOINのみ)し、tbl2またはtbl3で複数の行がマッチした場合には行数が増える場合があるからである。従って、いずれのパターンにおいてもLIMIT句が適用されるのはJOINとソートが完了した後なのである。LIMIT句が最後に適用されるということで問題になるのは、LIMITする前の結果セットが巨大な場合である。100が指定されているクエリにおいて、JOINした結果が全部で1万行になってしまったとしよう。最終的には先頭の100行しかクライアントへ返されることはないので、残りの9900行は無駄になってしまう。この様な無駄を作らないためには、JOINした結果が大きくならないようなクエリを書くしかない。出来ることなら、複数のテーブルをJOINするときにはLIMITで行数を絞ろうと考えないのがいい。最初のテーブルにしか検索条件がない最初のテーブルをフェッチした段階で結果行が多くなってしまうというような場合には、サブクエリの内部でLIMITを用いるという対策が可能である。100;サブクエリの中とクエリ全体で同じソート条件を指定しているのがミソである。ソート処理が2回行われることになるが、1万行をJOINしてから全体をソートするより、先に行を絞り込んでおけば無用なJOINを避けられるのでかなり効果的である。(2度目のソートは1万行よりずっと少ない場合が多いだろう。)上記以外の場合、例えばtbl2やtbl3でWHERE句による絞り込みが行われているような場合にはこの方法は使えない。tbl1を先に100行に絞り込んだとしても、tbl2やtbl3で行数が大幅に増えてしまうような場合にもこの方法はあまり有効ではない。(tbl2やtbl3を主キーやユニークキーでJOINするならOKである。JOINの場合もtbl2やtbl3に該当する行が存在しない場合があるのでサブクエリでの対策は難しい。tbl2やtbl3に50%程度の確率で該当する行が存在するような場合、サブクエリ内のLIMITを少し多め(500など)に設定してやることで近似的な結果を得ることが可能である。500を指定したため、tbl2やtbl3においてマッチする行が少ない場合には結果が100行未満になってしまうことがあるということである。)アプリケーション次第であるが、結果の正確さよりも性能を重視する場合にはこのような最適化も検討するといいだろう。以上の話をまとめると次のようになる。Filesortとはクイックソートのことである。BYが指定されているとき、ソートが行われるタイミングは3種類ある。filesortがEXPLAINに出ていないときはインデックスを用いてソートが行われている。filesortだけがEXPLAINの最初の行に出ている時は、先にソートしてからJOINが実行されている。filesortがEXPLAINの最初の行に出ている時は、先にJOINしてからソートされている。BYにおけるソート条件は、一つのテーブルに集中させる。JOINにおいてソート処理する場合には、LIMIT句が適用されるのはJOINとソートが完了した後である。JOINの場合には、LIMIT句の問題はサブクエリである程度対策が可能である。さんのコメント...いつも参考にさせて頂いています。図の直後にでてくる以下のクエリについて、2点お伺いしたいことがあります。lukesilviaさん1.Oops!!指摘ありがとうございます。<(小なり記号)から次の>までがBloggerによって短縮されてしまっていたようです。&lt;で書くことで対策できましたが、preタグで囲んで置いたので残しておいて欲しかったです。Bloggerはこういうところが嫌になってしまいますね。BYでcol2という別々のカラムを指定しているからです。col1だけにインデックスがある場合はWHERE句の条件絞り込みにおいてインデックスを利用することは出来ますが、ソートでインデックスを利用することは出来ません。そのためには(col1,col2)というインデックスが必要なのです。;)これなら表示もバッチリです。すごい見やすいです!!恐縮ですが、2について僕の質問が良くなかったので、もう一度質問させて下さい。col2)のインデックスがある場合、次のクエリならソートにもインデックスが使用できると思います。col2)のインデックスは使用できないのではないかなと思いました。30)のような場合です。ソートにはインデックスは使用できないのではないかなと思いました。lukesilviaさんご指摘の通りです!!コメントありがとうございます。さんのコメント...ちなみに今回の記事の方は修正しておきましたのであしからずご了承ください。さんコメントありがとうございざいます!僕も「オトコのソートテクニック2008」を読みながら考えました!基本から最新まで記事が網羅されていてとても勉強になります!これからも勉強させていただきます。さんのコメント...いつも拝見しております。こちらのインデックスの件とても参考になりました。このfilesortについて拝見後、設計を見直すこともありました。(笑)こちらの情報の掲載についてとても感謝しておりますので、チップをわずかですが贈らせていただきました。受け取りはこちらの記事も参考にして作らせていただいた弊社のサービスで行えますので、1度ご覧いただければ幸いです。Okuno私は所謂ギークです。普段はMySQL関係の仕事をしています。今の時代、フリーソフトウェア(自由なソフトウェア)こそギークの歩く道だと信じています。コンピュータに手を染めてはや二十年。コンピュータ道はいくら進んでも終わりが見えない果てしない世界。コンピュータにはロマンがある。男に生まれたからにはロマンを追わずして居られようか!詳細プロフィールを表示リンクはどうぞご自由に。(18)MySQLのEXPLAINを徹底解説!!なぜMySQLのサブクエリは遅いのか。filesortMySQLのプロンプトを変更する。高速SSDの落とし穴。本ブログにおいて示されている見解は、私自身の見解であって、オラクルの見解を必ずしも反映したものではありません。