データベース設計と最適化

概要

データベースのスキーマ設計、クエリ最適化、パフォーマンスチューニングを包括的に支援するスキルです。

実行フロー

Step 1: 要件分析

ビジネス要件の整理

• ユースケースとデータフロー
• トランザクション特性（OLTP vs OLAP）
• データボリューム予測
• 成長予測とスケーラビリティ要件

非機能要件

• パフォーマンス: 目標レスポンス時間、スループット
• 可用性: SLA、ダウンタイム許容度
• 整合性: ACID要件、結果整合性の許容度
• セキュリティ: 暗号化、アクセス制御

Step 2: データモデリング（新規設計時）

概念モデル（ER図）

エンティティと関係を定義：

User (ユーザー)
  - user_id (PK)
  - email
  - name
  - created_at

Order (注文)
  - order_id (PK)
  - user_id (FK)
  - total_amount
  - status
  - created_at

論理モデル

• 正規化（第3正規形まで）
• 非正規化のトレードオフ判断
• データ型とサイズの決定
• 制約の定義（NOT NULL、UNIQUE、CHECK）

Step 3: スキーマ設計

テーブル定義

CREATE TABLE users (
  user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);

インデックス戦略

インデックスが効果的な場合:

• WHERE句で頻繁に検索される列
• JOIN条件で使用される外部キー
• ORDER BY、GROUP BYで使用される列

複合インデックス:

CREATE INDEX idx_orders_user_status
ON orders(user_id, status, created_at);

パーティショニング

CREATE TABLE orders (
  order_id UUID,
  user_id UUID,
  total_amount DECIMAL(10, 2),
  created_at TIMESTAMP
) PARTITION BY RANGE (created_at);

Step 4: クエリ最適化（既存システム）

パフォーマンス分析

-- 実行計画の確認
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.order_id) as order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.name;

-- スロークエリTop 10
SELECT query, calls, mean_time, max_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

N+1問題の解消

// 改善: Eager Loading
const users = await User.findAll({
  include: [{ model: Order }]
});

Step 5: パフォーマンスチューニング

データベース設定の最適化

PostgreSQL:

shared_buffers = 4GB
work_mem = 16MB
maintenance_work_mem = 512MB
effective_cache_size = 12GB

コネクションプール

const pool = new Pool({
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

Step 6: スケーラビリティ戦略

• 読み取りレプリカ
• シャーディング
• キャッシング戦略（Redis Cache-aside パターン）

Step 7: 移行計画（必要に応じて）

ゼロダウンタイム移行:

-- 1. 新カラム追加
ALTER TABLE users ADD COLUMN new_email VARCHAR(255);

-- 2. データ移行
UPDATE users SET new_email = email WHERE new_email IS NULL;

-- 3. 旧カラム削除
ALTER TABLE users DROP COLUMN email;
ALTER TABLE users RENAME COLUMN new_email TO email;

パフォーマンス目標

データベース選定ガイド

SQL Databases

NoSQL Databases

ベストプラクティス

1. 測定に基づく最適化: 推測ではなくデータに基づいて最適化
2. 将来を見据えた設計: スケーラビリティを考慮
3. 保守性の確保: ドキュメント化、命名規則の統一
4. セキュリティ: 最小権限の原則、暗号化
5. バックアップと復旧: 定期的なバックアップ、DR計画

関連ファイル

• TEMPLATES.md - スキーマテンプレート
• QUERIES.md - 便利なクエリ集