EXPLAIN を使ってクエリが実行される内部を探る

『楽々ERDレッスン（CodeZine BOOKS）』を会社のチームで輪読した（あとがきもご参照あれ）。
この本の第四部は補足的なセクションになっていて、ここでEXPLAINコマンドを用いてSQLの実行計画をみてみよう、という内容になっている。
ただ、用いているRDBMSがPostgreSQLなので、同様のSQLをMySQLで行ってみた。

本記事では本の内容をトレースすることを主に進めている。なので、チューニングや表示される値の詳細など実践的な内容は参照は、下記資料などを参考にされると良いと思う。

（参考）

（公式）EXPLAIN構文、 EXPLAIN 出力フォーマット
- 5.7の日本語リファレンスは執筆時点でまだ内容なので、5.6のを見ている。
「MySQLのEXPLAINを徹底解説!!」 - 漢のコンピュータ道
実例で学ぶ、JOIN (NLJ) が遅くなる理屈と対処法 - Qiita

（MySQLのバージョン）

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.29    |
+-----------+
1 row in set (0.00 sec)

なお、テーブルは全て InnoDB である。

シンプルな SELECT

シンプルな SELECT の実行を行ってみる。対象となる sales テーブルのデータ数は10000レコード。

mysql> EXPLAIN SELECT * FROM sales;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | sales | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9991 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

\G のほうが見やすいかもなので、以下そうする。

mysql> EXPLAIN SELECT * FROM sales\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9991
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

select_type: SIMPLE となっている。MySQLでは、UNION やサブクエリーを使用しない限りは、JOINしたとしても SIMPLE（=単純なSELECT処理）になる。
type¹は結合型と呼ばれる。ここでは type: ALL となっており、フルテーブルスキャンがかかっている²。表全体を取得しているので、そうなるのだろう。
rows が10000ではないのは 「InnoDB テーブルの場合、これは推定値であり、常に正確ではないことがあります。」 という理由から。

WHERE 句を使った場合

絞り込みの条件 WHERE をする SELECT を実行してみる。

mysql> EXPLAIN SELECT * FROM sales WHERE Quantity > 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9991
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

Extra: Using where とあり、WHERE句によって抽出が実行されていることがわかる。インデックスが作られていない Quantity の値が抽出条件なので、type: ALL となっているようだ。
filtered は条件処理されるテーブル行の割合。この場合はMySQLは、1/3が条件にかかると見込んでいる。

集合関数を使った場合

集合関数を使ったケースを見てみる。ここでは avg() 関数を使う。なお、 products のレコード数は200件。

mysql> EXPLAIN SELECT avg(price) as 'avg_price' FROM products WHERE category_id < 3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: products
   partitions: NULL
         type: ALL
possible_keys: category_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 100
     filtered: 41.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

平均を求めるには全ての対象値を足し合わせ、レコード数で割り算を行う。このために、 type: ALL と全てスキャンされていることがわかる。

ORDER BY 句を使った場合

ORDER BY を使って並び替えを行ってみる。

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

ここでも type: ALL と全てスキャンされていることがわかる。

また、Extra: Using filesort とある。公式Docに "クエリーを可能なかぎり高速にしたい場合は、Using filesort および Using temporary の Extra 値に注意します。" とある。
Using filesort の意味や注意のしかたは、こちらの解説（『Using filesort』 - 漢のコンピュータ道）が参考になる。端的に言ってクイックソートで、インデックスが効いていないデータに対して行われているソート。

この場合、price にインデックスが作られていない。全 price 値を元にクイックソートで並び替えているのだろう。

DISTINCT を使う

本に倣って customers テーブルにある、重複のない文字列のカラム address を DISTINCT してみた。
customers テーブルのレコード数は10000件である。

mysql> EXPLAIN SELECT DISTINCT address FROM customers\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customers
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9926
     filtered: 100.00
        Extra: Using temporary
1 row in set, 1 warning (0.00 sec)

この場合、Extra: Using temporary となっている。 "クエリーを解決するために、MySQL は結果を保持する一時テーブルを作成する必要があります。" （公式Doc）とある。INDEXされていない address カラムを DISCINCT処理をするのに、一時的なテーブルが必要な状態になっている。重複排除のためにaddressを元に並べ直しているからかな。

GROUP BY句を使った場合

GROUP BY を使って並び替えを行ってみる。

mysql> EXPLAIN SELECT prefecture_id, count(*) as `num of customers` FROM customers 
    -> GROUP BY prefecture_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customers
   partitions: NULL
         type: index
possible_keys: prefecture_id
          key: prefecture_id
      key_len: 8
          ref: NULL
         rows: 9926
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

type: index となっていて、これは、フルインデックススキャンが行われている。 type: ALL と同じく、このtypeが出た時は注意が必要。ほぼやっていることがALLと同じなので。

JOIN を使った場合その１

JOIN を使ってみる。

mysql> EXPLAIN
    -> SELECT
    ->   prefectures.name,
    ->   customers.name
    -> FROM
    ->   customers
    ->   JOIN prefectures ON customers.prefecture_id = prefectures.id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: prefectures
   partitions: NULL
         type: ALL
possible_keys: PRIMARY,id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 47
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: customers
   partitions: NULL
         type: ref
possible_keys: prefecture_id
          key: prefecture_id
      key_len: 8
          ref: mydb.prefectures.id
         rows: 211
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

possible_keys: PRIMARY,id とあるが、possible_keys は、MySQLのほうで推測している利用可能そうなインデックス。ここに値がない場合は "関連するインデックスがない" 状態。

JOIN を使った場合その２（インデックスがない結合）

インデックスが設定されていないカラムを条件に結合するとどうなるか。本と同様の条件として、sales と salaries それぞれのテーフルの employee_id はインデックスが設定されてはいない。（salaries テーブルは20行）

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: salaries
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9991
     filtered: 10.00
        Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)

