Zenn
レバテック開発部
🐬

【MySQL】実行計画は３つに分けて読む

もりた
2024/03/01に公開
MySQL
SQL
クエリチューニング
tech

これはなに

どうも、レバテック開発部のもりたです。

今回はSQLチューニングをする上で欠かせない実行計画の読み方についてまとめます。この手の情報、インターネットに無限に資料あるんですが、全ての項目を上から読んでいくとマジで目が滑ってなにも頭に入ってこないので、それぞれの関係性がわかりやすい形で整理します。また、公式ドキュメントには言葉足らずな箇所もあるため、そちらも補足しています。
なお、対象とするRDBMSはMySQLです。また、インデックスの解説や実際のチューニングのコツ等は扱っていません。

実行計画の取得方法

いちおう記載しておきますが、実行計画の取得方法は以下の通りです。

-- 実行計画を見たいクエリの前にEXPLAINと書くだけ
EXPLAIN
SELECT ...(略)...
;

-- 結果
-- こんな感じで出る。表示のされ方は使っているクライアントアプリ次第
/*
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, INSERT, follows, , ALL, , , , , , ,
*/

その他、MySQLWorkbenchの機能としてVisual EXPLAINがありますが、今回は触れません。

参考: [MySQL Workbench] VISUAL EXPLAIN でインデックスの挙動を確認する - LINE Engineering

実行計画の説明

以前もりたが書いた記事（SQLチューニングとは宣言型言語でリファクタリングをするということ）でも紹介しましたが、実行計画の表示項目はだいたい３つに分類できます。（読んでない人は先に読んでね）

  • 名札情報（id, select_type, table, partitions）
  • 処理内容の情報
  • EXTRA

今回もその分類に合わせて解説していきます。

1. 名札情報（id, select_type, table, partitions）

これらは記載された行がどのSELECTを対象にしているかを示しています。

  • id
    • 単なる通し番号。実行順というわけではないので注意
  • select_type
    • 対象のSELECTがどんな形のSELECTかを示す。例えばFROM句のサブクエリだとかそういう情報。INSERT文やUPDATE、DELETEだとその旨記載される
    • ここがわかっていると問題のある箇所を特定しやすい
    • 表示される値は後述します
  • table
    • どのテーブルが参照されたのかという情報
  • partitions

1-1. select_typeの詳細

公式では以下の通り分類されています。ただ、若干わかりにくい単語もあるので補足します。

select_type 値 JSON 名 意味
SIMPLE なし 単純な SELECT (UNION やサブクエリーを使用しません)
PRIMARY なし もっとも外側の SELECT
UNION なし UNION 内の 2 つめ以降の SELECT ステートメント
DEPENDENT UNION dependent (true) UNION 内の 2 つめ以降の SELECT ステートメントで、外側のクエリーに依存します
UNION RESULT union_result UNION の結果。
SUBQUERY なし サブクエリー内の最初の SELECT
DEPENDENT SUBQUERY dependent (true) サブクエリー内の最初の SELECT で、外側のクエリーに依存します
DERIVED なし 導出テーブル
DEPENDENT DERIVED dependent (true) 別のテーブルに依存する導出テーブル
MATERIALIZED materialized_from_subquery 実体化されたサブクエリー
UNCACHEABLE SUBQUERY cacheable (false) 結果をキャッシュできず、外側のクエリーの行ごとに再評価される必要があるサブクエリー
UNCACHEABLE UNION cacheable (false) キャッシュ不可能なサブクエリー (UNCACHEABLE SUBQUERY を参照してください) に属する UNION 内の 2 つめ以降の SELECT

引用元：8.8.2 EXPLAIN 出力フォーマット - MySQL

2. 処理内容の情報（type, possible_keys, key, key_len, ref, rows, filltered）

ここではどんな処理が行われているのかが示されています。

  • type
    • どのようにデータにアクセスするのかのタイプ
    • ここが一番重要かつ種類も多いので後述します
  • possible_keys
    • 候補となるインデックスの一覧
    • NULLなら新たにインデックス追加することでクエリの速度を改善できるチャンス
  • key
    • 実際に使われたインデックス
    • カバリングインデックスを使った場合、possible_keysに上がってないインデックスが乗ることもある
  • key_len
    • インデックスに使ったカラムの長さ
    • インデックスはキー長が短い方が高速に動くため、そこを判断するための指標。長い場合はハッシュ化するとかするといい（気がする。やったことないけどできそうって意味）
  • ref
    • インデックスと比較する定数またはカラム。JOINされている場合はJOIN先のカラムが表示される
    • これをどう活かすのかはいまいちわかってない
  • rows
    • クエリ実行する時に取ってくる行数。JOIN後の場合はJOIN元1レコードに対しての行数。
    • これがでかけりゃクエリも重い
  • filtered
    • フィルタされて生き残った割合。100だとフィルタされてないということ

2-1. typeの詳細

typeはかなり重要。公式の内容を元に以下の通りまとめたが、かなり行間を読んで意味が通るようにしてあるので、もし間違いがあったら是非ご指摘ください。ほんまに頼みます。

  • system
    • テーブルに行が 1 つしかないケース (= system テーブル)
  • const
  • eq_ref
    • JOIN時に主キー、ユニークインデックスが見られるケース。内部表のキーにユニークインデックスが張られているということ
    • constとの違いは検索ではなく結合にインデックスが張られているという点
  • ref
    • ユニークでないインデックスで等価検索した場合
    • constとの違いはユニークではないためカーディナリティの問題で劣るという点
  • fulltext
  • ref_or_null
    • refとほぼ同じだが、結合条件に or is nullが入っている
  • index_merge
  • unique_subquery
    • INサブクエリ内でeq_refの検索が行われたもの
  • index_subquery
    • INサブクエリ内でrefの検索が行われたもの
  • range
    • インデックスを用いた範囲検索
    • 公式ドキュメントでは等価検索を含むとなっているがおそらく誤植
  • index
    • インデックスフルスキャン
      • インデックスを全て検索しており、遅い
      • インデックスがあるのにインデックスが効かない条件を利用しているケースでこうなる。NULLで検索してるとか関数と比較してるとか、複合インデックスの一部だけを使って検索しているとか
  • ALL
    • インデックスも貼ってないし全て見てるのでとても遅い

3. EXTRA

この項目では速度改善に役にたつ補足情報が記載されます。色々と種類があるので、主だったもののみ下記に記載します。残りは都度都度調べるのが良い。

  • Using where
    • WHERE句に検索条件が指定されているが、インデックスでは全ての条件が解決できないケース
  • Using index
    • インデックスのみを使ってクエリが実行されるケース。カバリングインデックスなどがそれで、高速
  • Using filesort
  • Using temporary
    • クエリの実行に一時テーブルが必要なケース。遅くなる

おわりに

この記事では実行計画の読み方を解説しました。これを参考にしつつ、あとはまあ場数踏んで慣れていければ良いんじゃないかと思っています。

参考資料

レバテック開発部
レバテック開発部

レバテック開発部の公式テックブログです！ レバテック開発部 Advent Calendar 2024 実施中: qiita.com/advent-calendar/2024/levtech

Discussion