SQL実践入門読了

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

• 集合論としてのクエリだけでなく手続き型のクエリや、それらのクエリ最適化の方法論を学べる
• 長くて複雑なクエリを読み解く勉強にもなる
• でも逆にテーブルの正規化などについては深追いしていない

集約

• GROUP BYは一時バッファを多く使うためスワップに注意
• GROUP BYではソートあるいはハッシュが使われる
• MySQLやPostgreSQLではSELECT句のASをGROUP BYで使えるため便利だが、これはSQL標準ではない
• PARTITION BYはカットするだけで集約をしないのがGROUP BYと異なる点
• GROUP BY句に指定しない列を結果に含める場合は、MAX関数かMIN関数でベクトルからスカラを求める必要がある

インデックス

• LIKEの場合、インデックスが使えるのは前方一致のみ
• 索引列では、カラムに対して演算や関数適用を行うとインデックスが使われない
• IS NULLや否定形では、インデックスが使われない
• インデックスオンリースキャンの考え方はデータマートに着想するもの

結合

• ウィンドウ関数でサブクエリを減らすことで、テーブルフルスキャン回数を減らすことができるかを考える
• 結合のアルゴリズム（Loop/Hash/Merge）をユーザ制御できるDBも存在する（PostgreSQL/SQL Server/Oracle）
  • MySQLは結合アルゴリズムはNested Loop系しかないので選択の余地がない
• 結合においてEXISTやINを使うときは半結合（SEMI JOIN）となり、内部表から最初の行を見つけた時点で、駆動表の次の行までスキップできる
• NOT EXISTを使うときは反結合（ANTI JOIN）となり、内部表から最初の行を見つけた時点で、駆動表の次の行までスキップし、その行は結果から削除する
  • NOT INはまた別の動作となるため注意
• INNER JOINでも、ON句に不等号を用いて求めた結果は、CROSS JOINのように直積に近くなるイメージ

その他

• coalesce()は、与えられた引数のうち、NULLでない最初の引数を返す
• MySQLはrow_number()が無いので、再帰集合を作りCOUNTで数える、あるいは、ユーザ変数でカウントする、などの対応が必要
• MySQLは更新クエリにおいて、テーブルの自己参照ができない
• シーケンスオブジェクトによって、物理層のホットスポット問題*1が起こる
• SET句でサブクエリを使えるSQL-92で導入された行式が利用できるのは、2014年現在でOracleとDB2のみ
• データマートは、同期のタイミングやバッチウィンドウの考慮が必要になり、またテーブルの管理の手間がかかる
• データモデルがコードを決めるのであって、その逆ではない
  • 間違ったデータモデルから出発すると、その間違いをコーディングによって正すことはできない

*1:同じリーフブロックにI/Oが偏る