MySQLのEXPLAINの読み方とチューニング時のチェックポイント

データベース

EXPLAINを実行することでSQLの実行計画が取得できます。クエリのチューニングにおいてEXPLAINの理解は必須です。
今回はEXPLAINの読み方とクエリをチューニングする際のチェックポイントについて紹介します。

MySQLは5.7.34を利用しています。

EXPLAINで表示される項目について

EXPLAINで表示される項目は以下の通りです。

項目 意味
id クエリの実行順番
select_type クエリの種類
table アクセス対象のテーブル
partitions 利用したpartisionテーブル
type レコードへのアクセス種別
possible_keys オプティマイザが候補として挙げたインデックス一覧
key 実際に利用されたインデックス
ken_len 選択されたキーの長さ
ref 検索条件でkeyと比較される対象
rows 選択されるレコード数の見積もり
filtered 検索条件によって絞り込まれるレコードの割合
Extra オプティマイザがクエリ実行で採用した戦略

補足説明が必要な項目について以下で紹介します。

idについて

複数のクエリがまとめて実行される場合は同一idが複数表に出現します。

JOINの場合は駆動表、内部表の順番でEXPLAINに表示されます。

select_typeについて

単一テーブルに対するクエリとJOINの場合、select_typeはSIMPLEになります。

サブクエリが関係する場合はPRIMARY、SUBQUERY、DEPENDENT SUBQUERY、 NCACHEABLE SUBQUERY、DERIVEDの5種類のうちのいずれかになります。1

クエリにUNIONが含まれる場合はPRIMARY、UNION、UNION RESULT、 DEPENDENT UNION、UNCACHEABLE UNIONの5種類のうちのいずれかになります。1

各select_typeの意味についてはMySQLのEXPLAINを徹底解説!!で詳細に解説されているので参考にしてください。

typeについて

代表的なtypeと意味の対応は以下の通りです。

type 意味
const Primary keyもしくはユニークインデックスによるアクセス。最速。
eq_ref JOIN時にPrimary keyもしくはユニークインデックスを利用したアクセス
ref ユニークではないインデックスによる等価検索
range インデックスを用いた範囲検索
index フルインデックススキャン
ALL フルテーブルスキャン

refについて

定数が指定されている場合はconst、JOINが実行される場合は『結合先のテーブルで利用されている検索条件のカラム』が表示されます。

Extraについて

Extraでよく見かける情報と意味の対応は以下の通りです。

情報 意味
Using where WHERE句を利用した絞り込みが行われている
Using index インデックスだけで解決ができている高速なクエリ
Using filesort filesort（クイックソート）が行われている
Using temporary クエリ実行時にテンポラリテーブルが必要

Extraの詳細についてはMySQL『8.8.2 EXPLAIN 出力フォーマット#EXPLAIN の追加情報』をご覧になってください。

EXPLAINでチェックすべきポイント

EXPLAINの結果を利用してクエリチューニングする際のチェックポイントについて紹介します。

typeに『ALL』『index』が表示されていないか

ALLはインデックスを利用せず、テーブルのフルスキャン（全表走査）が実行されていることを意味します。テーブルのフルスキャンはとても遅い処理です。

indexはALLと違いインデックスこそ使われていますが、当該インデックスをフルスキャンする必要のある遅い処理です。
ExtraのUsing indexはインデックスだけでクエリが解決できることを示すポジティブな指標ですが、typeのindexはネガティブな指標なので注意しましょう。

ただしORDER BYとLIMITを組み合わせた時のように、先頭の数レコードだけを読み込めばよい場合はindexでも大きな問題にならないこともあります。2

EXPLAIN実行後、typeの項目にALLまたはindexが表示されていたら要注意です。

Extraに『Using filesort』『Using temporary』が表示されていないか

Using filesortはソートが実行されていることを表します。
Using filesortはソート対象のレコード数が少なければ問題にならないですが、行数が多い場合はパフォーマンスに影響を与えます。

特にUsing filesortとUsing temporaryの組み合わせは『対象が多すぎるので一時テーブルを作成してソートを行う』ということを意味しているので改善必須です。

当該カラムにインデックスを作成し、クエリ実行時にソートを行わないで済むようにしましょう。

EXPLAINを利用したクエリチューニング例

EXPLAINの結果をみてクエリをチューニングする具体例について紹介します。

ケース1: 検索条件でインデックスが利用されていない

SELECT * FROM users WHERE age = 20;

チューニング前

ageにインデックスが作成されていない場合、EXPLAINの結果は以下のようになります。
なお、SQLの末尾にある\Gは結果を縦出力にするオプションです。

> EXPLAIN SELECT * FROM users WHERE age = 20\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 996364
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

