【技術書まとめ21】達人に学ぶ SQL徹底指南書

毎週１冊技術書を読んでブログでアウトプットすることが目標で今回が第21弾です。

今回は、達人に学ぶ SQL徹底指南書 を読みました。

SQLの正しい書き方・考え方について丁寧に解説している本です。

特にSQLのパフォーマンスチューニングのところは、すぐに仕事で実践できる具体的な内容で、それ以外のところも知らなかったことが多くとても勉強になったので、SQLに自信がない方は是非ご覧ください。

このまとめには概要だけを書くことでポイントを覚え、実際に使う際に本書の該当箇所にすぐたどり着けるようにしています。

達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ

第1部 魔法のSQL

CASE式のススメ

• CASE式を活用するとSQLでできることの幅がぐっと広がり、書き方もスマートになる

• CASE式には、単純CASE式と検索CASE式がある。検索CASE式の方ができることが多いので、検索CASE式を用いることが多い

• CASE式を使い、SELECT句で条件分岐させるとスッキリかけることがある

• CASE式は、明示的なELSE句がない場合、デフォルトでELSE NULLと見なすという仕様があるので注意が必要

• DECODE関数などと比べた時にCASE文の大きな利点は、式を評価できる点。つまり、CASE文の中でBETWEEN、LIKE、<、IN、EXISTSなどの便利な述語群を使用できる

• CASE式は実行時に評価されて1つの値に定まるので、列名や定数をかける場所には常に書くことができる

• CASE式を駆使することで複数のSQL文を1つにまとめられ、可読性もパフォーマンスも向上する

必ずわかるウィンドウ関数

• ウィンドウ関数のウィンドウとは、範囲という意味である

• ウィンドウ関数は移動平均を求める場合などによく利用される

• ウィンドウ関数は以下の操作を1つの関数に詰め込んでいる
• • PARTITION BY句によるレコード集合のカット
  • ORDER BY句によるレコードの順位付け
  • フレーム句によるカレントレコードを中心としたサブセットの定義

• SQLの内部動作を知るには、「実行計画(execution plan)」を調べればよい。実行計画とは、DBMSがSQL文を実行する際に、どのようなアクセス経路でデータを取得し、どのような計算を行うことが最も効率的かを判断するために作るものである

自己結合の使い方

• 同一のテーブルを対象に行う結合を自己結合(self join)と呼ぶ

• 自己結合は基本的に非等値結合と組み合わせて使われる

• 自己結合は異なるテーブルを結合していると考えると理解しやすい

3値論理とNULL

• 普通のプログラミング言語の真理値型（BOOLEAN型）とSQLの真理値型の違いは、普通の言語はtrue、falseという2つの値を持つのに対し、SQLはそれに加えてunknownという第三の値を持つことである。2つの真理値だけを持つ通常の論理体系が2値論理と呼ばれるのに対し、SQLの論理体系は3値論理と呼ばれて区別される

• NULLに比較述語（＝や > など）を適用した結果は常にunknownになってしまうのは、NULLが値でも変数でもないためである。NULLは、そこに値がないことを示すただの視覚的マーク、目印に過ぎないのに対し、比較述語を適用できるのは値だけなので、NULLに比較述語を適用してもunknownとなってしまう

• 3つの真理値の間には次のような優先順位があり、強い方が弱い方をのみ込む
• • ANDの場合：false > unknown > true（例：false AND unknown -> false）
  • ORの場合：true > unknown > false

• unknownが論理演算に紛れ込むと、SQLが直感に反する動作をしてしまう。これに対処するには、段階的なステップに分けてSQLの動作を追うことが有効

EXISTS述語の使い方

• EXISTSは、SQLにおいて量化を表現する重要な述語である。述語とは、戻り値が真理値（true、false、unknown）となる関数のこと

• EXISTS以外の述語（=や>など）は1行を入力とするのに対し、EXISTSは行の集合を入力とする

• 「全ての行について〜」という全称量化の表現を、「〜でない行が1つも存在しない」という二重否定分に変換し、NOT EXISTSを使う方法がよく使われる

HAVING句の力

• 現在の標準SQLでは、HAVING句を単独で使うことができる

• WHERE句が集合の要素の性質を調べる道具であるのに対し、HAVING句は集合自身の性質を調べる道具である

• SQLで検索条件を設定するときは、検索対象となる実体が集合なのか集合の要素なのかを見極めることが基本である
• • 実体1つにつき1行が対応している → 要素なのでWHERE句を使う
  • 実体1つにつき複数行が対応している → 集合なのでHAVING句を使う

ウィンドウ関数で行間比較を行う

• ウィンドウ関数を用いれば行間比較（異なる行の間で列同士を比較）することが簡単にできる。相関サブクエリでも同じことはできるが、パフォーマンス面、可読性の面でウィンドウ関数の方が優れている

