LoginSignup
12
5
ANDPADAdvent Calendar 2023Day 21andpad
@mamy1326in株式会社アンドパッド

MySQL の EXPLAIN を読むときの勘所

Last updated at Posted at 2023-12-21

ANDPAD アドベントカレンダー 2023 の 21 日目の記事です。

DBRE として活動している まみー です。
今回は 以前の登壇資料 や個人的にメモしていた「EXPLAIN を読むときの勘所」を解説します。

EXPLAIN を読む理由

アプリケーション開発の現場で全員が「必ず EXPLAIN 読みます！」というチームは少ないと思います。
とはいえ、Web システムのパフォーマンスが低下しユーザーや利益に影響を与える場合、原因は DB の扱いによるものが多い です。
その原因の根本はアプリケーションが実行している SQL によるものがほとんどです。

SQL はインデックスを用いた実行計画に基づいて処理されます。
この実行計画に関する情報を得るためのステートメントが EXPLAIN です。
EXPLAIN を読むことで、SQL が効率的に動いているのかいないのか、がより鮮明 になります。

作ったサービスを使ってもらえるからこそ負荷問題が表面化するとも言えます。
高いパフォーマンスとレスポンスを提供するためにも、そして何より開発者が幸せになるためにも、SQL が効率的かどうかを見ていきましょう。

TL;DR

EXPLAIN の各項目を複合的に読んでいきましょう。

  • type, key, rows, Extra を見る
  • type, key, rows, Extra を複合的に判断する

また、以下を設計・実装時に考慮し続けていけば、よりパフォーマンス改善しやすくなります。

  • SQL は原則的にインデックスで検索するものと心得る
  • レコードが増え続けた場合を想定して要件を決め絞り込む

EXPLAIN まずはここから

複数の項目を組み合わせて読むと効果的です。
以下について、順に説明していきます。

  1. EXPLAIN 実行方法
  2. type
  3. key
  4. rows
  5. Extra

EXPLAIN 実行方法

  • SELECT の手前に EXPLAIN をつける
  • 末尾は ; と \G を使い分ける
    • \G
      • エビデンスとして貼る、誰かに解説する場合に縦読みできて使いやすい
    • ;
      • 複数 JOIN でテーブルを比較したい場合は表形式で見やすい
  • 実行後に show warnings;
    • オプティマイザが書き換えた SQL が見れる
    • チューニングのヒントにもなるので確認してみよう

実際に実行してみた例を記載します。

; の場合
mysql> explain select * from test inner join test_child on test_child.test_id=test.id where test.nullable is not null;
+----+-------------+------------+------------+--------+----------------------+---------+---------+-------------------------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys        | key     | key_len | ref                     | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+----------------------+---------+---------+-------------------------+------+----------+-------------+
|  1 | SIMPLE      | test_child | NULL       | ALL    | idx_test_id          | NULL    | NULL    | NULL                    |    2 |   100.00 | NULL        |
|  1 | SIMPLE      | test       | NULL       | eq_ref | PRIMARY,idx_nullable | PRIMARY | 8       | test.test_child.test_id |    1 |    33.33 | Using where |
+----+-------------+------------+------------+--------+----------------------+---------+---------+-------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
\G の場合
mysql> explain select * from test inner join test_child on test_child.test_id=test.id where test.nullable is not null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_child
   partitions: NULL
         type: ALL
possible_keys: idx_test_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,idx_nullable
          key: PRIMARY
      key_len: 8
          ref: test.test_child.test_id
         rows: 1
     filtered: 33.33
        Extra: Using where
