はじめに
株式会社HRBrain エンジニアの入中です。
エンジニアを始めて約6年になります。その中で5年以上はMySQLを使用して仕事をしてきました。
現在、私が働いているHRBrainでは主にPostgreSQLをメインデータベースとして使用しています。PostgreSQL使用する中で、Explain機能を実行した際、MySQLとは全く表示内容が違うなと思ったので、MySQLとPostgreSQLのExplainで表示される実行計画について、それぞれ説明します。
Explainとは
SQLのクエリ実行計画を表示する機能です。
クエリプランナーがクエリをどのように実行するかを確認できるので、
クエリのパフォーマンス改善を行う時などに使います。
実行クエリの先頭にExplainをつけるだけで実行できます。
事前準備
Explainをしていくクエリについて
PostgreSQLとMySQLにそれぞれ以下のテーブルを作成しました。
- users: ユーザ情報
- department: 部署情報
- orders: 注文情報
ER図
以下のクエリで、MySQL・PostgreSQLのExplainの内容を確認していきます。
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の結果
MySQLのExplainは実行計画が表になって表示されます。
項目の説明
Explain結果に基づいたクエリ実行計画の見方
- usersテーブル
- typeがALLとなっているため、usersテーブルがフルスキャン（全行を読み取る）される
- possible_keysにdepartment_idというインデックスが表示されてるが、keyに何も表示されないためインデックスは使用されていない
- rowsが10なので、usersテーブルには合計10行のデータがあることを示している
- フィルタ条件が適用された行の割合が33.3%であることを示している
- departmentテーブル
- typeがeq_refとなっているため、インデックスを使用した等値結合(*1)が行われいる
- sample.users.department_idとdepartment.idが結合条件となっている
- rowsが1となっていることは、sample.users.idとorders.user_idを結合した際に結果の行が1つ生成される
- フィルタ条件が適用された行の割合が100%であることを示している
- ordersテーブル
- typeがrefとなっているため、インデックスを使用した範囲結合が行われている
- sample.users.idとorders.user_idが結合条件となっている
- sample.users.idとorders.user_idで結合した場合、ordersにはrowが1つ生成される
- クエリの実行により5つの行が処理される
- フィルタ条件が適用された行の割合が100%であることを示している
実行順序はテーブルから読み取る必要があります。
PostgreSQLの結果
MySQLのExplainとは全く違う表示です。
PostgreSQLのexplainは実行計画がツリー形式になって表示され、各ノードは親ノードと子ノードの関係になっています。
表示された内容の項目を見ていきます。
項目の説明
Explain結果に基づいたクエリ実行計画の見方
PostgreSQLの実行計画を読むルールは以下の2つです。
- 順番に子ノードをたどり一番子孫のノードから実行し、親ノードに遡っていく
- 兄弟要素(※2)がある場合は先に記述されている行から順にノードをたどる
理解しやすいように、実行計画を図にしてみます。
- ordersテーブルのデータをスキャン
- usersテーブルのデータをスキャン
- ordersテーブルと結合するためのHash表を作成
- departmentテーブルをスキャン
- ordersテーブルとusersテーブルをハッシュ結合
- departmentテーブルとusersテーブルを結合するためのハッシュ表を作成
- departmentテーブルとusersテーブルをハッシュ結合
- 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が兄弟関係です。
参考: