勉強日記

チラ裏

SQL Antipatterns ch5 Keyless Entry

SQL 勉強メモ 監視

pragprog.com

Make your database mistake-proof with constraints.

Keyless Entry

  • FK制約を使わなかったところDBに齟齬が生じた話
  • 品質管理スクリプトを定期実行
    • 孤立した子レコードを探し、見つけたらメール通知
    • レコードが増えるにつれ処理時間もレポート内容も長くなっていった...
  • 真に必要だったのは、不正な入力があった時点で早々にアプリケーションを落とすこと

Objective: Simplify Database Architecture

  • 参照整合性は、DB設計やDB運用において重要な位置をしめる
  • これを確保するのがFK制約
  • しかし下記理由をつけて忌避されることがある
    • データ更新が制約とぶつかる
    • 柔軟な設計につき、FK制約を適用できない
    • 暗黙に作成されるインデックスがパフォーマンスに悪影響を及ぼす
    • FKをサポートしていない製品を使用している
    • FK宣言のシンタックスをいちいち調べなければならない

Antipattern: Leave Out the Constraints

  • メリット
    • 設計の単純化
    • 柔軟性の向上
    • 高速化
  • デメリット
    • 参照整合性を自前で確保しなければならない

Assuming Flawless Code

  • アプリケーション側で参照整合性の確保を自前実装しなければならない
    • INSERT前に親をSELECT
    • DELETE前に子をSELECT
  • ロック等しない限り、race condition (競合状態)にはなりうる

One in a million is next Tuesday. (Gordon Letwin)

  • ロックは並列制御やスケーラビリティと相性悪い

Checking for Mistakes

  • 孤児レコードを探す
    • LEFT OUTER JOINで駆動表側がNULLになる行 = 内部表側が孤児
  • 疑問
    • どれだけの頻度で実行すればいいの？
    • 見つかったらどうすればいいの？修正できる？
  • そもそも孤児が生じないのが一番

"It's Not My Fault!"

  • DBのデータはつねに一貫性のある状態でなければならない
    • アプリケーションコードに変更を加えた後でも
    • DBを直接触った後でも

Catch-22 Updates

  • 親子を別々に更新できなくなることがある
    • 親を更新しないと子を更新できない
      • 子は親にない値に変更できない
    • 子を更新しないと親を更新できない
      • 親は子から参照されている値を変更できない

How to Recognize the Antipattern

  • こんなのが聞こえてきたら注意
    • 「一方のテーブルに存在する値が、他方のテーブルに存在しないことを確認するには、どうクエリすればいい？」
      • 孤児レコードを探そうとしている
    • 「INSERTしようとしている値が他方のテーブルに存在することを確認する高速な方法は？」
      • 親レコードが存在することを確認しようとしている
      • FK使え
    • 「FKはDBを遅くするから使うなと習った」
      • より大きな問題を生じる
        • パフォーマンス問題が生じることも

Legitimate Uses of the Antipattern

  • 製品がFK制約をサポートしていない場合
  • FK制約を適用できない、柔軟な設計の場合

Solution: Declare Constraints

  • FK制約使え
    • アプリケーション/DB直操作に共通の制限をかけられる
    • コード減る
      • バグも減る

Supporting Multitable Changes

  • 複数テーブルの更新をAtomicに行える
    • 親子同時更新
      • "Catch-22 Updates"にて挙げた、「親子を別々には更新できない状況」の打破
  • ON UPDATE/ON DELETE句を利用可能
    • テーブル追加時なども、アプリケーションコードの変更不要

Overhead? Not Really

  • 少しのオーバーヘッドがあるのは確か
  • だが、トータルで見るとむしろ効率が上がる説
    • INSERT/UPDATE/DELETE前のSELECT確認が不要に
    • 複数テーブル更新時のロックが不要に
    • 孤児レコードの修正のための定期スクリプトが不要に