SQLアンチパターン 第2版

本書への称賛の声
監訳者まえがき
謝辞
はじめに

1章　アンチパターンとは何か?
    1.1　アンチパターンのタイプ
    1.2　各章の構成──アンチパターンの「解剖」
        1.2.1　ER図
    1.3　サンプルデータベース

第I部　データベース論理設計のアンチパターン

2章　ジェイウォーク（信号無視）
    2.1　目的：複数の値を持つ属性を格納する
    2.2　アンチパターン：カンマ区切りフォーマットのリストを格納する
        2.2.1　特定のアカウントに関連する製品の検索
        2.2.2　特定の製品に関連するアカウントの検索
        2.2.3　集約クエリの作成
        2.2.4　特定の製品に関連するアカウントの更新
        2.2.5　アカウントIDの妥当性検証
        2.2.6　区切り文字の選択
        2.2.7　リストの長さの制限
    2.3　アンチパターンの見つけ方
    2.4　アンチパターンを用いてもよい場合
    2.5　解決策：交差テーブルを作成する
        2.5.1　特定のアカウントに関連する製品の検索／特定の製品に関連するアカウントの検索
        2.5.2　集約クエリの作成
        2.5.3　特定の製品に関連するアカウントの更新
        2.5.4　アカウントIDの妥当性検証
        2.5.5　区切り文字の選択
        2.5.6　リストの長さの制限
        2.5.7　交差テーブルの他のメリット
    2.6　ミニ・アンチパターン：CSV列を複数の行に分割する

3章　ナイーブツリー（素朴な木）
    3.1　目的：階層構造を格納し、クエリを実行する
    3.2　アンチパターン：常に親のみに依存する
        3.2.1　隣接リストへのクエリ実行
        3.2.2　隣接リストのツリーのメンテナンス
    3.3　アンチパターンの見つけ方
    3.4　アンチパターンを用いてもよい場合
    3.5　解決策：代替ツリーモデルを使用する
        3.5.1　再帰クエリ
        3.5.2　経路列挙（Path Enumeration）
        3.5.3　入れ子集合（Nested Set）
        3.5.4　閉包テーブル（Closure Table）
        3.5.5　どの設計を使うべきか
    3.6　ミニ・アンチパターン：「私のコンピューターでは動作しているのに」

4章　IDリクワイアド（とりあえずID）
    4.1　目的：主キーの規約を確立する
    4.2　アンチパターン：すべてのテーブルに「id」列を用いる
        4.2.1　冗長なキーが作成されてしまう
        4.2.2　重複行を許可してしまう
        4.2.3　キーの意味がわかりにくくなる
        4.2.4　USINGを使用する
        4.2.5　複合キーは使いにくい
        4.2.6　主キーは本当に必要なのか
    4.3　アンチパターンの見つけ方
    4.4　アンチパターンを用いてもよい場合
    4.5　解決策：状況に応じて適切に調整する
        4.5.1　わかりやすい列名にする
        4.5.2　規約に縛られない
        4.5.3　自然キーと複合キーの活用
    4.6　ミニ・アンチパターン：BIGINTは十分に大きい？

5章　キーレスエントリ（外部キー嫌い）
    5.1　目的：データベースのアーキテクチャを単純化する
    5.2　アンチパターン：外部キー制約を使用しない
        5.2.1　完璧なコードを前提にしている
        5.2.2　ミスを調べなければならない
        5.2.3　「私のミスではありません！」
        5.2.4　「キャッチ=22」なUPDATE
    5.3　アンチパターンの見つけ方
    5.4　アンチパターンを用いてもよい場合
    5.5　解決策：外部キー制約を宣言する
        5.5.1　複数テーブルの変更をサポートする
        5.5.2　オーバーヘッド……にはなりません

