KEN3pei 物理設計アンチパターンレビュー
『SQLアンチパターン Volume 1』第II部（データベース物理設計のアンチパターン）に基づき、 ユーザーが提示したテーブル定義・DDL・スキーマに対してレビューを実施してください。
input の取得（最初に必ず実行）
以下の優先順位でレビュー対象を特定すること。
1. 引数でファイルパスが指定された場合
スキル呼び出し時に引数としてファイルパスが渡された場合（例: /physical-design-antipattern-review ./schema.sql）、そのファイルを Read ツールで読み込む。
2. 引数がなく、会話にスキーマ・DDL・設計情報が貼り付けられている場合
会話中のテキストをそのままレビュー対象として使用する。
3. 引数もなく、会話にも設計情報がない場合
以下を質問してから処理を開始する：
レビュー対象を教えてください。以下のいずれかで指定できます：
- ファイルパス（例: ./schema.sql, ./er-diagram.drawio）
- DDL・テーブル定義を直接貼り付け
ファイル種別ごとの読み取り方針
| 拡張子 | 読み取り方法 |
|---|---|
.sql |
Read ツールでそのまま読み込み、CREATE TABLE / ALTER TABLE 等のDDLを解析する |
.drawio / .xml |
Read ツールでXMLを読み込み、<mxCell> タグのラベル・属性からテーブル名・列名・データ型・制約・リレーションを抽出してER構造を把握する |
.md / .txt |
Read ツールで読み込み、テーブル定義やER図の説明文からスキーマ情報を読み取る |
| その他 | Read ツールで読み込み、内容からスキーマ情報を最大限に読み取る |
drawio ファイルの場合の注意点:
swimlaneスタイルのセルがテーブルを表す- 子セルの
value属性が列名・データ型・制約を示す - エッジ（矢印）がテーブル間のリレーションを示す
- これらを解釈してER構造として理解した上でレビューを行う
物理設計レビューにおける drawio の限界:
- データ型・インデックス・CHECK制約などはdrawioに明記されていない場合が多い
- 読み取れた情報の範囲でレビューし、判断できない箇所は「drawioからは確認不可 — DDLで要確認」と明記する
レビュー手順
- 上記 input の取得手順に従って対象を特定し読み込む
- 以下の4つのアンチパターンチェックリストに従って順番に確認する
- 該当するアンチパターンを発見した場合は問題点と改善策を日本語で明確に報告する
- 問題がない場合は「問題なし」と明記する
チェックリスト
AP-9: ラウンディングエラー（丸め誤差）
チェック内容: 金額・数量など精度が重要な数値に FLOAT / DOUBLE / REAL 型を使っていないか
問題点:
- IEEE 754浮動小数点数は2進数形式で格納されるため、10進数の一部の値を正確に表現できない
59.95のような値が内部では59.950000762939...として格納される場合がある- 金額計算・集計結果に数ドル〜数円レベルの誤差が生じる
WHERE price = 59.95のような等値比較が期待通りに動かない場合がある
確認観点:
FLOAT,DOUBLE,DOUBLE PRECISION,REAL型で金額・価格・割合・計測値を格納していないか- 金融系・会計系・集計系の列に浮動小数点型が使われていないか
- 通貨・税率・時給・重量など精度が求められる値のデータ型を確認する
解決策: NUMERIC(精度, スケール) または DECIMAL(精度, スケール) 型を使用する
- 例: 金額なら
NUMERIC(10, 2)（小数点以下2桁） - 科学的な近似値（物理計測・統計など）や精度が不要な場面のみ FLOAT を許容する
AP-10: サーティワンフレーバー（31のフレーバー）
チェック内容: 有効な値セットを列定義（CHECK 制約・ENUM 型）に直接埋め込んでいないか
問題点:
- 値の追加・削除・変更のたびに
ALTER TABLEが必要になり、本番DBへの影響が大きい ALTER TABLE実行中はテーブルへのアクセスを停止しなければならない場合がある- 現在の有効値一覧をSQLで取得するにはメタデータAPIを叩く必要があり複雑
- 廃止された値（過去データに残る値）のサポートが困難
- DBMSをまたいだ移植性が下がる（ENUMはMySQL独自構文）
確認観点:
CHECK (status IN ('NEW', 'IN PROGRESS', 'FIXED'))のような制約がないかENUM('NEW', 'IN PROGRESS', 'FIXED')のようなMySQL ENUMを使っていないか- 値セットが変更されうる列（ステータス・カテゴリ・種別・区分）に列定義で値を埋め込んでいないか
解決策: 参照テーブル（ルックアップテーブル）を作成し、データで値セットを管理する
-- 良い例
CREATE TABLE BugStatus (
status VARCHAR(20) PRIMARY KEY
);
INSERT INTO BugStatus (status) VALUES ('NEW'), ('IN PROGRESS'), ('FIXED');
CREATE TABLE Bugs (
bug_id SERIAL PRIMARY KEY,
status VARCHAR(20),
FOREIGN KEY (status) REFERENCES BugStatus(status)
);
- 値の追加・削除はINSERT/DELETEで行え、ALTER TABLE不要
- アプリのUIで有効値一覧を取得するクエリがシンプルになる
- 廃止値は削除せず残すことで過去データとの整合性を維持できる
AP-11: ファントムファイル（幻のファイル）
チェック内容: 画像・動画・ファイルをファイルシステムに格納し、DBにはパスのみを保存していないか
問題点:
- DBのバックアップ対象外のディレクトリにファイルを置くと、バックアップ漏れが発生する
- DBの行を削除してもファイルは自動削除されず、孤立ファイルが蓄積する
- トランザクションのROLLBACKでDBの変更は取り消せてもファイルは残る
- トランザクション分離レベルがファイルシステムに適用されない（他クライアントから即見える）
- ファイルはSQLのアクセス権限（GRANT/REVOKE）で保護できない
- クラウド移行時にファイルサーバーとDBの整合性管理が複雑になる
確認観点:
portrait_image VARCHAR(255)のようにファイルパスを文字列で格納していないか- バイナリデータを格納すべき列が
VARCHAR/TEXTでパスになっていないか - ファイルストレージとDBのバックアップ戦略が整合しているか
解決策（トレードオフを考慮して選択）:
BLOBをDBに格納する場合の利点:
- バックアップ・リストア・トランザクション・アクセス制御が一元化される
- DBの行とファイルが常に一致する
ファイルシステム（またはオブジェクトストレージ）に格納する場合の注意点:
- バックアップ対象・タイミングをDBと揃える
- 削除時のファイル削除処理を必ずアプリに実装する（またはトリガーで対処）
- Amazon S3等のオブジェクトストレージのURLをDBに格納する場合も同様のリスクを認識する
判断基準: ファイルサイズが大きい・CDNが必要・DBサイズを抑えたい場合はオブジェクトストレージを選択し、バックアップ・整合性戦略を明確にする
AP-12: インデックスショットガン（闇雲インデックス）
チェック内容: インデックスが全くない、または闇雲に全列に作成されていないか
問題点（インデックスなし）:
- テーブルスキャンが発生し、データ増加に比例してクエリが遅くなる
- WHERE句・JOIN条件・ORDER BY の列にインデックスがないと全件スキャンになる
問題点（過剰なインデックス）:
- INSERT/UPDATE/DELETE のたびに全インデックスを更新するオーバーヘッドが発生
- 使われないインデックスはストレージを無駄に消費する
- 主キーは自動的にインデックスが作成されるため、明示的な再定義は冗長
- 長い文字列列（VARCHAR(255)等）のインデックスはサイズが大きく効果が薄い
- 複合インデックスは列の順序が重要（左端から使われない場合は機能しない）
- 低選択性の列（
is_activeなど値の種類が少ない列）のインデックスは効果薄
確認観点:
- 主キー以外のインデックスが一切定義されていないか
- 全列にインデックスが定義されていないか
- クエリの WHERE句・JOIN条件・ORDER BY で使われる列にインデックスがあるか
- カーディナリティが低い列（性別・フラグ等）に単独インデックスがないか
- 複合インデックスの列順序がクエリのフィルタ条件に合っているか
- 主キーと同じ列を重複してインデックス定義していないか
解決策: MENTORの原則に基づいてインデックスを管理する
| ステップ | 内容 |
|---|---|
| Measure（測定） | まず実際のクエリのボトルネックを計測する。推測で追加しない |
| Explain（解析） | EXPLAIN / EXPLAIN ANALYZE でクエリ実行計画を確認する |
| Nominate（指名） | ボトルネックになっている列・クエリを特定してインデックス候補を決める |
| Test（テスト） | インデックス追加前後でクエリ実行時間を比較する |
| Optimize（最適化） | カバーリングインデックスや複合インデックスの効果を検証する |
| Rebuild（再構築） | 断片化したインデックスを定期的に再構築・再編成する |
カバーリングインデックスの活用:
- SELECT する列もインデックスに含めることでテーブルアクセスを省略できる
- 例:
WHERE status = 'NEW' ORDER BY date_reportedに対してINDEX(status, date_reported)を作成
出力形式
## 物理設計アンチパターンレビュー結果
### 発見されたアンチパターン
#### AP-X: [アンチパターン名]
- **箇所:** （問題のあるテーブル名・列名・定義）
- **問題点:** （具体的な問題の説明）
- **改善策:** （推奨する設計変更とDDL例）
### 問題なし
- AP-X: [アンチパターン名] — 該当なし
### 総評
（全体的な評価と優先度の高い改善点のサマリー）