2 rows in set, 1 warning (0.00 sec)
show warnings\G
mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test`.`test`.`id` AS `id`,`test`.`test`.`name` AS `name`,`test`.`test`.`nullable` AS `nullable`,`test`.`test_child`.`id` AS `id`,`test`.`test_child`.`test_id` AS `test_id`,`test`.`test_child`.`test_value` AS `test_value` from `test`.`test` join `test`.`test_child` where ((`test`.`test`.`id` = `test`.`test_child`.`test_id`) and (`test`.`test`.`nullable` is not null))
1 row in set (0.00 sec)

explain_format=TREE

MySQL 8.0.32 から導入された explain_format システム変数 を利用すると、TREE 形式で EXPLAIN を表示することも可能 になります。
以下は公式からの引用です。JOIN やサブクエリの親子関係がより読みやすくなっています。

explain_format=TREE
mysql> SET @@explain_format=TREE;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@explain_format;
+------------------+
| @@explain_format |
+------------------+
| TREE             |
+------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT Name FROM country WHERE Code LIKE 'A%';
+--------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                      |
+--------------------------------------------------------------------------------------------------------------+
| -> Filter: (country.`Code` like 'A%')  (cost=3.67 rows=17)
    -> Index range scan on country using PRIMARY over ('A' <= Code <= 'A????????')  (cost=3.67 rows=17)  |
+--------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

format=json

TREE と比べるとちょっとややこしいですが、EXPLAIN の直後に format=json と書くとオプティマイザが計算したコストが見れます。
オプティマイザトレース せずともコストが見たい！というときに便利です。

作ったインデックスが使われない、先に選択されるはずのインデックスが選択されない、などの場合にコストを見るとヒントが得られることがあります。

コストの読み方などは コチラ など参考にしていただけると幸いです。

mysql> explain format=json select * from test inner join test_child on test_child.test_id=test.id where test.nullable is not null
    -> \G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.15"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "test_child",
          "access_type": "ALL",
          "possible_keys": [
            "idx_test_id"
          ],
          "rows_examined_per_scan": 2,
          "rows_produced_per_join": 2,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.20",
            "prefix_cost": "0.45",
            "data_read_per_join": "224"
          },
          "used_columns": [
            "id",
            "test_id",
            "test_value",
            "updated_at",
            "updated_at_2"
          ]
        }
      },
      {
        "table": {
          "table_name": "test",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY",
            "idx_nullable"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "8",
          "ref": [
            "test.test_child.test_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 0,
          "filtered": "33.33",
          "cost_info": {
            "read_cost": "0.50",
            "eval_cost": "0.07",
            "prefix_cost": "1.15",
            "data_read_per_join": "101"
          },
          "used_columns": [
            "id",
            "name",
            "nullable",
            "update_at",
            "updated_at",
            "teest_updated_at"
          ],
          "attached_condition": "(`test`.`test`.`nullable` is not null)"
        }
      }
    ]
  }
}
1 row in set, 1 warning (0.00 sec)

type

一番最初に見ます。普段多く見かける実例を列挙してみます。
特に ALL index はパフォーマンス改善に対するなんらかの対応が必須と言えます。

ALL

  • フルスキャン=全レコード検索
  • インデックスが全く使用されていない状態、もっとも遅い
  • クエリやインデックスの抜本的な見直しが必要

index

  • インデックスフルスキャン
    • インデックスでの検索とはいえ全レコード検索している
    • インデックスだけでは絞り込めない場合に起きる
  • 単純に検索対象が多い (範囲が広いなど) 場合がある
  • クエリやインデックスの抜本的な見直しが必要

range

  • 範囲での絞り込みが行われている
  • key で適切なインデックスが選択され、rows で十分に絞り込まれたレコード数であれば高速と言える
  • rows が想定より大幅に多い場合は要注意
    • 全レコード数に対する検索対象レコード数の割合が多い
    • 想定より絞り込めていない
      • 1000 件ヒット想定のはずが実際の検索対象が多く rows は 200000 件…など差が大きい場合など
  • 範囲を指定していなくても range になる
    • IN 句や 複数の AND, OR 句指定の場合は、各 const の値と値の間を検索するという意味で range になることがある
  • 範囲や絞り込み条件の見直しが必要な場合がある

const

  • PK または UK で特定のレコード狙い撃ちで検索できている
  • 等価比較時のみ
  • 最高速
  • 検索対象 1 件
  • これ自体は問題にならない

key

オプティマイザ (MySQL のエンジン部分) が選択したインデックスです。
候補は possible_keys に表示されており、その中から選択されます。

実際に使用されるインデックスが表示される

インデックスを適切に選択し、実行時間が最小になるよう処理方法を決めていて、その過程で選択されます。
想定と異なる場合は、なんらかの対応が必要と言えます。

想定と異なる場合

WHERE 句などを調べるかインデックスを調整する必要がある状態と言えます。

  • 単純にインデックスが不足している
    • 単一・複合インデックスを追加する
  • インデックスの実態が最適化に沿っていない
    • データ量が少ない時代は効いていたが今は変わってしまい遅くなっている…
      • うっかり広い範囲を指定している場合などに起こり得る
    • 統計情報の再作成が必要な場合もある
      • 負荷がそこそこ高いのでピーク時を避けるのが吉

rows

検索対象の想定レコード数で、もっとも重要な項目 です。
type や Extra に何が出ていたとしても、rows が小さければ SQL は高速に実行されます。 1
逆に type や Extra に納得のいく結果が表示されていても、rows が大きければパフォーマンスの悪い SQL と言えます。
十分に絞り込めているか必ず確認 しましょう。

想定より多い場合

rows が想定より大幅に多い場合は注意が必要です。
以下の原因でパフォーマンス低下を引き起こす可能性があります。

  • 絞り込み条件不足
  • サービスの成長に伴い対象レコード数増加

rows が多いということは、絞り込めていないと言えます。
効率的に SQL を実行し、目的のデータを取得する必要があります。
仕様上、絞り込み条件がこれ以上ない場合は、要件や利用シーンなども見直してみましょう。

Extra

Using index condition

複合インデックスを効率的に使っていて、高速に動いていると判断できます。

  • 複合インデックスを効率的に使っている、とは
    • Index Condition Pushdown という MySQL 独自のインデックス最適化の仕組みが機能している
    • MySQL 5.6 から実装された
  • 対象レコード数が多いと遅くなる可能性があるので rows の割合は必ず見る

Using index

高速に動いていると判断できます。

  • カバリングインデックス (SELECT 句で取得するカラムもインデックス内にある) での検索時など、インデックスで検索が完結する場合に表示される
  • rows を見て判断
    • 対象レコード数が多い場合はパフォーマンス低下の可能性があるので注意

何も表示されない

  • type, key, rows を見て判断
    • 範囲検索、想定のインデックス選択、適度な絞り込みレコード数であれば問題ないと言える

Using filesort

クイックソートで遅い場合によく表示されます。ORDER BY の条件を見直す、対象レコード数を絞るなどなんらかの対応をした方が良いです。

  • JOIN と ORDER BY の組み合わせでよく見る
  • メモリと実ファイルでクイックソートしている
  • 駆動表 (最初に対象となるテーブル) と ORDER BY が別テーブルの場合でも出る
    • 駆動表は WHERE 句などの条件によりオプティマイザが自動で判断を変える場合がある
      • FROM の直後に書いたテーブルとは別のテーブルが駆動表となることがある
        • show warnings で確認しよう！
  • GROUP BY の暗黙の ORDER BY でも出る
    • MySQL 8.0 系からは出ない
  • LIMIT 前のレコード数が多ければ遅いと言える
  • Using temporary が一緒に出ることが多い

まとめ

EXPLAIN を読むときの勘所として、各項目を複合的に読もう！を書いてきました。
これさえやっておけば大丈夫！というような決まり手がないのが EXPLAIN とも言えます。
複合的に各項目を見て情報を積み重ね、インデックスを作ったり、検索条件を見直したりして、クエリチューニングの数を重ねていきましょう！

複合的に見る

  • type, key, rows, Extra などを合わせて判断する
    • type=ALL type=index は対応が必要
    • インデックスが効いていても対象レコード数 rows が多いと遅い可能性あり
    • WHERE 句で const 指定していても type=range になってしまっている
      • IN 句の中の値が多くて遅いなど
    • etc...

数を重ねる

EXPLAIN は読む数を重ねていけば必ず読めるようになります。
とはいえ最初にどれから読めばいいの…？となることが多いので、この記事がみなさんの EXPLAIN LIFE に寄与できればいいなと思います。

EXPLAIN 後の対処例

EXPLAIN の読み方はなんとなくわかった、と仮定したとして、ではその対策は？が次の段階だと思います。
ここではその対策の一例を紹介してみます。

インデックスの使われ方を調べる

インデックスは作っただけではなく、本当に想定通りに使われているかを確認してこそです。
以下の内容でどのように使われているかチェックし、不足していれば対応しましょう。

  • インデックス不足であれば追加できないか？
  • インデックスで検索しきれていなければさらに絞り込めないか検討する
  • 得られた情報から判断し、適切なインデックスを作る

検索条件をさらに絞り込む

インデックスをいくら追加してもパフォーマンスが改善しないことは多々あります。
その原因の中に、絞り込み条件が足りない場合があります。
対策するために以下を検討してみましょう。

  • インデックスを作っても遅い場合は検索条件が足りないことが多い
  • さらに絞り込み条件を追加できないか、範囲を絞れないか検討する
  • どうしても絞り込み条件の追加が難しい場合は…
    • バッチ処理や遅延処理ができないか検討する
    • LIKE 検索を多用する場合は ElasticSearch など全文検索を検討する
    • 本当にリアルタイムで処理すべきものなのか疑うことも大切

Appendix

インデックス作成のコツ

  • SQL に出てくるテーブルに対して、使えるインデックスは 1 つだけ
  • JOIN 句に書いた ON 以外の句は WHERE 句になる
    • show warnings で確認するとインデックスを作りやすい
  • より効率のいい絞り込み条件のカラムからインデックスは使われる
  • 以上を考慮して複合インデックスを作成する

オプティマイザは SQL を書き換える

EXPLAIN すると 1 warning と表示されることがあります。
これはオプティマイザが SQL を書き換えたことを示すのですが、確認してみるとチューニングのヒントになることがあります。

  • オプティマイザは SQL を書き換えることがある
  • 要求された結果を効率的に返すため、実態 (統計情報) を鑑みて JOIN 順や WHERE 句の内容を書き換える
  • EXPLAIN すると 1 warning と出ていることがある
  • EXPLAIN 直後に show warinigs を実行すると書き換えた結果が見れる
  • この結果を見て、より効率的な SQL を構築するのも一手

  1. type=ALL, type=index などのフルスキャンや Extra に問題があるように見えても、対象のレコード数が少なければ (rows が小さければ) 問題ないとも言えます。 ↩

12
5
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
12
5