LoginSignup
3
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@SSKNOK

僕の高速かつ可読性の高いSQL（UNION編）

Last updated at Posted at 2022-10-01

経緯

「弊社のエンジニアであればSQLの軽いチューニングはできて当然」というレジェンドエンジニアのKさんの言葉を受け下記の本を読みましたのでそのアウトプット＆共有ができればと。

SQL実践入門──高速でわかりやすいクエリの書き方

筆者について

クレヨンしんちゃんが大好きなちゃらんぽらん文系出身エンジニア。
この記事も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も選択肢に入るケース

  1. そもそもUNION対象のテーブルが異なるケース
    　→これはどうしようもないです。
  2. indexの関係等でテーブルへのアクセスが複数回発生してもUNIONのほうがパフォーマンスがいいケース

本節を読んでの感想

筆者が
条件分岐をWHERE句で行うのは素人のやること。プロはSELECT句で分岐させる。
というSQLの格言を紹介してます。
少しはプロに近づけたかな？

3
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?