今日からできるSQLパフォーマンス向上テクニック3点

はじめに

こんにちは。新卒３年目になりましたtaku_76です。
今回はSQLについての記事を書こうと思います。その経緯としては、業務でSQLを少し書くことはあったのですが、 必要な情報を取り出せたらいいや、くらいの意識しかなく、あまりパフォーマンス面を意識するということはありませんでした。 しかし、今後の新規開発でSQLの改修があった際、パフォーマンスの考慮は必ず必要になってきますので学習することにしました。 学習にあたっては以下の書籍を用いて学習しました。1部と2部に分かれているのですが、今回の記事では1部について、パフォーマンス面に注視して印象に残ったことを具体例とともに取り上げています。あくまで考え方を取り上げたかったため、文法の説明などは割愛しています。

www.shoeisha.co.jp

パフォーマンスの向上のために覚えるべきこと

CASE式

CASE式を使うことで複数のSQL文を1つにまとめ、可読性もパフォーマンスも向上することができます。それを以下に示します。

CASE式の中で集約関数を使う

以下のような社員と所属サークルを一覧化するテーブルを考えます。主キーは、社員番号とサークルIDとします。

shain_cricleテーブル

shain_id	circle_id	circle_name	main_circle_flag
1000	1	野球	YES
1000	2	サッカー	NO
2000	2	サッカー	NO
2000	3	バスケットボール	YES
2000	4	バレーボール	NO
3000	4	バレーボール	NO
4000	5	吹奏楽	NO
5000	6	水泳	NO

社員は複数のクラブに所属している場合と、1つにしか所属していない場合があります。 複数のクラブを掛け持ちしている社員は、メインのサークルを示すフラグ列にYESまたはNOの値が入ります。そうでない社員はNOが入ります。
このテーブルから次のような条件でクエリを発行するとします。

1. 1つだけのクラブに所属している社員については、そのサークルIDを取得する
2. 複数のクラブを掛け持ちしている社員については、メインのサークルIDを取得する

1.2.についてそれぞれ以下のようなクエリを発行すれば抽出が可能です。

-- 1つのサークルに所属している社員を抽出
SELECT shain_id, MAX(circle_id) AS main_circle 
    FROM shain_circle 
  GROUP BY shain_id
HAVING COUNT(*) = 1
ORDER BY shain_id;

-- 結果
 shain_id | main_circle
----------+-------------
     3000 |           4
     4000 |           5
     5000 |           6
(3 行)

-- サークルを掛け持ちしている社員を選択
SELECT shain_id, circle_id AS main_circle
    FROM shain_circle
  WHERE main_circle_flag = 'YES'
ORDER BY shain_id;

-- 結果
 shain_id | main_circle
----------+-------------
     1000 |           1
     2000 |           3
(2 行)

上記でも条件を満たした抽出ができるのですが、場合によって複数のSQLが必要になりパフォーマンスの問題が発生します。 これをまとめたものを、CASE式を使えば1つのSQLで書くことができます。(UNIONでは結局クエリの発行回数は変わらないためパフォーマンスは同じ)

SELECT shain_id,
        CASE WHEN COUNT(*) = 1
            THEN MAX(circle_id)
        ELSE MAX(CASE WHEN main_circle_flag = 'YES'
                   THEN circle_id
                 ELSE NULL END) END AS main_circle
    FROM shain_circle
  GROUP BY shain_id
ORDER BY shain_id;

-- 結果
 shain_id | main_circle
----------+-------------
     1000 |           1
     2000 |           3
     3000 |           4
     4000 |           5
     5000 |           6
(5 行)

ポイントとしては、「１つだけのサークルに所属しているのか、複数のサークルに所属しているのか」という条件分岐をCASE式で表現することです。
元々、集計結果に対する条件はHAVING句を使うと学んでいましたが、CASE式を使うことでSELECT句でも同じような条件分岐を実現することができました。
CASE式はSELECT句で集約関数の中にも外にも書けたりと自由度が高いのでうまく扱えるようになりたいと思います。

EXISTS述語

EXISTSは複数行を一単位と見なした条件を記述することができ、相関サブクエリを利用するにもかかわらずパフォーマンスが優れているという特徴があります。
結論から言うと、EXISTSは「量化」という述語論理の機能を実現するためにSQLに取り込まれました。

述語とは

一言でいうと関数のことですが、EXISTSはSUMなどの普通の関数とは異なり、戻り値が真理値になる関数の事を指します。 例として、=,<,>,などの比較述語や、BETWEEN,LIKE,IN,IS NULLなどがあります。 ここで、=やBETWEENなどと、EXISTSを比較すると「引数に何を取るか」という面で大きな違いがあります。