6章　EAV（エンティティ・アトリビュート・バリュー）
    6.1　目的：可変属性をサポートする
    6.2　アンチパターン：汎用的な属性テーブルを使用する
        6.2.1　属性を取得するにはどうするか
        6.2.2　データ整合性をどう保つか
        6.2.3　行を再構築しなければならない
    6.3　アンチパターンの見つけ方
    6.4　アンチパターンを用いてもよい場合
    6.5　解決策：サブタイプのモデリングを行う
        6.5.1　シングルテーブル継承
        6.5.2　具象テーブル継承
        6.5.3　クラステーブル継承
        6.5.4　半構造化データ
        6.5.5　後処理

7章　ポリモーフィック関連
    7.1　目的：複数の親テーブルを参照する
    7.2　アンチパターン：二重目的の外部キーを使用する
        7.2.1　ポリモーフィック関連を定義する
        7.2.2　ポリモーフィック関連を作図する
        7.2.3　ポリモーフィック関連へのクエリ実行
        7.2.4　オブジェクト指向とは無関係の例
    7.3　アンチパターンの見つけ方
    7.4　アンチパターンを用いてもよい場合
    7.5　解決策：関連（リレーションシップ）を単純化する
        7.5.1　参照を逆向きにする
        7.5.2　共通の基底テーブルの作成

8章　マルチカラムアトリビュート（複数列属性）
    8.1　目的：複数の値を持つ属性を格納する
    8.2　アンチパターン：複数の列を定義する
        8.2.1　値の検索
        8.2.2　値の追加と削除
        8.2.3　一意性の保証
        8.2.4　増加する値の処理
    8.3　アンチパターンの見つけ方
    8.4　アンチパターンを用いてもよい場合
    8.5　解決策：従属テーブルを作成する
    8.6　ミニ・アンチパターン：価格の保存

9章　メタデータトリブル（メタデータ大増殖）
    9.1　目的：スケーラビリティを高める
    9.2　アンチパターン：テーブルや列をコピーする
        9.2.1　テーブルの増殖
        9.2.2　データ整合性の管理
        9.2.3　データの同期
        9.2.4　一意性の保証
        9.2.5　テーブルをまたいだクエリ実行
        9.2.6　メタデータの同期
        9.2.7　参照整合性の管理
        9.2.8　メタデータトリブル列の発生
    9.3　アンチパターンの見つけ方
    9.4　アンチパターンを用いてもよい場合
    9.5　解決策：パーティショニングと正規化を行う
        9.5.1　水平パーティショニングの使用
        9.5.2　垂直パーティショニングの使用
        9.5.3　メタデータトリブル列の修正

第II部　データベース物理設計のアンチパターン

10章　ラウンディングエラー（丸め誤差）
    10.1　目的：整数の代わりに小数値を使用する
    10.2　アンチパターン：FLOATデータ型を使用する
        10.2.1　丸めが避けられない
        10.2.2　SQLでのFLOATの使用
    10.3　アンチパターンの見つけ方
    10.4　アンチパターンを用いてもよい場合
    10.5　解決策：NUMERICデータ型を使用する

11章　サーティワンフレーバー（31のフレーバー）
    11.1　目的：列を特定の値に限定する
    11.2　アンチパターン：限定する値を列定義で指定する
        11.2.1　中身は何だろう
        11.2.2　新しいフレーバーの追加
        11.2.3　昔ながらの味は色褪せない
        11.2.4　移植が困難
    11.3　アンチパターンの見つけ方
    11.4　アンチパターンを用いてもよい場合
    11.5　解決策：限定する値をデータで指定する
        11.5.1　値セットの取得
        11.5.2　参照テーブルの値の更新
        11.5.3　廃止された値のサポート
        11.5.4　移植が容易
    11.6　ミニ・アンチパターン：予約語

