MySQLの最適化-2

※以前別の場所で書いた文章を備忘的に書き記しておきます。

【投稿年月日】2007-06-04 【ジャンル】PHP/MySQL

 最近「EXPLAIN SELECT ~」を利用してMySQLの最適化作業を行なったので備忘的にメモを残しておきます。なお、「EXPLAIN SELECT ~」を利用するにあたっては「phpMyAdmin」を使うのが最も手軽かと思われます。
 

【「EXPLAIN SELECT ~」を使った最適化の確認方法】

  1. phpMyAdminの「SQL」画面で、SQLクエリ(SELECT文)を実行
  2. 「実行時間」のチェック(●重要)
  3. 「Explain SQL」(or「SQLを分析する」)をクリックして、「EXPLAIN SELECT ~」を呼び出す(●重要)
 「EXPLAIN SELECT ~」の主な結果は以下の通りです。より詳しくは5.2.1. EXPLAIN 構文(SELECT に関する情報の取得)をご覧下さい。
項目
説明
備考
table
テーブル名
呼び出されたテーブルごとに結果表示
type
SELECT節の種類
「ALL」はダメ
possible_keys
使用可能なインデックス
「NULL」はダメ
key
実際に使用したインデックス
「NULL」はダメ
rows
調べる必要のあるレコード数
少ないほど良い
Extra
追加情報
「Using filesort」は最悪、「Using temporary」はダメ

 「EXPLAIN SELECT ~」で、「最悪」あるいは「ダメ」が多いテーブルについては、SELECT文のどこかにボトルネックがあるので見直す必要があります。ただ、実行時間がさほど遅くなければ、そんなに神経質にならなくてもいいと思いますが。

 あまりよくない結果が出る理由としては以下のようなケースが考えられます。



 せっかくですから、最適化のためのポイントについて書き残しておきます。最適化のポイントについては「MySQLの最適化」でも触れているので合わせてご覧下さい。

 「DISTINCT」を使わなければデータを絞り込むことができないようなSELECT文では、不要なフィールドを呼び出している可能性があります。呼び出すフィールドの数が多くなれば、それだけ速度が遅くなりますし、「DISTINCT」を使うことで余計な処理が加わるため効率が悪くなります。
 本当に必要なフィールドかどうか徹底的に検討した上で、「DISTINCT」を極力使わないSELECT文を目指した方がいいと思います。このWebサイトでSELECT文を見直したところ、「DISTINCT」を使わない場合、使ったときに比べて2~10倍(あるいはそれ以上)「実行時間」が早くなりました。

 テーブル結合は「LEFT JOIN」を使用した方が一般的に早くなるようです。「LEFT JOIN」ができないテーブル同士は関連性が薄いと思われるので、テーブル設計のレベルから見直した方がいいと思います。既存のフィールドを見直したり、新しくフィールドを作成したりすることで、「LEFT JOIN」できるようにテーブルを設計し直すのです。
 劇的に早くなるわけではありませんが、一定の効果が期待できます。

 インデックス情報は、テーブルのデータが変更された際、自動的に更新されないようです。更新されていないインデックスをもとに、SELECT文を実行しても最適な結果を得ることはできませんので、定期的に「ANALYZE TABLE」を実行してインデックスを最新状態に保った方がいいみたいです。

EDIUNET | PHP/MySQL | 独り言 | 提供サービス | JavaScript