• =やBETWEEN
  「x = y」や「x BETWEEN y AND z」などの述語の引数は、数字や名前といった単一の値、スカラ値。

• EXISTS
  行の集合(例. SELECT * FROM ○○テーブル WHERE ○○=○○)

上記から分かるように、EXISTSは入力のレベルに特徴があります。述語論理では「入力のレベル」に応じて述語が分類されています。 そして、=やBETWEENなど1行を入力とする述語を「一階の述語」、EXISTSのように行の集合を入力する述語を「二階の述語」といいます。

全称量化と存在量化

述語論理には、量化子という特別な述語が存在します。量化子とは以下の2つの文を書くための道具です。

• 全称量化子

すべてのxが条件Pを満たす

• 存在量化子

条件Pを満たすxが(少なくとも1つ)存在する

EXISTS述語は、述語論理の存在量化子を実装したものです。ここで重要なことがあるのですが、SQLにはもう一方の全称量化子に対応する述語は導入されませんでした。 しかし、全称量化子と存在量化子は片方が定義されていれば同値変形の規則がありますので、「すべての行が条件Pを満たす」という文を「条件Pを満たさない行が存在しない」と変換することで表現できます。これについてEXIST述語を使って書いていきます。

テーブルに存在「しない」データを探す

データベースからデータを検索する場合、一般的にはある条件を満たすものを抽出します。しかし、時にはテーブルに存在「しない」データを抽出する場合もあります。 例えば、複数回行われるミーティングと出席者を記録しておくテーブルがあるとします。

meetingsテーブル

meeting	shain
1回目	A
1回目	B
1回目	C
2回目	A
2回目	D
2回目	C
3回目	B
3回目	D

今回はこのテーブルから「出席しなかった社員」を求めます。
存在するデータに対して「○○という性質を満たす」という条件を設定するのではなく、そもそも「データが存在するかどうか」という、次数の1つ高い問題設定を行います。
これが「二階の問い合わせ」であり、EXISTS述語を使う機会です。
考え方としては、全員が出席したと仮定した場合の集合から、現実に出席した人を引き算することで求めます。

1.全員が出席した場合の集合を求める

SELECT DISTINCT m1.meeting,m2.shain 
    FROM meetings m1 CROSS JOIN meetings m2 
ORDER BY meeting, shain;

-- 結果
 meeting | shain
---------+-------
 1回目   | A
 1回目   | B
 1回目   | C
 1回目   | D
 2回目   | A
 2回目   | B
 2回目   | C
 2回目   | D
 3回目   | A
 3回目   | B
 3回目   | C
 3回目   | D
(12 行)

2.meetingsテーブルに存在しない組み合わせに絞り込む

SELECT DISTINCT m1.meeting,m2.shain 
    FROM meetings m1 CROSS JOIN meetings m2 
  WHERE NOT EXISTS
          (SELECT * 
              FROM meetings m3
            WHERE m1.meeting = m3.meeting AND m2.shain = m3.shain) 
ORDER BY meeting, shain;

-- 結果
 meeting | shain
---------+-------
 1回目   | D
 2回目   | B
 3回目   | A
 3回目   | C
(4 行)

これでうまく抽出することができました。このようにEXISTS述語を使うことで存在「しない」データを抽出することができます。
SQLを書くときに述語論理を意識的に考えることはなかったので、今後そういう視点からも考えてみたいと思います。

ウィンドウ関数

今回、ウィンドウ関数について基本的なことは割愛します。応用方法としては、特に、これまで行間比較において相関サブクエリを使わなければならなかったケースにおいて、 ウィンドウ関数を使うことでスッキリとしたSQL文を記述することができます。

ウィンドウ関数 vs 相関サブクエリ

相関サブクエリとウィンドウ関数を比較すると以下のような違いがあります。

• ウィンドウ関数は、サブクエリを使っているが、「相関」サブクエリではないためサブクエリ単体で実行することができる。 そのため、可読性が高く動作も理解しやすい

• テーブルに対するスキャンが1度だけで済むので、パフォーマンスが良い。

検証は割愛しますが、相関サブクエリもウィンドウ関数も、集合のカットとレコード単位のループという同じ機能を実現していると言えるので置き換えることができます。
次のような商品の名前や価格を格納するテーブルを見てみます。

shohinテーブル

