MySQLとPostgreSQLのExplainの見方

# はじめに

株式会社HRBrain エンジニアの入中です。
エンジニアを始めて約6年になります。その中で5年以上はMySQLを使用して仕事をしてきました。
現在、私が働いているHRBrainでは主にPostgreSQLをメインデータベースとして使用しています。PostgreSQL使用する中で、Explain機能を実行した際、MySQLとは全く表示内容が違うなと思ったので、MySQLとPostgreSQLの`Explain`で表示される実行計画について、それぞれ説明します。

# Explainとは

SQLのクエリ実行計画を表示する機能です。
クエリプランナーがクエリをどのように実行するかを確認できるので、
クエリのパフォーマンス改善を行う時などに使います。

実行クエリの先頭に`Explain`をつけるだけで実行できます。

# 事前準備

## Explainをしていくクエリについて

PostgreSQLとMySQLにそれぞれ以下のテーブルを作成しました。
- users: ユーザ情報
- department: 部署情報
- orders: 注文情報

## ER図

![ER.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/421982/e6b5e7f8-cab4-e1d8-a5fb-a14750b82759.png)

以下のクエリで、MySQL・PostgreSQLの`Explain`の内容を確認していきます。

```explain.sql
EXPLAIN 
SELECT 
  users.name, 
  department.name AS department_name, 
  COUNT(orders.id) AS order_count 
FROM 
  users 
  JOIN department ON users.department_id = department.id 
  LEFT JOIN orders ON users.id = orders.user_id 
WHERE 
  users.age > 25 
GROUP BY 
  users.id, 
  department.id;
```

# 実行計画の見方

## MySQLの結果

![スクリーンショット 2023-07-16 17.32.19.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/421982/5b4de785-5064-bef6-d7ef-09d547195450.png)

MySQLの`Explain`は実行計画が表になって表示されます。

## 項目の説明

![スクリーンショット 2023-07-20 16.30.07.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/421982/91065e2c-9866-883e-153a-49aeab0fbaf0.png)

<!--
|カラム名 |説明|添付画像に表示されている内容|
| --- | --- |---|
| id |select_type、tableなどと組み合わせてクエリの実行計画やデータ処理の順序を示す。連番で表示される。|クエリの実行計画内での行の順序が特に重要ではない場合、実行計画が単純な場合にidが1になることが多い。|
|select_type |クエリの種類。|**SIMPLE**: シンプルなSELECTステートメント。JOINやサブクエリなどが使用されていない。|
|table|クエリ実行によりアクセスするテーブル。||
|partitions|利用したpartitionテーブル。|特にpartitionはきられていない。|
|type|レコードへのアクセス種別。| **ALL** : フルテーブルスキャン。<br>**eq_ref** : JOIN時にPrimary keyもしくはユニークインデックスを利用したアクセス。<br>**ref**: ユニークではないインデックスによる等価検索。|
|possible_keys|選択可能なインデックス。|**users**:department_key<br>**department**:primary_key(id)<br>**orders**:user_id<br>がそれぞれ選択可能なkeyになっている。|
|key| クエリの実行に使用されるインデックスの情報。|**users**:なし <br>**department**:primary_key<br>**orders**:user_id|
|key_len|クエリの実行によって処理される行の数。|
|ref|インデックスと比較されるカラム。||
|rows|調査される行の見積もり。||
|filtered|テーブル条件によってフィルタ処理される行の割合。|usersは`WHERE users.age > 25`の条件でフィルタされている。|
|Extra|追加情報。|**Using where**: クエリにwhere句が使われている。ただし、インデックス情報だけではwhere句の条件を適用できない場合。<br>**Using temporary**: Joinの結果をsortしたりする場合、結果を一時的に保持する必要がある場合。<br>**Using index**:インデックスを使用し、テーブルからデータを読み取っている。|
-->

## Explain結果に基づいたクエリ実行計画の見方

1. usersテーブル
    - typeがALLとなっているため、usersテーブルがフルスキャン（全行を読み取る）される
    - possible_keysにdepartment_idというインデックスが表示されてるが、keyに何も表示されないためインデックスは使用されていない
    - rowsが10なので、usersテーブルには合計10行のデータがあることを示している
    - フィルタ条件が適用された行の割合が33.3%であることを示している
2. departmentテーブル
    - typeがeq_refとなっているため、インデックスを使用した等値結合(*1)が行われいる
    - sample.users.department_idとdepartment.idが結合条件となっている
    - rowsが1となっていることは、sample.users.idとorders.user_idを結合した際に結果の行が1つ生成される
    - フィルタ条件が適用された行の割合が100%であることを示している
3. ordersテーブル
    - typeがrefとなっているため、インデックスを使用した範囲結合が行われている
    - sample.users.idとorders.user_idが結合条件となっている
    - sample.users.idとorders.user_idで結合した場合、ordersにはrowが1つ生成される
    - クエリの実行により5つの行が処理される
    - フィルタ条件が適用された行の割合が100%であることを示している

実行順序はテーブルから読み取る必要があります。

## PostgreSQLの結果

