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処理)になる。
type1は結合型と呼ばれる。ここでは type: ALL
となっており、フルテーブルスキャンがかかっている2。表全体を取得しているので、そうなるのだろう。
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 を使った場合その1
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 を使った場合その2 (インデックスがない結合)
インデックスが設定されていないカラムを条件に結合するとどうなるか。本と同様の条件として、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 なので、想定どおりインデックスが使われていない。
2つめの Extra が Using where; Using join buffer (Block Nested Loop)
とある。公式Docの説明にあるようにBNLアルゴリズム3を使ってループ処理で結合していることを表す。
ただし、この場合はもちろん、インデックスを使っていない場合よりは計算量は多い。
JOIN を使った場合その3 (複合パターン)
これまでの条件を組み合わせた複合パターンを 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してからソートされている。(最も遅い。)" になっている。1つめと2つめの結合でこのようになっており、その結果と3つ目の結合で、BNLが行われているのかな。
UPDATE の場合
単純な UPDATE を行う4。なお、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
となっている。
あとがき
弊社では職域ごとにいくつかチームがあり、チーム単位でさまざまな活動をしていて、フレッシュ5で勉強好きなメンツが多い我がチームでは輪読形式の勉強会をコンスタントに続けている。『楽々ERDレッスン(CodeZine BOOKS)』はこの間とりあげた本で、RDBMSの基本をおさらいしながら学んだのだった。
ちなみに、この本の第三部は身近なレシートなどを元にDBを設計してみるというテーマになっている。我がチームでは、制限時間内に各自が紙にER図を描き、答え合わせをするというハンズオン方法で臨んだ。この手法、わりと脳に刺激的で面白く、良い勉強になったと思うのでお勧めだ。
『楽々ERDレッスン(CodeZine BOOKS)』