SQL Antipatterns ch22 Pseudokey Neat-Freak
- Pseudokey Neat-Freak
- Objective: Tidy Up the Data
- Antipattern: Filling in the Corners
- How to Recognize the Antipattern
- Legitimate Uses of the Antipattern
- Solution: Get Over It
- 英語
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がついていないと大変
- 可用性損ねる
- ON UPDATE CASCADEがついていないと大変
- せっかく欠番を詰めても、新しいレコードは「詰めて」くれない
- 自動増加カラムは「最後に採番した番号」+1
- 上記の例では、新しい行には
5が振られる4ではなく
- 欠番が多い場合、既存の大量のレコードに対してUPDATE必要
Manufacturing Data Discrepancies
- 主キー変えるな
- 外部システムがその主キーを参照している可能性がある
- 欠番使いまわすな
- まっとうな理由があって削除されたのかもしれない
- 悪いユーザを削除した等
- まっとうな理由があって削除されたのかもしれない
How to Recognize the Antipattern
- こんなのが聞こえてきたら注意
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への教育
- マネージャは大抵コストベースで物事を優先度付する
- 現実的なコストを知れば、しょうもない要求を取り下げてくれるだろう
- 現実的に見積もりを提示する
- 自然キーを使う
