SQL実戦入門を読んだので、ポイントをいくつかまとめておく。
SQLの発行順序
- ジョイン処理
- Whereによる絞込
- GroupByによるグループ化
- selectによる計算と集約
- havingによる絞込
- ウインドウ関数の計算
- Orderbyによる並び替え
- 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選
- その条件ではそもそもそんなに絞り込めない 例）男/女、Yes/No といったもの
- 中間一致、後方一致を使っている 例） column like %aaaa%
- 索引列で演算している 例） column * 0.1 > 0.3 や length(column) > 3
- is nullを使っている。インデックス内にはNULLが存在しないため
- 否定型を使っている。 例）column <> 1 や column not in (3) など