SQLアンチパターン第二版がとても良い書籍だったので理解を深める一環でアンチパターンの一覧を作成しました。
一覧には概要を記載したので、記述を読んで「ああ、これね」とピンとこなければ学習のチャンスです。ぜひ書籍を読んでみてください。
書籍紹介の後に一覧を記載しています。
書籍紹介
SQLアンチパターンは、データベース設計で“ついやってしまう誤り”を体系的に整理した実践書です。
2013年が初版で第二版として発売されました。私は初版を読んだことがなかったので知らないパターンも多く、とても勉強になりました。一方で、第二版が出るくらいですから初版も結構浸透しており、内容としては知っているものもちらほらありました。
ただ、内容を知っているのと深く理解しているのは別です。アンチパターンに対して、とても丁寧に問題点を説明してあるので、理解が深まります。とてもおすすめの書籍です。
目的に対して自然と導出できてしまう方法がアンチパターンになったりします。何故それらがアンチパターンになるのかを理解しておくことで、他のアンチパターンに陥らないためのより汎用的な思考の土台になると思います。
例えばメタデータトリブルでは、クエリで扱うデータ量を減らすためにBugs_2025のように作成年に基づいてテーブルを分割するアンチパターンを紹介しています。
これで面白かったのが、テーブルを跨いだ場合のSQLの話です。テーブルを跨ぐと以下のようなUnionになります。
SELECT b.status, COUNT(*) AS count_per_status FROM (
SELECT * FROM Bugs_2023
UNION ALL
SELECT * FROM Bugs_2024
UNION ALL
SELECT * FROM Bugs_2025 ) AS b
GROUP BY b.status;
では2026年になったら、このクエリはどうなるでしょうか。このコードを書いた開発者は年末年始にゆっくり休めなくなるかもしれません。Bugs_2026 テーブルが増えてSQLを変える必要があるのでアプリケーションコードの変更が必要になります。
この例では検索のクエリが長期でどう変化するか想像できればアンチパターンを避けられそうです。
汎化と具体例の手札を持つ
自身の解法に対して以下を想像してみるだけでもアンチパターンにハマらなくなります。
- 長期でデータはどのように増えていき、どのような種類ができていくか
- 作成、検索、更新、削除のクエリはどうなるか、JOINやWHAREが複雑にならないか
当たり前じゃんと思ってしまいますね。
まとめてしまうと当然のことになってしまう系のやつでは、具体例の手札を知識として増やしておくことがいざという時の応用力に直結します。シニアなエンジニアは豊富な経験や知識から瞬時に上記の想像をして設計判断をしています。
まとめ
代表的なアンチパターンと何故それらがアンチパターンになってしまうのかを丁寧に説明しているとても良い書籍でした。しっかりと理解することで他のアンチパターンにハマらないための思考の土台を作れます。
以降で表面的なアンチパターンを一覧で紹介していますが、それらがアンチパターンになる理由を理解することが一番重要です。何故アンチパターンなのか説明できなそうな項目があれば書籍を購入して読んでみることおすすめします。
前提
- 課題や解決策は要約する過程で削ぎ落としている部分があります。より深い理解のために書籍を読むことをおすすめします。
- 「アンチパターンを用いても良い場合」を省略しています。
- 書籍内の「データベース論理設計」「データベース物理設計」「クエリ」カテゴリを対象にしています。「アプリケーション開発」のカテゴリは個人的に学びが少なかったのでまとめていません。
凡例
アンチパターン名を各節のタイトルにして、以下の概要をそれぞれに記載しています。
| 項目 |
内容 |
| 目的 |
本来の解決したい問題。 |
| 概要 |
目的を達成するために用いられるアンチパターンの概要。 |
| 解決 |
アンチパターンを用いない解決方法。 |
ジェイウォーク（信号無視）
| 項目 |
内容 |
| 目的 |
複数の値を持つ属性を格納する。 |
| 概要 |
複数の値をカンマ区切りフォーマットのリストとして 1 つの列に格納する。これにより、クエリの作成が困難になり、データ整合性の保証ができない。 |
| 解決 |
新たに交差テーブルを作成し、複数の値を複数の行として格納することで、多対多の関係をモデル化する。 |
ナイーブツリー（素朴な木）
| 項目 |
内容 |
| 目的 |
階層構造を格納し、クエリを実行する。 |
| 概要 |
常に親のみに依存する隣接リスト設計（parent_id列）を使用する。これにより、階層の深さに関係なくすべての子孫を取得するクエリが複雑になり、非効率になる。 |
| 解決 |
近年では隣接リスト+再帰クエリにより解決できる。
または代替ツリーモデル（経路列挙、入れ子集合、閉包テーブル）を使用する。 |
ID リクワイアド（とりあえず ID）
| 項目 |
内容 |
| 目的 |
主キーの規約を確立する。 |
| 概要 |
すべてのテーブルに、自動生成される整数値の「id」列を主キーとして用いる。これにより、本来複合キーで一意性が保証されるべきテーブルで重複行を許可したり、キーの意味がわかりにくくなったりする。 |
| 解決 |
状況に応じて適切に調整する。自然キーや、複数の列で構成される複合キーの活用を検討する。 |
キーレスエントリ（外部キー嫌い）
| 項目 |
内容 |
| 目的 |
データベースのアーキテクチャを単純化する。 |
| 概要 |
データベースの参照整合性制約をアプリケーションコードで処理しようと試み、外部キー制約を使用しない。これにより、データ不整合が生じるリスクが高まる。 |
| 解決 |
外部キー制約を宣言することで、データ登録時点で不整合を阻止する。 |
EAV（エンティティ・アトリビュート・バリュー）
| 項目 |
内容 |
| 目的 |
可変属性をサポートする。 |
| 概要 |
属性を列ではなく「行」に格納する汎用的な属性（Entity-Attribute-Value, EAV）テーブルを使用する。これにより、必須属性の設定や参照整合性の強制ができず、属性を取得するためのクエリが複雑になる。 |
| 解決 |
サブタイプのモデリングを行う。具体的には、シングルテーブル継承、具象テーブル継承、クラステーブル継承、または半構造化データ（JSONなど）の使用を検討する。 |
| 項目 |
内容 |
| 目的 |
複数の親テーブルを参照する。 |
| 概要 |
参照する親テーブルを識別する列を作る。複数の親テーブルへの外部キー制約を設けられないため、参照整合制約を定義できない。 |
| 解決 |
関連を単純化する。参照を逆向きにする（親テーブルごとに交差テーブルを作成）か、すべての親テーブルが継承する共通の基底テーブルを作成する。 |
| 項目 |
内容 |
| 目的 |
複数の値を持つ属性を格納する。 |
| 概要 |
属性の値をそれぞれ 1 つずつ格納するために、tag1, tag2, tag3 のように複数の列を定義する。これにより、値の検索や追加・削除が複雑になる。一意性が担保できない。 |
| 解決 |
属性の値を格納する列を 1 つだけ持つ従属テーブルを作成する。同じ意味を持つ値は 1 つの列に格納する。 |
| 項目 |
内容 |
| 目的 |
スケーラビリティを高める。 |
| 概要 |
データの増加に対応して、Bugs_2022のように値に基づいてテーブルを分割する。テーブルをまたいだクエリ実行が必要になった場合、アプリケーションコードを毎年変更する必要がある。参照整合性の管理が困難になる。 |
| 解決 |
パーティショニング（水平/垂直）と正規化を行う。 |
ラウンディングエラー（丸め誤差）
| 項目 |
内容 |
| 目的 |
整数の代わりに小数値を使用する。 |
| 概要 |
FLOAT データ型を使用する。これにより、丸めが避けられず、値が正確に等しくならなかったり、集約計算（SUMなど）で誤差が累積したりする。 |
| 解決 |
財務データなど正確な値が必要な場合は、NUMERICまたはDECIMALデータ型を使用する。 |
サーティワンフレーバー（31のフレーバー）
| 項目 |
内容 |
| 目的 |
列を特定の値に限定する。 |
| 概要 |
ENUM 型や CHECK 制約を用いて、限定する値を列定義（メタデータ）で指定する。値の変更が必要になった場合、テーブル定義（メタデータ）の変更が必要になり、複雑でコストがかかる。 |
| 解決 |
限定する値をデータで指定する（参照テーブルを作成し、外部キー制約で参照する）。値の追加や削除が容易になり、移植性も高まる。 |
ファントムファイル（幻のファイル）
| 項目 |
内容 |
| 目的 |
画像をはじめとする大容量メディアファイルを格納する。 |
| 概要 |
物理ファイルに保存し、データベースにはファイルパスのみを格納する。トランザクション分離、ロールバック、バックアップ時にデータベースとファイルシステム間で問題が生じる。 |
| 解決 |
必要に応じてBLOB 型を採用する。データベースのトランザクション管理やアクセス制御を利用し、データ整合性を維持する。 |
インデックスショットガン（闇雲インデックス）
| 項目 |
内容 |
| 目的 |
パフォーマンスを最適化する。 |
| 概要 |
闇雲にインデックスを使用する。インデックスを定義しない、または多く定義し過ぎる/役立たないインデックスを定義する。インデックスが多すぎるとオーバーヘッドが増す。 |
| 解決 |
「MENTOR」の原則（Measure, Explain, Nominate, Test, Optimize, Rebuild）に基づき、効果的なインデックス管理を行う。必要なインデックスのみを作成する。 |
フィア・オブ・ジ・アンノウン（恐怖の unknown）
| 項目 |
内容 |
| 目的 |
欠けている値を区別する。 |
| 概要 |
NULLを一般値として使う、または一般値を NULLとして使う（NULLの代わりに -1 などの値を使う）。NULL との比較は「不明（unknown）」を返し、WHERE句の条件を満たさないため、意図しない結果を生む。 |
| 解決 |
NULLを一意な値として使う。NULL の検索には等価演算子ではなく IS NULL 述語を使う。欠けている値には NULL を用いる。SQLは true / false / unknown という3値論理であることを理解する。 |
アンビギュアスグループ（曖昧なグループ）
| 項目 |
内容 |
| 目的 |
グループ内で最大値を持つ行を取得する。 |
| 概要 |
GROUP BY句や集約関数で指定されていない非グループ化列を参照する。グループ内のどの行の値になるか不定となり、意図しない結果になる。 |
| 解決 |
曖昧でない列を使用する。ウィンドウ関数、相関サブクエリ、導出テーブル、または OUTER JOIN などを使用して、最大値を持つ行を正確に特定する。 |
ランダムセレクション
| 項目 |
内容 |
| 目的 |
ランダムに 1行をフェッチする。 |
| 概要 |
ORDER BY RAND()/RANDOM() を使ってデータをランダムにソートして1行取得する。インデックスが活用できず、テーブルスキャンによる高コストなソートが発生し、パフォーマンスが低下する。 |
| 解決 |
特定の順番に依存しない方法を採用する。例えば、キー値の範囲内の乱数を使って行を特定する、またはキー値のリストを取得してアプリケーション側でランダムに選択する。 |
スパゲッティクエリ
| 項目 |
内容 |
| 目的 |
SQLクエリの数を減らす。 |
| 概要 |
複雑な問題をワンステップで解決しようとする。デカルト積の発生や、クエリの可読性が悪化する。保守性、デバッグの困難さが増す。 |
| 解決 |
分割統治を行う。複雑なタスクを、それぞれシンプルで効率的な複数のクエリに分割して実行する。 |
インプリシットカラム（暗黙の列）
| 項目 |
内容 |
| 目的 |
タイプ数を減らす。 |
| 概要 |
ワイルドカード（*）の使用や、INSERT文での列名省略といったショートカットの罠に陥る。テーブル定義の変更（列の追加/削除/順番変更）により、予期せぬエラーやデータ不一致が生じる。 |
| 解決 |
列名を明示的に指定する。必要な列だけを指定することで、リファクタリング時の誤りを防ぎ、不要なデータ転送を減らす。 |
