ちょっと細かいSQL論

SQL実戦入門を読んだので、ポイントをいくつかまとめておく。

SQLの発行順序

1. ジョイン処理
2. Whereによる絞込
3. GroupByによるグループ化
4. selectによる計算と集約
5. havingによる絞込
6. ウインドウ関数の計算
7. Orderbyによる並び替え
8. Limitによる絞込

Group by はホールケーキのカット

　集約とカットを行う。※集約を行わないのがpartition by
　つまり、見方を変えるとその基準がない下記のようなSQLは理論的には成立する。
　select count(*) from address group by ()
　※ 結果的に省略可なだけ
　GroupByを利用する際は、結合との順番に注意する。

　　select C.co_id ,C.district,sum(emp_nbr) as sum_emp 
　　from Companies C inner join Shops S On C.co_id = S.co_id
　　where main_flg = 'U'
　　group by C.co_id

　よりも

　　select C.co_id,C.districe,sum_emp
　　from Copmanies C innder join 
　　　( select co_id,sum(emp_nbr) as sum_emp
　　　　from Shops S where main_flg = 'U' group by co_id ) CSUM
　　where C.co_id = CSUM.co_id

のほうが、結合対象となる行が少なくなるためパフォーマンスが出やすい 。

ビューはselect文をDBに保存しているだけ

　つまり、ビュー発行とは、内部的には２段階SQLを実行している。
　たとえば、vというビューがあったとして
　select * from v としても、
　内部的には下記のように展開されて実行されている。↓
　select * from ( select * from viewmoto) as vv
　よくある多段ビューはさらにその入れ子になるため、パフォーマンスがさらに悪くなる。

union句に注意

　SQLとしては１つだが、実際には複数回テーブルへのアクセスが行われている。
　case式で代用できないか検討すること。
　```
　 select item_name,year,price_a
　from Items
　where year <= 2001
　union
　select item_name,year,price_b
　from Items
　where year > 2001

　よりも

　select item_name,year,

　　　case when year <= 2001 then price_a
　　　　　 when year > 2001 then price_b
　from Items

　のほうが効率は良い場合がある。

## アプリ側から何度も小さいSQL vs 少々小難しいSQLで一発
　一般的にはアプリ側からループで小さいSQLを繰り返し実行し、
　小難しい処理はプログラムで対処する。
　ただ、SQLで一発による対応は下記メリットがあるので、比較検討の上実装する。
　・チューニングポテンシャルが高い（複雑であればあるほど）
　・SQL実行時のオーバーヘッドが低い（繰り返し発行されないため）
　・DBの進化によって受けられる恩恵が高い（エンジンの進化によるもの）

## サブクエリが遅い理由
・一時的に結果をメモリに保持するためオーバーヘッドが生じる
・実行時のみの発行のため、インデックスや制約の情報がなく、DBエンジンによる最適化が受けられない
・相関サブクエリは結果行数分、実行されるためさらにパフォーマンスが悪い

## パフォーマンス向上の為のよくある施策について
・データマートの作成
　⇒　予め必要データを保持しておくため計算処理を省略できるが、
　　　データの鮮度を保つために定期的な同期が必要になる。
- データマートとしては、マテビュー／テンポラリテーブル／ビューの３種類がある

|                  | データ鮮度         | パフォーマンス      | データ保持 | データの格納領域 |
|:-----------------|------------------:|:------------------:|:---------:|:---------------:|
| ビュー                  | 高        | 低             | しない     | -                    |
| マテリアライズド・ビュー | 中         | 高            | する        | データファイル       |
| テンポラリテーブル      | 中         | 中             | 一時的     | 一時ファイル         | 



・検索画面内におけるUI改善
　⇒　検索時における項目間の制約（他項目の条件も必須にするなど）
　　　要はインデックスを効かせやすいようにUI設計する。

・インデックスオンリースキャンの活用
　⇒　select句やwhere句の項目をまとめたカバリングインデックスを作成することで、
　　　テーブルへのアクセスを省略する。（レコード志向ではなくカラム志向へ）

## 行間比較の際に参考になるSQL書き換え
過去の直近を求める、みたいなケースでは行と行を比較する必要がある。
◆相関サブクエリ

　　select LS0.sample_date as cur_date,
　　　(
　　　　select Max(sample_date) from LoadSample LS1
　　　　where LS1.sample_date < LS0.samole_date
　　　) as latest
　　from LoadSample LS0

◆自己結合

　　select LS0.sample_date as cur_date,
　　　　　MAX(LS1.sample_date) as latest
　　from LoadSample LS0
　　　　left join LoadSample LS1 on LS1.sample_date < LS0.sample_date
　　group by LS0.sample_date

いずれも考え方は同じで、カレント（LS0）よりも小さい集合であるLS1から最大の日付を取得する。
※ちなみにウインドウ関数が使えるDBは、結合コストを抑えられる為ウインドウ関数を使うべし。

## インデックスが使われない時にクイックチェック5選
1. その条件ではそもそもそんなに絞り込めない　例）男/女、Yes/No　といったもの
2. 中間一致、後方一致を使っている　例） column like %aaaa%
3. 索引列で演算している　例） column * 0.1 > 0.3　や length(column) > 3
4. is nullを使っている。インデックス内にはNULLが存在しないため
5. 否定型を使っている。　例）column <> 1 や column not in (3) など