データベース設計：正規化とインデックス最適化の実践

データベース設計は、アプリケーションのパフォーマンス、拡張性、保守性に直接影響する重要なスキルです。この記事では、正規化の理論から実践的な設計パターン、そしてパフォーマンスを向上させるインデックス戦略まで、包括的に学びます。

効率的で保守しやすいデータベーススキーマを設計できるようになることを目指します。

---

1. データベース設計の基本原則

1.1 良いデータベース設計の特徴

• データの整合性: データの正確性と一貫性を保証
• 非冗長性: データの重複を最小化
• 拡張性: 将来的な変更に対応可能
• パフォーマンス: クエリの高速実行
• 保守性: 理解しやすく、変更しやすい構造

1.2 設計プロセス

1. 要件分析: ビジネス要件の理解
2. 概念設計: ER図の作成
3. 論理設計: 正規化とテーブル設計
4. 物理設計: インデックスとパーティショニング
5. 実装: SQLスキーマの作成

2. ER図（Entity-Relationship Diagram）

2.1 エンティティと属性

[User]
--------
id (PK)
email
name
created_at

[Post]
--------
id (PK)
user_id (FK)
title
content
created_at

[Category]
--------
id (PK)
name
slug

2.2 リレーションシップ

• 1対1（1:1）: 1人のユーザーが1つのプロフィールを持つ
• 1対多（1:N）: 1人のユーザーが複数の投稿を持つ
• 多対多（N:M）: 1つの投稿が複数のタグを持ち、1つのタグが複数の投稿に属する

-- 1対1の例
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY REFERENCES users(id),
    bio TEXT,
    avatar_url VARCHAR(255)
);

-- 1対多の例
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    title VARCHAR(255) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 多対多の例
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE post_tags (
    post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
    tag_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

3. 正規化

正規化は、データの冗長性を減らし、整合性を保つためのプロセスです。

3.1 第一正規形（1NF）

条件:

• 各セルに単一の値のみが含まれる
• 各列に一意の名前がある
• 行の順序が意味を持たない

-- ❌ 1NF違反の例
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_name VARCHAR(255),
    items VARCHAR(500)  -- カンマ区切りの値: "item1,item2,item3"
);

-- ✅ 1NFに準拠
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id)
);

CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    item_id INTEGER REFERENCES items(id),
    quantity INTEGER NOT NULL
);

3.2 第二正規形（2NF）

条件:

• 1NFに準拠している
• 部分関数従属を排除（非キー属性が主キーの一部にのみ依存しない）

-- ❌ 2NF違反の例
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    product_name VARCHAR(255),  -- product_idにのみ依存
    quantity INTEGER,
    price DECIMAL(10, 2),
    PRIMARY KEY (order_id, product_id)
);

-- ✅ 2NFに準拠
CREATE TABLE order_items (
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL,
    price DECIMAL(10, 2) NOT NULL,  -- 注文時の価格を保持
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    current_price DECIMAL(10, 2) NOT NULL
);

3.3 第三正規形（3NF）

条件:

• 2NFに準拠している
• 推移的関数従属を排除（非キー属性が他の非キー属性に依存しない）

-- ❌ 3NF違反の例
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    customer_name VARCHAR(255),  -- customer_idに依存
    customer_email VARCHAR(255),  -- customer_idに依存
    order_date DATE
);

-- ✅ 3NFに準拠
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    order_date DATE NOT NULL
);

3.4 BCNF（Boyce-Codd正規形）

条件:

• 3NFに準拠している
• 全ての関数従属において、左側がスーパーキーである

-- 複雑な例
-- 講師が複数の科目を教えることができ、各科目は複数の教室で開講される
-- ❌ BCNF違反
CREATE TABLE teaching (
    instructor_id INTEGER,
    subject VARCHAR(100),
    classroom VARCHAR(50),
    PRIMARY KEY (instructor_id, subject, classroom)
);
-- 問題: instructor_id -> subject という従属関係

-- ✅ BCNFに準拠
CREATE TABLE instructor_subjects (
    instructor_id INTEGER REFERENCES instructors(id),
    subject VARCHAR(100) REFERENCES subjects(name),
    PRIMARY KEY (instructor_id, subject)
);

