MySQLクエリ最適化入門：オプティマイザの仕組みを理解しクエリチューニングを実践

はじめに

最近、仕事でSQLクエリのパフォーマンス改善に取り組む課題があり、「このクエリはなぜ遅いのか？」と悩んでいました。

これをきっかけに、MySQLのクエリ実行計画やオプティマイザの仕組みについて改めて学び直すことにしました。

この記事は、自分が学んだ内容を整理しまとめたものですが、以下のような方にもはおすすめです。

• SQLクエリのパフォーマンスを改善したいが、どこから手をつけるべきかわからない方
• MySQLがクエリを受け取ってから結果を返すまでの処理の流れに興味がある方
• MySQLオプティマイザの仕組みや、EXPLAIN を使った実行計画の読み解きに興味がある方
• インデックス設計やクエリチューニングの基礎知識を復習したい方

この記事で使うデータベースは、MySQLの最新メジャーバージョン 8.4 を使用します。
また、クエリの実行計画を解説するために、MySQL公式が提供する「employees」データベースを使用します。

以下のリンクからデータベースをダウンロードできます。

• データダウンロード: https://github.com/datacharmer/test_db
• テーブル構造の参照: employees.sql

それでは、MySQLのクエリ処理の仕組みについて詳しく見ていきましょう！

MySQLはどのようにクエリを処理するのか

クエリのライフサイクル

まず、クエリのライフサイクルを理解しましょう。
MySQLがクエリを受け取ってから結果を返すまでの処理フローは、以下の段階に分かれます。

1. クエリの解析
MySQLはクエリを受け取ると、最初にそのクエリの解析を行います。

• 予約語やテーブル名、カラム名などの入力ミスがないか
• SQL文の文法が正しいか

上記のような点をチェックし、問題がなければクエリを内部で処理するための解析ツリーを構築し、次のステップへ渡します。

2. オプティマイザによる最適化(実行計画の策定)
MySQL は、クエリを解析した後、その構文どおりにそのまま実行するのではなく、最適な方法でデータを取得するための実行計画を策定します。
この重要なプロセスを担当するのが オプティマイザ です。

オプティマイザは最も効率的な実行計画を決定する際、主に以下の要素を考慮します。
• 利用可能なインデックス: どのインデックスを使用すべきかを判断する。
• テーブル結合の順序: 複数テーブルをどの順番・方法で結合すると最も効率的かを決定する。
• 統計情報: テーブル内のデータ分布や行数などの統計情報をもとに、最適なアクセス方法を選択する。

クエリのパフォーマンスを改善するためには、オプティマイザの判断基準を理解し、実行計画を確認することが非常に重要です。
実行計画は EXPLAIN キーワードを使って確認できるため、後ほど詳細に説明します。

3. クエリの実行
最適化された実行計画に基づき、ストレージエンジン（通常はInnoDB）が必要なデータを取得します。
この段階で WHERE 句によるフィルタリングや計算、グルーピングなどが行われ、最終的な結果が生成されます。

4. 結果の返却
ストレージエンジンでの処理が完了すると、結果はMySQLサーバに返され、最終的にクライアントへと返却されます。

クエリの実行コストとは

オプティマイザの判断基準を理解するには、まず実行コストという概念を理解することが必要です。
どの実行プランが最も良いのか、MySQLのオプティマイザはコストベースで判断します。

コストとは、CPU時間やディスクI/Oなど、クエリを実行する際にかかるリソースの消費量を指します。
オプティマイザは、クエリの各ステップを実行する際に必要なCPUリソースやディスクから読み込むデータ量などを評価し、実行コストを計算します。

そして、各実行プランのコストを比較し、コストが最も低いプランを最終的な実行計画として選定します。

注意:
コスト値は絶対的な時間やリソース使用量を示すものではなく、相対的な指標であり、他のプランと比較するための目安であるということです。
「コストが 100 だから 1 秒かかる」というような直接的な対応関係がありません。
一般的にはコストが小さい方が高速に実行される傾向にあります。

コストの計算要素

コスト計算には以下のような要素が含まれます。

• テーブルサイズと行数:
大きなテーブルほどコストが高くなる可能性があります。
• インデックスの使用:
適切なインデックスが利用されると、絞り込みが効率化され、コストは低くなります。
• 結合方式:
MySQLは主にネストループ結合を採用しており、結合順序によってコストが変わります。
• サブクエリや集約:
複雑なサブクエリや大規模な GROUP BY などの集約操作は、追加の計算負荷を伴い、コストが高くなる場合があります。

MySQL では、コスト計算プロセスで使用される係数（定数）を mysql.server_cost テーブルや mysql.engine_cost テーブルに保持しており、以下のように参照できます。

mysql> select * from mysql.server_cost;
+------------------------------+------------+---------------------+---------+---------------+
| cost_name                    | cost_value | last_update         | comment | default_value |
+------------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost   |       NULL | 2025-01-14 00:58:40 | NULL    |            20 |
| disk_temptable_row_cost      |       NULL | 2025-01-14 00:58:40 | NULL    |           0.5 |
| key_compare_cost             |       NULL | 2025-01-14 00:58:40 | NULL    |          0.05 |
| memory_temptable_create_cost |       NULL | 2025-01-14 00:58:40 | NULL    |             1 |
| memory_temptable_row_cost    |       NULL | 2025-01-14 00:58:40 | NULL    |           0.1 |
| row_evaluate_cost            |       NULL | 2025-01-14 00:58:40 | NULL    |           0.1 |
+------------------------------+------------+---------------------+---------+---------------+

mysql> select * from mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| engine_name | device_type | cost_name              | cost_value | last_update         | comment | default_value |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| default     |           0 | io_block_read_cost     |       NULL | 2025-01-14 00:58:40 | NULL    |             1 |
| default     |           0 | memory_block_read_cost |       NULL | 2025-01-14 00:58:40 | NULL    |          0.25 |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+

上記の出力を見ると、それぞれのコストにデフォルト値が設定されていることがわかります。
以下に、各コストパラメータの意味を簡単に説明します。

IOコスト
• disk_temptable_create_cost：ディスク上に一時テーブルを作成する際のコスト
• disk_temptable_row_cost：ディスク上の一時テーブルのレコードの読み書きにかかるコスト
• memory_temptable_create_cost：メモリ上に一時テーブルを作成する際のコスト
• memory_temptable_row_cost：メモリ上の一時テーブルのレコードの読み書きにかかるコスト
• io_block_read_cost：ストレージ・エンジンがディスクからページを読み込む際のコスト
• memory_block_read_cost：ストレージ・エンジンがバッファ・プールからページを読み込む際のコスト

CPUコスト

• row_evaluate_cost: 1行を評価（フィルタや計算）する際のコスト
• key_compare_cost: B+Tree 検索などでインデックスのキーを比較する際のコスト

より詳細な算出ロジックを知りたい場合は MySQL のソースコードを読むのが最も正確ですが、下記のように一部解説した記事もあるため、興味がある方はご参照ください。

• Analysis of MySQL Cost Estimator

EXPLAIN と EXPLAIN ANALYZE

以上の説明で、MySQL がクエリを処理する際の考え方について、少しイメージがついたでしょうか。

ここからは、EXPLAIN コマンドを使い、クエリの実行計画を確認する方法について解説します。さらに、実行時の統計情報を取得できるEXPLAIN ANALYZE についても紹介します。

EXPLAIN の出力項目を読み解く

EXPLAIN <SQL文> を実行すると、オプティマイザが選んだ実行計画をデフォルト形式（Traditional） で確認できます。

この出力をもとに、クエリのどこが非効率か（フルテーブルスキャンか、インデックスが使われていないか、ソートに大きなコストがかかっていないか など）を判断します。

それでは実際の出力サンプルと見てみましょう。

実行例: employees テーブルから emp_no が 10010 未満の行を検索

EXPLAIN
SELECT emp_no, first_name, last_name
FROM employees
WHERE emp_no < 10010;

以下のようなテーブル形式で実行計画が出力されます。

+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    9 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

デフォルト形式の主な出力項目

• id:
  SELECT 文内のクエリブロック番号。サブクエリなどがあれば複数の行になることがあります。

• select_type:
  クエリの種類（SIMPLE, PRIMARY, SUBQUERY, DERIVED など）。
  一般的な単一テーブルアクセスなら SIMPLE。

• table:
  アクセスされるテーブル名

• type:
  テーブルへのアクセス方法。
  • ALL (フルテーブルスキャン)
  • range (インデックス範囲検索)
  • ref / eq_ref (インデックスを用いた結合または検索)
  • const (主キーやユニークインデックスに基づく、1行だけの取得) など

• possible_keys:
  クエリに対して利用可能だと判断できるインデックス候補の一覧。
  WHERE 句や JOIN 条件のカラムに対応するインデックスがあれば、ここに表示される。

