SQLのクエリを最適化するための「EXPLAIN」

2013年9月15日

SQLのクエリを最適化するための「EXPLAIN」

大量のデータを扱うようになると、SQLも慎重にチューニングする必要が出てきます。今回はMySQLにおいてクエリを最適化するための「EXPLAIN」について書いてみます。

この「EXPLAIN」をどのような時に使用するかというと、データベースに蓄積された情報量が多くなるにつれ動作が遅くなったり、エラーで表示されなくなってしまうような時に使用します。「EXPLAIN」を使用する事でSELECTがどのように処理されているかを確認し、適宜クエリを見直しましょう、という内容です。

「EXPLAIN」で何を確認できるのか、という点については公式サイト等を確認した方が良いところですが、重要なポイントとして「type」という情報があります。

例えば「table」というテーブルに対してこのように実行すると、「type」が「ALL」となっています。

mysql> EXPLAIN SELECT * FROM table;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
|  1 | SIMPLE      | table | ALL  | NULL          | NULL | NULL    | NULL | 2057552 |       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+

これに対して「WHERE」句を追記して実行すると、「type」が「const」となりました。
※uidにPRIMARY KEYが設定されている前提

mysql> EXPLAIN SELECT * FROM table WHERE uid = 10;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | table | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

この「type」には下記のような種類があります。

const　・・・　最速。PRIMARY KEYもしくはUNIQUEインデックス。
eq_ref　・・・　constに並ぶ速度で、JOINにおけるPRIARY KEYまたはUNIQUE KEY。
ref　・・・　ユニーク（PRIMARYもしくはUNIQUE）ではないインデックスを使った等価検索（WHERE key = value）。
range　・・・　インデックスによる範囲検索。
index　・・・　遅い。インデックス全体のスキャン。
ALL　・・・　遅い。全表検索。OLTP系の処理では改善必須。

これらで何となくつかめる通り、「type」が「index」もしくは「ALL」となっていると、表示速度が遅くなってしまう要因となるため、クエリの改善が必要な状態であると言えます。上記例は単純にWHERE句を追記する事で解決していますが、例えばJOINを使用している場合は結合順の見直しも効果がある場合があります。

データベースを利用したコンテンツで表示速度が遅くなったりエラーで表示されない等の事態に陥った場合は「EXPLAIN」を使ってクエリを見直してみましょう。

MySQL, EXPLAIN