CREATE TABLE subject_classrooms (
    subject VARCHAR(100) REFERENCES subjects(name),
    classroom VARCHAR(50),
    PRIMARY KEY (subject, classroom)
);

4. 非正規化の適切な使用

正規化は重要ですが、過度な正規化はパフォーマンスを低下させる場合があります。

4.1 計算値の保存

-- オプション1: 正規化（計算値は都度計算）
CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    unit_price DECIMAL(10, 2)
    -- total_priceは計算: quantity * unit_price
);

-- オプション2: 非正規化（計算値を保存）
CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    unit_price DECIMAL(10, 2),
    total_price DECIMAL(10, 2)  -- パフォーマンスのため保存
);

4.2 スタースキーマ（データウェアハウス）

データウェアハウスでは、非正規化が適切な場合があります。

-- ファクトテーブル（非正規化）
CREATE TABLE sales_fact (
    id INTEGER PRIMARY KEY,
    date_id INTEGER,
    product_id INTEGER,
    customer_id INTEGER,
    store_id INTEGER,
    quantity INTEGER,
    amount DECIMAL(10, 2),
    discount DECIMAL(10, 2),
    profit DECIMAL(10, 2)
);

-- ディメンションテーブル
CREATE TABLE dim_date (
    date_id INTEGER PRIMARY KEY,
    date DATE,
    year INTEGER,
    quarter INTEGER,
    month INTEGER,
    day INTEGER,
    day_of_week VARCHAR(20)
);

5. インデックス設計

インデックスは、クエリのパフォーマンスを大幅に向上させます。

5.1 インデックスの種類

-- B-treeインデックス（デフォルト、最も一般的）
CREATE INDEX idx_user_email ON users(email);

-- ユニークインデックス
CREATE UNIQUE INDEX idx_user_email_unique ON users(email);

-- 複合インデックス
CREATE INDEX idx_post_user_date ON posts(user_id, created_at DESC);

-- 部分インデックス（条件付きインデックス）
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- 関数インデックス
CREATE INDEX idx_user_lower_email ON users(LOWER(email));

-- 全文検索インデックス（PostgreSQL）
CREATE INDEX idx_post_content_search ON posts USING gin(to_tsvector('english', content));

5.2 インデックスの最適化

-- クエリの分析
EXPLAIN ANALYZE
SELECT * FROM posts 
WHERE user_id = 123 
ORDER BY created_at DESC 
LIMIT 10;

-- インデックスの作成戦略
-- 1. WHERE句で頻繁に使用される列
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- 2. ORDER BYで使用される列
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);

-- 3. JOINで使用される列（外部キー）
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- 4. 複合インデックス（カバリングインデックス）
CREATE INDEX idx_posts_user_created 
ON posts(user_id, created_at DESC, title);
-- このインデックスで以下のクエリが最適化される:
-- SELECT title FROM posts WHERE user_id = 123 ORDER BY created_at DESC;

5.3 インデックスの使用状況の確認

-- インデックスの使用統計（PostgreSQL）
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan as index_scans,
    idx_tup_read as tuples_read,
    idx_tup_fetch as tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- 使用されていないインデックスの特定
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexname NOT LIKE 'pg_toast%';

-- インデックスのサイズ
SELECT 
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

6. クエリ最適化のための設計

6.1 パーティショニング

-- 範囲パーティショニング（日付による分割）
CREATE TABLE orders (
    id INTEGER,
    order_date DATE NOT NULL,
    customer_id INTEGER,
    total_amount DECIMAL(10, 2)
) PARTITION BY RANGE (order_date);

-- パーティションの作成
CREATE TABLE orders_2023 PARTITION OF orders
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- ハッシュパーティショニング
CREATE TABLE users (
    id INTEGER,
    email VARCHAR(255),
    name VARCHAR(255)
) PARTITION BY HASH (id);

CREATE TABLE users_0 PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE users_1 PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 1);
CREATE TABLE users_2 PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 2);
CREATE TABLE users_3 PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 3);

6.2 マテリアライズドビュー

