😎

【達人に学ぶSQL徹底指南書】を読んでみて③

2023/12/20に公開

SQL

初心者

tech

①本を読んでみて新しい知識になった箇所

9.SQLで集合演算

集合演算子：集合を入力にとる演算を行う。
①UNION : 和 (昔からある)
②INTERSECT : 交差
③EXCEPT : 差

集合演算に関するいくつかの注意点

①ALLオプションが存在する
通常、集合といえば重複する要素を認めない。(例:[1,1,2,2,2]は[1,2]と見なす。)
だが、SQLは重複を認める。そのため、重複行を残すかどうかを考える必要がある。

UNIONなどはデフォルトで重複行を削除するため、重複行を残したい場合にはALLオプションを付ける。
(例:UNION ALL)

またALLオプション無しでは暗黙でソートを行うが、有りだとソートが行われない。
(→パフォーマンスが向上する)
そのため、重複を気にしなくて良い場合はALLオプションを付けるほうが良い。

②演算の順番に優先順位がある
UNIONとEXCEPTより、INTERSECTのほうが先に実行される。

集合の相当性チェック

バックアップや新環境などと比較したい場合など、テーブル同士が等しいかどうかを調べる場合。
このクエリの結果が、tbl_aとtbl_bの行数と等しければ、テーブル同士は等しい。
集合と集合を足して、それでも行数が増えないということは、2つのテーブルの内容が同じだったという原理。

SELECT COUNT(*)
　　　　FROM (SELECT *
  　　　　　　　　　　　　　　　　FROM tbl_a
  　　　　　　　　　　　　　　UNION
  　　　　　　　　　　　　SELECT *
  　　　　　　　　　　　　　　　　FROM tbl_b );

10.SQLで数列を扱う

SQLでのデータの扱い方は2通りある。
①順序を無視した集合と見なす方法。
　　　この場合は、伝統的なSQLの集合と述語による考え方に基づいて考える。
②順序を持った集合と見なす方法。
この場合は、基本的な指針はウィンドウ関数によるダイレクトな順序の操作。

SQLで全称文を記述したいときは、存在文の否定に同値変形して、NOT EXISTS述語を使う必要がある。
これは、SQLが述語論理の存在量化子しか実装していないため。

3人なんですけど、座れますか？

席が3人が並んで座れるような席があるかを調べる。

SELECT s1.seat AS start_seat
      ,'~'
      ,s2.seat AS end_seat
  FROM Seats   AS s1
      ,Seats   AS s2
-- 上で席の全て組み合わせを作っているため、そこから3人が並んで座る場合の1人目の席と3人目の席の組み合わせのみ絞る
 WHERE s2.seat = s1.seat + (3 - 1) -- 3人分
-- 始まり(1人目)の位置〜終わり(3人目)の位置に対して、全て空席であること(→空席じゃない行が無いこと)
   AND NOT EXISTS(SELECT *
                    FROM Seats AS s3
                   WHERE s3.seat BETWEEN s1.seat AND s2.seat
                     AND s3.status <> '空')
 ORDER BY s1.seat
         ,s2.seat;

また、3席連続で空いているということは、空席だけで絞りこんだ後の「ある席(seat)の2行後ろの席番号は seat+2」であるというように考えることができる。
それをウィンドウ関数で実現できる。

SELECT seat AS start_seat
      ,'~'
      ,end_seat
  FROM (SELECT seat
             ,MAX(seat) over(ORDER BY seat rows BETWEEN 2 following 
	                       AND 2 following) AS end_seat 
			     --(3 - 1)とするとsyntaxエラー出た
         FROM Seats
        WHERE status = '空') AS tmp
 WHERE end_seat = seat + (3 - 1);

11.SQLを速くするぞ

レスポンスが悪いのは、SQLだけでなく、システムの物理的な設計が原因であることもしばしば。

・システムの物理的な設計
　　①メモリの配分が悪い
②ストレージ構成が不適切など
・SQL : 実行計画を見て判断することが必要
そのため、本格的なパフォーマンスチューニングを行うためには、使用しているハードウェアやDBMSの機能や特徴についての知識が不可欠。
ここでは、実装非依存でSQLを見直すだけで手軽にできるチューニング方法を記載。

効率の良い検索を利用する

①サブクエリを引数に取る場合、INよりもEXISTSを使う

-- IN
SELECT *
　　　　FROM Class_A
　WHERE id IN (SELECT id
  　　　　　　　　　　　　　　　　　　　　　　　　　　　FROM Class_B);

-- EXISTS
SELECT *
  FROM Class_A  AS A
 WHERE EXISTS (SELECT id
                 FROM Class_B AS B
                WHERE A.id = B.id);

