初めに

バックエンドエンジニアとしてそれなりに仕事をしているので、度々データベースのパフォーマンスを調査する事がある。この機会に、最適なデータベースとは何なのか一度棚卸ししたいと思い、データベース設計のベストプラクティスを見直してみた(ついでに社内でRailsを触れているのでデータベース設計も軽くRailsで書いた)

最適なデータベース設計とは

データベースの正規化とパフォーマンスのバランスは、データモデリングにおいて非常に重要なテーマです。この記事では、商品管理データベースを例に、Railsを使用して次の2つの異なるアプローチでテーブルを作成する方法を紹介します。

正規化が適正なテーブル作成 - データの整合性と再利用性を最大化する
パフォーマンスが優先のテーブル作成 - クエリの速度と効率を最大化する

1. 正規化が適正なテーブル作成

正規化は、データの重複を避け、整合性を保つために行います。商品管理データベースでは、以下のようにテーブルを設計します。

products テーブル: 商品ID、商品名、カテゴリID
categories テーブル: カテゴリID、カテゴリ名
product_details テーブル: 商品ID、価格、在庫数、説明

class CreateProducts < ActiveRecord::Migration[6.0]
  def change
    create_table :categories do |t|
      t.string :name

      t.timestamps
    end
  
    create_table :products do |t|
      t.string :name
      t.references :category, foreign_key: true

      t.timestamps
    end

    create_table :product_details do |t|
      t.references :product, foreign_key: true
      t.decimal :price
      t.integer :stock
      t.text :description

      t.timestamps
    end
  end
end

この設計はデータベース設計において、基本的かつ理想的な設計になります。このテーブルは全て第3正規化されているため、更新操作が簡単でデータの不整合リスクを減らしたり、新規のテーブル追加が容易になります。しかし、実際の業務ではこうならないケースが多々あります。
例えば、ユーザーの行動や好みに基づいてリアルタイムで商品を推薦する機能をアプリケーションに組み込んだらどうなるでしょうか？このテーブルには、即時のレスポンスを求められる責務が出てきます。商品数が増えていくると、お互いのテーブルのJOIN操作が増大しパフォーマンス低下を招きます。また、アプリケーションは常に成長していくものです。新規のテーブルをどんどんと作成するため、テーブル構成が複雑となる事も懸念点としてあげられます。

この設計のメリット・デメリットをまとめると下記になります。

メリット:

データの整合性: 正規化されたデータベースは、データの重複を排除し、参照整合性を強化することで、データの整合性を向上させます
更新の容易さ: 一箇所のデータ変更で済むため、更新操作が簡単になり、データの不整合リスクを減らします
柔軟性の向上: 正規化されたデータ構造は、将来の変更や新しい要件への対応が容易です
ストレージの効率化: 重複データの排除により、ストレージをより効率的に使用できます

デメリット:

クエリの複雑さ: データが複数のテーブルに分散しているため、JOIN操作が多くなり、クエリが複雑になることがあります
パフォーマンスの低下: 複雑なクエリと多数のJOIN操作は、特に大量のデータを扱う場合、パフォーマンスに影響を与える可能性があります

では、これに対する対応策である「パフォーマンスが優先のテーブル作成」を見て見ましょう

2. パフォーマンスが優先のテーブル作成

パフォーマンスを最優先する場合、クエリの速度を上げるために正規化を緩和することがあります。商品とその詳細を1つのテーブルに統合することで、JOIN操作を減らしてパフォーマンスを向上させることができます。

products テーブル: 商品ID、商品名、カテゴリ名、価格、在庫数、説明

class CreateProductsForPerformance < ActiveRecord::Migration[6.0]
  def change
    create_table :products do |t|
      t.string :name
      t.string :category # カテゴリ名を直接格納
      t.decimal :price
      t.integer :stock
      t.text :description

      t.timestamps
    end
  end
end

この設計はデータベース設計において、パフォーマンスをあげる一般的な形となります。テーブルが第2正規化されており、JOINが不要または少なくなり、クエリの実行速度が向上するため、先ほどの「ユーザーの行動や好みに基づいてリアルタイムで商品を推薦する機能」などの要求に答えられそうです。しかし、こちらはこちらで問題があります。例えば、以下のようなテーブルに変更したとしましょう。

rubyCopy code
class CreateProductsDenormalizedAntipattern < ActiveRecord::Migration[6.0]
  def change
    create_table :products do |t|
      t.string :name
      t.string :category # カテゴリ名を直接格納
      t.decimal :price
      t.integer :stock
      t.text :description
      t.string :supplier_name # 供給者情報を直接格納
      t.string :supplier_contact_info # 供給者の連絡先情報を直接格納
      t.text :other_product_details # 他の製品詳細を非構造化フィールドで格納

      t.timestamps
    end
  end
end

この設計では、supplier_nameやsupplier_contact_infoなどの供給者情報が各製品レコードに直接組み込まれています。これは、供給者情報が変更された場合、関連するすべての製品レコードを更新する必要があることを意味します。これはデータの重複を引き起こしたり、整合性の問題につながります。つまり、パフォーマンスを優先したらしたで問題は発生するのです。

この設計のメリット・デメリットをまとめると下記になります。

メリット:

クエリの高速化: データが一つのテーブルにまとまっているため、JOINが不要または少なくなり、クエリの実行速度が向上します
アプリケーションの単純化: データモデルがシンプルになると、アプリケーションのロジックも単純化され、開発が容易になる場合があります
読み込み操作の最適化: 頻繁に読み込み操作が行われるアプリケーションでは、非正規化によりデータアクセスの効率が向上します

デメリット:

データの重複: 非正規化によりデータが重複すると、ストレージの無駄遣いにつながり、データの整合性を維持するのが難しくなります
更新の困難さ: 重複データの存在は、データを更新する際に複数の場所を変更する必要があり、作業の複雑さとエラーのリスクを増加させます
スケーラビリティへの影響: 初期のパフォーマンス向上と引き換えに、長期的にはデータ量の増加に伴う管理の難しさやパフォーマンスの低下が懸念されます

銀の弾丸はない

「ソフトウェア開発においては抱えてる問題を一挙に解決できものは存在しない」という比喩で「銀の弾丸はない」というものがあります。そして、これはデータベース設計でも同様な事が言えます。つまり、データベースの整合性とパフォーマンスはトレードオフの関係なのです。
今回の商品管理データベースに当てはめると、整合性を優先する場合「リアルタイム対応が必要な機能」の実装を慎重に検討する必要がありますし、反対にパフォーマンスを優先する場合「更新頻度が高い供給者のカラム追加」の実装を慎重に検討する必要があります。

結論

過度な正規の追求によるテーブルの第3正規化はパフォーマンスの低下やテーブルの複雑性につながります。反対に、パフォーマンス向上の追求によるテーブルの第2正規化は、短期的には一部のクエリの速度を向上させるかもしれませんが、長期的にはデータ管理の複雑さを増加させ、データの品質に悪影響を及ぼすことがあります。データベース設計では、パフォーマンスとデータの正確性・保守性のバランスを見極める事が重要となります。

最後に

データベースは本当に奥が深い分野です。今回はその触りの部分を見直して見ました。また、参考にした書籍は下記となります。今回話した、正規化とパフォーマンスのトレードオフ以外にインデックスやデータベースアンチパターンなども記載されていますので、良かったらそちらも読んでみて見て下さい。

達人に学ぶDB設計徹底指南書～初級者で終わりたくないあなたへ | 翔泳社