Open Source Web Development Tutorials - Dev Shed

MySQLクエリー最適化およびスキーマ設計
（2009/01/26公開）

クエリー最適化

　読者にとってベストのスキーマが完成したら、クエリーを指定し最大限に活用する時間だ。クエリー最適化に関して、押さえておくべきコマンド、ヒント、指針がいくつかある。ただ、いつもそうだが、とり上げる内容は一般的なガイドラインに過ぎず、読者独自のニーズに適応させる必要がある。読者固有のケースに当てはまる度合いそれぞれ異なるであろう。

　まず、explain文をご紹介しよう。非常に有用なコマンドで、どのクエリー／インデックスがパフォーマンスを向上／低下させるかを判断するのに役立つ。簡単に言えば、explain文はdescribeと同義で、MySQLがselect文を実行する方法に関する情報を集めたり見つけ出したりするのに使える。explain文はクエリー実行計画に関する情報を返す。そして、最適化に使用できる。

　これは恐らく、クエリーの最適化に使うコマンドとしてはもっとも普及しているものの1つだ。同コマンドには多くのパラメータや引数があるから、MySQL Referenceの公式説明書をぜひとも参照していただきたい。

　次へ進もう。ジャンプキューを操作したり、特定のコマンドの実行をもう一歩進めたりしたいときは優先順位を利用する。よくあるのは、select high_priorityとinsert low_priorityを使用したいというシナリオだ。このような状況では、コマンドは優先順位に従って実行される。例えば、テーブルは残りのコマンドより先に選択される。

　ディスク入出力を節約するのは重要な習慣だ。どうすればよいか。1つの方法は、insert delayedをできるだけ頻繁に使うことだ。ここでは、データをすぐに必要としない状況に言及している。修正事項はメモリに保存されているが、ディスクに実際に書き込まれはしない。後ほど、追加的なシークや入出力活動なしに、すべての変更が同時に書き込まれる。これが起きるのは、クライアントがテーブルを閉じるときだ。

　ときどき、MySQLテーブルから多くの行を削除する必要が生じることがあるだろう。その場合、optimize tableを使ってテーブルのデフラグを行うと役立つかもしれない。テーブルにかなり変更が加えられたあと、このコマンドがクエリーのパフォーマンスにどれほどの影響を与えるかをチェックすることができる（変更の前とあとにベンチマーキングを行い、結果を比較する。パフォーマンス向上の保証はないが）。

　シンプルに。そう、シンプルに。もう一度言おう。シンプルに、だ。複雑なパーミッションをもっとシンプルなものに分割する。そうすると、パーミッションチェック業務費を削減できる。MySQLは、許可されたコマンドの実行後に定期的なチェックを行う。シンプルさと言えば、クエリー（whereなど）から不要なカッコを除去していただきたい。カッコがあると見やすいが、カッコ1つにつきナノ秒かミリ秒だが、積もり積もると遅延につながる。

　それから、キャッシュについて実験したり試行したりしていただきたい。SQL_NO_CACHEコマンドをお忘れのないように。同コマンドは、MySQL内蔵のクエリー自動キャッシング機能を無効にする。業務に習熟している読者にとって、クエリーキャッシングに関する明確なヒントは非常に役立つであろう。その場合、内蔵した機能を無効にすることでキャッシングに関するヒントを最大限に活用できる。

　上記では、読者がすでにクエリーキャッシング機能を有効にしていると想定している。だが、必ずしも有効になっているわけではない。有効になっているか見当もつかない場合は、MySQL公式ウェブサイトに掲載された記事が非常に助けになるであろう。クエリーキャッシング機能について知るべきことが網羅されている。

　そして、処理の遅いクエリーを特定し、問題を軽減すべく研究するのは読者の仕事だ。遅いクエリーを記録するMySQLの機能を有効にする必要がある。そうすると、ログファイルを見るだけで、処理に時間がかかり過ぎるクエリーが見分けられる。Lock_timeなどを使用して、ロックのゆえにクエリーが不必要に待たされた時間を示すデータも入手できる。

　最後に、MySQLに付属したPerlスクリプトmysqldumpslowも試していただきたい。この小粋なユーティリティーからは処理の遅いクエリーが実行される頻度が分かる。これは重要だ。特定の遅いクエリーが実行される頻度と待ち時間の長さが分かれば、クエリーを最適化する際の優先順位を決定できるようになる。

　理想的には、必ずしも処理時間は長くないが頻繁に実行されサーバーに大きな負荷（スパイク）をかけるクエリーも最適化すべきだ。負荷が大き過ぎると、サーバーが処理しきれなくなり、思ったように応答しなくなることがある。クエリーをキャッシュするとよい。memcacheやキャッシュテーブルを試していただきたい。大切な点として、結果ではなくオブジェクトをキャッシュすること。前者は再使用できるが、後者はできない。