データベース設計:正規化とインデックス最適化の実践
データベース設計は、アプリケーションのパフォーマンス、拡張性、保守性に直接影響する重要なスキルです。この記事では、正規化の理論から実践的な設計パターン、そしてパフォーマンスを向上させるインデックス戦略まで、包括的に学びます。
効率的で保守しやすいデータベーススキーマを設計できるようになることを目指します。
1. データベース設計の基本原則
1.1 良いデータベース設計の特徴
- データの整合性: データの正確性と一貫性を保証
- 非冗長性: データの重複を最小化
- 拡張性: 将来的な変更に対応可能
- パフォーマンス: クエリの高速実行
- 保守性: 理解しやすく、変更しやすい構造
1.2 設計プロセス
- 要件分析: ビジネス要件の理解
- 概念設計: ER図の作成
- 論理設計: 正規化とテーブル設計
- 物理設計: インデックスとパーティショニング
- 実装: 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図: エンティティとリレーションシップの設計
- インデックス設計: 適切なインデックスの作成と最適化
- パーティショニング: 大規模データの分割
- クエリ最適化: パフォーマンス向上のテクニック
- 実践例: ブログシステムの設計
設計のベストプラクティス
- 正規化と非正規化のバランス: 過度な正規化を避ける
- 適切なインデックス: クエリパターンに基づいたインデックス設計
- 外部キー制約: データ整合性の保証
- 命名規則: 一貫性のある命名
- ドキュメント化: ER図とスキーマドキュメントの作成
次のステップ
- レプリケーション: 読み取りスケーリング
- シャーディング: 水平分割によるスケーリング
- NoSQLデータベース: ドキュメントDB、キーバリューストア
- データウェアハウス: 分析用データベース設計
- バックアップと復旧: データ保護戦略
データベース設計は、アプリケーションの基盤となる重要なスキルです。継続的な学習と実践を通じて、効率的で保守しやすいデータベースを設計できるようになりましょう!
Happy Designing!
コメント
コメントを読み込み中...
