D.Horiyama's output

SQL Antipatterns ch22 Pseudokey Neat-Freak

SQL勉強メモ

出典: 

Pseudokey Neat-Freak

Use pseudokeys as unique row identifiers; they’re not row numbers.

  • 「このIDの欠番はなんだ、詰めろ」
  • 帳票出力で上司に指摘される

  • 帳票生成用に主キーの欠番を詰めるとなると、さらに疑問が生ずる

    • 新しいレコードが、古いレコードに割り当てられていた主キーを使いまわしてしまう
    • 古いレコードを再採番前の番号に戻すと、新しいレコードと主キーが重複してしまう

Objective: Tidy Up the Data

bug_id status product_name
1 OPEN Open Roundfile
2 FIXED ReConsider
4 OPEN ReConsider

  • bug_id = 3に何が起きた？

    • DB上からデータが失われてしまった？

  • こういう疑問を解消するために、主キーbug_idの欠番を無くしたくなる

    • DBについての理解が浅い

Antipattern: Filling in the Corners

Assigning Numbers Out of Sequence

bug_id status product_name
1 OPEN Open Roundfile
2 FIXED ReConsider
4 OPEN ReConsider
3 NEW Visual TurboBuilder
  • 最小の欠番を探して、それを使用する

  • デメリット

    • 効率が悪い

      • 「最小の欠番を探す」ための自己結合が余計に必要

    • 並列処理時にエラーが発生してしまう

      • 同じ欠番に2つのクライアントからINSERTを試みてしまう

Renumbering Existing Rows

bug_id status product_name
1 OPEN Open Roundfile
2 FIXED ReConsider
3 OPEN ReConsider
  • 新しいレコードに欠番をちんたら割り当てていては間に合わないケース

  • 既存のレコードの主キーを採番しなおす

    • 主キー値最大のレコードを最小の欠番にUPDATEする

  • 前述の方法に加えてさらなるデメリット

    • 欠番が多い場合、既存の大量のレコードに対してUPDATE必要

      • そのたびごとに並列処理でエラーが出る可能性がある

      • 他テーブルから参照されている場合、影響が波及する

        • ON UPDATE CASCADEがついていないと大変

          • 可用性損ねる

    • せっかく欠番を詰めても、新しいレコードは「詰めて」くれない

      • 自動増加カラムは「最後に採番した番号」+1

      • 上記の例では、新しい行には5が振られる

        • 4ではなく

Manufacturing Data Discrepancies

  • 主キー変えるな

    • 外部システムがその主キーを参照している可能性がある

  • 欠番使いまわすな

    • まっとうな理由があって削除されたのかもしれない

      • 悪いユーザを削除した等

How to Recognize the Antipattern

  • こんなのが聞こえてきたら注意

    • 「INSERTをロールバックしたときのID欠番を使いまわすにはどうすればよいですか」

      • 自動増加カラムの採番はロールバックしない

        • さもないと、採番もトランザクションのスコープ内で行わなければならない

          • 並列処理時に問題になる

    • 「bug_idが4のデータに何が起きた？」

      • 杞憂

    • 「IDの最小の欠番を得るにはどうクエリすれば良い？」

      • 動機はまず間違いなく欠番を再利用すること

    • 「(IDの)数値を使い果たしたらどうなるだろう」

      • 欠番の再利用を正当化する常套句

Legitimate Uses of the Antipattern

  • サロゲートキーなら、ない

    • 意味のない値なので欠番が生じても問題ない
  • 自然キーなら、値を変更することはおかしいことではない

Solution: Get Over It

Numbering Rows

  • 自動増加カラムは1ずつ増える単調増加だが、たまたま実装がそうであるだけ
  • 行番号と自動増加主キーとを混同するな

  • 本当に行番号が欲しければ、ウィンドウ関数ROW_NUMBER()等を使え

    • pagenate等で欲しくなる
    • 【補】なければ自己結合とか

Using GUIDs

  • GUID: Globally Unique ID

  • 128ビットの疑似乱数

    • ふつう32文字の16進表記
    • 実用上ユニーク

  • 利点

    • 複数のDBサーバ共通で使用できる
    • 欠番について文句を言われることがない

  • 欠点

    • 長い

      • タイプするのが大変
      • 16バイト食う
    • ランダムなので、レコードの新旧を類推できない

コラム: Are Integers a Nonrenewable Resource?

  • 単純な算数
  • 64ビット整数ともなると実用上枯渇することはない

The Most Important Problem

  • 上司からの「欠番を詰めろ」という指令をどのようにかわすか

  • 技術面を誠実に説明する

    • 無害
    • 工数削減
    • 実行時性能向上
    • 不具合減少

  • 変更コストを明確にする

    • 現実的に見積もりを提示する

      • 新しい値の算出
      • 実装
      • テスト
      • 変更のカスケーディング
      • 他システムへの影響調査
      • ユーザ、DBAへの教育
    • マネージャは大抵コストベースで物事を優先度付する
    • 現実的なコストを知れば、しょうもない要求を取り下げてくれるだろう

  • 自然キーを使う

    • 主キーの値に意味があるとしたら、それはサロゲートキーではなく自然キーである

      • 自然キーの欠番を論ずるほうが話は簡単
    • サロゲートキーと、表示用カラムを両方使っても良い

英語

  • neat-freak

    • 潔癖症

  • tidy up

    • 整理整頓する

  • get over it

    • (欠番を)乗り越えていく

  • nonrenewable resource

    • 枯渇性の資源

  • misplaced

    • 見当違いの

  • fend off

    • (うるさい質問などを)かわす

  • frivolous

    • どうでもいい