SQL outer joinは外部結合結合カラムの選び方でパフォーマンスが劣化する

SQLのouter joinについてまとめてます。

1 outer joinは外部結合結合条件次第でパフォーマンス劣化の可能性あり
2 まとめ

outer joinは外部結合結合条件次第でパフォーマンス劣化の可能性あり

外部結合とは、left側のテーブルに対応するデータがない場合もNULL値としてデータを取得する結合方法です。

以下のSQLは、employees（社員）テーブルとdept_emp（部署・社員紐付けテーブル）をemp_no（社員番号）でouter joinし、さらにdepartments（部署）テーブルとdept_no（部署番号）でouter joinするSQLです。

//データが完全なので、left joinと同じ結果
SELECT 
	first_name, 
	last_name, 
	dept_name 
FROM 
	employees 
	left outer join dept_emp on employees.emp_no = dept_emp.emp_no 
	left outer join departments on departments.dept_no = dept_emp.dept_no 
limit 20;
+------------+-------------+--------------------+
| first_name | last_name   | dept_name          |
+------------+-------------+--------------------+
| Georgi     | Facello     | Development        |
| Bezalel    | Simmel      | Sales              |
| Parto      | Bamford     | Production         |
| Chirstian  | Koblick     | Production         |
| Kyoichi    | Maliniak    | Human Resources    |
| Anneke     | Preusig     | Development        |
| Tzvetan    | Zielinski   | Research           |
| Saniya     | Kalloufi    | Development        |
| Sumant     | Peac        | Quality Management |
| Duangkaew  | Piveteau    | Production         |
| Duangkaew  | Piveteau    | Quality Management |
| Mary       | Sluis       | Customer Service   |
| Patricio   | Bridgland   | Development        |
| Eberhardt  | Terkki      | Human Resources    |
| Berni      | Genin       | Development        |
| Guoxiang   | Nooteboom   | Research           |
| Kazuhito   | Cappelletti | Sales              |
| Cristinel  | Bouloucos   | Marketing          |
| Kazuhide   | Peha        | Production         |
| Kazuhide   | Peha        | Development        |
+------------+-------------+--------------------+
20 rows in set (0.00 sec)

存在しない部署番号を持つ社員データを追加して、取得できるかどうか試してみましょう。

以下の通り、対応するdepartments（部署）テーブルのレコードがなくてもデータを取得できました。

// employeesテーブルのみ新規データ挿入 dept_empにデータが存在しない
insert employees (emp_no, birth_date, first_name, last_name, gender, hire_date) 
values ( 500000,'1970-01-04','TARO', 'Yamada', 'M', '2001-04-07' );
 
// 部署名（dept_name）の紐付けができないのでNULL値を取得
SELECT 
	first_name, 
	last_name, 
	dept_name 
FROM 
	employees 
	left outer join dept_emp on employees.emp_no = dept_emp.emp_no 
	left outer join departments on departments.dept_no = dept_emp.dept_no 
WHERE
	employees.emp_no=500000;
+------------+-----------+-----------+
| first_name | last_name | dept_name |
+------------+-----------+-----------+
| TARO       | Yamada    | NULL      |
+------------+-----------+-----------+
1 row in set (0.01 sec)

outer joinは、left側のデータを問答無用で全件取得するため、ケースによっては遅いクエリになりやすいので注意が必要です。

例えば、where句でインデックスのついていないカラムを指定した場合は、実行速度が10倍以上違ってきます。

// where句の条件にプライマリキー（emp_no）を指定した場合
mysql> SELECT 
	first_name, 
	last_name, 
	dept_name 
FROM 
	employees 
	left outer join dept_emp on employees.emp_no = dept_emp.emp_no 
	left outer join departments on departments.dept_no = dept_emp.dept_no 
WHERE
	employees.emp_no=500000;
+------------+-----------+-----------+
| first_name | last_name | dept_name |
+------------+-----------+-----------+
| TARO       | Yamada    | NULL      |
+------------+-----------+-----------+
1 row in set (0.00 sec)
 
where句の条件にインデックスのないカラム（first_name）を指定した場合
mysqlSELECT 
	first_name, 
	last_name, 
	dept_name 
FROM 
	employees 
	left outer join dept_emp on employees.emp_no = dept_emp.emp_no 
	left outer join departments on departments.dept_no = dept_emp.dept_no 