• key:
  実際に使用されたインデックス。
  オプティマイザが複数候補の中から最適だと判断したものが表示される。

• key_len:
  インデックスのバイト長。ここでは 4 なので、INT 型のカラム（emp_no）を使っているとわかる。

• ref:
  key 列に示されているインデックスと比較する際に、どの列または定数が使われるかを示す。
  JOIN などで他テーブルのカラムを参照している場合、この欄に表示される。

• rows:
  オプティマイザが予測するスキャン行数。実際の値とは異なる可能性もあるが、あくまで目安。

• filtered:　
  最終的に条件を満たす行がどの程度か を百分率で示す。
  • 例: rows が 100、filtered が 50.00 なら「約 50 行が最終的に条件に合致する」と見込んでいる。
  • 100.00 に近いほど「絞り込みがあまり効いていない」、低いほど「絞り込みが大きい」。

• Extra:
  Using index, Using filesort, Using temporary などの追加情報が表示される。
  ボトルネックを特定するうえで非常に重要。

Extra 項目に注目しよう

Extra はクエリの最適化状況を把握するのに欠かせない情報源です。
特によく出るものを簡単に整理すると:

• Using index

  • クエリで必要な全てのカラムがインデックスに含まれており、テーブルデータを参照せずにインデックスのみで結果を取得できる状態。
  • ディスクI/Oが削減され、高速化につながる。

• Using where ※

  • WHERE 条件を適用してフィルタを行っていることを示す。

• Using filesort
  • インデックスを使ったソートができず、MySQL が独自にメモリや一時ファイルを使ってソートを行っていることを示す。
  • ORDER BY で指定したカラムにインデックスが無い、またはインデックス順序と合わない場合などに発生し、パフォーマンス低下の原因になりやすい。

• Using temporary
  • 一時テーブルを作成して中間データを保持している。
  • GROUP BY や DISTINCT、複雑な JOIN などでよく使われ、大規模データの場合は特に注意が必要。

※Using whereに関しては、"WHERE条件に対して、インデックスで絞りきれない場合を示し、改善が必要"という説明をよく見かけますが、実際はWHERE条件を適用する場合なら、基本的に出ています。

各項目の詳細や他のフィールドについては、下記の公式ドキュメントを参照してください。
MySQL 8.4: explain output

EXPLAIN の出力形式を指定する

Explain出力内容は三つのフォーマットがあります。

• デフォルト（Traditional）
• JSON
• ツリー（Tree）

先ほどの例は デフォルト形式 です。
特に指定しなければこの形式になりますが、次のように記述するとフォーマットを切り替えられます:

EXPLAIN FORMAT=JSON SELECT ...;
EXPLAIN FORMAT=TREE SELECT ...;

JSON形式: コストの内訳が詳しくわかる

以下のように、JSON 形式で出力します。

EXPLAIN FORMAT=JSON
SELECT emp_no, first_name, last_name
FROM employees
WHERE emp_no < 10010;

サンプル出力内容：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.08"
    },
    "table": {
      "table_name": "employees",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY",
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "emp_no"
      ],
      "key_length": "4",
      "rows_examined_per_scan": 9,
      "rows_produced_per_join": 9,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "1.18",
        "eval_cost": "0.90",
        "prefix_cost": "2.08",
        "data_read_per_join": "1K"
      },
      "used_columns": [
        "emp_no",
        "first_name",
        "last_name"
      ],
      "attached_condition": "(`employees`.`employees`.`emp_no` < 10010)"
    }
  }
}

cost_info や rows_examined_per_scan、used_columns など、デフォルト形式では見られない追加情報が格納されています。
特にquery_cost, read_cost, eval_cost など、オプティマイザが見積もったコストの内訳がより詳しくわかります。

ツリー形式: クエリ実行の流れを階層的に可視化

ツリー形式は MySQL 8.0.16 以降で導入された新しい形式で、実行計画を インデントされた階層構造 で表示し、クエリの実行フローをより直感的に把握できるのが特徴です。

以下はツリー形式の出力です。

+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (employees.emp_no < 10010)  (cost=2.08 rows=9)
    -> Index range scan on employees using PRIMARY over (emp_no < 10010)  (cost=2.08 rows=9)
 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+

実行ステップが階層構造で表示されるため、テーブルスキャンやフィルタリングの順序が一目でわかりやすいです。
処理の順序は逆順で表示されています。

上記の例では、二つの操作が行われていることがわかります。