• ウィンドウ関数は、サブクエリを使っているが、相関サブクエリではない。そのため、サブクエリ単体でも実行できるので、可読性が高く動作を理解しやすい。サブクエリ内部だけで実行することで、デバッグも容易に行うことができる

SQLで集合演算

• 集合演算子は重複排除のために暗黙のソートが発生するので、ALLオプションをつけるとソートが行われなくなりパフォーマンスが向上する。よって、重複を気にしなくていい場合、または重複が発生しないことが確実な場合はにはALLオプションをつけるとよい

• UNIONとINTERSECTは冪等性を持つ

SQLを速くするぞ

• SQLパフォーマンスチューニング
• • 効率の良い検索を利用する
  • • サブクエリを引数に取る場合、INよりもEXISTSや結合を使う

• • ソートを回避する
  • • ソートが発生する代表的な演算は次の通り
    • • GROUP BY句
      • ORDER BY句
      • 集約関数（SUM、COUNT、AVG、MAX、MIN）
      • DISTINCT
      • 集合演算子（UNION、INTERSECT、EXCEPT）
      • ウィンドウ関数（RANK、ROW_NUMBER等）

• • • ソートがメモリ上で行われてる間はまだいいが、それでは足りずにストレージを使ったソートが行われるとパフォーマンスが大きく低下する

• • • 集合演算子のALLオプションをうまく使う：重複を気にしなくてよい場合、重複が発生しないことが明らかな場合は、ソートが発生しないALLオプションをつけて使う

• • • DISTINCTをEXISTSで代用する：2つのテーブルを結合した結果を一意にするためにDISTINCTを使っているケースでは、EXISTSで代用することでソートを回避することができる

• • 極値関数（MAX/MIN）でインデックスを使う

• • WHERE句でかける条件はHAVING句には書かない：GROUP BY句による集約はソートなどを行うので事前に行数を絞り込んだ方がよく、うまくいけばWHERE句でインデックスが利用できるため

• • GROUP BY句とORDER BY句でインデックスを使う

• • インデックスが使われない書き方
  • • 索引列に加工を行なっている
    • インデックス列にNULLが存在する
    • 否定形を使っている
    • ORを使っている
    • 後方一致、または中間一致のLIKE述語を用いている
    • 暗黙の型変換を行なっている

• • 中間テーブルを減らす：中間テーブルの問題点は、データを展開するためにメモリを消費することや、元テーブルに存在したインデックスを使うのが難しくなることである
  • • 中間テーブルを使うのではなく、HAVING句を活用する
    • IN述語で複数のキーを利用する場合は、一箇所にまとめる
    • 集約よりも結合を先に行う

• • レコード数を絞れる条件は早い段階で記述する

• SQLにおいて、最大のボトルネックになるのはストレージへのアクセスである。ソートを減らすのも、インデックスを利用するのも、中間テーブルを省略するのも、すべてストレージへのアクセスを減らすことを目的にしている

• SQLの実行順序は、FROM → WHERE → GROUP BY → HAVING → SELECT （→ ORDER BY）である（ORDER BYは正確にはSQLの一部ではないのでカッコとしている）。複雑なSQLを書くときは、いきなりSELECT句から書くより、実行順序に沿ってFROM句から書いた方が自然にロジックを追える

 

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

• 2000年代に、RDBの欠点であるパフォーマンスのスケーラビリティや非構造化データの扱いといった問題がクローズアップされるようになり、それに対する解決策としてNoSQLが登場してきた

• NoSQLには明確な定義はなく、RDBとは異なるアーキテクチャやデータモデルに基づくデータベースという程度のゆるい定義である

• NoSQLの一つに、KVS（Key-Value Store）がある。KVSは、キーとそれによって一意に決まる値という非常にシンプルな構造しか持たない。Redisやmemcachedなどの製品がKVSの機能を備えている

• NoSQLには、ドキュメント指向型DBと呼ばれるタイプもある。JSONやXMLのような自由度の高いドキュメントを、RDBのテーブルに変換することなくネイティブに扱う機能を持つ。製品としては、MongoDB、CouchDBなどが該当する

• NULLについては、まず以下のようにデフォルト値を入れられないか検討し、どうしようもない場合のみNULLを許可するのがよい
• • フラグなどのコードの場合、未コード化用のコードを割り振る
  • 名前の場合、名前が不明用の値を決めて用いる
  • 数値の場合、0で代替する
  • 日付の場合、0000-01-01や9999-12-31のように最大値・最小値で代替する

 

おわりに

ここまで読んでいただきありがとうございます。 

具体例が多くとても分かりやすかったので、気になる方は是非本書をご覧ください。

達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ

次は Effective Ruby を読む予定です。

来週も頑張ります！