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

構成

1章 DBMSのアーキテクチャ〜この世にただ飯はあるか
2章 SQLの基礎〜母国語を話すがごとく
3章 SQLにおける条件分岐〜文から式へ
4章集約とカット〜集合の世界
5章ループ〜手続型の呪縛
6章結合〜結合を制するものはSQLを制す
7章サブクエリ〜困難は分割すべきか
8章 SQLにおける順序〜甦る手続型
9章更新とデータモデル〜盲目のスーパーソルジャー
10章インデックスを使いこなす〜秀才の弱点

メモ

DBMS

多くのトレードオフのバランスを取るために努力を重ねているミドルウェア
- ディスクへの同期処理をすれば整合性・耐障害性は向上するが、パフォーマンスが低下
- パフォーマンスを追求するとその逆。など
SQLの実行時間の大半は、I/Oに費やされる
- I/Oコストが膨らむのを回避すべき
- <- UNION を使った表現はデメリットが大きい。など
  - SELECT文を複数回発行したり、テーブルへの余分なアクセスを発生させて、ストレージのI/Oコストも消費
  - （UNION のメリットが勝るシーンも存在）
  - IN や CASE をうまく使うとテーブルへのスキャンを減らせる可能性

SQL の基礎

SQLの特徴は、行の集合単位でひとまとめにして記述できること
- これが最もよく現れるのが GROUP BY句と HAVING句
- GROUP BYについてパフォーマンス上の注意点は、集約に使われるのがソートであれハッシュであれ、メモリを多く使用する演算であり、十分なワーキングメモリが確保できないと、スワップが発生してストレージ上のファイルが使用されることになり、大幅な遅延が発生すること
GROUP BY はカットと集約の2つの機能から成り立っている
GROUP BY と CASE 式を組み合わせると、強力な表現力

ウインドウ関数

集約機能を省いた GROUP BY句
カットを行った後、その対象の集約をしないため、出力結果の行数が入力となるテーブルの行数と変わらない

ぐるぐる系とガツン系

SQL にはループ処理が無い
- RDB考案者のCodd「関係操作では、関係全体をまとめて操作の対象とする。目的は繰り返し（ループ）を無くすこと」
- 要はSQL は最初に考えられた時から「ループを無くそう」という発想で作られた言語だった
ぐるぐる系
- ループに依存したコードのこと
  - ループが無くて困ったユーザーが、1レコードずつアクセスする細かいSQLをループで回し、ビジネスロジックはホスト言語側で実装する。という手段で生み出されることが多い。
  - ex. 1行ずつレコードにアクセスするSELECT文をループさせる、1行ずつテーブルを更新する。etc
ガツン系
- 複数行を一度に処理するSQL
基本ガツン系の方が良さそうだが、ここも両方にメリデメあってトレードオフ
- ガツン系、パフォーマンスは良いが複雑になって保守性低下など
- ぐるぐる系、基本SQLのパース回数や並列分散のしづらさなどから、パフォーマンス劣る
  - 実行計画安定、処理時間見積りやすい等の利点も

結合

クロス結合
- 全ての結合の母体。2つのテーブルのレコードから、可能な全ての組み合わせ網羅する演算。
内部結合
- クロス結合の結果の一部、つまり部分集合。
外部結合
- マスタ側のテーブルにのみ存在するキーがあった場合、それを削除せず結果に保存するよう動作する。その行はクロス結合の結果のどの行とも一致せず、外部にはみ出している。←ことから外部結合

結合はSQLの性能問題の火薬庫

SQLで結合演算を行う時には、内部で選択されるアルゴリズムを基準に結合を考えることになる
オプティマイザが選択可能な結合アルゴリズムには大きく3種類
基本は Nested Loops というアルゴリズム
結合対象となるテーブルを「駆動表」、もう一方のテーブルを「内部表」と呼ぶ。
Nested Loopsの性能を改善するキーワード「駆動表に小さなテーブルを選ぶ」。
- ↑ の前提として「内部表の結合キーの列にインデックスが存在すること」
- 内部表の結合キー列にインデックスがあれば、そのインデックスを辿ることによって、DBMSは駆動表の1行に対して内部表を馬鹿正直にループする必要がなくなる。
- いわば内部表のループをある程度スキップできる。
結合キーが内部表に対して一意な場合、内部表のループを完全にスキップできる。
- （等値結合であれば、内部表のアクセス対象行を必ず1行に限定できる。）
こうして考えてみると、「駆動表を小さく」という格言は、裏返して「内部表を大きく」という風に解釈するとわかりやすい。
- <- 内部表が大きいほどに、インデックスによるループのスキップ効果が大きくなるから。

結合を減らす

オプティマイザが失敗することもある。
RDBの原則として、実行計画は統計情報からオプティマイザが自動的に組み立てることになっている。
それが最適なものにならないこともしばしば
典型例は、データ量の増加等によって統計情報が変化し、ある一定の閾値を超えたところでオプティマイザが実行計画を変化させることによって起こるもの
そしてこれを最も引き起こしやすい演算が結合。
- <- 考えてみれば当然で、結合の際には複数アルゴリズムが選択できるため、アルゴリズムが変動するタイミングで性能が変動する。
従ってSQLの性能変動リスクを抑えるには、「なるべく結合を回避する」ことが重要な戦略になる

サブクエリ

サブクエリの計算コストが上乗せされることや、その計算結果のI/Oコストがかかること、インデックスなどの情報がないことから最適化を受けられないなどの問題点がある
シンプルな実行計画ほど性能が安定する

上記の通り、結合を使うことは、変動リスクを抱え込むこと。ウインドウ関数などで代替してパフォーマンスを改善できる可能性がある。

インデックス

インデックス利用が有効かの判断に用いられる、2指標
- カーディナリティ（ばらつき具合）の高さ
  - 最も高いのは、全て値が異なる一意キーの列
  - 最も低いのは、値が一種類のみしか存在しない列
- 選択率の低さ
  - すなわち少ない行に絞り込めること（5~10%が目安らしい）
  - 例）100件のレコードを持つテーブルにおいて、一意キーに対して等号指定すれば必ず1件に絞り込める時、この条件の選択率は1%
選択率が極めて高い場合にインデックスを使うと、テーブルフルスキャンより遅くなることもある
インデックスが使用できないパターンも存在
- 中間一致・後方一致のLIKE述語、IS NULL述語を使っている etc

インデックスが使用できない場合の対処

一つに、UI設計による対処
入力フォームを多めに用意して、ユーザーが自由度高く入力条件を組み合わせられるようにし、選択率の高い検索条件を許容するよう実装。
- -> 例えば店舗IDで検索するときには受付日も入力必須とすれば、絞り込みがかなり効くようになる。
したがって、どんな検索条件があり得るかを、ユーザに対する業務要件やユーザインタフェース等も勘案して考える必要がある。