まずは、Index range scan（インデックス範囲スキャン）です。
employees テーブルの PRIMARY(emp_no) インデックスを使用して、条件 emp_no < 10010 にマッチする行を探索します。
この操作が返す行の数は9行で、実行コストは2.08と予測されます。

そして、Index range scanで取得したデータに対して、employees.emp_no < 10010 の条件を満たす行のみを絞り込むフィルター処理が行われます。
今回の場合は、必要なデータはすでにIndex range scanで効率的にピックアップしたことがわかりますが、このステップは取得した行が条件に完全に適合しているかを確認するために必要です。

EXPLAIN ANALYZE: クエリ実行時の情報を取得する

通常の EXPLAIN コマンドはあくまで 推定 に基づく実行計画を示すのみで、実際の実行時間や行数などの実測値は分かりません。

MySQL 8.0.18 以降では、EXPLAIN ANALYZE がサポートされました。
これは クエリを実際に実行 して、各ステップにかかった実行時間や処理された行数など、実行時の具体的な統計情報 をツリー形式でレポートする機能です。

EXPLAIN ANALYZE
SELECT emp_no, first_name, last_name
FROM employees
WHERE emp_no < 10010;

クエリを実行すると、下記の出力が出ます。

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                    |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (employees.emp_no < 10010)  (cost=2.08 rows=9) (actual time=0.636..0.684 rows=9 loops=1)
    -> Index range scan on employees using PRIMARY over (emp_no < 10010)  (cost=2.08 rows=9) (actual time=0.53..0.578 rows=9 loops=1)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

EXPLAIN ANALYZE の出力例には、以下のような情報が含まれます:

• actual time: 操作が開始してから完了するまでの実際の経過時間（ミリ秒）。
  (actual time=0.636..0.684) は、開始が 0.636ms、終了が 0.684ms、実質 0.048ms かかったことを示す。
• rows: 実際に処理された行数
• loops: 同じ操作が繰り返し実行された回数。

たとえば複数テーブルを JOIN する際、予測よりも実際の処理行数が多い場合（例: rows が想定の倍数）、そこがボトルネックになっていると判断できます。

また、予想されるインデックスが使われていなかったり、思ったほど効果が出ていなかったりする理由の分析にも役立ちます。

EXPLAINを使い、クエリのパフォーマンスを改善する

以下の2ステップで紹介する事例を通じて、オプティマイザの雰囲気を掴みつつ、具体的なパフォーマンス改善方法を考えてみましょう。

1. 単一テーブルクエリ: インデックスの有無やインデックス強制指定がどうオプティマイザの挙動を変えるか
2. 複数テーブルJOINクエリ: JOIN 順序や複合インデックス、WHERE + ORDER BY + LIMIT などでの最適化

単一テーブルでの簡単SELECTクエリ

まずは、employees テーブルのみを使ったシンプルな SELECT クエリを見て、オプティマイザの基本的な動きを確認しましょう。

例として、従業員の生年月日が「1965年1月1日以降」であるレコードを取得するケースを想定します。

SELECT emp_no, first_name, last_name, birth_date
FROM employees
WHERE birth_date >= '1965-01-01';

問題点

まず、EXPLAINの出力結果を見ます。

+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299025 |    33.33 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+

実行計画をみると、下記のことがわかります。

• type が ALL となり、フルテーブルスキャンが行われる
• possible_keys に何も表示されていない、つまり birth_date 用のインデックスが存在しない
• rows が299025で、従業員全レコード数に近い推定値と見積もっている

このようなテーブルフルスキャンは、大量データを持つテーブルに対して、パフォーマンスが低下する可能性が高いです。

EXPLAIN ANALYZE の結果:

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (employees.birth_date >= DATE'1965-01-01')  (cost=30135 rows=99665) (actual time=0.545..66.9 rows=1940 loops=1)
    -> Table scan on employees  (cost=30135 rows=299025) (actual time=0.517..54.3 rows=300024 loops=1)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

実際の出力レポートでは、下記の結果がわかります。

• 読み込み行数は299025と見積もったが、実際はやはり従業員全レコード数をフルスキャンした
• フルスキャンの後、そこから birth_date >= 1965-01-01 でフィルタを適用して最終的に 1940 行が残った

インデックス追加後

フルスキャンを回避するため、birth_date にインデックスを作成してみましょう。

ALTER TABLE employees 
ADD INDEX idx_birth_date (birth_date);

EXPLAIN の結果

+----+-------------+-----------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_birth_date | idx_birth_date | 3       | NULL | 1940 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+

