[SQLのはじめ方] Part22: 正規化と非正規化の考え方

2025.03.31
SQL
SQLプログラミング入門学習教科書

データベース設計の重要な概念、正規化と非正規化を学んでいきましょう！

はじめに：なぜ正規化と非正規化を考えるの？ 🤔

データベースを設計するとき、「データをどのように整理して保存するか」は非常に重要です。ここで登場するのが正規化（Normalization）と非正規化（Denormalization）という考え方です。

  • 正規化: データの重複をなくし、データの整合性を保ちやすくするための整理整頓術です。これにより、データの管理が容易になり、更新時の不整合を防ぎます。
  • 非正規化: データの取得速度（パフォーマンス）を上げるために、あえてデータの重複を許容する手法です。読み取りが多いシステムで効果を発揮することがあります。

これらはデータベース設計における重要なトレードオフの関係にあります。どちらが良い・悪いではなく、目的や状況に応じて使い分けることが大切です。

正規化：データの整理整頓術 ✨

正規化の目的

正規化の主な目的は以下の2つです。

  • データの冗長性を排除する: 同じ情報が複数の場所に重複して保存されるのを防ぎます。これにより、データ更新時の手間やミスが減り、ディスクスペースの節約にもつながります。
  • データの一貫性と整合性を維持する: データが矛盾なく、常に正しい状態に保たれるようにします。例えば、顧客の住所を変更する際に、一箇所だけ更新すれば全ての関連データに反映されるようになります。これにより、更新時の不整合（更新漏れなど）を防ぎます。

正規化の段階（ステップ）

正規化にはいくつかの段階（正規形）がありますが、主に以下の3つが基本となります。

  1. 第一正規形 (1NF): テーブルの各列の値が「単一の値（原子値）」であること。つまり、一つのセルに複数の値が入っていない状態（繰り返し項目がない状態）です。
  2. 第二正規形 (2NF): 第一正規形であり、かつ、主キーの一部だけに依存する列がないこと（部分関数従属がない）。これは、複数の列で構成される主キー（複合主キー）の場合に特に考慮します。
  3. 第三正規形 (3NF): 第二正規形であり、かつ、主キー以外の列に推移的に依存する列がないこと（推移的関数従属がない）。つまり、主キーに直接依存しない列を排除します。

通常、データベース設計では第三正規形を目指すことが多いです。これにより、多くの冗長性や更新時の問題を解消できます。

正規化のメリット・デメリット

項目メリット 👍デメリット 👎
データ更新更新漏れや矛盾が起きにくい（整合性が高い）–
データ整合性保ちやすい–
データ冗長性少ない（ディスク効率が良い）–
データ取得 (検索)–複数のテーブルを結合（JOIN）する必要があり、クエリが複雑になり、パフォーマンスが低下することがある
設計・実装データ構造が整理され、保守しやすいテーブル数が増え、構造が複雑になることがある

簡単な例：注文データ

例えば、以下のような正規化されていない注文テーブルがあったとします。

-- 正規化されていないテーブル例 (非正規形に近い状態)
CREATE TABLE Orders_NotNormalized (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT,
    CustomerName VARCHAR(100),
    CustomerAddress VARCHAR(255),
    ProductID INT,
    ProductName VARCHAR(100),
    ProductPrice DECIMAL(10, 2),
    Quantity INT
);

このテーブルでは、同じ顧客情報（CustomerName, CustomerAddress）や商品情報（ProductName, ProductPrice）が注文のたびに繰り返し記録され、冗長です。顧客の住所が変わった場合、その顧客の全ての注文レコードを更新する必要があり、手間がかかり、更新漏れのリスクもあります。

これを正規化すると、例えば以下のようにテーブルを分割できます（第三正規形を目指す例）。

-- 顧客テーブル
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    CustomerAddress VARCHAR(255)
);

-- 商品テーブル
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    ProductPrice DECIMAL(10, 2)
);

-- 注文テーブル
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT, -- 外部キー
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- 注文明細テーブル
CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY,
    OrderID INT, -- 外部キー
    ProductID INT, -- 外部キー
    Quantity INT,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

このように分割することで、顧客情報や商品情報はそれぞれのテーブルで一元管理され、冗長性が排除されます。住所変更も `Customers` テーブルの該当レコードを1つ更新するだけで済みます。

非正規化：パフォーマンス向上のための工夫 🚀

非正規化の目的

非正規化の主な目的は、データ取得のパフォーマンス（速度）を向上させることです。

正規化を進めるとテーブルが細かく分割され、データを取り出す際に複数のテーブルを結合（JOIN）する必要が出てきます。データ量が多くなったり、複雑なクエリを実行したりすると、この結合処理がボトルネックとなり、応答速度が遅くなることがあります。

非正規化は、このようなパフォーマンスの問題を解決するために、あえて正規化のルールを破り、データの冗長性を許容する手法です。これにより、クエリの実行に必要なJOIN操作を減らすことができます。

非正規化を行うタイミング

