はじめに
こんにちは。開発部の國友です。
普段はデータベース管理者の業務を担当しています。
アプリケーションのパフォーマンス改善に取り組む際、「クエリが遅い」と感じたらまず行うべきことがあります。それは、RDBMSがそのクエリをどう実行しているか、その実行計画を確認することです。
皆さんは実行計画の確認方法、そして読み方を理解されていますか？
よく見るテーマではありますが、基礎だからこそ具体的な事例を通じて改めて実行計画を再確認しましょう。
実行計画とは
実行計画とはRDBMSがユーザーから受け取った SQL クエリを最も効率よく処理するために立てる、具体的な手順書です。
ゴールは一つ
ユーザーが書く SQL は目的を示しています。例えば、"SELECT * FROM employees WHERE emp_no > 400000"は、「従業員番号が 400000 より大きいユーザーを全部見つけてきてほしい」という目的を示しています。
ルート（実行計画）は複数ある
この目的を達成する方法（ルート）は複数存在します。
ルートA (非効率)
テーブルの先頭から全行を端まで見ていく（フルスキャン）。
ルートB (効率的)
emp_noカラムに貼られたインデックスを使って、該当するレコードの場所まで一気に飛ぶ。
オプティマイザがルートを決める
RDBMS内部のオプティマイザが統計情報や設定を元にコスト（時間やリソース）が最も低くなるベストなルート（実行計画）を決定し、それに基づいてクエリを実行します。
なぜ実行計画を見る必要があるのか？
オプティマイザは賢いですが、常に最良の判断をするとは限りません。
インデックスの確認
適したインデックスが存在しない場合や、存在してもオプティマイザが利用しないと判断した場合、フルスキャンのような非効率なルートを選んでしまうことがあります。
ボトルネックの特定
クエリのどの部分（どのテーブルアクセス、どのソート処理）に時間がかかっているかを客観的に把握し、ピンポイントで修正（インデックス追加、SQLの見直し）を行うことができます。
今回使用するDB
この記事の実例ではMySQL 8系を使用します。
MySQLから提供されているサンプルデータベースのemployeesを利用しています。
（実在の店舗データや社内データは一切含まれていません）
1. 実行計画の確認方法
クエリの実行計画を見るにはEXPLAINを使用します。
EXPLAINの使い方はとっても簡単です。分析したい SELECT クエリの前に付けるだけで実行できます。
構文
EXPLAIN SELECT 列名 FROM テーブル名 WHERE ...;
結果のサンプル
employees テーブルをフルスキャンするクエリの実行結果を見てみましょう
mysql> explain select * from employees\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: employees partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 299645 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)
上記のような結果が返ってきます。この中で、type、key、Extra の3つのカラムに注目します。
2. EXPLAINの結果を見てみよう
① type カラム
type カラムはMySQLがテーブルからデータを取得するためにどのような方法（アクセスタイプ）を使ったかを示します。これはクエリの性能を判断するうえで最も重要な指標です。
| type の値 | 意味 | 評価 | 対策 |
|---|---|---|---|
| ALL | テーブルの全行をスキャンしている（フルスキャン）。 | 対策しよう | WHERE句の列にインデックスを作成するなど。 |
| range | インデックスを使って特定の範囲をスキャンしている。 | 許容範囲 | 範囲が狭ければ良い。 |
| ref | インデックスを使って特定のキー値と一致する行を取得している。 | 良い感じ | 理想的なインデックス利用。 |
| const/eq_ref | プライマリキーやユニークキーを使って単一行を特定している。 | 最高 | 最も効率的。 |
② key カラム
key カラムは、実際にデータ取得に使われたインデックス名を示します。
keyの値が NULL の場合インデックスが使われていないことを示します。
typeがALLの場合は特にインデックスがないか、あっても利用する価値がないとオプティマイザが判断しています。possible_keysに表示されているのにkeyが NULL の場合インデックス自体は存在するが、オプティマイザが使わない判断をしたことを意味します。
③ 追加の実行情報を示す Extra カラム
Extra カラムには、MySQLが実行計画を立てるうえでの追加情報や注意点が表示されます。ここに特定のメッセージが出たら、要注意です。
| Extra の値 | 意味 | 対策 |
|---|---|---|
| Using filesort | 結果のソートにメモリやディスクを使っている。 | ORDER BY句の列にもインデックスを追加する。 |
| Using temporary | クエリ処理のために一時テーブルを作成している。 | GROUP BYやDISTINCT句の見直し、インデックス追加を検討。 |
| Using index | カバリングインデックス。インデックスのデータだけで全ての情報が取得できた（テーブルアクセス不要）。 | 青信号（最も高速な実行方法の一つ）。 |
例：Using filesortが表示されるようなクエリ
mysql> EXPLAIN SELECT * FROM employees WHERE last_name = 'Facello' ORDER BY birth_date\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: employees partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 299556 filtered: 10.00 Extra: Using where; Using filesort 1 row in set, 1 warning (0.00 sec)
employeesテーブルにはemp_no(従業員No)のプライマリインデックスが設定されています。
上記クエリでは下記の流れで処理が行われます。
last_nameが「Facello」で一致するレコードを見つけます。- 一致したレコードはDBが格納されている順序(
emp_no)でしか得られないためbirth_dateでソートします。
ソートし直しの際に結果をメモリやディスク上にコピーしてbirth_dateによる並び替えを実行する必要があるためUsing filesortと表示されます。
3. サブクエリ実行時のEXPLAINの読み方
サブクエリ（副問い合わせ）を含むクエリを EXPLAIN にかけると、結果が複数行に分かれます。このとき、どの行がどのクエリに対応しているかを理解するために、id と select_type カラムに注目する必要があります。
id カラムで実行順序を把握する
id カラムは、クエリ全体における処理の単位を示します。
idが大きい数字の行から先に実行される（最も内側のサブクエリ）。idが同じ数字の行は、基本的に結合処理（JOIN）として実行される。
select_type カラムでサブクエリの種類を判別する
select_type カラムは、その行のクエリがどのような役割を持っているかを分類します。
| select_type の値 | 意味 | 注意点 |
|---|---|---|
| PRIMARY | サブクエリを含むクエリ全体の最も外側の SELECT。 | |
| SUBQUERY | WHERE 句や SELECT 句にある、非相関サブクエリ（外側のクエリに依存しない）。 |
メインクエリの実行前に一度だけ実行される。 |
| DEPENDENT SUBQUERY | 相関サブクエリ（外側のクエリの行ごとに実行される）。 | 注意が必要。繰り返し実行されるため、パフォーマンスのボトルネックになりやすい。 |
| DERIVED | FROM 句で定義された派生テーブル。 |
メインクエリの実行前に一時テーブルとして作成される。 |
検証例：相関サブクエリ（DEPENDENT SUBQUERY）
employees DBの employees テーブルと salaries テーブルを使い、各従業員の直近の給与を相関サブクエリで取得してみます。
mysql> EXPLAIN -> SELECT -> e.emp_no, -> (SELECT s.salary FROM salaries s -> WHERE s.emp_no = e.emp_no -> ORDER BY s.to_date DESC LIMIT 1) AS latest_salary -> FROM employees e -> LIMIT 10\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: e partitions: NULL type: index possible_keys: NULL key: lastname_birthday key_len: 69 ref: NULL rows: 299556 filtered: 100.00 Extra: Using index *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: s partitions: NULL type: ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: employees.e.emp_no rows: 9 filtered: 100.00 Extra: Using filesort 2 rows in set, 2 warnings (0.00 sec)
id: 1 (PRIMARY)
外側のメインクエリです。ここでは
employeesテーブルを読み込んでいます。id: 2 (DEPENDENT SUBQUERY)
内側のサブクエリです。この
select_typeが出た場合、外側のクエリの読み込んだ行数分だけ、このサブクエリが繰り返し実行されることを意味します。
従業員(emp_no)に対して毎回サブクエリ(最新の給与を取得する)が実行されます。従業員全員を対象とすると、この非効率性からパフォーマンスが大幅に低下します。
サブクエリを使う場合は、DEPENDENT SUBQUERY になっていないかを確認し、可能であればJOIN等を使って別の効率的な書き方に変換できないかを検討しましょう。
まとめ
MySQLのクエリパフォーマンス改善は、EXPLAINの結果を見て、判断して、改善するの繰り返しです。
typeが ALL や range ならインデックス設定など改善の余地あり。keyが NULL ならインデックス設計を見直す。Extraに Using filesort や Using temporary が出たらクエリ設計を見直す必要あり。- サブクエリでは
select_type: DEPENDENT SUBQUERYを避ける。
これらを改善すれば、クエリは劇的に速くなる可能性があります。 是非今実行されているクエリを見直してみてください。
最後までお読みいただきありがとうございました！
