【MySQL】実行計画のtypeについて調べてみた
はじめに
MySQLの実行計画を勉強していた際に、項目が多すぎて何を見たらいいのかわからなくなりました。
そこで今回はパフォーマンスに直接関連するtypeという項目についてまとめたいと思います。
そもそも実行計画とは？
DBMSがユーザーからクエリを受け取ると以下のように処理を行います。
- クエリを解析する。
- テーブルの統計情報を元に複数のデータの取得方法を立案する。
- 最も推定コストの低いデータの取得方法を選択する。
このとき選択されたデータの取得方法が実行計画と呼ばれます。
MySQLにおいては、EXPLAINというコマンドを使うことでクエリの実行計画を出力することができます。
例
explain select * from actor;
出力結果：
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | actor | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
各項目の詳細については公式ドキュメントを参照ください。
typeとは？
typeはテーブルへのアクセス方法を示す項目です。
この項目を確認することでテーブルにアクセスする際のインデックスの使用方法や絞り込みの方法等を知ることができます。
const
constはプライマリーキーやユニークキーを使った1行検索を行った場合に表示されます。
以下の例ではactorテーブルに対してactor_idというプライマリーキーで検索を行っているためconstが表示されています。
constが表示されている場合は高速な検索ができています。
explain select * from actor where actor_id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | actor | NULL | const | PRIMARY | PRIMARY | 2 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
ref
1件に絞ることができたconstに対して、refはインデックスを使って複数行を検索を行った場合に表示されます。
以下の例ではaddressテーブルに対してcity_idで検索をかけています。
出力された実行計画のkeyの項目にidx_fk_city_idとありますが、これはidx_fk_city_idというインデックスを使って検索をしていることを示しています。
つまりcity_idにインデックスが設定されていますが、city_id=300のレコードが複数あるためrefとなっています。
refが表示されている場合も高速な検索ができています。
explain select * from address where city_id = 300;
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | address | NULL | ref | idx_fk_city_id | idx_fk_city_id | 2 | const | 2 | 100.00 | NULL |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
eq_ref
eq_refはテーブル結合にユニークキーやプライマリーキーを使って検索を行った場合に表示されます。
以下の例ではactorテーブルのtypeがeq_refとなっています。
これはfilm_actorテーブルとactorテーブルを結合する際にプライマリーキーであるactor_idを使用しているので、1件のみの取得となりeq_refが表示されています。
eq_refの場合も高速な検索ができています。
explain
select
first_name,
last_name
from
actor
left join
film_actor
on actor.actor_id = film_actor.actor_id
left join
film
on film.film_id = film_actor.film_id
where
film.film_id in (3, 4, 5);
+----+-------------+------------+------------+--------+------------------------+----------------+---------+----------------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+------------------------+----------------+---------+----------------------------+------+----------+--------------------------+
| 1 | SIMPLE | film | NULL | range | PRIMARY | PRIMARY | 2 | NULL | 3 | 100.00 | Using where; Using index |
| 1 | SIMPLE | film_actor | NULL | ref | PRIMARY,idx_fk_film_id | idx_fk_film_id | 2 | sakila.film.film_id | 5 | 100.00 | Using where; Using index |
| 1 | SIMPLE | actor | NULL | eq_ref | PRIMARY | PRIMARY | 2 | sakila.film_actor.actor_id | 1 | 100.00 | NULL |
+----+-------------+------------+------------+--------+------------------------+----------------+---------+----------------------------+------+----------+--------------------------+
range
rangeはインデックスを使って範囲検索（BETWEEN,IN,<,>など）を行った場合に表示されます。
以下の例ではactor_idをin句で絞ることにより範囲検索を行っています。
explain select first_name from actor where actor_id in (1,2,3,4,5);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | actor | NULL | range | PRIMARY | PRIMARY | 2 | NULL | 5 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
index
indexはインデックスフルスキャンを行った場合に表示されます。
インデックスフルスキャンとは、インデックス全体を検索してデータを取得することです。
以下の例では、actorテーブルのlast_nameカラムにインデックスが設定されていて、last_nameを全件検索しているためindexと表示されています。
explain select last_name from actor;
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | actor | NULL | index | NULL | idx_actor_last_name | 182 | NULL | 10 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
ALL
ALLは全件検索（フルテーブルスキャン）を行った場合に表示されます。
以下の例のようにindexやユニークキーを何も使用せずに検索するとALLとなります。
typeの中では最も遅い検索の方法となります。
ALLが表示された場合はスロークエリとなっている可能性があるため、where句でインデックスを利用するなど、クエリの改善をした方が良いです。
explain select * from actor;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | actor | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
まとめ
今回はtypeについて具体例と共にまとめました。
少しでも参考になれば嬉しいです！
Discussion