![スクリーンショット 2023-07-17 0.57.15.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/421982/2fb0c8ef-98a0-e93e-8748-2f416fddb70d.png)

MySQLの`Explain`とは全く違う表示です。
PostgreSQLのexplainは実行計画がツリー形式になって表示され、各ノードは親ノードと子ノードの関係になっています。

表示された内容の項目を見ていきます。

## 項目の説明

![スクリーンショット 2023-07-20 16.34.21.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/421982/289833d0-fc1c-4dcf-1101-27796c028294.png)

<!--
|カラム名 |説明|添付画像に表示されている内容|
| --- | --- |---|
| cost |`cost=◯◯..△△`<br>◯◯が検索結果の1行目を返すまでにかかる準備のコスト。<br>△△が処理完了までにかかるコスト。|`Seq Scan on orders  (cost=0.00..24.50 rows=1450 width=8)`<br>costが0.00から24.50かかると推定されている。<br>コストは、時間や他のメトリックとの直接的な対応関係ではなく、オプティマイザが推定した相対的な実行コストを表している。|
|rows |返却される行数の結果。|`Seq Scan on orders  (cost=0.00..24.50 rows=1450 width=8)`<br>rowsが1450行と推定されている。<br>PostgreSQLのオプティマイザが統計情報や推定モデルに基づいて行った予測によるものなので、実際のデータ量と異なることがある。|
|width|計画ノードが出力する行のバイト単位での推定平均幅。|`Seq Scan on orders (cost=0.00..24.50 rows=1450 width=8)`<br>推定平均幅が8となっている|
|HashAggregate|集計操作を行うために使用される演算子。GROUP BY句で指定されたカラム）に基づいてデータをグループ化し、集計関数を適用する。||
|Group Key|クエリにGroup Byがある場合に表示される。|users.idとdepartment.idがグループ化されている。|
|Hash Join|Join句が使用されているクエリがある場合に表示される。||
|Hash Cond|Joinを行う際の結合条件。||
|Hash|テーブルの結合を行うためのハッシュ表を作成している。||
|Seq Scan|インデックスと使用せず全行スキャンしている。| テーブルのデータ量が小さい場合、クエリに適切なインデックスがない場合に表示される。|
|Filter|クエリにfilterが含まれていると表示される。||
-->

## Explain結果に基づいたクエリ実行計画の見方

PostgreSQLの実行計画を読むルールは以下の2つです。

1. 順番に子ノードをたどり一番子孫のノードから実行し、親ノードに遡っていく
2. 兄弟要素(※2)がある場合は先に記述されている行から順にノードをたどる

理解しやすいように、実行計画を図にしてみます。

![postgreSQL_2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/421982/1e3c023d-e4f3-d91d-1810-24068a7f9463.png)

1. ordersテーブルのデータをスキャン
2. usersテーブルのデータをスキャン
3. ordersテーブルと結合するためのHash表を作成
4. departmentテーブルをスキャン
5. ordersテーブルとusersテーブルをハッシュ結合
6. departmentテーブルとusersテーブルを結合するためのハッシュ表を作成
7. departmentテーブルとusersテーブルをハッシュ結合
8. user.idとdepartment.idでグループ化

実行順序やクエリのコストなどが明示的に示されています。

## まとめ

**MySQL**

- MySQLの`Explain`の表示は12の項目で実行計画が表示される。
- 表示された12項目の内容を読み取り実行順序を読み取る必要がある。

**PostgreSQL**
- QUERY PLANというテキスト形式で出力される。
- 項目は実行したクエリにより内容が変わる。
- 実行計画の予測cost等を出してくれる。
- 実行順序はツリー形式で明示的に表示され、親ノードと子ノードの関係が明確に示される。
- クエリのコストや結合順序などの詳細情報が提供される。

Explain機能を使用し実行計画の内容を見て、Indexの貼り方やクエリの条件などを見直し、よりパフォーマンスの良いクエリができないかを確認していきます。

**個人的な感想**

PostgreSQLのExplainの表示はMySQLと全く違いましたが、意味がわかるとクエリの実行計画が明示的に示されているのでとてもわかり易いと感じました。

## 補足

※1: 等価検索とは結合条件において2つのテーブルの列同士が等しい値で結びつくことを指します。
※2:兄弟要素とは、Explainで表示した際に同じ親ノードを持ち、同階層に表示されたノードのこと。
表示例では`Hash Right Join`を親ノードに持つ、`Seq Scan on orders`と`Hash`が兄弟関係です。

参考: 
- [MySQL 8.0 リファレンスマニュアル](https://dev.mysql.com/doc/refman/8.0/ja/explain-output.html)
- [USE THE INDEX, LUKE!](https://use-the-index-luke.com/ja/sql/explain-plan/postgresql/operations)
- [【PostgreSQL】Explainの見方（analyze , cost , scan , sort）についてのまとめ](https://postgresweb.com/post-4047)
- [【PostgreSQL】初心者でも読める実行計画の基礎知識](https://tech-blog.rakus.co.jp/entry/20200612/postgreSQL)