物理設計の考え方

このレッスンで分かること

  • 物理設計はデータ型・PK・インデックス・制約・ストレージを決める工程です
  • アクセスパターン・データ量・整合性・運用性の 4 軸で判断します
  • 「速くする」だけでなく「壊れにくくする」のも物理設計の役割

物理設計の考え方 とは

データベースの効率的な構成を考える物理設計の基礎を学びます。本レッスンでは、物理設計の考え方 の基本から実際の使いどころまでを整理し、現場で迷わず使える形に落とし込みます。

なぜ重要か

論理設計で「どんなデータを、どのテーブルで、どう関連付けて持つか」を決めたあと、実際の DBMS の上にそれを落とし込む工程が 物理設計 です。同じ ER 図でも、データ型・インデックス・パーティション・ストレージ設定の選び方で、クエリの応答時間が 100 倍以上変わることが珍しくありません。物理設計はパフォーマンス・運用コスト・データ品質を直接決めるため、設計の最後の砦と言えます。

ここでは物理設計の全体像と、どのような観点で意思決定していくかを整理します。

物理設計で決めること

物理設計でカバーする領域を一度俯瞰しておきます。

diagram (will load when visible)

図の関係を箇条書きで整理すると、次の通りです。

  • 論理 ER 図から物理テーブル定義に落とす
  • データ型と桁 → ストレージ容量に直結
  • 主キーと一意性 → クエリ性能とデータ品質に直結
  • インデックス設計 → 検索性能に直結
  • 制約と既定値 → データ品質に直結

論理設計が「概念」を相手にするのに対し、物理設計は DBMS の制約と性能特性 を相手にします。例えば PostgreSQL では、VARCHAR / TEXT の選び方、INT / BIGINT の選択、TIMESTAMP の扱い、インデックスの張り方が性能と運用性に効いてきます。

代表例 物理設計の比較

論理設計から物理設計に落とすときの典型的な判断ポイントを SQL で見てみましょう。最初は「論理設計のまま」の素朴な実装です。

SQL クエリ

CREATE TABLE users_v1 ( id VARCHAR(255), email VARCHAR(255), name VARCHAR(255), age VARCHAR(255), created_at VARCHAR(255) );

このテーブルは動きますが、年齢が文字列のため範囲検索が遅く、id に主キーがないので 1 行特定にもフルスキャンが必要です。物理設計を意識して書き直すとこうなります。

SQL クエリ

CREATE TABLE users_v2 ( id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, email VARCHAR(255) NOT NULL UNIQUE, name VARCHAR(100) NOT NULL, age SMALLINT CHECK (age BETWEEN 0 AND 255), created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX idx_users_created_at ON users_v2 (created_at);

変更点として、id を BIGINT GENERATED BY DEFAULT AS IDENTITY の自動採番にして主キー化、email に UNIQUE を付与、age は SMALLINT と CHECK 制約で範囲を守り、検索が頻発する created_at にインデックスを追加しました。これだけで「メール重複登録」「年齢範囲検索」「直近作成順」がすべて高速かつ安全になります。

物理設計の判断軸

物理設計を進めるときの 4 つの軸を意識しておくと迷いません。

  • アクセスパターン どのクエリが何回呼ばれるか。OLTP か OLAP か
  • データ量と成長率 1 年後・3 年後の行数とサイズの見積もり
  • 整合性要件 トランザクション・外部キー・一意制約をどこに置くか
  • 運用性 バックアップ・移行・スキーマ変更のしやすさ

物理設計は「速くする」だけではなく「壊れにくくする」設計でもあります。NOT NULL や UNIQUE などの制約は、後から壊れたデータを掃除するコストに比べれば桁違いに安いので、最初に厳しく入れておきましょう。

代表例 アクセスパターンから決める

例えば EC サイトで「ユーザが自分の注文履歴を新しい順に見る」というクエリが 1 日 100 万回走るとします。

SQL クエリ

SELECT id, total_amount, ordered_at FROM orders WHERE user_id = 42 ORDER BY ordered_at DESC LIMIT 20;

このクエリを高速化するには、orders テーブルに (user_id, ordered_at) の 複合インデックス を張るのが定石です。物理設計とは、こうした 「クエリの主役」を特定し、そのクエリが定数時間に近づくようにスキーマを最適化する 作業でもあります。

逆に「念のため全カラムにインデックスを張る」は典型的なアンチパターンです。INSERT/UPDATE のたびに全インデックスが更新されるため書き込みが遅くなり、ストレージも膨らみます。

物理設計のチェックリスト

物理設計が一周終わったか確認するためのチェックリストを置いておきます。

SQL クエリ

-- 想定クエリを EXPLAIN で確認できるサンプル EXPLAIN SELECT id, total_amount FROM orders WHERE user_id = 42 ORDER BY ordered_at DESC LIMIT 20;
  • 各テーブルに主キーがあるか
  • 一意であるべき列に UNIQUE 制約が付いているか
  • 頻出クエリの WHERE ORDER BY JOIN に効くインデックスがあるか
  • データ型の桁が業務要件と合っているか (VARCHAR(255) の乱発になっていないか)
  • 1 年後・3 年後の行数とサイズの見積もりがあるか
  • バックアップとリストアの所要時間を見積もったか

「物理設計は一度では終わらない。本番ログを見ながら継続的にチューニングする工程である」 — 多くの DBA が経験的に語る言葉です。最初から完璧を目指すよりも、計測 → 改善のサイクルを回し続ける文化のほうが大事です。

この章のポイント

ここまでの要点 物理設計 = 論理を DBMS に落とす。型・PK・インデックス・制約・ストレージの 5 点。EXPLAIN で実行計画を確認、計測 → 改善を回す。

まとめ

  • 物理設計は論理設計を DBMS 上に落とし込む工程で、性能と運用性を決める
  • データ型・主キー・インデックス・制約・ストレージ設定の 5 つが主な対象
  • アクセスパターン・データ量・整合性・運用性の 4 軸で判断する
  • 「速くする」だけでなく「壊れにくくする」のも物理設計の役割

演習

簡略化したテーブルがあります。各テーブルの行数と平均長から、ストレージ使用量の目安を計算してみましょう。

次のレッスン

次は 命名規則のベストプラクティス です。データベース設計における命名規則の重要性と、SQLで推奨されるベストプラクティスを実践的に解説します。

事前確認 — 進む前に次の 3 つができることを確認しましょう。

  1. 物理設計 の要点を自分の言葉で説明できる
  2. このレッスンの最小コード (または操作手順) を見ずに書ける
  3. 練習問題やクイズで間違えた箇所を読み直して理解した

理解度チェック (30 秒)

Q. 物理設計 とは何か、1 文で説明してください。

この章のポイント

A. 本文の「このレッスンで分かること」または冒頭の説明文を見直し、自分の言葉で要約できれば OK。詰まったら本レッスンの最初の H2 セクションを読み返してみましょう。

関連レッスン

テーブル構造

schema.sql

CREATE TABLE table_stats ( table_name VARCHAR(50) PRIMARY KEY, row_count BIGINT NOT NULL, avg_row_bytes INT NOT NULL ); INSERT INTO table_stats (table_name, row_count, avg_row_bytes) VALUES ('users', 1000000, 200), ('orders', 5000000, 150), ('order_items', 20000000, 80), ('products', 50000, 500), ('reviews', 800000, 300);

期待される出力

table_nameestimated_mb
order_items1525.88
orders715.26
reviews228.88
users190.73
products23.84

ヒント

query.sql
query.sql
学習モード
コードの実行結果
データベースを初期化中...