database-designer

Database Designer - POWERFUL Tier スキル

概要

最新のデータベースシステム向けに、専門家レベルの分析、最適化、移行機能を提供する包括的なデータベース設計スキルです。このスキルは理論的な原則と実践的なツールを組み合わせ、アーキテクトや開発者がスケーラブルで高性能、かつ保守しやすいデータベーススキーマを作成できるよう支援します。

主な能力

スキーマ設計と分析

• 正規化分析：正規化レベル（1NF から BCNF まで）を自動検出
• 非正規化戦略：パフォーマンス最適化のための的確な推奨
• データ型の最適化：不適切な型やサイズの問題を特定
• 制約分析：外部キー、一意制約、NULL チェックの不足を検出
• 命名規則の検証：テーブルとカラムの命名パターンの一貫性を確認
• ERD の生成：DDL から Mermaid ダイアグラムを自動作成

インデックスの最適化

• インデックス不足の分析：外部キーやクエリパターンに対するインデックスの不足を特定
• 複合インデックス戦略：複数カラムのインデックスに最適なカラム順を決定
• インデックスの冗長性検出：重複するインデックスや未使用のインデックスを削除
• パフォーマンス影響のモデル化：選択性の推定とクエリコストの分析
• インデックスタイプの選択：B-tree、ハッシュ、部分、カバリング、特殊なインデックス

移行管理

• ダウンタイムゼロの移行：拡張・縮小パターンを実装
• スキーマの進化：カラムの追加、削除、型変更を安全に実施
• データ移行スクリプト：データ変換と検証を自動化
• ロールバック戦略：検証機能を備えた完全な取り消し機能
• 実行計画：依存関係を解決したうえで、移行手順を順序付け

ツールのワークフロー（これらを実行してください。スキーマを手作業で分析しないでください）

すべてのパスはこのスキルのフォルダーからの相対パスです。サンプル入力は assets/ にあります。

1. スキーマを分析する

python3 schema_analyzer.py --input schema.sql --generate-erd --output-format json -o analysis.json

SQL DDL または JSON スキーマ（assets/sample_schema.sql / sample_schema.json）を受け付けます。出力には正規化に関する検出結果、不足している制約、命名上の問題、Mermaid ERD が含まれます。ERD をユーザーに提示し、最適化の前に指摘された問題を修正してください。

2. 実際のクエリパターンに基づいてインデックスを最適化する

python3 index_optimizer.py --schema assets/sample_schema.json --queries assets/sample_query_patterns.json --analyze-existing --format json -o indexes.json

まず、ユーザーが頻繁に実行するクエリをクエリパターン JSON に記述してください（assets/sample_query_patterns.json をコピーします）。出力には、優先順位順の CREATE INDEX 推奨事項と、冗長なインデックスの削除が含まれます。

3. 移行を生成する

python3 migration_generator.py --current current_schema.json --target target_schema.json --zero-downtime --format sql -o migration.sql

--zero-downtime は拡張・縮小プランを出力します。--validate-only は SQL を生成せずに実行可能性をチェックします。

4. 検証ループ

手順 1 を 移行先 スキーマに対して再実行し、最初の検査で見つかった問題が解消されていることを確認します。移行を引き渡す前に migration_generator.py --validate-only を実行してください。

データベース設計の原則

→ 詳細については references/database-design-reference.md を参照してください

ベストプラクティス

スキーマ設計

1. 意味のある名前を使う：明確で一貫性のある命名規則を使用します
2. 適切なデータ型を選ぶ：ストレージ効率を高める適切なサイズのカラムを使用します
3. 適切な制約を定義する：外部キー、チェック制約、一意インデックスを設定します
4. 将来の成長を考慮する：最初からスケーリングを計画します
5. リレーションシップを文書化する：外部キーの関係とビジネスルールを明確にします

パフォーマンスの最適化

1. 戦略的にインデックスを設定する：過剰なインデックスを避けながら、一般的なクエリパターンをカバーします
2. クエリのパフォーマンスを監視する：遅いクエリを定期的に分析します
3. 大きなテーブルをパーティション分割する：クエリのパフォーマンスとメンテナンス性を向上させます
4. 適切な分離レベルを使用する：一貫性とパフォーマンスのバランスを取ります
5. コネクションプーリングを実装する：リソースを効率的に利用します

セキュリティ上の考慮事項