12章　ファントムファイル（幻のファイル）
    12.1　目的：画像をはじめとする大容量メディアファイルを格納する
    12.2　アンチパターン：物理ファイルの使用を必須と思い込む
        12.2.1　ファイルの削除時における問題
        12.2.2　トランザクション分離の問題
        12.2.3　ロールバック時における問題
        12.2.4　データベースのバックアップツール使用時における問題
        12.2.5　SQLアクセス権限使用時における問題
        12.2.6　ファイルはSQLデータ型ではない
    12.3　アンチパターンの見つけ方
    12.4　アンチパターンを用いてもよい場合
        12.4.1　常に2つの設計を検討する
    12.5　解決策：必要に応じてBLOB型を採用する

13章　インデックスショットガン（闇雲インデックス）
    13.1　目的：パフォーマンスを最適化する
    13.2　アンチパターン：闇雲にインデックスを使用する
        13.2.1　インデックスをまったく定義しない
        13.2.2　インデックスを多く定義し過ぎる
        13.2.3　インデックスが役立たないとき
    13.3　アンチパターンの見つけ方
    13.4　アンチパターンを用いてもよい場合
    13.5　解決策：「MENTOR」の原則に基づいて効果的なインデックス管理を行う
        13.5.1　Measure（測定）
        13.5.2　Explain（解析）
        13.5.3　Nominate（指名）
        13.5.4　Test（テスト）
        13.5.5　Optimize（最適化）
        13.5.6　Rebuild（再構築）
    13.6　ミニ・アンチパターン：すべての列にインデックスを作成する

第III部　クエリのアンチパターン

14章　フィア・オブ・ジ・アンノウン（恐怖のunknown）
    14.1　目的：欠けている値を区別する
    14.2　アンチパターン：NULLを一般値として使う、または一般値をNULLとして使う
        14.2.1　式でNULLを扱う
        14.2.2　NULLを許容する列に対する検索
        14.2.3　クエリパラメータでNULLを扱う
        14.2.4　NULLの使用を避ける
    14.3　アンチパターンの見つけ方
    14.4　アンチパターンを用いてもよい場合
    14.5　解決策：NULLを一意な値として使う
        14.5.1　スカラー式でのNULL
        14.5.2　論理式でのNULL
        14.5.3　NULLの検索
        14.5.4　列にNOT NULL制約を宣言する
        14.5.5　動的なデフォルト
    14.6　ミニ・アンチパターン：NOT IN (NULL)

15章　アンビギュアスグループ（曖昧なグループ）
    15.1　目的：グループ内で最大値を持つ行を取得する
    15.2　アンチパターン：非グループ化列を参照する
        15.2.1　単一値の原則（Single-Value Rule）
        15.2.2　SQLがクエリの意図を汲んでくれるとは限らない
    15.3　アンチパターンの見つけ方
    15.4　アンチパターンを用いてもよい場合
    15.5　解決策：曖昧でない列を使用する
        15.5.1　関数従属性のある列のみにクエリを実行する
        15.5.2　ウィンドウ関数を使用する
        15.5.3　相関サブクエリを使用する
        15.5.4　導出テーブルを使用する
        15.5.5　JOINを使用する
        15.5.6　他の列に対しても集約関数を使用する
        15.5.7　グループごとにすべての値を連結する
        15.5.8　独自ソリューションを使用する
    15.6　ミニ・アンチパターン：ポータブルSQL

16章　ランダムセレクション
    16.1　目的：ランダムに1行をフェッチする
    16.2　アンチパターン：データをランダムにソートする
    16.3　アンチパターンの見つけ方
    16.4　アンチパターンを用いてもよい場合
    16.5　解決策：特定の順番に依存しない
        16.5.1　最小値と最大値の間からランダムにキー値を選択する
        16.5.2　欠番の次のキー値を選択する
        16.5.3　すべてのキー値のリストを受けとり、ランダムに1つを選択する
        16.5.4　オフセットを用いてランダムに行を選択する
        16.5.5　ベンダー依存の解決策
        16.5.6　リデュース・リユース・リサイクル
    16.6　ミニ・アンチパターン：クエリでランダムに複数行を取得する