• type = range に変わり、key は idx_birth_date が使用される
• 読み込み行数は 1940 に大幅減少している。最終の実行結果と同じ行数で、範囲検索で非常に効率的に絞り込むことができる見込み
• Extra に Using index conditionが表示される。これは必要なデータを取り出す前に、フルテーブルスキャンより、インデックスを先に読み込んでから判断するという計画

EXPLAIN ANALYZE の結果

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                      |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index range scan on employees using idx_birth_date over ('1965-01-01' <= birth_date), with index condition: (employees.birth_date >= DATE'1965-01-01')  (cost=873 rows=1940) (actual time=0.802..3.55 rows=1940 loops=1)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

インデックス追加後の実行結果では、パフォーマンスが向上されたことがわかります。

• 実行時間：actual time=0.545..66.9 -> 0.8..3.55 に短縮
• コスト：cost=30135 -> 873 に減少
• Index range scan により、実際のスキャン行数や処理時間も少なくなり、フルスキャン時より大幅に改善されている。

上記の例で、オプティマイザの気持ちは少しわかりました。

複数テーブルJOINクエリ: WHERE + ORDER BY + LIMIT

次に、複数テーブルを結合し、WHERE + ORDER BY + LIMIT を含むクエリの例を見ていきます。

ここでは employees / dept_emp / departments の 3 テーブルのうち、実際には主に employees と dept_emp を JOIN し、さらに departments で部門名を絞り込む事例を取り上げます。

クエリ例
1980年以降 に入社した従業員のうち、'Development' 部門に所属する人を 入社日（hire_date） 昇順で 最大 10 件まで取得

SELECT employees.emp_no, employees.first_name, employees.last_name, departments.dept_name
FROM dept_emp
JOIN employees ON employees.emp_no = dept_emp.emp_no
JOIN departments ON dept_emp.dept_no = departments.dept_no
WHERE employees.hire_date >= '1980-01-01'
  AND departments.dept_name='Development'
ORDER BY employees.hire_date
LIMIT 10;

取得結果サンプル

+--------+------------+------------+-------------+
| emp_no | first_name | last_name  | dept_name   |
+--------+------------+------------+-------------+
| 110511 | DeForest   | Hagimont   | Development |
|  20539 | Poorav     | Gecsei     | Development |
| 102004 | Arvin      | Birdsall   | Development |
| 296777 | Divier     | Marrakchi  | Development |
|  13029 | Selwyn     | Demke      | Development |
|  17389 | Taiji      | Kemmerer   | Development |
|  28092 | Yongmao    | Tetzlaff   | Development |
|  32293 | Jayson     | Gimarc     | Development |
|  38782 | Udi        | Oskamp     | Development |
|  43969 | Sajjad     | Benzmuller | Development |
+--------+------------+------------+-------------+
10 rows in set, 1 warning (0.12 sec)

インデックス追加前の状況

EXPLAIN 結果:

+----+-------------+-------------+------------+--------+-------------------+-----------+---------+----------------------------------+--------+----------+-----------------------------+
| id | select_type | table       | partitions | type   | possible_keys     | key       | key_len | ref                              | rows   | filtered | Extra                       |
+----+-------------+-------------+------------+--------+-------------------+-----------+---------+----------------------------------+--------+----------+-----------------------------+
|  1 | SIMPLE      | departments | NULL       | const  | PRIMARY,dept_name | dept_name | 162     | const                            |      1 |   100.00 | Using index; Using filesort |
|  1 | SIMPLE      | employees   | NULL       | ALL    | PRIMARY           | NULL      | NULL    | NULL                             | 299025 |    33.33 | Using where                 |
|  1 | SIMPLE      | dept_emp    | NULL       | eq_ref | PRIMARY,dept_no   | PRIMARY   | 20      | employees.employees.emp_no,const |      1 |   100.00 | Using index                 |
+----+-------------+-------------+------------+--------+-------------------+-----------+---------+----------------------------------+--------+----------+-----------------------------+

まずテーブルの処理順番に注目したいです。
クエリ順は FROM dept_emp -> JOIN employees -> JOIN departments と記述したにもかかわらず、オプティマイザが最適化後の実行計画では、
departments -> employees -> dept_empの処理順になっています。

クエリの 記述順 と 実際の処理順 は必ずしも同じではない点に注意しましょう。

他のポイントも下記にまとめます：

