目的: 欠番を詰める
| bug_id | status | product_name |
|---|---|---|
| 1 | OPEN | OPEN RoundFile |
| 2 | FIXED | ReConsider |
| 4 | OPEN | ReConsider |
- 番号が連番になっていない事を気にしない人もいる
- 一方、欠番を気にする人がいる
- なんで bug_id 3 がないんだ
- 欠番が生じた事の責任をとらされるんではないか
欠番があることの不安を解消するため、番号を埋めようとする ← アンチパターン
アンチパターン: 隙間を埋める
欠けてる行を見つけたとき、それを埋めたいと考える
欠番を割り当てる
新しい行の挿入時に、欠番番号のうち最も小さいものを割り当てる方法
- 最も小さい欠番の値特定のために独自のクエリを利用しなければならない
SELECT b1.bug_id + 1 AS max_bug_id
FROM Bugs b1
LEFT OUTER JOIN Bugs AS b2 ON b1.bug_id + 1 = b2.bug_id
WHERE b2.bug_id IS NULL
ORDER BY b1.bug_id LIMIT 1;
ばぐりそう。。。
- 複数の挿入が同時に実行された場合
duplicateしそう。。。
| bug_id | status | product_name |
|---|---|---|
| 1 | OPEN | OPEN RoundFile |
| 2 | FIXED | ReConsider |
| 4 | OPEN | ReConsider |
| 3 | NEW | Visual TurboBuilder |
| ※ bug_id 3 が新しいレコード |
既存業に番号を振り直す
新しい行の挿入時に埋めていたら欠番がなくなるのに時間がかかる！
=> 既存行並び替えちゃえ
| bug_id | status | product_name |
|---|---|---|
| 1 | OPEN | OPEN RoundFile |
| 2 | FIXED | ReConsider |
| 3 | OPEN | ReConsider |
| ※ bug_id 4 を bug_id=3 に変更 |
-
そもそも降り直しが大変
- 欠番の特定
- 欠けてる行が多かった場合の処理
-
外部キーとして参照されている場合、主キーが変更されると参照できなくなっちゃう
-
存在しない事に意味があるかもしれない
- 削除されたデータ
アンチパターンの見つけ方
- 「挿入をロールバックした後で自動生成された ID 値を再利用するにはどうすればいい？」
- 「bug_id の４番になにが起こったの」
- 「使用されていない最初のIDを取得するクエリはどうやって書いたらいい？」
- 「番号が足らなくなったらどうしよう」
アンチパターンを用いてもよい場合
なし！ 疑似キー潔癖症だめ、絶対
- 疑似キーの値そのものが意味を持つべきでない
- 主キーが意味をもつのであれば、それは自然キーである可能性を疑うべき
解決策: 疑似キーの欠番は埋めない
主キーの値は一意で非NULL の値である必要gあある。
ルールはそれだけで連続する必要がない。
行のナンバリング
- 疑似キーは単調増かする整数値なので行番号と同等に見える
- しかし主キーは一意に特定するためのもの、行番号は結果セットの行の順序であり役割が違う
- 主キーを行番号として利用してはいけない
- 行番号を使用する場合(Pagination などで行のサブセットを取得したい場合)には、単調増かで連続した真の行番号を使う必要がある
- Window function の ROW_NUMBER
- LIMIT, OFFSET
GUID (UUID) の使用
128bit の擬似乱数
- 同じ機別子が生成される可能性がきわめて低いので、事実上一意な
- 並列性がたかい
- 値がランダムなので誰も順番がきにならない
- インデックス効率がシーケンシャルな ID より劣る
- GUID の格納には16バイト 一般的な整数は4バイトでディスク効率がわるい
- 値がランダムなので生成された順番などを推測できない
最も重要な問題
「疑似キーの欠番をうめろ」という依頼がきたらどうやって断るのか
コミュニケーションの問題です
- 主キーを変更したいという要求にたいして、適切に説明し納得してもらう
- 欠番はシステムに害はない、ロールバックや削除なので欠番ができるのは当然のことである
- 欠番を探すのではなく、新しい番号を割り当てる事によって処理が高速、エラーが減る
- 主キーの変更がどれだけリスクが大きくコストがかかるか見積もりを提示する
- それでも主キーの変更が必要であれば、主キー自体に意味が発生しており自然キーなので、自然きオーをしようする
まとめ
疑似キーは行を一意にとくていするもの
行番号とはちがいます！