正規化 — 更新異常をなくす規則

TT Labで続きを見る

一言でいうと

正規化は、同じ事実が複数の場所に書かれないように表を分ける手順であり、そうする理由は、保存容量ではなく更新異常をなくすためです。

なぜ必要なのか

注文表1つに、顧客の名前や住所まで一緒に書いておいたとします。3つの問題が生じます。

更新異常: 顧客が引っ越すと、その顧客のすべての注文行を直さなければなりません。1つでも漏らすと、同じ顧客の住所が2通りになります。
挿入異常: まだ注文していない顧客は、登録する場所がありません。
削除異常: 最後の注文を削除すると、その顧客の情報まで一緒に消えます。

正規化は、この3つの異常をなくす手順です。容量を節約できるのは、副次的な効果にすぎません。

どう動くのか

実務で使われるのは、おおむね第3正規形までです。

第1正規形: すべての属性が原子値である必要があります。1つのセルにカンマで複数の値を入れません。
第2正規形: 第1正規形であり、かつ主キーの一部にだけ従属する属性がない必要があります。複合キーのときにだけ問題になります。たとえば(주문번호, 상품번호)がキーなのに、상품명が商品番号にだけ従属するなら、分離します(韓国語の語は、それぞれ注文番号、商品番号、商品名を意味します)。
第3正規形: 第2正規形であり、かつキーでない属性に従属する属性がない必要があります。우편번호が도시を決定するなら、その関係を別の表に切り出します(韓国語の語は、それぞれ郵便番号と都市を意味します)。

核心は、ルールの形ではなく、1つの事実は1か所にだけ書くという原理です。この原理を守れば、更新が常に1か所で終わるので、不整合が構造的に起こりえなくなります。

関数従属を手で探してみる

正規形の定義は、すべて関数従属という1つの概念の上に立っています。A → Bは、「Aが決まればBが1つに決まる」という意味です。表を置いてこの矢印を描いてみると、正規形の判定が機械的にできます。

注文表を例にします。

주문상세(주문번호, 상품번호, 수량, 상품명, 단가, 고객번호, 고객명, 고객주소)

키: (주문번호, 상품번호)

화살표를 그려 보면:
  (주문번호, 상품번호) → 수량          ← 키 전체에 종속. 정상
  상품번호 → 상품명, 단가              ← 키의 "일부" 에만 종속. 2정규형 위반
  주문번호 → 고객번호                  ← 키의 "일부" 에만 종속. 2정규형 위반
  고객번호 → 고객명, 고객주소          ← 키가 아닌 것에 종속. 3정규형 위반

矢印のとおりに、表を分割します。左側が新しい表のキーになります。

주문상세(주문번호, 상품번호, 수량)
상품(상품번호, 상품명, 단가)
주문(주문번호, 고객번호, 주문일)
고객(고객번호, 고객명, 고객주소)

この手順で判断が入る場所は、1つだけです。矢印が実際に成立するかどうかです。「郵便番号 → 都市」は、韓国ではおおむね正しいものの例外があり、アメリカではZIP1つが複数の都市にまたがることもあります。ドメイン知識が必要になる点が、ここです。

正規形の要約表

正規形	なくすもの	一言での判定
1NF	繰り返しグループ、多値	1つのセルに値が1つか
2NF	部分関数従属	複合キーの一部にだけぶら下がったカラムがあるか
3NF	推移的関数従属	キーでないカラムが他のカラムを決定するか
BCNF	候補キーが絡む例外	すべての決定子が候補キーか

BCNFは、3NFを満たしているのに異常が残る、まれなケースを捕まえます。候補キーが複数あり、互いに重なるときに生じ、実務で出会う頻度は低いです。3NFまでが実戦です。

正規化が性能を損なうという誤解

「正規化すると結合が増えて遅くなる」という言葉は、半分しか正しくありません。結合は増えますが、正規化された表は行が短いので、1ページにより多く入り、インデックスも小さくなります。更新は1か所だけに触れるので、はるかに速くなります。

実際に遅くなる場合は、たいていインデックスがないことであって、正規化そのものではありません。外部キーのカラムにインデックスがないと、結合のたびに全体探索が起こります。PostgreSQLは、主キーにはインデックスを自動的に作りますが、外部キーには作りません。これを知らずに、「正規化のせいで遅い」と結論づけることがよくあります。

現場での姿

正規化はデフォルトであって、宗教ではありません。崩すべきときがあり、そのとき何を代償として払うのかを知ったうえで崩す必要があります。

非正規化が正当化される典型的なケースは、集計値の物理化です。投稿のコメント数を毎回数える代わりに、カラムに保持する方式です。代償は明確です。2か所の値がずれる可能性があり、それを防ぐ責任がアプリケーションに移ってきます。ずれる経路は1つではありません。トリガーを迂回した大量削除、トリガーを一時的にオフにしたマイグレーション、複数のコード経路のうちの1つの漏れです。

そのため、非正規化を導入するときは、3つを一緒に残す必要があります。なぜ崩したのか、何が整合性を保証するのか、そしてずれたときに直す再計算クエリです。3つ目を事前に書いておかないと、事故対応の最中に急ごしらえすることになります。

人気の投稿にコメントが集中すると、全員が同じ行を更新しようとして、ロック競合が起きるという点も、知っておく価値があります。代替案は、増分を別の表に追加するだけにして定期的に合算するか、そもそも正確なリアルタイムの値が必要なのかを問い直すことです。ほとんどのカウンターは、5秒遅れても何も起こりません。

続くクイズで確認すること

正規形の定義を暗記する代わりに、どの異常現象をなくそうとしているのかと、いつ崩すのが合理的かを判断できるかを確認します。