• departments テーブル
  • ユニークキーにより、1行に確定できるため、type=const と表示
  • Extra に Using filesortがあり、ORDER BY employees.hire_date がインデックスでカバーされていないため、MySQL がメモリやディスク上でソートしていることを示唆
• employees テーブル
  • type=ALL でフルテーブルスキャンが行われる見込み。
  • 使えるインデックスがなく、約30万行を全件読み取り、WHERE employees.hire_date >= '1980-01-01'でフィルタリング。
• dept_emp テーブル
  • type=eq_ref で PRIMARY (emp_no, dept_no) を使用し、結合を行う
  • Extra = Using index となっており、必要カラムが全てインデックス上で完結

実際の処理フロー: EXPLAIN ANALYZE

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 10 row(s)  (cost=84954 rows=10) (actual time=144..145 rows=10 loops=1)
    -> Nested loop inner join  (cost=84954 rows=299025) (actual time=144..145 rows=10 loops=1)
        -> Sort: employees.hire_date  (cost=30135 rows=299025) (actual time=144..144 rows=52 loops=1)
            -> Filter: (employees.hire_date >= DATE'1980-01-01')  (cost=30135 rows=299025) (actual time=0.199..68.4 rows=300024 loops=1)
                -> Table scan on employees  (cost=30135 rows=299025) (actual time=0.194..50.4 rows=300024 loops=1)
        -> Single-row covering index lookup on dept_emp using PRIMARY (emp_no=employees.emp_no, dept_no='d005')  (cost=0.25 rows=1) (actual time=0.00234..0.00234 rows=0.192 loops=52)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

上記を簡単にツリー表現で整理すると、下記のようになります。

Limit: 10 rows
└─ Nested Loop Inner Join
    ├─ 左側の子ノード: Sort by employees.hire_date
    │    └─ Filter: employees.hire_date >= '1980-01-01'
    │         └─ Table Scan on employees
    └─ 右側の子ノード: Single-row Covering Index Lookup on dept_emp
         (結合キー: employees.emp_no, dept_no='d005')

ネストされたループ結合 (Nested Loop Inner Join)
まずはNested Loop Inner Joinの動作について説明します。

上記のツリー構造でわかるように、Nested Loop Inner JoinがJOIN全体の親ノードとなり、その下に2つの子ノードを持っています：

• 左側の子ノード：Sort: employees.hire_date 以下の部分
• 右側の子ノード：Single-row covering index lookup on dept_emp

ネストされたループ結合では、まず左側の子ノードを評価して外側の結合ソースを取得します。
その後、取得した各行に対して右側の子ノードを繰り返し実行し、結合条件を満たす行を探します。

dept_name='Developmentからdept_no=‘d005’への変換処理
上記の処理フローをみると、dept_name='Developmentの条件がすでにdept_no=‘d005’に変換されていることがわかります。

これは、オプティマイザが実行計画を策定する段階で、処理最適化のために事前に行なった処理です。

departments テーブルに UNIQUE KEY(dept_name) があるため、dept_name='Developmentの条件を満たす部門が一意に決まる場合、オプティマイザは実行計画の段階で、その条件を解決し、該当する dept_noを特定しました。
これにより、後続の処理では dept_name='Development' のチェックを省略し、代わりに具体的な dept_no='d005' を利用するようになります。

実行計画に出ていたdepartments テーブルに対しての処理は、既に最適化段階で完了したため、クエリ実行段階ではスキップとなり、表示に出ていません。

LIMITの適用
Limit: 10 row(s)の処理が最先頭に立つことは、一番最後に処理されることがわかります。
10行のみでも、全部のデータ処理が終わってから、最終結果から先頭10件を返す流れになります。

もう一つ気になるのは、ネストされたループ結合で処理する対象はソート処理後のrows=52行ではなく、rows=10となっていることです。

つまり、結合の時点では、最後に必要なのは10件のみのことがわかるため、結合処理が10件の結果を得た時点で処理を打ち切ったと推測できるでしょう。

クエリ実行段階の処理フロー
クエリ実行段階の処理フローは下記となります。

1. まずはemployees テーブルをフルスキャンし、全件データを取得
2. employees.hire_date >= '1980-01-01' に基づいてフィルタリングを行い、52行を取得
3. フィルタリングされた行は、ORDER BY employees.hire_date の条件で、hire_date の順にソートされる。これで左側の子ノードの処理が完了。
4. ソート済みの各 employeesの行を1行ずつ処理し、対応する dept_emp の行を PRIMARY インデックスを用いて、emp_no=employees.emp_no, dept_no='d005' の条件で検索する。対象行は52行なので、52回繰り返して処理する。
5. 左側のの子ノードでソートされた結果と、右側の子ノードで取得された dept_emp のデータを組み合わせて結合行を生成
6. 最後に先頭10件の結果を返す。