1. 最小権限の原則：必要最小限の権限を付与します
2. 機密データの暗号化：保存時と転送時に暗号化します
3. アクセスパターンの監査：データベースへのアクセスを監視して記録します
4. 入力の検証：SQL インジェクション攻撃を防ぎます
5. 定期的なセキュリティ更新：データベースソフトウェアを最新の状態に保ちます

クエリ生成パターン

JOIN を使用した SELECT

-- INNER JOIN: only matching rows
SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;

-- LEFT JOIN: all left rows, NULLs for non-matches
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

-- Self-join: hierarchical data (employees/managers)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

共通テーブル式（CTE）

-- Recursive CTE for org chart
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e INNER JOIN org o ON o.id = e.manager_id
)
SELECT * FROM org ORDER BY depth, name;

ウィンドウ関数

-- ROW_NUMBER for pagination / dedup
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders;

-- RANK with gaps, DENSE_RANK without gaps
SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM leaderboard;

-- LAG/LEAD for comparing adjacent rows
SELECT date, revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change
FROM daily_sales;

集計パターン

-- FILTER clause (PostgreSQL) for conditional aggregation
SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'active') AS active,
  AVG(amount) FILTER (WHERE amount > 0) AS avg_positive
FROM accounts;

-- GROUPING SETS for multi-level rollups
SELECT region, product, SUM(revenue)
FROM sales
GROUP BY GROUPING SETS ((region, product), (region), ());

---

マイグレーションパターン

Up/Down マイグレーションスクリプト

すべてのマイグレーションには、元に戻せる対応するスクリプトが必要です。順序付けのため、ファイル名にはタイムスタンプのプレフィックスを付けます。

migrations/
├── 20260101_000001_create_users.up.sql
├── 20260101_000001_create_users.down.sql
├── 20260115_000002_add_users_email_index.up.sql
└── 20260115_000002_add_users_email_index.down.sql

ダウンタイムなしのマイグレーション（拡張／縮小）

実行中のコードのロックや破損を避けるため、拡張・縮小パターンを使用します。

1. 拡張 — 新しいカラム／テーブルを追加します（NULL 許容、デフォルト値あり）
2. データの移行 — バッチ単位でバックフィルし、アプリケーションから二重書き込みを行います
3. 移行 — アプリケーションが新しいカラムから読み取るようにし、古いカラムへの書き込みを停止します
4. 縮小 — 後続のマイグレーションで古いカラムを削除します

データのバックフィル戦略

-- Batch update to avoid long-running locks
UPDATE users SET email_normalized = LOWER(email)
WHERE id IN (SELECT id FROM users WHERE email_normalized IS NULL LIMIT 5000);
-- Repeat in a loop until 0 rows affected

ロールバック手順

• up.sql を本番環境にデプロイする前に、必ずステージング環境で down.sql をテストしてください
• ロールバック可能な期間は短く保ちます。縮小の手順が実行された後のロールバックには、新たな前進マイグレーションが必要です
• 元に戻せない変更（データを含むカラムの削除など）の前に、論理バックアップを取得してください

---

パフォーマンスの最適化

インデックス戦略

インデックスの種類	用途	例
B-tree（デフォルト）	等価検索、範囲検索、ORDER BY	CREATE INDEX idx_users_email ON users(email);
GIN	全文検索、JSONB、配列	CREATE INDEX idx_docs_body ON docs USING gin(to_tsvector('english', body));
GiST	ジオメトリ、範囲型、最近傍検索	CREATE INDEX idx_locations ON places USING gist(coords);
部分インデックス	行のサブセット（サイズを削減）	CREATE INDEX idx_active ON users(email) WHERE active = true;
カバリングインデックス	インデックスのみのスキャン	CREATE INDEX idx_cov ON orders(customer_id) INCLUDE (total, created_at);

EXPLAIN プランの読み方

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

注目すべき主な兆候：

• 大きなテーブルに対する Seq Scan — インデックスが不足しています
• 行数の推定値が大きい Nested Loop — ハッシュ結合／マージ結合の使用、またはインデックスの追加を検討してください
• Buffers shared read が hit より大幅に多い — ワーキングセットがメモリ容量を超えています

N+1 クエリの検出

症状：アプリケーションが行ごとにクエリを1つずつ実行します（例：ループ内で関連レコードを取得する）。

対処法：

• JOIN またはサブクエリを使って、1回の往復で取得します
• ORM のイーガーローディング（select_related／includes／with）
• GraphQL リゾルバーに DataLoader パターンを使用します

