経緯
「弊社のエンジニアであればSQLの軽いチューニングはできて当然」というレジェンドエンジニアのKさんの言葉を受け下記の本を読みましたのでそのアウトプット＆共有ができればと。
筆者について
クレヨンしんちゃんが大好きなちゃらんぽらん文系出身エンジニア。
この記事もhuluのクレヨンしんちゃんを見ながら書いてます。
SQLをカリッカリにチューニングしたりという経験はないので、誤りがあれば教えてください。
当記事の内容
SQL実践入門──高速でわかりやすいクエリの書き方 の第3章に即した内容になります。
テーマは WHERE句やUNIONで分岐させずにSELECTで分岐させよう！ です
UNIONを使ったSQLの改善
今回はpostgreSQLを使って色々改善例を挙げていきます。
実行計画の見方がわからない場合はコチラを参考に。
①UNIONを使った基礎的な条件分岐
メニューテーブルから2018年以前はprice_8_taxの値を、2019年以降はprice_10_taxの値を取得するSQLを考えます。
対象テーブルのデータ
オリジナルのデータ
| id | name | year | price_8_tax | price_10_tax |
|---|---|---|---|---|
| 1 | お子様ランチ | 2017 | 700 | 756 |
| 2 | お子様ランチ | 2018 | 720 | 778 |
| 3 | お子様ランチ | 2019 | 750 | 810 |
| 4 | お子様ランチ | 2020 | 780 | 842 |
| 5 | ランチセット | 2017 | 1200 | 1296 |
| 6 | ランチセット | 2018 | 1250 | 1350 |
| 7 | ランチセット | 2019 | 1300 | 1404 |
| 8 | ランチセット | 2020 | 1400 | 1512 |
| 9 | モーニング | 2017 | 850 | 918 |
| 10 | モーニング | 2018 | 880 | 950 |
| 11 | モーニング | 2019 | 900 | 972 |
| 12 | モーニング | 2020 | 910 | 983 |
取得したいデータ
| name | year | price |
|---|---|---|
| お子様ランチ | 2017 | 700 |
| お子様ランチ | 2018 | 720 |
| お子様ランチ | 2019 | 810 |
| お子様ランチ | 2020 | 842 |
| ランチセット | 2017 | 1200 |
| ランチセット | 2018 | 1250 |
| ランチセット | 2019 | 1404 |
| ランチセット | 2020 | 1512 |
| モーニング | 2017 | 850 |
| モーニング | 2018 | 880 |
| モーニング | 2019 | 972 |
| モーニング | 2020 | 983 |
改善前のSQL(UNIONで分岐させている)
2018年以前と2019年以降でそれぞれテーブルを作ってunion allで統合しています。
UNIONだとソートやらしてしまうのでUNION ALLで統合。
select name, year, price_8_tax as price
from menu
where year <= 2018
union all
select name, year, price_10_tax as price
from menu
where year >= 2019
;
実行計画
当然といえば当然ですが、テーブルを2回フルスキャンしてますね。
Append (cost=0.00..37.51 rows=334 width=126) (actual time=0.011..0.018 rows=12 loops=1)
-> Seq Scan on menu (cost=0.00..16.25 rows=167 width=126) (actual time=0.011..0.013 rows=6 loops=1)
Filter: (year <= 2018)
Rows Removed by Filter: 6
-> Seq Scan on menu menu_1 (cost=0.00..16.25 rows=167 width=126) (actual time=0.003..0.004 rows=6 loops=1)
Filter: (year >= 2019)
Rows Removed by Filter: 6
Planning Time: 0.084 ms
Execution Time: 0.036 ms
改善後のSQL(SELECT句内で分岐)
SELECT句内でCASE文を使って出力する値を変えてます。
改善前のSQLとレコードの順番は違えど内容はもちろん同じです。
select
name,year,
case
when year <= 2018 then price_8_tax
else price_10_tax
end as price
from menu
;
実行計画
テーブルのスキャン回数が1回になりました。
単純に考えたらテーブルスキャンの回数が2回から1回になったので性能が2倍になったって解釈ができますね。
Seq Scan on menu (cost=0.00..16.25 rows=500 width=126) (actual time=0.014..0.016 rows=12 loops=1)
Planning Time: 0.066 ms
Execution Time: 0.031 ms
②分岐したうえで集計
各プロジェクトについて男女数（member_count）を1レコードにまとめて取得するSQLで考えてみます。
1が男性で0が女性です。
現実のプロジェクトの男女比に近い。。。
対象テーブルのデータ
オリジナルのデータ
| id | project_name | sex | member_count |
|---|---|---|---|
| 1 | プロジェクトA | 1 | 15 |
| 2 | プロジェクトA | 0 | 2 |
| 3 | プロジェクトB | 1 | 38 |
| 4 | プロジェクトB | 0 | 4 |
| 5 | プロジェクトC | 1 | 8 |
| 6 | プロジェクトC | 0 | 0 |
取得したいデータ
| project_name | male_count | female_count |
|---|---|---|
| プロジェクトA | 15 | 2 |
| プロジェクトB | 38 | 4 |
| プロジェクトC | 8 | 0 |
改善前のSQL（男女を別々に集計後にunion allで統合して集計）
select project_name, sum(male_count) as male_count, sum(female_count) as female_count
from (
select project_name, member_count as male_count, null as female_count
from project
where sex = '1' -- 男性
union all
select project_name, null as male_count,member_count as female_count
from project
where sex = '0' -- 女性
) temp
group by project_name;
実行計画
当然ですがテーブルを2回フルスキャンして、、、結合して、、、という実行計画になっております。
GroupAggregate (cost=32.60..32.68 rows=4 width=134) (actual time=0.034..0.036 rows=3 loops=1)
Group Key: project.project_name
-> Sort (cost=32.60..32.61 rows=4 width=126) (actual time=0.029..0.029 rows=6 loops=1)
Sort Key: project.project_name
Sort Method: quicksort Memory: 25kB
-> Append (cost=0.00..32.56 rows=4 width=126) (actual time=0.013..0.019 rows=6 loops=1)
-> Seq Scan on project (cost=0.00..16.25 rows=2 width=126) (actual time=0.012..0.013 rows=3 loops=1)
Filter: (sex = '1'::bpchar)
Rows Removed by Filter: 3
-> Seq Scan on project project_1 (cost=0.00..16.25 rows=2 width=126) (actual time=0.004..0.004 rows=3 loops=1)
Filter: (sex = '0'::bpchar)
Rows Removed by Filter: 3
Planning Time: 0.131 ms
Execution Time: 0.067 ms
改善後のSQL（select句内で条件分岐して集計）
今度はselect句内で男女それぞれを条件分岐させて集計しています。
直感的にわかりやすいSQLで可読性も高くなっているかと思います。
select
project_name,
sum(case when sex='1' then member_count else 0 end) as male_count,
sum(case when sex='0' then member_count else 0 end) as female_count
from project
group by project_name
;
実行計画
テーブルスキャンが1度になってだいぶスッキリした実行計画になりました。
HashAggregate (cost=21.25..23.25 rows=200 width=134) (actual time=0.024..0.025 rows=3 loops=1)
Group Key: project_name
Batches: 1 Memory Usage: 40kB
-> Seq Scan on project (cost=0.00..15.00 rows=500 width=130) (actual time=0.011..0.013 rows=6 loops=1)
Planning Time: 0.096 ms
Execution Time: 0.053 ms
③集計したうえで分岐
各従業員が所属しているプロジェクトを管理するテーブルで、
1プロジェクトのみに所属している場合はプロジェクト名
2プロジェクトに所属している場合は「2プロジェクトを兼務」
3プロジェクト以上に所属している場合は「3プロジェクト以上を兼務」
と表示
対象テーブルのデータ
オリジナルのデータ
| employee_id | employee_name | project |
|---|---|---|
| 1 | なおき | A |
| 2 | だいき | A |
| 3 | だいき | B |
| 4 | ともき | A |
| 5 | ともき | B |
| 6 | ともき | C |
| 7 | まさき | A |
| 8 | まさき | B |
| 9 | まさき | C |
取得したいデータ
| employee_name | project |
|---|---|
| なおき | A |
| だいき | 2プロジェクトを兼務 |
| ともき | 3プロジェクト以上を兼務 |
| まさき | 3プロジェクト以上を兼務 |
改善前のSQL（集計したテーブルを条件分岐してUNIONでくっつける）
select employee_name, max(project) as project
from employee
group by employee_name
having count(1) = 1
union all
select employee_name, '2プロジェクトを兼務'
from employee
group by employee_name
having count(1) = 2
union all
select employee_name, '3プロジェクト以上を兼務'
from employee
group by employee_name
having count(1) >= 3;
実行計画
テーブルへのアクセスとハッシュ演算による集計が3回おこなわれています。
明らかにコストがよくないです。
これがプロジェクト数が1,2,3の場合以上に条件分岐があれば、その回数だけテーブルへのアクセス回数をふやすことになってしまいます。
Append (cost=15.25..52.79 rows=69 width=150) (actual time=0.025..0.041 rows=4 loops=1)
-> HashAggregate (cost=15.25..17.75 rows=1 width=150) (actual time=0.024..0.025 rows=1 loops=1)
Group Key: employee.employee_name
Filter: (count(1) = 1)
Batches: 1 Memory Usage: 40kB
Rows Removed by Filter: 3
-> Seq Scan on employee (cost=0.00..13.00 rows=300 width=236) (actual time=0.011..0.012 rows=9 loops=1)
-> HashAggregate (cost=14.50..17.00 rows=1 width=150) (actual time=0.007..0.007 rows=1 loops=1)
Group Key: employee_1.employee_name
Filter: (count(1) = 2)
Batches: 1 Memory Usage: 40kB
Rows Removed by Filter: 3
-> Seq Scan on employee employee_1 (cost=0.00..13.00 rows=300 width=118) (actual time=0.003..0.004 rows=9 loops=1)
-> HashAggregate (cost=14.50..17.00 rows=67 width=150) (actual time=0.007..0.008 rows=2 loops=1)
Group Key: employee_2.employee_name
Filter: (count(1) >= 3)
Batches: 1 Memory Usage: 40kB
Rows Removed by Filter: 2
-> Seq Scan on employee employee_2 (cost=0.00..13.00 rows=300 width=118) (actual time=0.004..0.005 rows=9 loops=1)
Planning Time: 0.156 ms
Execution Time: 0.091 ms
改善後のSQL（select句内で集計結果を分岐）
select
employee_name,
case
when count(1) = 1 then max(project)
when count(1) = 2 then '2プロジェクトを兼務'
when count(1) >= 3 then '3プロジェクト以上を兼務'
end as project
from employee
group by employee_name
;
実行計画
テーブルへのアクセスおよび集計のハッシュ演算のコストを1/3にできています。
HashAggregate (cost=15.25..18.75 rows=200 width=150) (actual time=0.024..0.026 rows=4 loops=1)
Group Key: employee_name
Batches: 1 Memory Usage: 40kB
-> Seq Scan on employee (cost=0.00..13.00 rows=300 width=236) (actual time=0.011..0.012 rows=9 loops=1)
Planning Time: 0.095 ms
Execution Time: 0.057 ms
UNIONが必要になる、もしくはUNIONも選択肢に入るケース
- そもそもUNION対象のテーブルが異なるケース
→これはどうしようもないです。 - indexの関係等でテーブルへのアクセスが複数回発生してもUNIONのほうがパフォーマンスがいいケース
本節を読んでの感想
筆者が
条件分岐をWHERE句で行うのは素人のやること。プロはSELECT句で分岐させる。
というSQLの格言を紹介してます。
少しはプロに近づけたかな？