-- 集計結果の事前計算
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    SUM(total_amount) AS total_sales,
    COUNT(*) AS order_count,
    AVG(total_amount) AS avg_order_amount
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

-- インデックスの追加
CREATE UNIQUE INDEX ON monthly_sales(month);

-- リフレッシュ
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

-- 自動リフレッシュ（トリガーやスケジュールで実行）

7. 実践的な設計例：ブログシステム

-- ユーザーテーブル
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- カテゴリテーブル
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- タグテーブル
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL
);

-- 投稿テーブル
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    excerpt TEXT,
    content TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'draft',  -- draft, published, archived
    published_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, slug)
);

-- 投稿タグの中間テーブル
CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

-- コメントテーブル
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    parent_id INTEGER REFERENCES comments(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- インデックスの作成
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_category_id ON posts(category_id);
CREATE INDEX idx_posts_status ON posts(status);
CREATE INDEX idx_posts_published_at ON posts(published_at DESC);
CREATE INDEX idx_posts_slug ON posts(slug);
CREATE INDEX idx_posts_user_slug ON posts(user_id, slug);  -- 複合インデックス

CREATE INDEX idx_post_tags_post_id ON post_tags(post_id);
CREATE INDEX idx_post_tags_tag_id ON post_tags(tag_id);

CREATE INDEX idx_comments_post_id ON comments(post_id);
CREATE INDEX idx_comments_user_id ON comments(user_id);
CREATE INDEX idx_comments_parent_id ON comments(parent_id);
CREATE INDEX idx_comments_created_at ON comments(created_at DESC);

-- 全文検索インデックス
CREATE INDEX idx_posts_content_search ON posts 
USING gin(to_tsvector('english', title || ' ' || content));

8. パフォーマンス最適化のベストプラクティス

8.1 クエリ最適化のチェックリスト

-- 1. EXPLAIN ANALYZEで実行計画を確認
EXPLAIN ANALYZE
SELECT p.*, u.username, c.name as category_name
FROM posts p
INNER JOIN users u ON p.user_id = u.id
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.status = 'published'
ORDER BY p.published_at DESC
LIMIT 10;

-- 2. 適切なJOIN順序
-- 小さなテーブルから大きなテーブルへ

-- 3. WHERE句の最適化
-- インデックスが使用できる条件を先に
-- 関数の使用を避ける（可能な場合）

-- 4. SELECT * を避ける
SELECT id, title, published_at FROM posts;  -- 良い
SELECT * FROM posts;  -- 悪い（必要な列のみ選択）

-- 5. LIMITの使用
SELECT * FROM posts ORDER BY published_at DESC LIMIT 20;

-- 6. サブクエリよりJOINを優先（場合による）
-- JOINの方が効率的な場合が多い

8.2 統計情報の更新

-- 統計情報の更新（PostgreSQL）
ANALYZE posts;
ANALYZE users;
ANALYZE categories;

-- 全テーブルの統計情報を更新
ANALYZE;

9. まとめと次のステップ

この記事を通じて、データベース設計の基礎から実践的な最適化まで学びました。

学んだこと

• 正規化: 1NF、2NF、3NF、BCNFの理解
• ER図: エンティティとリレーションシップの設計
• インデックス設計: 適切なインデックスの作成と最適化
• パーティショニング: 大規模データの分割
• クエリ最適化: パフォーマンス向上のテクニック
• 実践例: ブログシステムの設計

設計のベストプラクティス

1. 正規化と非正規化のバランス: 過度な正規化を避ける
2. 適切なインデックス: クエリパターンに基づいたインデックス設計
3. 外部キー制約: データ整合性の保証
4. 命名規則: 一貫性のある命名
5. ドキュメント化: ER図とスキーマドキュメントの作成

次のステップ

1. レプリケーション: 読み取りスケーリング
2. シャーディング: 水平分割によるスケーリング
3. NoSQLデータベース: ドキュメントDB、キーバリューストア
4. データウェアハウス: 分析用データベース設計
5. バックアップと復旧: データ保護戦略

データベース設計は、アプリケーションの基盤となる重要なスキルです。継続的な学習と実践を通じて、効率的で保守しやすいデータベースを設計できるようになりましょう！

Happy Designing!