WHERE
	employees.first_name='TARO';
+------------+-----------+-----------+
| first_name | last_name | dept_name |
+------------+-----------+-----------+
| TARO       | Yamada    | NULL      |
+------------+-----------+-----------+
1 row in set (0.14 sec)

1つ目のSQLの実行時間は0.00sec、2つ目は0.14secで速度は14倍違います。explainを実行して実行計画を確認すると、後者はtype=ALL、key=NULLのテーブルフルスキャンを実行していることがわかります。

// where条件にプライマリキー(emp_no)を使っている場合
mysql> explain SELECT 
    -> first_name, 
    -> last_name, 
    -> dept_name 
    -> FROM 
    -> employees 
    -> left outer join dept_emp on employees.emp_no = dept_emp.emp_no 
    -> left outer join departments on departments.dept_no = dept_emp.dept_no 
    -> WHERE
    -> employees.emp_no=500000;
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------------------+------+--------
--+-------------+
| id | select_type | table       | partitions | type   | possible_keys | key     | key_len | ref                        | rows | filtere
d | Extra       |
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------------------+------+--------
--+-------------+
|  1 | SIMPLE      | employees   | NULL       | const  | PRIMARY       | PRIMARY | 4       | const                      |    1 |   100.0
0 | NULL        |
|  1 | SIMPLE      | dept_emp    | NULL       | ref    | PRIMARY       | PRIMARY | 4       | const                      |    1 |   100.0
0 | Using index |
|  1 | SIMPLE      | departments | NULL       | eq_ref | PRIMARY       | PRIMARY | 16      | employees.dept_emp.dept_no |    1 |   100.0
0 | NULL        |
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------------------+------+--------
--+-------------+
 
// where条件にインデックスのないカラムを指定している場合
mysql> explain SELECT 
    -> first_name, 
    -> last_name, 
    -> dept_name 
    -> FROM 
    -> employees 
    -> left outer join dept_emp on employees.emp_no = dept_emp.emp_no 
    -> left outer join departments on departments.dept_no = dept_emp.dept_no 
    -> WHERE
    -> first_name='TARO';
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------------------+--------+------
----+-------------+
| id | select_type | table       | partitions | type   | possible_keys | key     | key_len | ref                        | rows   | filte
red | Extra       |
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------------------+--------+------
----+-------------+
|  1 | SIMPLE      | employees   | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                       | 299247 |    10
.00 | Using where |
|  1 | SIMPLE      | dept_emp    | NULL       | ref    | PRIMARY       | PRIMARY | 4       | employees.employees.emp_no |      1 |   100
.00 | Using index |
|  1 | SIMPLE      | departments | NULL       | eq_ref | PRIMARY       | PRIMARY | 16      | employees.dept_emp.dept_no |      1 |   100
.00 | NULL        |
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------------------+--------+------
----+-------------+

クエリの速度を改善するには、where句で使用しているカラムにインデックス付与するなどの対策が必要です。

まとめ

ポテパンダの一言メモ

outer joinは、left側のテーブルを全件取得するjoin。値がない場合はNULLとなる。
whereの条件次第で大きく速度が落ちることがある。
件数が多いテーブルの場合は、explainで実行計画を確認しindexを付与するなどの対策をする。

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか？

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。

SQL outer joinは外部結合結合カラムの選び方でパフォーマンスが劣化する

outer joinは外部結合結合条件次第でパフォーマンス劣化の可能性あり

まとめ

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ」

卒業生の多くがWebエンジニアとして活躍

経験豊富なエンジニア陣が直接指導

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

MOVIE
動画カリキュラム

RECOMMEND
おすすめ記事

SQL outer joinは外部結合 結合カラムの選び方でパフォーマンスが劣化する

outer joinは外部結合 結合条件次第でパフォーマンス劣化の可能性あり

まとめ

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

卒業生の多くがWebエンジニアとして活躍

経験豊富なエンジニア陣が直接指導

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

MOVIE 動画カリキュラム

RECOMMEND おすすめ記事

SQL outer joinは外部結合結合カラムの選び方でパフォーマンスが劣化する

outer joinは外部結合結合条件次第でパフォーマンス劣化の可能性あり

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ」

MOVIE
動画カリキュラム

RECOMMEND
おすすめ記事