Enjoy IT Life

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

（更新: 2021-07-10）
データベース

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意味
constPrimary keyもしくはユニークインデックスによるアクセス。最速。

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

refについて

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

Extraについて

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

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

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

MySQL 8.0で追加されたEXPLAIN拡張機能

本記事のEXPLAINは表形式（縦出力\G）の出力を扱っていますが、MySQL 8.0では実行計画をより詳細に確認するための機能が追加されています。

機能導入バージョン用途
EXPLAIN FORMAT=TREEMySQL 8.0.16実行計画をツリー形式で表示。アクセス述語・コスト見積もりを把握しやすい
EXPLAIN ANALYZEMySQL 8.0.18実際にクエリを実行し、各ノードの実時間ベースの計測値を出力する
EXPLAIN FORMAT=JSONMySQL 5.7+実行計画をJSON形式で詳細出力。コスト・使用バッファ等を取得できる

EXPLAIN FORMAT=TREE

EXPLAIN FORMAT=TREE SELECT * FROM users WHERE age = 20;

-> Index lookup on users using index_users_on_age (age=20)  (cost=2224.80 rows=22248)

ツリー形式では各ノードにアクセス述語（age=20）と推定コスト・行数が出力されます。

複合インデックスの利用状況や、どこまでが絞り込みに使われているかを把握しやすい形式です。

EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM users WHERE age = 20;

-> Index lookup on users using index_users_on_age (age=20)
   (cost=2224.80 rows=22248) (actual time=0.045..18.220 rows=22248 loops=1)

actual timeは各ノードの実時間（先頭行までの時間..最終行までの時間）、rowsは実際に返された行数、loopsはそのノードが評価された回数です。

見積もり（estimated rows / cost）と実測値（actual rows / time）の乖離を見ることで、統計情報の鮮度や偏りの問題を発見できます。

EXPLAIN ANALYZEはクエリを実際に実行するため、UPDATEやDELETEでの使用には注意してください。

アクセス述語とフィルタ述語の判別

複合インデックスのチューニングでは、検索条件のうちどこまでがインデックス走査に使われ、どこからがインデックス外でのフィルタリングになっているかを切り分けることが重要です。

  • アクセス述語: B-tree走査で絞り込みに使われる条件
  • フィルタ述語: 走査範囲は変えず、走査中の行を判定する条件

EXPLAIN FORMAT=TREEでは、Index lookup on ... (条件)がアクセス述語、Filter: ...がフィルタ述語に対応します。これにより、複合インデックスの先頭から何列までが効いているかが直接確認できます。

詳細は【図解】B-treeを理解し、複合インデックスの順番を正しく作るを参照してください。

参考: PostgreSQLのEXPLAIN

PostgreSQLの場合はEXPLAIN (ANALYZE, BUFFERS)で実時間ベースの計測値とバッファアクセス情報が取得できます。

出力例は以下の通りです。

Index Scan using idx_users_age on users  (cost=0.42..892.15 rows=22000 width=...)
                                         (actual time=0.020..15.430 rows=22248 loops=1)
  Index Cond: (age = 20)
  Filter: (status = 'active')
  Rows Removed by Filter: 130
  Buffers: shared hit=300 read=120
項目意味
Index Cond:アクセス述語（インデックスで絞り込みに使われた条件）
Filter:フィルタ述語（インデックス外で評価された条件）
Rows Removed by Filterフィルタ述語で捨てた行数（多ければ複合インデックスの追加列を検討）
Buffers: shared hit/readバッファキャッシュヒット数とディスク読み込み数

PostgreSQLでは標準でIndex Cond:とFilter:が分かれて出力されるため、述語の切り分けがMySQLより容易です。

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;

*************************<strong> 1. row </strong>*************************
           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;

*************************<strong> 1. row </strong>*************************
           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;
*************************<strong> 1. row </strong>*************************
           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;
*************************<strong> 1. row </strong>*************************
           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;

*************************<strong> 1. row </strong>*************************
           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
*************************<strong> 2. row </strong>*************************
           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に対してインデックスを作成すれば改善できそうだということがわかります。

チューニング後

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

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

*************************<strong> 1. row </strong>*************************
           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
*************************<strong> 2. row </strong>*************************
           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』が表示されていないか
  • 複合インデックス利用時はEXPLAIN FORMAT=TREE/ANALYZEでアクセス述語/フィルタ述語まで確認する
  • EXPLAIN ANALYZEで見積もりと実測値の乖離が大きい場合は統計情報の更新を検討する

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

参考

  1. MySQLのEXPLAINを徹底解説!! ↩ ↩
  2. MySQL実行計画（EXPLAIN）の見方 ↩
タグ: MySQL , パフォーマンスチューニング