インデックス追加後

employees.hire_date がソートキーかつフィルタ条件に使われているので、以下のようにインデックスを作成します。

ALTER TABLE employees
ADD INDEX hire_date (hire_date);

EXPLAIN の変化

+----+-------------+-------------+------------+--------+-------------------+-----------+---------+----------------------------------+--------+----------+-----------------------+
| id | select_type | table       | partitions | type   | possible_keys     | key       | key_len | ref                              | rows   | filtered | Extra                 |
+----+-------------+-------------+------------+--------+-------------------+-----------+---------+----------------------------------+--------+----------+-----------------------+
|  1 | SIMPLE      | departments | NULL       | const  | PRIMARY,dept_name | dept_name | 162     | const                            |      1 |   100.00 | Using index           |
|  1 | SIMPLE      | employees   | NULL       | range  | PRIMARY,hire_date | hire_date | 3       | NULL                             | 149512 |   100.00 | Using index condition |
|  1 | SIMPLE      | dept_emp    | NULL       | eq_ref | PRIMARY,dept_no   | PRIMARY   | 20      | employees.employees.emp_no,const |      1 |   100.00 | Using index           |
+----+-------------+-------------+------------+--------+-------------------+-----------+---------+----------------------------------+--------+----------+-----------------------+

下記のポイントを注目しましょう。

• employeesテーブル
  • type がrange となり、key=hire_date を使用
  • Extra 列に Using index condition が表示される
  • 想定処理行数はrows=149512となり、前回より半減
• ソートに関する Using filesort が表示されなくなる

これで、前回よりはインデックスが活用され効率的な絞り込みを行えることが想定されます。

EXPLAIN ANALYZE後

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 10 row(s)  (cost=82464 rows=10) (actual time=1.9..2.26 rows=10 loops=1)
    -> Nested loop inner join  (cost=82464 rows=149512) (actual time=1.85..2.21 rows=10 loops=1)
        -> Index range scan on employees using hire_date over ('1980-01-01' <= hire_date), with index condition: (employees.hire_date >= DATE'1980-01-01')  (cost=30135 rows=149512) (actual time=1.68..1.68 rows=38 loops=1)
        -> Single-row covering index lookup on dept_emp using PRIMARY (emp_no=employees.emp_no, dept_no='d005')  (cost=0.25 rows=1) (actual time=0.0109..0.0109 rows=0.263 loops=38)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

実際の処理フローをみると、前回より処理ステップが少なくなり、実行時間も 2ms 程度に大幅に短縮されて、クエリの実行がより効率化になっていることがわかります。

練習：自分でオプティマイザの動きを試してみよう

オプティマイザの動きを理解するには、実際にクエリを変えながら EXPLAIN や EXPLAIN ANALYZE の出力を比較してみるのが最も効果的です。

ここでは、簡単な例として employees / dept_emp / departments を使ったクエリを少しずつ変更して、実行計画がどう変わるかを観察してみましょう。

以下のよう、JOIN 順や WHERE 条件を自由に変えて実験してみてください。

-- 例: JOIN 順や WHERE 条件を変更してみる
SELECT employees.emp_no, employees.first_name, employees.last_name, departments.dept_name
FROM employees
JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
JOIN departments ON dept_emp.dept_no = departments.dept_no
WHERE employees.emp_no > 1001
  AND departments.dept_name = 'Development'
ORDER BY employees.hire_date
LIMIT 10;

• JOIN の順序: たとえば FROM dept_emp -> JOIN employees -> JOIN departments に書き換えるなど、クエリの記述順を変えてみても面白いでしょう。オプティマイザが実際にどの順序を選ぶのかを確認してください。
• WHERE 条件: employees.emp_no > 1001 を別の条件に変えたりすると、オプティマイザの選ぶプランが変化する可能性があります。
• インデックスの追加・強制: インデックスを新しく作ってみたり、USE INDEX や FORCE INDEX を使ってオプティマイザにヒントを与えたりすると、EXPLAIN の結果がどう変わるかも興味深いポイントです。

また、複合インデックスの場合は、インデックスをわざと間違ったカラム順で作るなど、望ましくない状況を意図的に作り、オプティマイザがどう選択するかを確認しても良いでしょう。