非正規化は、正規化されたデータベースで実際にパフォーマンスの問題が発生した場合や、特定のクエリの応答速度が厳しく要求されるシステム（例：リアルタイム分析、高頻度アクセスされるWebページの表示データ、レポート生成など）で検討されます。

闇雲に非正規化を行うのではなく、まずは正規化された状態を目指し、必要に応じてパフォーマンス測定を行った上で、ボトルネックとなっている箇所に対して慎重に適用するのが一般的です。非正規化は最適化の最後の手段として考えられることもあります。

非正規化のメリット・デメリット

項目メリット 👍デメリット 👎
データ取得 (検索)テーブル結合（JOIN）が減り、クエリが単純化され、パフォーマンスが向上することがある–
データ更新–冗長なデータを持つため、更新漏れや矛盾が発生しやすくなる（更新パフォーマンスが低下することも）
データ整合性–保つのが難しくなる（アプリケーション側でのケアや、追加の更新処理が必要になることも）
データ冗長性–増える（ディスク容量をより多く消費する）
設計・実装特定のクエリは単純化されるデータ管理が複雑になり、将来の変更が難しくなることがある

簡単な例：集計データの保持

例えば、先の正規化された例で、「顧客ごとの注文合計金額」を頻繁に表示する必要があるとします。毎回 `Orders` と `OrderDetails` と `Products` を結合して集計するのは、データ量が多いと時間がかかる可能性があります。

そこで、非正規化の一環として、`Customers` テーブルに `TotalPurchaseAmount` のような列を追加し、注文が入るたびにこの列を更新するようにします（導出項目を持たせる）。

-- 非正規化の例：顧客テーブルに合計購入金額を追加
ALTER TABLE Customers
ADD COLUMN TotalPurchaseAmount DECIMAL(15, 2) DEFAULT 0;

-- 注文処理時に合計金額を更新する（※アプリケーションロジックやトリガーで実装）
-- 例：新しい注文が入ったら、該当顧客のTotalPurchaseAmountを更新する

これにより、「顧客ごとの注文合計金額」を取得するクエリは `Customers` テーブルを見るだけで済み、非常に高速になります。しかし、その代償として、注文データの追加・更新・削除時に `TotalPurchaseAmount` も正確に更新し続ける必要があり、データ整合性を保つための管理コストが増加します。

正規化と非正規化の使い分け：バランスが重要 ⚖️

正規化と非正規化は、どちらか一方が絶対的に正しいというものではありません。データベースの目的、アクセスパターン（読み取り/書き込みの頻度）、パフォーマンス要件、データ更新の頻度などを考慮して、最適なバランスを見つけることが重要です。

使い分けの指針

  • 基本は正規化を目指す: まずは第三正規形までを目指して設計し、データの整合性と保守性を確保します。特に、データの登録・更新・削除（OLTP: Online Transaction Processing）が頻繁に行われるシステムでは正規化が重要です。
  • パフォーマンス要件を確認する: システムの応答速度に関する要件（SLAなど）を確認します。特定のクエリで厳しい要件があるか、全体的なパフォーマンスが問題ないかを見極めます。
  • ボトルネックを特定する: パフォーマンス測定ツール（例：`EXPLAIN`）などを使って、実際にどのクエリやテーブル結合が遅いのかを特定します。
  • 限定的に非正規化を検討する: ボトルネックが特定され、正規化された状態では要件を満たせない場合に限り、影響範囲を考慮しながら非正規化を検討します。非正規化によるデメリット（データ更新の複雑化、整合性維持のコスト）を許容できるかを慎重に判断します。非正規化を実施した場合は、その理由や箇所をドキュメントに残すことが推奨されます。
  • 参照が主体のシステムでは非正規化が有効な場合も: データウェアハウスや分析用のデータベース（OLAP: Online Analytical Processing）、レポート生成、検索システムなど、データの参照（読み取り）が中心で、更新頻度が低いシステムでは、クエリパフォーマンスを優先して非正規化が積極的に採用されることがあります。

例えば、ECサイトの商品検索機能では、検索速度を上げるために商品名、カテゴリ名、ブランド名などを一つのテーブル（あるいは検索エンジン用のインデックス）にまとめて保持する（非正規化する）ことがあります。一方で、注文処理システムでは、データの正確性を保つために正規化されたテーブル構造が適しています。

まとめ 🔖

今回は、データベース設計における重要な概念である「正規化」と「非正規化」について学びました。

  • 正規化は、データの冗長性を排除し、整合性を高めるための基本的なアプローチです。データの更新が多いシステムに適しています。
  • 非正規化は、主にパフォーマンスを向上させるために、あえて冗長性を許容するアプローチです。データの読み取りが多いシステムで検討されます。

どちらの手法にもメリットとデメリットがあり、両者の特性を理解し、システムの要件に合わせて適切に使い分けることが、効率的で信頼性の高いデータベースを構築する鍵となります 🔑。

データベース設計は奥が深いですが、この正規化と非正規化の考え方は、その基礎として非常に重要です。しっかり理解しておきましょう！

コメント

タイトルとURLをコピーしました