MySQL の実行計画とは？基本から EXPLAIN の読み方まで

ミライトデザイン Advent Calendar 2024 9日目の記事になります。

https://qiita.com/advent-calendar/2024/miraito-inc

8日目は [takuma](https://qiita.com/tkek321) さんの [MySQL のギャップロック（とネクストキーロック）について](https://qiita.com/tkek321/items/b5c6bd46006c34842fa6)の記事でした。

まだ記事を読んでいないのですが、タイトルにある「MySQL のギャップロック」と言う言葉を初めて聞きました。
ギャップロックとはどう言うものなのか？読むのが楽しみです。

# はじめに
この内容は、以前 YouTube 動画撮影用に調べた内容を、技術記事として改めて整理・形に残す目的で作成しました。

「実行計画」という言葉を聞いたことがあるけれど、具体的にどこでどう動いているのかを理解できていない方に向けて書いています。

この記事を読むことで、実行計画の基礎や`EXPLAIN`の出力結果の見方を理解していただけると幸いです。

# 実行計画とは？
実行計画は、SQL実行時にデータベースマネジメントシステム（DBMS）のオプティマイザが「データを実際に取得する手順」を計画したものです。
※オプティマイザについては後述します

以下に「ユーザーがWebサイトで検索を行った」ときを例に、図を用いて説明します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/360742/562cb97f-c960-f862-8b54-88955a5dbfd3.png)

## ①ユーザーが検索を行う
ユーザーが検索フォームにキーワード入力して検索を実行すると、
パーサ (Parser)というモジュールが動き「構文解析」を行います。

この段階では、SQLの構文に誤りがないかをチェックをし、エラーがあれば通知します。

パーサは、DBMS 内の門番のような役割を担っています。
構文チェックをクリアしないと、後続の処理には進めません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/360742/5fbc08bd-11e6-7795-a621-5b3370da2007.png)

## ②パーサ で構文チェックを通った情報を オプティマイザ へ渡す
オプティマイザ（optimizer）は、その名の通り「最適化」を行うモジュールで、DBMS の中でも非常に重要な役割を果たします。

具体的には、後の処理で使用する実行計画を作成し、最適なデータ取得経路（アクセスパス）を選択します。

ただし、この段階では必要な情報（のちに出てるくる統計情報）が不足しているため、まだ実行計画の作成は行いません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/360742/f710770d-8b71-8724-a261-0170fd8de5b1.png)

## ③オプティマイザ が カタログマネージャ へ統計情報の照会をかける
カタログマネージャ は オプティマイザ にとって必要な統計情報を提供する役割を担っています。

統計情報とは、テーブルやインデックス、データなどに関する情報のことで、
いわゆるDBMSの地図のようなものです。

この地図が最新でない場合、最短のアクセスパスを選ぶことができないため、正確な統計情報が必要です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/360742/ba8a4989-0364-376d-82fc-de6c0725a962.png)

## ④ カタログマネージャ が オプティマイザ へ統計情報を返却する
カタログマネージャ から得た統計情報をもとに、オプティマイザは作成した複数のアクセス を比較し、最短時間で処理できると思われる経路を選択します。

ここで生成される手順、がいわゆる「実行計画」です。

実行計画では以下のような決定が行われます。
- インデックスを使用するか（例：インデックススキャン、フルテーブルスキャン）
- 結合の順序をどうするか（例：テーブルAとBのどちらを先に処理するか）
など

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/360742/e3c2f06c-3d7d-ffc0-86ab-5303660e4a69.png)


## ⑤実行計画に従ってテーブルへアクセスを行う
ここまでに作成された実行計画に基づいて、DBMSがテーブルからデータを取得します。

この結果、ユーザーが入力した検索条件に一致するデータが返され、Webサイト上で結果として表示されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/360742/a361e09d-03d3-617d-39fb-20468b8c3c38.png)

# EXPLAIN を実行した環境
- MySQL 8.0