1.もし結合キー(この場合はid)にインデックスが張られている場合、Class_Bの実表は見に行かず、インデックスを参照するのみで済む。
INの引数にサブクエリを与える場合、DBはまずサブクエリから実行し、その結果を一時的なワークテーブルに格納して、その後ビューを全件走査する。
多くの場合これは非常にコストがかかるし、一般にワークテーブルにはインデックスが張られない。
それに比べ、EXISTSはワークテーブルを作らない。そのためINよりもEXISTSのほうが速いと期待できる。

2.EXISTSは1行でも条件に合致する行を見つけたらそこで検索を打ち切るので、INのように全件検索する必要がない。

②サブクエリを引数に取る場合、INよりも結合を使う

-- Join
SELECT A.*
  FROM Class_A As A
  JOIN Class_B AS B -- INNER JOIN
    ON A.id = B.id;

1.少なくともどちらかのテーブルのid列のインデックスが利用できる。
2.サブクエリが無くなったのでワーキングテーブルも作られない。

ソートを回避する

①集合演算子のALLオプションを上手く使う
UNIONなどの集合演算子にALLオプションを付けるとソートが行われないため、重複を気にしなくて良い場合はALLオプションを付けるほうが良い。

②DISTINCTをEXISTSで代用する
DISTINCTも内部的にソートを行っているため、EXISTSで代用するほうが良い。

-- DISTINCT(ソート発生)
SELECT DISTINCT i.item_no
　　　　FROM Items AS i
 INNER JOIN SalesHistory AS sh
  　　　　ON i.item_no = sh.item_no;

-- EXISTS
SELECT i.item_no
　　　　FROM Items 　AS i
 WHERE EXISTS(SELECT *
                FROM SalesHistory AS SH
	       WHERE i.item_no = sh.item_no);

WHERE句で書ける条件はHAVING句には書かない

GROUP BYで集約する前に絞り込むほうが、それ以降に計算する行数が減らせる。

-- 集約した後にHAVING句でフィルタリング
SELECT sale_date
      ,SUM(quantity)
  FROM SalesHistory
 GROUP BY sale_date
HAVING sale_date = "2007-10-01";

-- 集約する前にWHERE句でフィルタリング
SELECT sale_date
      ,SUM(quantity)
  FROM SalesHistory
 WHERE sale_date = "2007-10-01"
 GROUP BY sale_date;

中間テーブルを減らせ

中間テーブルの問題点は、データを展開するためにメモリを消費すること。
また、元テーブルに存在したインデックスを使うのが難しくなること。

①HAVING句を活用する
集約した結果に対する条件は、HAVING句で設定するのが原則。
(慣れていないエンジニアはWHERE句に頼ろうとする。)

SELECT sale_date
　　　　　　　　　　　　,MAX(quantity)
　　　　FROM SomeTable
　　GROUP BY sale_date
HAVING max_qty > 100;

②集約よりも結合を先に行う
集約よりも結合を先に行うことで、中間テーブルが省略(小さく)できる。

12.SQLプログラミング作法

ワイルドカード*は使わない

・取得不要な列まで含まれる。
・SELECT句での結果が、テーブル列の並び順に左右されるため
　　テーブルの列の順番の入れ替えや追加・削除が発生すると結果が狂う可能性がある。

実装依存の関数・演算子を使わない

①使わないようにする : 実装に依存してしまう。
・DECODE (Oracle)
・NVL (Oracle)
・IF (MySQL) : CASE式を使うなど
②極力使わないようにする : 実装状況にバラツキがあり、DBMSの互換性が低下するため。
・EXTRACT
・||
・POSITIONなど
③使ってもいい : 標準SQLではないが、ほぼ全ての実装で使えるため実害がない。
・SIGN : 符号を返す
・ABS : 絶対値
・REPLACE : 文字列を置換など

結合には標準の構文を使う

外部結合のときは、内部結合でなく外部結合であることを示すため、OUTERを省略せず書くのが良い。

相関サブクエリを追放せよ

ウィンドウ関数により、相関サブクエリを消去する。これにより、可読性もパフォーマンスも上がる。
また、単体で実行できない相関サブクエリに対し、ウィンドウ関数は単体で実行できるためデバッグしやすい。

FROM句から書く

(SELECT句などでなく)FROM句から書いたほうが、自然にロジックを追える。

SQLの実行順序
1.FROM句
2.JOIN句
3.WHERE句
4.GROUP BY句
5.HAVING句
6.SELECT句
7.ORDER BY句
8.LIMIT句

第2部　リレーショナルデータベースの世界

第2部に関してはリレーショナルデータベースの歴史等が記載されておりますので
是非、作品を購入してみて読んで見ていただきと思います。
そのため今回は省略させていただきます。

②感想

今回で【達人に学ぶSQL徹底指南書】の最終章となりました。
SQLも単純そうに見えてかなり奥が深いものだとこの作品を通して学ぶことができました。
読むだけではイメージだけで終わってしまうので仕事場含め実践でも使っていこうと思います。