オプティマイザの複雑性を理解する

ここまでで、MySQLのオプティマイザの動きがかなり高度であることが分かります。
多くの最適化を行う一方で、常に最適解を選ぶとは限らないことも心がけましょう。

オプティマイザが得意なこと

• 結合テーブルの順序再定義
  クエリに書いた順序ではなく、コストモデルに基づいて順番を再構成できる
• 等値伝播(Constant Propagation)
  dept_name='Development' が一意に dept_no='d005' に対応する場合、自動的に置き換えて処理を効率化できる
• カバリングインデックス(Covering Index)
  すべての必要なカラムがインデックス上に含まれている場合、テーブルデータを参照せずインデックスだけで結果を取得る
• クエリ実行の早期終了
  必要な行数や条件を満たした時点で処理を打ち切る（例: LIMIT 10 など）
• 式の簡略化や正規化、サブクエリ最適化
  代数的等価変換ルールの利用などで、クエリをよりシンプルな形に書き換える

オプティマイザの限界

一方で、オプティマイザは常に正しい判断をしているわけではありません。

『実践ハイパフォーマンスMySQL 第4版』により、原因の一部を以下に抜粋します。

• 統計情報が不正確だと、誤ったプランを選ぶ場合がある
• コストモデルが実行時間やリソース使用量と必ずしも一致しない
• 特定の固定ルールやヒューリスティックによる最適化が行われ、真に最適なプランを見逃す可能性がある
• MySQL が評価し切れない複雑なプランもあり得る

実行コストにまつわる注意点

クエリ事例を用意していたところ、実行コストに関して、一つ興味深いことに気づきました。

インデックス追加前後に、下記のようにオプティマイザが見積もった実行コストを取得しました。

-- インデックス追加前
mysql> SHOW STATUS LIKE 'Last_query_cost';
+-----------------+--------------+
| Variable_name   | Value        |
+-----------------+--------------+
| Last_query_cost | 65017.509796 |
+-----------------+--------------+

-- インデックス追加後
mysql> SHOW STATUS LIKE 'Last_query_cost';
+-----------------+--------------+
| Variable_name   | Value        |
+-----------------+--------------+
| Last_query_cost | 82463.949332 |
+-----------------+--------------+

上記の結果を見る限り、実はインデックス追加前の方が、実行コストが少ない想定です。

でも、実際の実行結果から見ると、インデックス追加後の方が、実行時間が大幅に改善されたことも事実です。

実行コストはあくまで、オプティマイザがコストモードや統計情報に基づいて見積もった数値です。
見積もりコストが低い方は必ず実行時間が短いとは限りません。
コストが高くなることは、必ずしも実際のパフォーマンスが悪化することを意味するでもありません。

実際のコストは見積もりより高くなったり、低いなったりすることがよくあります。

例えば、コストが高いに見えるが、実際にはデータの読み込みが順序良く行われ、メモリ上にキャッシュされていることで実行効率が想定よりだいぶ良い場合があります。
MySQL は実行時の物理I/O を厳密に把握できないため、推定と実測に乖離が生じることがあります。

そのため、実行コストを絶対的な判断基準にせず、実行計画の詳細や実際の実行時間、クエリの応答性などを確認して、総合的に評価しましょう。

おわりに

以上で、MySQL オプティマイザがどのようにクエリの実行計画を組み立てるかを見てきました。

実際の開発現場では、これよりもはるかに複雑なクエリに直面することが多いでしょう。
その際も EXPLAIN / EXPLAIN ANALYZE を用いて、実行計画と実測値を付き合わせながら、最適化を繰り返していければと思います。

もしさらに深く学びたい場合は、MySQL 公式ドキュメントや「実践ハイパフォーマンスMySQL 第4版」などの専門書、計測ツール（MySQL Workbench など）の活用も検討してみてください。

参照

• MySQL 8.4 公式ドキュメント
• MySQL運用・管理［実践］入門 〜安全かつ高速にデータを扱う内部構造・動作原理を学ぶ
  特に第4章の「クエリ実行計画」の部分
• 実践ハイパフォーマンスMySQL 第4版 (英語版)
  特に第8章の「クエリのパフォーマンスの最適化」の部分
  ※日本語訳は現在第3版しか出ていません（実践ハイパフォーマンスMySQL 第3版）
• MySQL Queries: Behind the Scenes — A Deep Dive into How It All Works
• MySQL 8.4: cost-model
• MySQL Query Optimization: Why the Optimizer Skips Indexes