コネクションプーリング

ツール	プロトコル	最適な用途
PgBouncer	PostgreSQL	トランザクション／ステートメントのプーリング、低オーバーヘッド
ProxySQL	MySQL	クエリのルーティング、読み取り／書き込みの分割
組み込みプール（HikariCP、SQLAlchemy pool）	任意	アプリケーションレベルのプーリング

目安： プールサイズは (2 * CPU cores) + disk spindles に設定します。クラウド SSD の場合は 2 * vCPUs から始め、調整してください。

読み取りレプリカとクエリのルーティング

• すべての SELECT クエリをレプリカに、書き込みをプライマリにルーティングします
• レプリケーション遅延を考慮してください（非同期では通常 1 秒未満、同期では 0）
• 重要なデータを読み取る前に、pg_last_wal_replay_lsn() を使って遅延を検出します

---

マルチデータベース選定マトリクス

基準	PostgreSQL	MySQL	SQLite	SQL Server
最適な用途	複雑なクエリ、JSONB、拡張機能	Web アプリ、読み取り中心のワークロード	組み込み、開発／テスト、エッジ	エンタープライズ向け .NET スタック
JSON サポート	優秀（JSONB + GIN）	良好（JSON 型）	最小限	良好（OPENJSON）
レプリケーション	ストリーミング、論理レプリケーション	グループレプリケーション、InnoDB クラスター	該当なし	Always On AG
ライセンス	オープンソース（PostgreSQL License）	オープンソース（GPL）／商用	パブリックドメイン	商用
実用上の最大サイズ	数 TB	数 TB	約 1 TB（単一ライター）	数 TB

選定の目安：

• PostgreSQL — 新規プロジェクトでのデフォルトの選択肢。拡張性と標準への準拠性に最も優れています
• MySQL — 既存の MySQL エコシステムを活用する場合、またはシンプルな読み取り中心の Web アプリケーション
• SQLite — モバイルアプリ、CLI ツール、単体テスト用データベース、IoT／エッジ
• SQL Server — エンタープライズポリシーで指定されている場合、または .NET／Azure との高度な統合が必要な場合

NoSQL を検討する際のポイント

データベース	モデル	使用する場面
MongoDB	ドキュメント	スキーマの柔軟性、迅速なプロトタイピング、コンテンツ管理
Redis	キーと値／キャッシュ	セッションストア、レート制限、リーダーボード、パブ／サブ
DynamoDB	ワイドカラム	サーバーレスの AWS アプリ、あらゆる規模で 1 桁ミリ秒のレイテンシー

SQL をデフォルトとして使用してください。アクセスパターンが明らかに適している場合にのみ、NoSQL を選択してください。

---

シャーディングとレプリケーション

水平分割と垂直分割

• 垂直分割：列を複数のテーブルに分割します（例：BLOB 列を分離する）。対象列が少ないクエリの I/O を削減できます。
• 水平分割（シャーディング）：行を複数のデータベース／サーバーに分割します。単一ノードでデータセットを保持できない場合や、必要なスループットを処理できない場合に必要です。

シャーディング戦略

戦略	仕組み	メリット	デメリット
ハッシュ	shard = hash(key) % N	均等に分散できる	シャードの再分割にコストがかかる
範囲	日付または ID の範囲でシャードを分割	シンプルで、時系列データに適している	最新のシャードに負荷が集中する
地理的分割	ユーザーの地域ごとにシャードを分割	データの局所性、コンプライアンス	リージョンをまたぐクエリが難しい

レプリケーションパターン

パターン	一貫性	レイテンシー	用途
同期	強い	書き込みレイテンシーが高い	金融取引
非同期	結果整合性	書き込みレイテンシーが低い	読み取り中心の Web アプリ
準同期	少なくとも 1 つのレプリカで確認済み	中程度	安全性と速度のバランス

---

関連項目

• sql-database-assistant — 日常的な SQL 作業におけるクエリの作成、最適化、デバッグ
• database-schema-designer — ERD モデリング、正規化の分析、スキーマの生成
• migration-architect — データベースエンジン間の大規模な移行や、主要なスキーマ刷新の計画
• senior-backend — アプリケーション層のパターン（コネクションプーリング、ORM のベストプラクティス）
• senior-devops — データベースクラスターとレプリカのインフラストラクチャのプロビジョニング