t100のプログラミング脱出作戦

自分のプログラミング脳をプログラムにして、いつかプログラミングから脱出してやるぞっ!とか夢見ながら、日々プログラム作っていく 百野 貴博 の日記です!今は、屋号『百蔵。』として、Silverlight・WPFを追跡中です! (2007/09/30)
スポンサーサイト
上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。
【--/--/-- --:--】 | スポンサー広告 | トラックバック(-) | コメント(-) top↑
なぜ mysql の using temporary; using filesort が遅いのか
追記 2010-08-12

今回のエントリですが、「漢のコンピューター道 - Using filesort」で既に解説されていました・・・。
しかも、「漢のコンピューター道」さんの方が分かりやすいっ

うわぁぁぁん。゜(゚´Д`゚)ノ


----

帰宅してからBlog書こうと思っていたら、さっそく帰宅後の仕事でハマってしまってBlogが中断してしまいました、、、

いけませんね!
というわけで、日中の仕事の中でもBlogのネタを溜めるようにして頑張ります!


さて、今日はMySQLのチューニングネタです!

mysql のSQLをチューニングしていて、EXPLAIN を使って実行計画を見たときに
using temporary; using filesort に出くわす場合があります。

調べてみると、これはなんとしても回避した状況が内部で起こっているサインのようなんですが
じゃぁ何処を見直せばいいのかという判断は、意外と難しかったりします。

そもそも、このサインはなぜ遅いのか?なぜ発生してしまうのか?
その仕組みを理解しておかないと、原因調査もままなりません。

が、日本語サイトでは、なかなかそこまでの情報が引っかからないんですよね・・・。


というわけで、意を決して英語サイトに当たりを付けてみました。

中学の片言英語的な恥ずかしい条件でアタックw

mysql using filesort why



と思ったら、なんかいい感じのサイトがヒットしましたーっ!!

やりました。(`・ω・´)9m ビシッ

What does Using filesort mean in MySQL?

ここでもザックリ解説してあるのですが
さらに詳細として、セルゲイのサイトを紹介してくれています。

すばらしい。(`・ω・´)9m ビシッ


How MySQL executes ORDER BY


というわけで、以下訳です!
英語が苦手なので、雰囲気レベルの翻訳ですがご容赦ください・・・。

#英語が読める方は、元サイトを直接読まれることをオススメします。
#また、間違いを見つけた方は、指摘いただけるとありがたいです・・・。

■Available means to produce ordered sequences


●MySQL は、並び替えの方法が2つあります。

・一つ目は、"range", "ref", "index" で、これらはインデックス上で完結します。
 5.1 以降は index の並び順を使ってレコードを返します。(今までは?)
 5.2以降は、MultiRangeRead optimization というのも活用されるそうです。(よく知らないですが・・。)
・上記以外の全ては、ファイルソート アルゴリズムを使います。

ファイルソートは、メモリに入る単位でデータを切り出してクイックソートします。
メモリに入らない場合は、データを分解してテンポラリファイルに書き出します。テンポラリファイルの結合には、マージソートを使います。
並び替えに使うメモリのサイズは、@@sort_buffer_size で調整できます。

ファイルソートの元データは常に一つのテーブルから提供されます。データのソート結果が必要な他のテーブルがある場合、最初のデータをテンポラリテーブルに集めてから、そのテンポラリテーブルをファイルソートします。


●ファイルソートは、さらに二つの方法があります。

・ モード1 : 並び替えに必要な要素がソーステーブルに全てそろっている場合。 並び替え完了以降にソーステーブルへのアクセスは発生しません。
・ モード2 : ソートキーとrowid を使った並べ替えの場合。 並び替えに必要な rowid を取得する為にテーブルアクセスが発生します。(大抵のばあい、ランダムアクセスになってしまうので遅いです)

モード2になるのは、ソートの組にblob の列があったり、可変長の列(variable-length columns)?がある場合です。
しかしExplainでは、どちらのモードを使っているか表示されないので、出力のカラムリストにblob の列が無いかどうか、自分で調べる必要があります。


■Executing join to produce ordered stream

MySQLは、JOINを処理して結果を並び替える際に、3つの方法を取ります。

・ インデックスを使って結果を並べ替えます。
  この場合、Explain にはfilesort に関することは何も出力されません。
・ 最初のテーブル(non-const table)にファイルソートを実行します。
  この場合、Explain には最初の行に "Using filesort" と出力されます。
・ JOINの結果をテンポラリテーブルに入れてから、ファイルソートを実行します。
  この場合、Explain には最初の行に "Using temporary; Using filesort" と出力されます。

以下、この3つに関する詳細です。

最初の方法は、JOINに含まれる最初のテーブル(non-const table) が ORDER BY のリストに一致するインデックスを持っている場合に使われます。
結果を出力する為に、並び替えた結果に対して他のテーブルを拘束します。
この方法は、追加のソートが発生しないので、他の二つよりオススメです。

mysql-sort-00.png


二つ目の方法は、ORDER BY の要素がJOIN 順の最初のテーブルに全て含まれる場合に使われます。
このケースでは、最初のテーブルに対して ファイルソート が使われ、それからJOINが実行されます。

このファイルソートでは、モード1かモード2の処理を選択します。

ここで、なぜファイルソートを最初のテーブルの後に制限するのか疑問に思う人もいるかもしれません。
だって、二つ目以降のテーブルにも、テーブル1とテーブル2のレコードの結合やテンポラリテーブルへの書き出し、ソート等にもファイルソートしてもよいはずです。
これは完璧な使い勝手のように思えるかもしれません、、、が、悲しいかなMySQLはクエリプランをそこまで考慮してくれないのです。

mysql-sort-01.png



最後の方法は、JOINの結果をテンポラリテーブルに書いてから、ファイルソートを実行します。

mysql-sort-02.png


こ、これは確かに遅そうですね!


テーマ: プログラミング - ジャンル: コンピュータ













管理者にだけ表示を許可する


トラックバックURL:
上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。