shohin_id	shohin_name	shohin_bunrui	tanka
1	マウス	PC	3500
2	扇風機	家電	7000
3	キーボード	PC	1500
4	文房具	シャーペン	600
5	ドライヤー	家電	3000
6	電球	家電	600
7	電動歯ブラシ	家電	800
8	ボールペン	文房具	150

このshohinテーブルから、各商品分類について平均単価より高い商品を抽出してみます。(相関サブクエリで解く典型的な問題)
まずは、相関サブクエリです。

SELECT shohin_bunrui, shohin_name, tanka
    FROM shohin s1 
  WHERE tanka >
        (SELECT AVG(tanka)
            FROM shohin s2
          WHERE s1.shohin_bunrui = s2.shohin_bunrui
        GROUP BY shohin_bunrui)
ORDER BY shohin_bunrui;

-- 結果
 shohin_bunrui | shohin_name | tanka
---------------+-------------+-------
 PC            | マウス      |  3500
 家電          | 扇風機      |  7000
 家電          | ドライヤー  |  3000
 文房具        | シャーペン  |   600
(4 行)

ポイントとしては、「s1.shohin_bunrui = s2.shohin_bunrui」というs1集合とs2集合に対するバインド条件です。これにより、2つのテーブルの商品分類を同じレコード集合に限定して、その集合の平均単価と各レコードの単価を1行ずつ比較しています。

上記コードと同じ結果を抽出するウィンドウ関数は次のようになります。

SELECT shohin_name, shohin_bunrui, tanka
    FROM (SELECT shohin_name, shohin_bunrui, tanka, AVG(tanka)
                    OVER(PARTITION BY shohin_bunrui) AS avg_tanka
                   FROM shohin) tmp
  WHERE tanka > avg_tanka;

-- 結果
 shohin_name | shohin_bunrui | tanka
-------------+---------------+-------
 マウス      | PC            |  3500
 ドライヤー  | 家電          |  3000
 扇風機      | 家電          |  7000
 シャーペン  | 文房具        |   600
(4 行)

こちらの挙動としては、まずサブクエリのウィンドウ関数で商品分類ごとの平均単価(avg_tanka)を計算しています。

SELECT shohin_name, shohin_bunrui, tanka, AVG(tanka)
  OVER(PARTITION BY shohin_bunrui) AS avg_tanka
    FROM shohin;

-- 結果
 shohin_name  | shohin_bunrui | tanka |       avg_tanka
--------------+---------------+-------+-----------------------
 キーボード   | PC            |  1500 | 2500
 マウス       | PC            |  3500 | 2500
 ドライヤー   | 家電          |  3000 | 2850
 扇風機       | 家電          |  7000 | 2850
 電球         | 家電          |   600 | 2850
 電動歯ブラシ | 家電          |   800 | 2850
 シャーペン   | 文房具        |   600 |  375
 ボールペン   | 文房具        |   150 |  375
(8 行)

あとは各行で「tanka > avg_tanka」という条件で簡単に平均単価と単価の比較が行えます。 このウィンドウ関数の結果のよいところは、商品分類ごとの平均単価を計算しますが、この際、レコードを集約せずに元のテーブルに列として結果を追加するだけ、という情報保全性が働くところです。
個人的に相関サブクエリは動作の理解が難しくあまり好んではいなかったのですが、ウィンドウ関数で同じことを表現することができ、 パフォーマンスも可読性もよいことが分かったので学習を進めてみたいと思いました。

おわりに

いかがだったでしょうか。もし私と同じように、SQLのパフォーマンスを向上するにはどうすればよいか、何を使えば分からないという方の学習のきっかけになれば幸いです。 今回紹介しているのはほんの一部ですので、他にも知りたい、深掘りをしたいと思った方にはぜひおすすめの書籍です。SQLを書くときの考え方が変わるかと思います。
私も今回学習してみて新しい気付きがたくさんありましたので、何度か読み直してみて定着させたいと思います。

---

• エンジニア中途採用サイト
  ラクスでは、エンジニア・デザイナーの中途採用を積極的に行っております！
  ご興味ありましたら是非ご確認をお願いします。
  
  https://career-recruit.rakus.co.jp/career_engineer/

• カジュアル面談お申込みフォーム
  どの職種に応募すれば良いかわからないという方は、カジュアル面談も随時行っております。
  以下フォームよりお申込みください。
  rakus.hubspotpagebuilder.com

• ラクスDevelopers登録フォーム
  
  https://career-recruit.rakus.co.jp/career_engineer/form_rakusdev/

• イベント情報
  会社の雰囲気を知りたい方は、毎週開催しているイベントにご参加ください！

◆TECH PLAY
techplay.jp

◆connpass
rakus.connpass.com