EXPLAINを実行することでSQLの実行計画が取得できます。クエリのチューニングにおいてEXPLAINの理解は必須です。
今回はEXPLAINの読み方とクエリをチューニングする際のチェックポイントについて紹介します。
MySQLは5.7.34を利用しています。
EXPLAINで表示される項目について
EXPLAINで表示される項目は以下の通りです。
項目 | 意味 |
---|---|
id | クエリの実行順番 |
select_type | クエリの種類 |
table | アクセス対象のテーブル |
partitions | 利用したpartisionテーブル |
type | レコードへのアクセス種別 |
possible_keys | オプティマイザが候補として挙げたインデックス一覧 |
key | 実際に利用されたインデックス |
ken_len | 選択されたキーの長さ |
ref | 検索条件でkeyと比較される対象 |
rows | 選択されるレコード数の見積もり |
filtered | 検索条件によって絞り込まれるレコードの割合 |
Extra | オプティマイザがクエリ実行で採用した戦略 |
補足説明が必要な項目について以下で紹介します。
idについて
複数のクエリがまとめて実行される場合は同一idが複数表に出現します。
JOIN
の場合は駆動表、内部表の順番でEXPLAINに表示されます。
select_typeについて
単一テーブルに対するクエリとJOIN
の場合、select_typeはSIMPLE
になります。
サブクエリが関係する場合はPRIMARY
、SUBQUERY
、DEPENDENT SUBQUERY
、 NCACHEABLE SUBQUERY
、DERIVED
の5種類のうちのいずれかになります。1
クエリにUNION
が含まれる場合はPRIMARY
、UNION
、UNION RESULT
、 DEPENDENT UNION
、UNCACHEABLE UNION
の5種類のうちのいずれかになります。1
各select_typeの意味についてはMySQLのEXPLAINを徹底解説!!で詳細に解説されているので参考にしてください。
typeについて
代表的なtypeと意味の対応は以下の通りです。
type | 意味 |
---|---|
const | Primary keyもしくはユニークインデックスによるアクセス。最速。 |
eq_ref | JOIN時にPrimary keyもしくはユニークインデックスを利用したアクセス |
ref | ユニークではないインデックスによる等価検索 |
range | インデックスを用いた範囲検索 |
index | フルインデックススキャン |
ALL | フルテーブルスキャン |
refについて
定数が指定されている場合はconst
、JOIN
が実行される場合は『結合先のテーブルで利用されている検索条件のカラム』が表示されます。
Extraについて
Extraでよく見かける情報と意味の対応は以下の通りです。
情報 | 意味 |
---|---|
Using where | WHERE句を利用した絞り込みが行われている |
Using index | インデックスだけで解決ができている高速なクエリ |
Using filesort | filesort(クイックソート)が行われている |
Using temporary | クエリ実行時にテンポラリテーブルが必要 |
Extraの詳細についてはMySQL『8.8.2 EXPLAIN 出力フォーマット#EXPLAIN の追加情報』をご覧になってください。
EXPLAINでチェックすべきポイント
EXPLAINの結果を利用してクエリチューニングする際のチェックポイントについて紹介します。
typeに『ALL』『index』が表示されていないか
ALL
はインデックスを利用せず、テーブルのフルスキャン(全表走査)が実行されていることを意味します。テーブルのフルスキャンはとても遅い処理です。
index
はALL
と違いインデックスこそ使われていますが、当該インデックスをフルスキャンする必要のある遅い処理です。
ExtraのUsing index
はインデックスだけでクエリが解決できることを示すポジティブな指標ですが、typeのindexはネガティブな指標なので注意しましょう。
ただしORDER BY
とLIMIT
を組み合わせた時のように、先頭の数レコードだけを読み込めばよい場合はindex
でも大きな問題にならないこともあります。2
EXPLAIN実行後、typeの項目にALL
またはindex
が表示されていたら要注意です。
Extraに『Using filesort』『Using temporary』が表示されていないか
Using filesort
はソートが実行されていることを表します。
Using filesort
はソート対象のレコード数が少なければ問題にならないですが、行数が多い場合はパフォーマンスに影響を与えます。
特にUsing filesort
とUsing temporary
の組み合わせは『対象が多すぎるので一時テーブルを作成してソートを行う』ということを意味しているので改善必須です。
当該カラムにインデックスを作成し、クエリ実行時にソートを行わないで済むようにしましょう。
EXPLAINを利用したクエリチューニング例
EXPLAINの結果をみてクエリをチューニングする具体例について紹介します。
ケース1: 検索条件でインデックスが利用されていない
SELECT * FROM users WHERE age = 20;
チューニング前
ageにインデックスが作成されていない場合、EXPLAINの結果は以下のようになります。
なお、SQLの末尾にある\G
は結果を縦出力にするオプションです。
> EXPLAIN SELECT * FROM users WHERE age = 20\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: users
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 996364
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
インデックスが作成されていないためpossible_keysやkeyはNULLです。インデックスが利用されないためtypeはALL
になります。
typeがALL
の場合は適切なインデックスを作成してクエリチューニングをします。
チューニング後
ageにインデックスを作成すると実行計画は以下のようになります。
> EXPLAIN SELECT * FROM users WHERE age = 20\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: users
partitions: NULL
type: ref
possible_keys: index_users_on_age
key: index_users_on_age
key_len: 5
ref: const
rows: 22248
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
keyに新しく作成したインデックス(index_users_on_age
)が表示されました。
インデックスが利用されるようになったため、typeがALL
からref
にかわりました。
ケース2: ソートでインデックスが利用されていない
SELECT * FROM users ORDER BY age LIMIT 100;
チューニング前
ageにインデックスが作成されていない場合、EXPLAINの結果は以下のようになります。
> EXPLAIN SELECT * FROM users ORDER BY age LIMIT 100\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: users
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 996364
filtered: 100.00
Extra: Using filesort
1 row in set, 1 warning (0.00 sec)
インデックスが作成されていないためpossible_keysやkeyはNULLです。インデックスが利用されないためtypeはALL
になります。
また、ソート処理が実行されているためExtraにUsing filesort
が表示されています。
今回の場合、rowsが10万件近くあるためソート対象のカラムにインデックスを作成したほうがよさそうです。
チューニング後
ageにインデックスを作成すると実行計画は以下のようになります。
> EXPLAIN SELECT * FROM users ORDER BY age LIMIT 100\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: users
partitions: NULL
type: index
possible_keys: NULL
key: index_users_on_age
key_len: 5
ref: NULL
rows: 100
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
ExtraのUsing filesort
がなくなり、typeがALL
からindex
に変更されました。
また、rowsはチューニング前の約10万件から、SQLのLIMIT
で指定された100件になりました。
以上より、インデックスによってソート処理が高速化したことがわかります。
ケース3: 結合条件にインデックスが利用されていない
1対多で紐づくusersテーブルとbooksテーブルを結合し、userのageが30のbooksレコードを取得するSQLをチューニングしてみます。
説明の便宜上、STRAIGHT_JOINを利用してusersテーブルを駆動表、booksテーブルを内部表に固定しています。
SELECT STRAIGHT_JOIN b.* FROM users u, books b WHERE u.id = b.user_id AND u.age = 30\G;
なお、usersテーブルにはageにインデックスが作成されている、booksテーブルにはPrimary key以外にインデックスは作成されていないとします。
チューニング前
結合条件にインデックスが作成されていない場合、EXPLAINの結果は以下のようになります。
JOIN
の場合は駆動表、内部表の順番でEXPLAINに表示されるため、今回の場合はusersテーブルが駆動表、booksテーブルが内部表となります。
> EXPLAIN SELECT STRAIGHT_JOIN b.* FROM users u, books b WHERE u.id = b.user_id AND u.age = 30\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: u
partitions: NULL
type: ref
possible_keys: PRIMARY,index_users_on_age
key: index_users_on_age
key_len: 5
ref: const
rows: 22196
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2980163
filtered: 10.00
Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)
駆動表のレコードの絞り込みをする1. row
のクエリではインデックス(index_users_on_age
)が利用されているので問題なさそうです。
しかし、内部表を検索する2. row
のクエリのtypeがALL
になっているのでチューニングが必要そうです。
結合条件はu.id = b.user_id
ですので、booksテーブルのuser_idに対してインデックスを作成すれば改善できそうだということがわかります。
チューニング後
booksテーブルのuser_idにインデックスを作成すると実行計画は以下のようになります。
> EXPLAIN SELECT STRAIGHT_JOIN b.* FROM users u, books b WHERE u.id = b.user_id AND u.age = 30\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: u
partitions: NULL
type: ref
possible_keys: PRIMARY,index_users_on_age
key: index_users_on_age
key_len: 5
ref: const
rows: 22196
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: ref
possible_keys: index_books_on_user_id
key: index_books_on_user_id
key_len: 5
ref: rails6_api_mysql8_development.u.id
rows: 3
filtered: 100.00
Extra: Using index condition
2 rows in set, 1 warning (0.00 sec)
結合条件でインデックスが利用されるようになったため、2番目のクエリのtypeがALL
からref
にかわりました。
まとめ
- typeに『ALL』『index』が表示されていないか
- Extraに『Using filesort』『Using temporary』が表示されていないか
Twitter(@nishina555)やってます。フォローしてもらえるとうれしいです!