インデックスが作成されていないためpossible_keysやkeyはNULLです。インデックスが利用されないためtypeはALLになります。

typeがALLの場合は適切なインデックスを作成してクエリチューニングをします。

チューニング後

ageにインデックスを作成すると実行計画は以下のようになります。

> EXPLAIN SELECT * FROM users WHERE age = 20\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ref
possible_keys: index_users_on_age
          key: index_users_on_age
      key_len: 5
          ref: const
         rows: 22248
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

keyに新しく作成したインデックス（index_users_on_age）が表示されました。
インデックスが利用されるようになったため、typeがALLからrefにかわりました。

ケース2: ソートでインデックスが利用されていない

SELECT * FROM users ORDER BY age LIMIT 100;

チューニング前

ageにインデックスが作成されていない場合、EXPLAINの結果は以下のようになります。

> EXPLAIN SELECT * FROM users ORDER BY age LIMIT 100\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 996364
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

インデックスが作成されていないためpossible_keysやkeyはNULLです。インデックスが利用されないためtypeはALLになります。
また、ソート処理が実行されているためExtraにUsing filesortが表示されています。

今回の場合、rowsが10万件近くあるためソート対象のカラムにインデックスを作成したほうがよさそうです。

チューニング後

ageにインデックスを作成すると実行計画は以下のようになります。

> EXPLAIN SELECT * FROM users ORDER BY age LIMIT 100\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: index
possible_keys: NULL
          key: index_users_on_age
      key_len: 5
          ref: NULL
         rows: 100
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ExtraのUsing filesortがなくなり、typeがALLからindexに変更されました。
また、rowsはチューニング前の約10万件から、SQLのLIMITで指定された100件になりました。

以上より、インデックスによってソート処理が高速化したことがわかります。

ケース3: 結合条件にインデックスが利用されていない

1対多で紐づくusersテーブルとbooksテーブルを結合し、userのageが30のbooksレコードを取得するSQLをチューニングしてみます。

説明の便宜上、STRAIGHT_JOINを利用してusersテーブルを駆動表、booksテーブルを内部表に固定しています。

SELECT STRAIGHT_JOIN b.* FROM users u, books b WHERE u.id = b.user_id AND u.age = 30\G;

なお、usersテーブルにはageにインデックスが作成されている、booksテーブルにはPrimary key以外にインデックスは作成されていないとします。

チューニング前

結合条件にインデックスが作成されていない場合、EXPLAINの結果は以下のようになります。

JOINの場合は駆動表、内部表の順番でEXPLAINに表示されるため、今回の場合はusersテーブルが駆動表、booksテーブルが内部表となります。

> EXPLAIN SELECT STRAIGHT_JOIN b.* FROM users u, books b WHERE u.id = b.user_id AND u.age = 30\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: u
   partitions: NULL
         type: ref
possible_keys: PRIMARY,index_users_on_age
          key: index_users_on_age
      key_len: 5
          ref: const
         rows: 22196
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2980163
     filtered: 10.00
        Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)

駆動表のレコードの絞り込みをする1. rowのクエリではインデックス（index_users_on_age）が利用されているので問題なさそうです。
しかし、内部表を検索する2. rowのクエリのtypeがALLになっているのでチューニングが必要そうです。

結合条件はu.id = b.user_idですので、booksテーブルのuser_idに対してインデックスを作成すれば改善できそうだということがわかります。

MySQLは外部キーに対して自動でインデックスを作成しますが、今回はわざと外部キーのインデックスを削除した状態でEXPLAINを実行しています。

チューニング後

booksテーブルのuser_idにインデックスを作成すると実行計画は以下のようになります。

> EXPLAIN SELECT STRAIGHT_JOIN b.* FROM users u, books b WHERE u.id = b.user_id AND u.age = 30\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: u
   partitions: NULL
         type: ref
possible_keys: PRIMARY,index_users_on_age
          key: index_users_on_age
      key_len: 5
          ref: const
         rows: 22196
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ref
possible_keys: index_books_on_user_id
          key: index_books_on_user_id
      key_len: 5
          ref: rails6_api_mysql8_development.u.id
         rows: 3
     filtered: 100.00
        Extra: Using index condition
2 rows in set, 1 warning (0.00 sec)

結合条件でインデックスが利用されるようになったため、2番目のクエリのtypeがALLからrefにかわりました。

まとめ

EXPLAINのチェックポイント
  • typeに『ALL』『index』が表示されていないか
  • Extraに『Using filesort』『Using temporary』が表示されていないか

Twitter(@nishina555)やってます。フォローしてもらえるとうれしいです！

参考

  1. MySQLのEXPLAINを徹底解説!! ↩ ↩
  2. MySQL実行計画（EXPLAIN）の見方 ↩
RECOMMEND