【MySQL】実行計画は3つに分けて読む
これはなに
どうも、レバテック開発部のもりたです。
今回は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チューニングとは宣言型言語でリファクタリングをするということ)でも紹介しましたが、実行計画の表示項目はだいたい3つに分類できます。(読んでない人は先に読んでね)
- 名札情報(id, select_type, table, partitions)
- 処理内容の情報
- EXTRA
今回もその分類に合わせて解説していきます。
1. 名札情報(id, select_type, table, partitions)
これらは記載された行がどのSELECTを対象にしているかを示しています。
-
id
- 単なる通し番号。実行順というわけではないので注意
-
select_type
- 対象のSELECTがどんな形のSELECTかを示す。例えばFROM句のサブクエリだとかそういう情報。INSERT文やUPDATE、DELETEだとその旨記載される
- ここがわかっていると問題のある箇所を特定しやすい
- 表示される値は後述します
-
table
- どのテーブルが参照されたのかという情報
-
partitions
- スキーマ定義としてのPARTITIONに関する情報。どのPARTITIONが参照されたのかを示す。
- PARTITIONの説明はこちら
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
- インデックスマージが最適化された場合
- 参考: https://dev.mysql.com/doc/refman/8.0/ja/index-merge-optimization.html
-
unique_subquery
- INサブクエリ内でeq_refの検索が行われたもの
-
index_subquery
- INサブクエリ内でrefの検索が行われたもの
-
range
- インデックスを用いた範囲検索
- 公式ドキュメントでは等価検索を含むとなっているがおそらく誤植
-
index
- インデックスフルスキャン
- インデックスを全て検索しており、遅い
- インデックスがあるのにインデックスが効かない条件を利用しているケースでこうなる。NULLで検索してるとか関数と比較してるとか、複合インデックスの一部だけを使って検索しているとか
- インデックスフルスキャン
-
ALL
- インデックスも貼ってないし全て見てるのでとても遅い
3. EXTRA
この項目では速度改善に役にたつ補足情報が記載されます。色々と種類があるので、主だったもののみ下記に記載します。残りは都度都度調べるのが良い。
- Using where
- WHERE句に検索条件が指定されているが、インデックスでは全ての条件が解決できないケース
- Using index
- インデックスのみを使ってクエリが実行されるケース。カバリングインデックスなどがそれで、高速
- Using filesort
- ORDER BYでインデックスを使わずクイックソートしているため遅い
- 参考: https://nippondanji.blogspot.com/2009/03/using-filesort.html
- Using temporary
- クエリの実行に一時テーブルが必要なケース。遅くなる
おわりに
この記事では実行計画の読み方を解説しました。これを参考にしつつ、あとはまあ場数踏んで慣れていければ良いんじゃないかと思っています。
Discussion