以下の記事を参考に、Docker を使って MySQL の環境を作り実施しました。
[Dockerを使ってMySQLの環境を構築してみました](https://zenn.dev/bloomer/articles/7c879ab2a67abc)

テーブル構造
```sql
mysql> CREATE TABLE user (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(50) NOT NULL,
    ->     email VARCHAR(100) NOT NULL,
    ->     age INT NOT NULL,
    ->     INDEX (email)
    -> );
```
データ
```sql
mysql> select * from user;
+----+---------+---------------------+-----+
| id | name    | email               | age |
+----+---------+---------------------+-----+
| 11 | Alice1  | alice@example.com   |  25 |
| 12 | Alice2  | alice@example.com   |  16 |
| 13 | Bob     | bob@example.com     |  30 |
| 14 | Charlie | charlie@example.com |  35 |
| 15 | David   | david@example.com   |  40 |
| 16 | Emma    | emma@example.com    |  22 |
| 17 | Fiona   | fiona@example.com   |  28 |
| 18 | George  | george@example.com  |  33 |
| 19 | Hannah  | hannah@example.com  |  37 |
| 20 | Ian     | ian@example.com     |  29 |
+----+---------+---------------------+-----+
10 rows in set (0.00 sec)
```

# EXPLAINで実行計画を確認する
`EXPLAIN`ステートメントを使って、SQL文の実行計画を確認できます。
例えば、以下のようなクエリを使用して試してみます。
まずは SELECT 文で検索。

```sql
SELECT * FROM user WHERE email = 'alice@example.com' AND age > 20;
```

結果は以下のようになります。
```sql
+----+--------+-------------------+-----+
| id | name   | email             | age |
+----+--------+-------------------+-----+
| 11 | Alice1 | alice@example.com |  25 |
+----+--------+-------------------+-----+
1 row in set (0.01 sec)
```

次に、SQLの先頭に`EXPLAIN`をつけます。
```sql
EXPLAIN SELECT * FROM user WHERE email = 'alice@example.com' AND age > 20;
```

結果は以下のようになります
```sql
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | email         | email | 402     | const |    2 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

```


# EXPLAIN の出力結果の読み方
ここではチューニング方法の詳細な解説は行いませんが、
チューニング時に注目すべき重要な項目について補足説明します。

※EXPLAIN の重要性
クエリの最適化には、EXPLAIN の出力結果を確認し、必要に応じてクエリを修正 → 再度 EXPLAIN を実行して結果を確認する、というプロセスを繰り返すことが欠かせません。

なお「クエリを最適化する」とは以下を指します。
- 修正前後でクエリの実行結果が同じであること
- EXPLAIN の出力で、より効率的な実行計画が表示されること


## id（クエリの識別子）
各`SELECT`文の識別子を示します。

`JOIN`や`UNION`などある場合は複数行で出力されます。
同じ`id`が複数行に表示される場合、それらのクエリは1つのクエリとして実行されています。

### 今回の結果
```sql
id: 1
```
- クエリが 1 つの単純なSELECT文であるため、`id=1`が表示されています

## ※ select_type（クエリの種類）
クエリの種類を示します。

主な値
- SIMPLE：UNIONやサブクエリを含まない`SELECT`, `JOIN`など
- PRIMARY：主要な`SELECT`
- UNION：`UNION`内の2つ目以降の`SELECT`
- SUBQUERY：サブクエリ内の最初の`SELECT`

### 今回の結果
```sql
select_type: SIMPLE
```
- `UNION`やサブクエリがないため`SIMPLE`と表示されています

## table（対象テーブル）
クエリの対象となるテーブル名を示します。

※テーブル名にエイリアス（別名）を設定していると、エイリアス名が表示されます。

### 今回の結果
```sql
table: user
```
- クエリ対象のテーブルが`user`であるため、`user`と表示されています


## partitions
一致するパーティションを示します。

パーティションを設定している場合、パーティション情報が表示されます。
設定していない場合は`NULL`になります。
（パーティションとは DB の検索を早くするために、テーブルを内部的に分割することです）

### 今回の結果
```sql
partitions: NULL
```
- パーティションを設定していないため`NULL`となっています

## ※ type（アクセス方法）
対象のテーブルへのアクセス方法を示します（結合方法など）
クエリの効率性に大きな影響を与える重要な項目です。

主な値
- const：定数比較（`PRIMARY KEY`や`UNIQUE`による高速検索）
- ref：インデックスを用いた等価検索（`=`または`<=>`演算子を使用）
- ALL：インデックスを利用していないフルテーブルスキャン -> <font color="red">改善必須</font>

:::note warn
改善ヒント:
「ALL」と表示された場合、該当カラムにインデックスを張るか、クエリの条件を見直しましょう。特に大規模データではパフォーマンスへの影響が大きくなります。
:::

### 今回の結果
```sql
type: ref
```
- `email`カラムにインデックスが設定されているため、インデックス参照（`ref`）で検索が行われています
- `email`インデックスを使って検索し、`age > 20`は後でフィルタリングされています

## possible_keys（使用可能なインデックス）
クエリで使用できるインデックスを示します。
`NULL`が表示された場合、使用可能なインデックスが存在しません。

### 今回の結果
```sql
possible_keys: email
```
- クエリ内の`email`カラムにインデックスが設定されているため、`possible_keys: email`が表示されています

## ※ key（使用されたインデックス）
実際に使用されたインデックス名を示します。
`NULL`の場合、インデックスが使用されていないためパフォーマンス改善の余地があります。

:::note warn
想定したインデックスが使われていない場合は、インデックス設定やクエリ内容を見直す余地があると考えられます。
:::

### 今回の結果
```sql
key: email
```
- `email`インデックスが使用され、効率的な検索が行われています

## key_len（インデックスの長さ）
使用されたインデックスの長さ（バイト単位）を示します。
インデックスの型により値が異なります。

※`key`カラムに`NULL`と表示されている場合は、`key_len`カラムも`NULL`となります。

### 今回の結果
```sql
key_len: 402
```
- `email`カラムのデータ型が`VARCHAR(100)`です
- `int`型などを使うと値は小さくなります


## ref（インデックス参照の内容）
`key`（インデックス）カラムに対して、どのような検索条件が適用されたかを示します。

主な値
- const：指定された条件が固定値（定数）であることを示します
    - インデックスを使用した効率的な検索が行われます
- NULL：インデックスが使用されていない場合

### 今回の結果
```sql
ref: const
```
- 検索条件が固定値（`email = 'alice@example.com'`）であるため、const と表示されています

## rows（対象行数の推定）
検索する行数の推定値を示します。
多い場合はパフォーマンス改善の余地があるかもしれません。

### 今回の結果
```sql
rows: 2
```
- インデックス検索の結果、2件の行が対象となったため`rows: 2`が表示されています

## filtered（フィルタリングの割合）
クエリ条件に一致するテーブルの行の推定割合（%）を示します。

- `100`（最大値）
    - 条件にすべての行が一致し、追加のフィルタリングが行われなかったことを示します
    - 効率的な検索が行われています
- `0`に近い場合
    - 条件に一致する行が少なく、多くの行がフィルタリングされたことを意味します
    - 非効率な検索の可能性があります

### 今回の結果
```sql
filtered: 33.33
```
- `age > 20`の条件でデータがフィルタリングされたため、`filtered`が`33.33`となっています

## ※ Extra（追加情報）
オプティマイザがクエリ実行時に選択した戦略や追加処理の内容を示します。
クエリの最適化状況を把握するための**重要な指標**です。

主な値
- Using where
    - インデックスだけでは検索条件を満たせず、`WHERE`句で追加のフィルタリングが行われた場合に表示されます
- Using filesort
    - インデックスを使用したソートが行われず、フルスキャンソートが実行された場合に表示され、パフォーマンスが低下する可能性があります

`Extra`で表示される項目は数が多く紹介しきれないため、一部を紹介しました。
気になる方は調べてみてください。

### 今回の結果
```sql
Extra: Using where
```
- `email`インデックスは使用されていますが、`age > 20`の条件はインデックス外でフィルタリングされているため、`Using where`が表示されています


# EXPLAIN の出力結果の読み方：重要な項目まとめ
これまで多くの項目について説明してきましたが、
チューニングする際に全部見るのは大変だよ、って思うかもしれません。

その場合、まずは以下の項目に注目すると良いと思います。
- select_type
- type
- key
- Extra

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/360742/cf2f0112-7e2e-2628-1b38-47fbb8e103ad.png)