possible_keys も key も NULL なので、想定どおりインデックスが使われていない。
２つめの Extra が Using where; Using join buffer (Block Nested Loop) とある。公式Docの説明にあるようにBNLアルゴリズム³を使ってループ処理で結合していることを表す。
ただし、この場合はもちろん、インデックスを使っていない場合よりは計算量は多い。

JOIN を使った場合その３（複合パターン）

これまでの条件を組み合わせた複合パターンを EXPLAIN したものが次の内容。

mysql> EXPLAIN
    -> SELECT 
    ->   p.name,
    ->   p.price,
    ->   avg( s.quantity * p.price ),
    ->   avg( sl.amount )
    -> FROM
    ->   products p 
    ->     JOIN sales s ON p.id = s.product_id
    ->     LEFT JOIN salaries sl ON s.employee_id = sl.employee_id
    -> GROUP BY
    ->   p.name,
    ->   p.price
    -> HAVING
    ->   avg( s.quantity * p.price ) >= 100
    -> ORDER BY
    ->   avg( sl.amount ) DESC;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
   partitions: NULL
         type: ALL
possible_keys: PRIMARY,id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 100
     filtered: 100.00
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s
   partitions: NULL
         type: ref
possible_keys: product_id
          key: product_id
      key_len: 8
          ref: mydb.p.id
         rows: 99
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: sl
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20
     filtered: 100.00
        Extra: Using where; Using join buffer (Block Nested Loop)
3 rows in set, 1 warning (0.00 sec)

こちらの解説（『Using filesort』 - 漢のコンピュータ道）を参照すると書かれているが、 "Using temporary; Using filesortがEXPLAINの最初の行に出ている時は、先にJOINしてからソートされている。（最も遅い。）" になっている。１つめと２つめの結合でこのようになっており、その結果と３つ目の結合で、BNLが行われているのかな。

UPDATE の場合

単純な UPDATE を行う⁴。なお、EXPLAIN は実行計画を表示するので、実際には更新されない。

mysql> EXPLAIN UPDATE customers SET name = 'hoge' WHERE id=1;
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: customers
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set (0.01 sec)

主キーであるIDを指定して更新しているので、インデックスが効いている。

さて、本のほうで紹介しているフルスキャンが必要な場合の更新をやってみる。

mysql> EXPLAIN UPDATE customers SET name = 'hoge' WHERE id<>1\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: customers
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 4964
     filtered: 100.00
        Extra: Using where
1 row in set (0.00 sec)

こちらは、type:range となっていて、インデックスを用いた範囲検索が行われているようだ。

次に、インデックスがついている列とそうでない列の両方を条件に指定してみる。

mysql> EXPLAIN UPDATE customers SET name = 'hoge' WHERE id = 1 AND name <> 'hoge'\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: customers
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set (0.00 sec)

この場合はインデックスが効いている。条件式から、インデックスを使って対象を抽出すれば良いからだ。

一方、WEHRE 句の AND を OR にすると、

mysql> EXPLAIN UPDATE customers SET name = 'hoge' WHERE id = 1 OR name <> 'hoge'\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: customers
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 9926
     filtered: 100.00
        Extra: Using where
1 row in set (0.00 sec)

type: index となっていて、インデックスについてフルスキャンが行われていることがわかる。

DELETE の場合

DELETE も UPDATE と同じような検索をしていることが確認できる。

mysql> EXPLAIN DELETE FROM customers WHERE id = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: DELETE
        table: customers
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set (0.00 sec)

mysql> EXPLAIN DELETE FROM customers WHERE id <> 1\G
*************************** 1. row ***************************
           id: 1
  select_type: DELETE
        table: customers
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 4964
     filtered: 100.00
        Extra: Using where
1 row in set (0.00 sec)

mysql> EXPLAIN DELETE FROM customers WHERE id = 1 AND name <> 'hoge'\G
*************************** 1. row ***************************
           id: 1
  select_type: DELETE
        table: customers
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set (0.00 sec)

mysql> EXPLAIN DELETE FROM customers WHERE id = 1 OR name <> 'hoge'\G
*************************** 1. row ***************************
           id: 1
  select_type: DELETE
        table: customers
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9926
     filtered: 100.00
        Extra: Using where
1 row in set (0.00 sec)

最後のだけ、 UPDATE と異なり type: ALL となっている。

あとがき

弊社では職域ごとにいくつかチームがあり、チーム単位でさまざまな活動をしていて、フレッシュ⁵で勉強好きなメンツが多い我がチームでは輪読形式の勉強会をコンスタントに続けている。『楽々ERDレッスン（CodeZine BOOKS）』はこの間とりあげた本で、RDBMSの基本をおさらいしながら学んだのだった。

ちなみに、この本の第三部は身近なレシートなどを元にDBを設計してみるというテーマになっている。我がチームでは、制限時間内に各自が紙にER図を描き、答え合わせをするというハンズオン方法で臨んだ。この手法、わりと脳に刺激的で面白く、良い勉強になったと思うのでお勧めだ。
『楽々ERDレッスン（CodeZine BOOKS）』

英語では join type。 ↩
PostgreSQLでは「シーケンシャルスキャン」と呼ばれている（ようだ）。 ↩
BNLアルゴリズムのイメージは、このページ（MySQLチューニング虎の巻 - EnterprizeZine）の説明がわかりやすい。このアルゴリズムのおかげで、NxM行分のスキャン処理をしなくて済むようにはなっている。 ↩
MySQL で、UPDATE文、DELETE文、INSERT文、REPLACE文の EXPLAIN ができるようになったのは 5.6.3 から。 ↩
ただし、私はフレッシュではなく、熟した側。 ↩