ソリッドシード株式会社

EXPLAINでSQLが遅い原因を見つける

2021年3月26日

システムの実行速度が遅い原因としてMySQLやPostgreSQLなどのデータベースがボトルネックとなる場合があります。
データベースのボトルネックの解消方法としてインデックスを適切に設定しチューニングを行う方法があります。
インデックスは闇雲に設定しても正しく使用されず、設定しすぎるとINSERTやUPDATEで負荷がかかり処理が遅くなる可能性があります。
そこでEXPLAINの出番となります。

EXPLAINで何が分かるのか？

EXPLAINはインデックスが適切に使用されているかの実行計画を把握する事ができます。
実行計画とはテーブルがフルスキャンでデータ取得しているか、インデックスを使用して特定範囲をスキャンしてデータ取得しているかの結果です。
結果にはステータスが記述され、このステータスからどのように実行されているか把握する事ができます。

MySQLのEXPLAINのステータスになります。
例えばtypeにALLやindexが記述されていたらインデックスが正しく使用されてないので遅いので改善が必要で、constだとPRIMARY KEYまたはUNIQUEが使用されているので最速と判断できます。

  • select_type：クエリの種類
    • SIMPLE：UNIONやサブクエリを使用しない単純なSELECT文
    • SUBQUERY：FROM句ではないサブクエリに含まれているSELECT文
    • DERIVED：FROM句のサブクエリに含まれているテーブルのSELECT文
    • PRIMARY：UNIONの1つ目のSELECT文
    • UNION：UNIONの2つ目以降のSELECT文
    • UNION_RESULT：UNIONの無名の一時テーブルから結果を取得するためのSELECT文
  • table：参照テーブル
  • type：テーブル内の行を検索する方法
    • ALL：フルテーブルスキャンでインデックスがまったく利用されていないことを示す
    • index：フルインデックススキャンでインデックス全体をスキャンする必要がある
    • range：インデックスを用いた範囲検索
    • ref：ユニーク（PRIMARY or UNIQUE）でないインデックスを使って等価検索（WHERE key = value）を行った時に使われるアクセスタイプ
    • eq_ref：JOINにおいてPRIARY KEYまたはUNIQUE KEYが利用される時のアクセスタイプ
    • const：PRIMARY KEYまたはUNIQUEインデックスのルックアップによるアクセス
  • possible_keys：利用可能なインデックスの候補
  • key：実際に利用されたキー
  • key_len：選択されたキーの長さ
  • ref：keyとともに使用されるカラムまたは定数
  • rows：フェッチされる行数の見積もり
  • Extra：その他の情報
    • Using where：WHERE句に検索条件が指定されており、なおかつインデックスを見ただけではWHERE句の条件を全て適用することが出来ない場合に表示される
    • Using index：クエリがインデックスだけを用いて解決できることを示し、Covering Indexを利用している場合などに表示される
    • Using filesort：filesort（クイックソート）でソートを行っていることを示す
    • Using temporary：JOINの結果をソートしたり、DISTINCTによる重複の排除を行う場合など、クエリの実行にテンポラリテーブルが必要なことを示す
    • Using index for group-by：MIN()/MAX()がGROUP BY句と併用されているとき、クエリがインデックスだけを用いて解決できることを示す
    • Range checked for each record (index map: N)：JOINにおいてrangeまたはindex_mergeが利用される場合に表示される
    • Not exists：LEFT JOINにおいて、左側のテーブルからフェッチされた行にマッチする行が右側のテーブルに存在しない場合に右側のテーブルはNULLとなるが、右側のテーブルがNOT NULLとして定義されたフィールドでJOINされている場合にはマッチしない行を探すのを示す

どのように使用するか？

使用する方法は対象のSELECT文の先頭にEXPLAINを付けるだけです。
例えば「test」というテーブルがあり「SELECT * FROM test」の実行計画を見る場合は「EXPLAIN SELECT * FROM test」と実行します。
実行すると下記のように表示されます。

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

ちなみにPRIMARY KEYで検索したSELECT文で実行すると下記のようになります。

> EXPLAIN SELECT * FROM test WHERE id = 1;
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

データベースのボトルネックを調査する時はスロークエリを見て遅いSELECT文を抽出すると思います。
抽出したSELECT文にEXPLAINで実行して遅い原因を特定してチューニングすると速度が向上します。

投稿者：Saito