17章　プアマンズ・サーチエンジン（貧者のサーチエンジン）
    17.1　目的：全文検索を行う
    17.2　アンチパターン：パターンマッチ述語を使用する
    17.3　アンチパターンの見つけ方
    17.4　アンチパターンを用いてもよい場合
    17.5　解決策：適切なツールを使用する
        17.5.1　ベンダー拡張
        17.5.2　サードパーティーのサーチエンジン
        17.5.3　転置インデックスの自作

18章　スパゲッティクエリ
    18.1　目的：SQLクエリの数を減らす
    18.2　アンチパターン：複雑な問題をワンステップで解決しようとする
        18.2.1　意図に反した結果
        18.2.2　さらなる弊害
    18.3　アンチパターンの見つけ方
    18.4　アンチパターンを用いてもよい場合
    18.5　解決策：分割統治を行う
        18.5.1　ワンステップずつ
        18.5.2　上司の問題を解決する
        18.5.3　SQLを用いたSQLの自動的な記述

19章　インプリシットカラム（暗黙の列）
    19.1　目的：タイプ数を減らす
    19.2　アンチパターン：ショートカットの罠に陥る
        19.2.1　リファクタリングにおける問題
        19.2.2　隠れた代償
        19.2.3　求めなければ得られない
    19.3　アンチパターンの見つけ方
    19.4　アンチパターンを用いてもよい場合
    19.5　解決策：列名を明示的に指定する
        19.5.1　誤りの防止
        19.5.2　それは多分、必要ない（YAGNI：You Ain’t Gonna Need It）
        19.5.3　ワイルドカードを使えない局面はいずれ訪れる

第IV部　アプリケーション開発のアンチパターン

20章　リーダブルパスワード（読み取り可能パスワード）
    20.1　目的：パスワードのリカバリーとリセットを行う
    20.2　アンチパターン：パスワードを平文で格納する
        20.2.1　パスワードの格納
        20.2.2　パスワードの認証
        20.2.3　パスワードを電子メールで送信する
    20.3　アンチパターンの見つけ方
    20.4　アンチパターンを用いてもよい場合
    20.5　解決策：ソルトを付けてパスワードハッシュを格納する
        20.5.1　ハッシュ関数を理解する
        20.5.2　SQLでのハッシュの使用
        20.5.3　ハッシュにソルトを加える
        20.5.4　SQLからパスワードを隠す
        20.5.5　パスワードをリカバリーするのではなく、リセットする
    20.6　ミニ・アンチパターン：ハッシュ文字列をVARCHAR型で格納する

21章　SQLインジェクション
    21.1　目的：動的SQLを記述する
    21.2　アンチパターン：未検証の入力をコードとして実行する
        21.2.1　アクシデントは起こる
        21.2.2　最大級のウェブセキュリティ脅威
        21.2.3　対処法の追求
    21.3　アンチパターンの見つけ方
    21.4　アンチパターンを用いてもよい場合
    21.5　解決策：誰も信用してはならない
        21.5.1　入力のフィルタリング
        21.5.2　動的値のパラメータ化
        21.5.3　動的値を引用符で囲む
        21.5.4　ユーザーの入力をコードから隔離する
        21.5.5　他の開発者にコードをレビューしてもらう
    21.6　ミニ・アンチパターン：引用符内のクエリパラメータ

22章　シュードキー・ニートフリーク（疑似キー潔癖症）
    22.1　目的：欠番を詰める
    22.2　アンチパターン：隙間を埋める
        22.2.1　欠番を割り当てる
        22.2.2　既存行に番号を振り直す
        22.2.3　データ不一致の元
    22.3　アンチパターンの見つけ方
    22.4　アンチパターンを用いてもよい場合
    22.5　解決策：疑似キーの欠番は埋めない
        22.5.1　行のナンバリング
        22.5.2　GUIDの使用
        22.5.3　最も重要な問題
    22.6　ミニ・アンチパターン：グループごとの自動インクリメント