# インデックス有無での比較
ここまで、インデックスの有無についていくつか話が出てきました。
インデックスの有無でそんなに変わるの？と思った方もいるかもしれないので、
実行計画の違いを比較してみます。

---
`email`カラムにインデックスがある場合の実行計画
```sql
mysql> EXPLAIN SELECT * FROM user WHERE email = 'alice@example.com' AND age > 20;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | email         | email | 402     | const |    2 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
```

`email`カラムにインデックスが無い場合の実行計画
```sql
mysql> EXPLAIN SELECT * FROM user WHERE email = 'alice@example.com' AND age > 20;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
```
### インデックス有無による結果の違い
| 項目 | インデックス有 | インデックス無 |
|:-:|:-:|:-:|
| type | ref | ALL |
| key | email  | NULL  |
| rows | 2 | 10 |
| filtered | 33.33 | 10.00 |

整理すると
- インデックス有りの場合
    - `email`カラムにインデックスが使用されるため、効率的に検索が行われ、1件のデータのみが対象となります
- インデックス無しの場合
    - フルテーブルスキャン（ALL）が行われ、データ全体をスキャンするため、効率が悪化し、フィルタリングされた行の割合も減少します

※今回はデータが少ない例ですが、通常のシステムではデータ量が膨大になるため、適切にインデックスを張ることが重要です。
インデックスをうまく活用することで、検索速度が大きく向上し、パフォーマンスの改善が期待できます。

# まとめ
実行計画は、SQLの最適化とパフォーマンス改善において非常に重要です。
特に以下のポイントに注目することで、効率的なSQLを作成できます。

- 必要に応じてインデックスを適切に設定する
- EXPLAINで結果を確認し、不要なフルスキャンを避ける
- 項目ごとの意味を理解し、クエリの改善点を見つける

これを機に、実行計画についてさらに詳しく学び、活用してみてください！

# 終わりに
今回の記事を通して、「実行計画」について考えが整理できたので、よかったなと思います。

明日は、[FrozenVoice](https://qiita.com/FrozenVoice)さんの[2024年に導入して満足度が高かったアイテム10選](https://qiita.com/FrozenVoice/items/2852a9333c73959f4145)という記事です。

毎年色々なアイテムを購入されているので、今年はどんなアイテムが紹介されるのか楽しみです。

# 参考
https://nippondanji.blogspot.com/2009/03/mysqlexplain.html

https://zenn.dev/bloomer/articles/7c879ab2a67abc

https://amzn.asia/d/5eELVwo