Warning: include() [function.include]: URL file-access is disabled in the server configuration in /home/users/0/main.jp-bacteria/web/2005/10/using_filesort.html on line 99

Warning: include(http://bacteria.jp//include/topad.php) [function.include]: failed to open stream: no suitable wrapper could be found in /home/users/0/main.jp-bacteria/web/2005/10/using_filesort.html on line 99

Warning: include() [function.include]: Failed opening 'http://bacteria.jp//include/topad.php' for inclusion (include_path='.:/usr/local/php5.2/php') in /home/users/0/main.jp-bacteria/web/2005/10/using_filesort.html on line 99
アフィリエイト ケータイ ゲーム一般 テクノロジー セキュリティ サーバ管理 スポーツ コラム その他
2005年10月29日

MySQLのUsing Filesort


Warning: include() [function.include]: URL file-access is disabled in the server configuration in /home/users/0/main.jp-bacteria/web/2005/10/using_filesort.html on line 141

Warning: include(http://bacteria.jp//include/entryadbox.php) [function.include]: failed to open stream: no suitable wrapper could be found in /home/users/0/main.jp-bacteria/web/2005/10/using_filesort.html on line 141

Warning: include() [function.include]: Failed opening 'http://bacteria.jp//include/entryadbox.php' for inclusion (include_path='.:/usr/local/php5.2/php') in /home/users/0/main.jp-bacteria/web/2005/10/using_filesort.html on line 141

週に一度か二度、Bacteriaが管理するサイトは大規模なプロモーションをすることがある。
140万通ものメール広告でもってやるわけだけど、そのレスポンスが強烈に重い。
サーバが悲鳴を上げているのがわかる。

ちょっと重すぎるよこれどうなってんのってことで、MySQLのslow_queryで分析をはじめることにした。
すると一番最初に引っかかったのが下記の事例だった。

MySQLにクエリを投げる場合、Explainして最も見たくないのがUsing file sortの文字だ。
file sortは負荷がかかると、Using Temporaryまで出てき始める。
こうなるともうお手上げで、クエリは結果を表示するためにディスクアクセスを繰り返し、速度は低下の一途をたどることになる。

file sortが発生するときは、たいてい決まっているがorder byを使うときだろう。
そりゃそうだ、order by するからsortするわけだ、超そのまま!
だからorder byに使用するキーにインデックスを設定してやればいいじゃん。

とまあこんな感じでガシガシインデックスを貼ってみたが、全然改善しない。
何だよおい頼むよ俺が何したって言うんだよ、と、いろいろ調べると、

MySQL による ORDER BY の最適化 http://dev.mysql.com/doc/refman/4.1/ja/order-by-optimisation.html

MySQL で ORDER BY の解決にインデックスを使用できない場合は以下のとおりです

複数のキーに対して ORDER BY を実行する場合。

SELECT * FROM t1 ORDER BY key1,key2

連続しないキー部分に対して ORDER BY を実行する場合。

SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2

ASC と DESC が混在している場合。

SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 ASC

レコードの取り出しに使用されるキーが ORDER BY の実行に使用されるキーと異なる場合。

SELECT * FROM t1 WHERE key2=constant ORDER BY key1

ORDER BY で多くのテーブルとカラムを結合していて、それら全てがレコードの取り出しに使用される最初の非 const テーブルではない場合(これは EXPLAIN で出力される最初のテーブルで、かつ、const メソッドを使用していないテーブル)。

ORDER BY と GROUP BY 式が異なる場合。

使用されたテーブルインデックスが、並び順にレコードを格納していないインデックスタイプの場合(HEAP テーブルの HASH インデックスなど)。

ひどい。結構使用できないパターンがある。
今回引っかかったのは

・複数のキーに対して ORDER BY を実行する場合。
・連続しないキー部分に対して ORDER BY を実行する場合。

この二つだった。
こういった場合、MySQLでの解決策は以下の2点しかない。

・テーブルの非正規化
・スナップショットテーブルの作成

テーブルの非正規化については別にもうやりたい放題してくれればよいのだ。
ログテーブルを作るでも何でも、好きにしてくれって感じ。

スナップショットは、リアルタイム性が1時間をきらないところで許容できるなら、Order byした順にスナップショットテーブルでPKをはり、あとはcronでtruncateとインサートを繰り返すのみである。
通常selectしたときはPK順にレコードは取り出されるので、ふつーにselectしてくれればよい。

これでも解決できなければそれはもう要求にMySQLが見合ってないか、ただのアプリケーション設計ミスだろう。
健闘を祈る。

posted by Bacteria : 2005年10月29日 23:14 : サーバ管理



トラックバック

このエントリーのトラックバックURL:


Warning: include() [function.include]: URL file-access is disabled in the server configuration in /home/users/0/main.jp-bacteria/web/2005/10/using_filesort.html on line 281

Warning: include(http://bacteria.jp//include/sidead.php) [function.include]: failed to open stream: no suitable wrapper could be found in /home/users/0/main.jp-bacteria/web/2005/10/using_filesort.html on line 281

Warning: include() [function.include]: Failed opening 'http://bacteria.jp//include/sidead.php' for inclusion (include_path='.:/usr/local/php5.2/php') in /home/users/0/main.jp-bacteria/web/2005/10/using_filesort.html on line 281

Warning: include() [function.include]: URL file-access is disabled in the server configuration in /home/users/0/main.jp-bacteria/web/2005/10/using_filesort.html on line 287

Warning: include(http://bacteria.jp//include/footer.php) [function.include]: failed to open stream: no suitable wrapper could be found in /home/users/0/main.jp-bacteria/web/2005/10/using_filesort.html on line 287

Warning: include() [function.include]: Failed opening 'http://bacteria.jp//include/footer.php' for inclusion (include_path='.:/usr/local/php5.2/php') in /home/users/0/main.jp-bacteria/web/2005/10/using_filesort.html on line 287