23章　シー・ノー・エビル（臭いものに蓋）
    23.1　目的：簡潔なコードを書く
    23.2　アンチパターン：肝心な部分を見逃す
        23.2.1　診断せずに判断する
        23.2.2　見逃しがちなコード
    23.3　アンチパターンの見つけ方
    23.4　アンチパターンを用いてもよい場合
    23.5　解決策：エレガントにエラーから回復する
        23.5.1　戻り値と例外のチェックを行う
        23.5.2　デバッグには実際に構築されたSQLクエリを使用する
    23.6　ミニ・アンチパターン：構文エラーメッセージ解読のすすめ

24章　ディプロマティック・イミュニティ（外交特権）
    24.1　目的：ベストプラクティスを採用する
    24.2　アンチパターン：SQLを特別扱いする
    24.3　アンチパターンの見つけ方
    24.4　アンチパターンを用いてもよい場合
    24.5　解決策：包括的に品質問題に取り組む
        24.5.1　文書化
        24.5.2　バージョン管理
        24.5.3　テスティング
        24.5.4　複数のブランチを扱う
    24.6　ミニ・アンチパターン：名前の変更

25章　スタンダード・オペレーティング・プロシージャ（さびついた開発標準）
    25.1　目的：ストアドプロシージャを使用する
    25.2　アンチパターン：声の大きい人に従う
        25.2.1　手続き型言語
        25.2.2　開発とデプロイ
        25.2.3　パフォーマンスと拡張性
    25.3　アンチパターンの見つけ方
    25.4　アンチパターンを用いても良い場合
    25.5　解決策：現代のアプリケーションアーキテクチャを採用する
    25.6　ミニ・アンチパターン：MySQLのストアドプロシージャ
        25.6.1　パッケージの使用
        25.6.2　デバッグ
        25.6.3　テスト
        25.6.4　コンパイル
        25.6.5　デプロイ
        25.6.6　シャードアーキテクチャの使用

第V部　ボーナス：外部キーのミニ・アンチパターン

26章　標準SQLにおける外部キーの誤った使い方
    26.1　参照方向を逆にしようとする
    26.2　作成前のテーブルを参照しようとする
    26.3　親テーブルのキーを参照していない
    26.4　複合キーの列ごとに個別の制約を作成しようとする
    26.5　間違った列順で外部キーを定義しようとする
    26.6　データ型の不一致
    26.7　文字照合順序の不一致
    26.8　孤立したデータを作成しようとする
    26.9　NULLにできない列に対してSET NULLオプションを使おうとする
    26.10　重複する制約識別子を作成しようとする
    26.11　互換性のないテーブルタイプを使用してしまう

27章　MySQLにおける外部キーの誤った使い方
    27.1　互換性のないストレージエンジンを使おうとする
    27.2　外部キーに大きなデータ型を使おうとする
    27.3　一意でないインデックスへの外部キーを定義しようとする
    27.4　インライン参照構文を使おうとする
    27.5　デフォルト参照構文を使おうとする
    27.6　互換性のないテーブルタイプを使おうとする

付録A　正規化のルール
    A.1　リレーションとは何か
        A.1.1　行に上下の順番がない
        A.1.2　列に左右の順番がない
        A.1.3　重複行を許可しない
        A.1.4　すべての列は単一の型を持ち、各行に1つの値を持つ
        A.1.5　行に隠されたコンポーネントがない
    A.2　正規化の神話
    A.3　正規化とは何か
        A.3.1　第1正規形
        A.3.2　第2正規形
        A.3.3　第3正規形
        A.3.4　ボイス・コッド正規形
        A.3.5　第4正規形
        A.3.6　第5正規形
        A.3.7　他の正規形
    A.4　正規化は常識的なもの

参考文献
索引