SQLアンチパターンを学ぶ

はじめに

先日 t-wada さんが監督された書籍 SQLアンチパターン が発売されました。

本はまだ読んでいないのですが、読む前に予習として自分なりに整理してみようと思います。
その後実際に読んでみて理解を深める狙いです。

参考書籍

SQLアンチパターンの紹介は以下に t-wada さんの発表資料が公開されています。

同資料を使った解説動画も公開されております。

こちらの動画を見ながら書いていきます。
またベースはgemini-2.5-proで書き、追記修正しています。

前章ほぼ以下の内容で解説がされているとのことで、ココでもそれに従います。以下の内容となっています。

目的

解決するべき対象。アンチパターンとは、タスク解決のために用いられたものの、逆にもっと多くの問題を生じさせてしまうものなのです。

アンチパターン

アンチパターンの具体例の紹介です。

アンチパターンの見つけ方

直面している問題の種類や、メンバー間の会話での何気ない言葉がヒントになります。

アンチパターンを用いても良い場合

アンチパターンでも状況によってはそれが適切である場合や一番マシという場合の説明です。

解決策

解決策を提示します。

論理設計のアンチパターン

2章：ジェイウォーク(信号無視)

交差テーブルを避けてカンマ区切りを入れてしまう

目的：複数の値を持つ属性を格納する

あなたは、バグ管理アプリケーションの新機能を開発しています。製品の連絡窓口として、ユーザーを一人指定する機能です。最初の仕様では、各製品の連絡先となるユーザは一人のみでした。ところが案の定、後になって1つの製品に複数ユーザーを連絡先として登録できるようにしてほしいという要望が出てきました。

アンチパターン

はじめはその変更は簡単だと思いました。列にアカウントIDを1件のみ格納するのではなく、カンマ区切りで複数のアカウントIDのリストを格納できる要因すればよいと考え、その通りに変更を行いました。

これにより以下の問題が発生することになります。

• WHEREで検索する時にIDマッチングするために正規表現を使う必要が出てくる。めんどくさいだけではなくこれによりインデックスが効かなくなる。
• 同様にJOINでも正規表現を使う必要が出る。
• 集約クエリにおいても単純なCOUNTなどはできなくなり、IDの数をカウントするロジックの実装が必要になる。
• IDの更新もIDのリストの編集をしてからINSERTするアプリケーションコードが必要になる。
• 区切り文字としてカンマを使う場合、値としてはカンマを使えないなど区切り文字の選択問題が発生する。
• リストの長さ制限に引っかかる場合がある。

Postgresqlの場合 text は 1,073,741,823 バイトまで保持できるようなので、UUID4+カンマの場合は1,073,741,823 ÷ 33 ≈ 32,536,116 件で3000万件以上は行けそうです。格納数という意味では実質問題は起きず、先にクエリー性能の限界を迎えそうな気がします。

アンチパターンの見つけ方

• 「このリストでサポートしなければならない最大のエントリ数は？」
• 「SQLで単語境界を一致させる方法を知ってる？」
• 「リストの要素に絶対使われない文字ってなんだっけ？」

アンチパターンを用いても良い場合

パフォーマンス向上の為にテーブル構造に非正規化を適用する場合などには採用がありえます。
例えばJSON型やARRAY型を使う場合も正確には連続したデータを持つため非正規化となりますが、使うケースはよくあると思います。
しかしアプリケーションの柔軟性・データの整合性の観点からまずは正規化されたテーブル構造の採用を検討したほうが良いです。
どうしてもだめだったところだけ非正規化しましょう。

解決策

交差テーブルを作成し、アカウントテーブルと製品テーブルを多対多の接続を作る。

• インデックスが活用できる
• 集約クエリが簡単に実行できる
• データの更新が簡単にできる
• データの妥当性検証および参照整合性の保証ができる
• 区切り文字の考慮は不要
• データサイズの考慮は不要

ミニアンチパターン CSV列を複数の行に分割する

同様の話で、CSV列を複数の行に分割するミニアンチパターンがある。
横持ちしているデータを縦持ちに変換して使用する。これによりテーブル設計の悪さをカバーする。

3章：ナイーブツリー(素朴な木)

親IDの列を持ち、行同士が紐づく隣接リストを形作るパターン。

目的：階層構造を格納する

ファイルシステムのディレクトリ構造や、組織の上下関係など、階層構造を持つデータを格納したいケースはよくあります。

アンチパターン

parent_id のような列を追加し、同じテーブルの別の行を自己参照する形で階層を表現します。これは隣接リストと呼ばれ、最も直感的で素朴な方法です。

しかし、この方法には大きな欠点があります。

• クエリが複雑になる: 特定のノードのすべての子孫や祖先を取得するには、再帰的なクエリが必要になります。多くのSQL方言では、WITH RECURSIVE (CTE) のような構文が必要となり、クエリが複雑で読みにくくなります。
• パフォーマンスの問題: 深い階層や多数のノードを持つツリーに対して再帰クエリを実行すると、パフォーマンスが著しく低下します。クエリのたびに何度も自己結合を繰り返す必要があるためです。
• データベースへの負荷: アプリケーション側でループ処理を行うと、データベースへのクエリが大量に発生し（N+1問題）、システム全体に負荷をかけます。

最近ではCTE(Common Table Expression)が使えるDBが多いので、このパターンも一概にアンチパターンとは言えなくなってきています。しかし、パフォーマンスの問題は依然として残るため、利用には注意が必要です。

アンチパターンの見つけ方

• 「ツリーの深さはどれくらいまで許容する？」
• 「あるノードのすべての子孫を取得するSQLはどう書けばいい？」
• 「このクエリ、なんでこんなに遅いんだろう？」

アンチパターンを用いても良い場合

• 階層の深さが固定されている、または非常に浅いことが保証されている場合: 例えば、国、都道府県、市区町村のように階層が3階層までと決まっているなら、隣接リストでも問題ないかもしれません。
• ノードの直接の親子関係しか問い合わせない場合: ツリー全体を走査する必要がなく、常に直接の親や子を取得するだけであれば、このパターンはシンプルで効果的です。

解決策

隣接リストの代替案として、いくつかの方法があります。

• 入れ子集合モデル (Nested Sets): 各ノードに左値と右値を持たせ、ツリーを包含関係で表現します。子孫の取得は高速ですが、ノードの挿入・更新・削除のコストが高いのが欠点です。

入れ子集合モデル（Nested Sets）とは

• ツリーの各ノードに 左値（lft） と 右値（rgt） の2つの数値を割り振ります。
• これらの値は「ノードが含む範囲」を表します。
• 親ノードの範囲は、その子ノードの範囲をすべて含みます。

name	lft	rgt
家具	1	10
テーブル	2	5
ダイニング	3	4
椅子	6	9

• 経路列挙モデル (Path Enumeration): 各ノードにルートからのパスを文字列として保持させます（例: /1/4/5/）。LIKE 演算子で子孫を検索でき、比較的直感的です。

経路列挙モデルとは

• 各ノードに、ルートからそのノードまでの「パス」を文字列で保存します。
• パスは親子の ID を / 区切りで並べたものが典型です。

id	name	path
1	家具	/1/
2	テーブル	/1/2/
3	ダイニング	/1/2/3/
4	椅子	/1/4/

• 閉包テーブル (Closure Table): すべてのノード間の親子関係（直接的・間接的問わず）を別のテーブルにすべて列挙して保持します。これにより、あるノードのすべての子孫や祖先を単純なJOINで取得できます。データの冗長性と引き換えに、クエリのパフォーマンスを劇的に向上させます。

閉包テーブルとは

• 階層構造を別テーブルに「すべての親子関係」を展開して持つ方法です。
• 直接の親子だけでなく、間接的な祖先・子孫関係も全て保存します。
• 「家具の子孫全部」は ancestor=1 の行を探せば一発で取れる

SELECT descendant
FROM closure
WHERE ancestor = 1;

• 「ダイニングの祖先全部」は descendant=3 の行を探せば一発で取れる

SELECT ancestor
FROM closure
WHERE descendant = 3;

id	name
1	家具
2	テーブル
3	ダイニング
4	椅子

ancestor	descendant	depth
1	1	0
1	2	1
1	3	2
1	4	1
2	2	0
2	3	1
3	3	0
4	4	0

どの解決策が最適かは、アプリケーションの要件（読み取りと書き込みの頻度、ツリーの構造など）によって異なります。一般的には、閉包テーブルが柔軟性とパフォーマンスのバランスに優れているため、多くのケースで推奨されます。

4章：IDリクワイアド(とりあえずID)

すべてのテーブルに思考停止で id という名前の代理キー（サロゲートキー）を追加してしまうアンチパターン。

目的：テーブルの主キーを定義する

テーブルの各行は、一意に識別できる必要があります。そのための列が主キーです。

アンチパターン

テーブルを設計する際、そのテーブルの性質をよく考えずに、とりあえず id という名前の自動インクリメントされる整数型を主キーとして設定します。そして、本来その行を識別するはずの自然なキー（例えば、国テーブルにおける国コードや、ユーザーテーブルにおけるメールアドレスなど）の役割を無視してしまいます。

これにより、以下の問題が発生する可能性があります。

• データの重複: id さえ異なっていれば、他の列がすべて同じ値の行を複数作成できてしまいます。本来ユニークであるべきデータ（例：同じメールアドレスのユーザー）が重複して登録される原因となります。
• ビジネスルールの欠如: 主キーは単なる識別子ではなく、データの整合性を保つための重要な制約です。ナチュラルキーにユニーク制約をかけないと、データモデルがビジネスルールを正しく表現できません。
• 冗長なキー: 意味のあるナチュラルが存在するにもかかわらず代理キーを追加すると、テーブルに冗長なキーが生まれます。

アンチパターンの見つけ方

• 「このテーブルで、行をユニークに識別するビジネス上の意味を持つ列はどれ？」
• 「なぜこのテーブルには UNIQUE 制約がないの？」
  (代理キーで業務キーの一意性が保証されている可能性がある)
• 開発者がテーブルのナチュラルキーを答えられず、「主キーは id です」としか言えない。

アンチパターンを用いても良い場合

代理キーが常に悪というわけではありません。以下のような場合には、代理キーの使用が推奨されます。

• 適切なナチュラルキーが存在しない場合: 行を一位に特定できる自然な列の組み合わせが見つからない。
• ナチュラルキーが変更される可能性がある場合: 例えば、結婚によって姓が変わる可能性がある人の名前を主キーにするのは危険です。主キーの値は不変であるべきです。
• ナチュラルキーが大きすぎる場合: 複数の列からなる複合キーが非常に大きい場合、それを外部キーとして他のテーブルで参照すると、ストレージとパフォーマンスに影響を与える可能性があります。

解決策

テーブルを設計する際には、まずナチュラルキーを探します。

1. テーブルの列の中から、その行を一意に識別できる候補キー（candidate key）をすべて見つけます。
2. 候補キーの中から、最も主キーとしてふさわしいものを一つ選び、主キーとします。
3. 主キーにしなかった他の候補キーには、UNIQUE 制約を追加して、データの重複を防ぎます。
4. 適切なナチュラルキーが見つからない場合に限り、代理キーの使用を検討します。その場合でも、ビジネス上ユニークであるべき列には UNIQUE 制約を忘れずに追加しましょう。

5章：キーレスエントリ(外部キー嫌い)

データベースの参照整合性を強制する外部キー制約を使わないアンチパターン。

目的：テーブル間のリレーションシップを定義する

テーブル設計において、あるテーブルの行が別のテーブルの行を參照することは頻繁にあります。例えば、Bugs テーブルの reported_by 列は Accounts テーブルの特定のユーザーを參照します。

アンチパターン

外部キー制約を定義せずに、アプリケーションのコードでリレーションシップを維持しようとします。開発者は、外部キーは管理が面倒で、データベースの柔軟性を損なうと考えているかもしれません。

しかし、外部キー制約を省略すると、以下のような深刻な問題を引き起こします。

• 孤立したレコード（オーファン）の発生: 参照先の行が削除されても、参照元の行はそのまま残ってしまいます。例えば、あるアカウントが削除された後も、そのアカウントが報告したバグがデータベースに残り続け、存在しないユーザーを参照している状態になります。
• データの不整合: 存在しない値を外部キー列に挿入できてしまいます。例えば、Accounts テーブルに存在しない account_id を Bugs テーブルの reported_by に登録できてしまいます。
• アプリケーションロジックの複雑化: データ整合性を保つためのロジックをすべてアプリケーション側で実装する必要があり、コードが複雑になり、バグの温床となります。

アンチパターンの見つけ方

• 「この列が參照しているテーブルの行が削除されたらどうなる？」
• 「データベースのスキーマを見ただけでは、テーブル間の関連が分からない」
• データベースダイアグラムにテーブル間のリレーションシップを示す線が描かれていない。

アンチパターンを用いても良い場合

基本的には外部キー制約は常に使用するべきですが、ごく稀に例外があります。

• パフォーマンスが最重要視される超大規模システム: 例えば、巨大なソーシャルネットワークのバックエンドなど、書き込みのスループットを極限まで高める必要がある場合、外部キー制約のオーバーヘッドが許容できないことがあります。しかし、これは非常に特殊なケースであり、ほとんどのアプリケーションには当てはまりません。
• 分散データベース: 複数のデータベースにまたがるリレーションシップを扱う場合、外部キー制約を適用するのが難しいことがあります。

解決策

解決策はシンプルです。外部キー制約を使いましょう。

• FOREIGN KEY 制約を定義して、テーブル間のリレーションシップをデータベースレベルで強制します。
• ON DELETE 句（CASCADE, SET NULL, RESTRICT など）を適切に設定し、参照先の行が削除された際の動作を定義します。これにより、データの整合性が自動的に保たれます。

データベースにデータの整合性を任せることで、アプリケーションはよりシンプルになり、堅牢性も向上します。

こんなアンチパターンがあるとは思いませんでした。これこそ脳死で外部キーを設定してました。外部キー設定も避けるようなキンキンなパフォーマンス設定の場面に出会ってみたいものです 💪

6章：EAV(エンティティ・アトリビュート・バリュー)

柔軟性を追い求めるあまり、行を列のように使ってしまうアンチパターン。

目的：可変属性を格納する

将来、製品に追加される属性が予測できない場合や、製品の種類によって属性が大きく異なる場合に対応しようとします。例えば、あるPCは「CPU速度」や「メモリ容量」を持ち、別のPCは「画面サイズ」を持つ、といった状況です。

アンチパターン

Products テーブルのような固定的な列を持つテーブルの代わりに、Entities (実体)、Attributes (属性)、Values (値) の3つのテーブル（またはそれに類する構造）を使います。

• Entities: 製品などを表す（例: product_id=123）
• Attributes: 属性名を表す（例: attr_name='CPU'）
• Values: そのエンティティのその属性の値（例: value='3.0GHz'）

このEAVモデルは究極の柔軟性を提供しますが、その代償は非常に大きいです。

• クエリが非常に複雑になる: 特定の製品のすべての属性を取得するには、属性の数だけ自己結合を繰り返すか、複雑なピボット操作が必要になります。
• データ型の保証ができない: Values テーブルの value 列は、文字列、数値、日付など、あらゆるデータ型を格納できるように、通常は VARCHAR 型などで定義されます。これにより、データベースレベルでのデータ型チェックが機能しなくなります。
• 必須属性の強制ができない: ある製品には「価格」が必須である、といったルールをデータベースレベルで強制することが困難です。
• 外部キー制約が使えない: 属性の値が別のテーブルを参照している場合（例：メーカーID）、外部キー制約を正しく適用できません。
• パフォーマンスの低下: 巨大なEAVテーブルに対するクエリは、通常のテーブルに対するクエリよりもはるかに遅くなります。

アンチパターンの見つけ方

• 「属性を取得するために、なんでこんなにJOINが必要なんだ？」
• テーブル名に Attributes や Values が含まれている。
• value 列のデータ型が VARCHAR や TEXT になっており、そこに数値や日付が文字列として格納されている。

アンチパターンを用いても良い場合

EAVモデルは、ほとんどのアプリケーション開発において避けるべきです。しかし、臨床医療データなど、属性が非常に動的で、まばら（スパース）なデータを扱う特定の専門分野では、有効な場合があります。ただし、これは一般的なWebアプリケーションやビジネスアプリケーションには当てはまりません。

解決策

• サブタイプ（具象クラス継承）: 共通の属性を親テーブルに持たせ、種類ごとの固有の属性をそれぞれの子テーブルに持たせます。例えば、Products テーブルに共通属性（価格、名前など）を置き、Laptops テーブルに「CPU速度」、Monitors テーブルに「画面サイズ」を格納します。
• クラス・テーブル継承: 親テーブルに共通属性、子テーブルに固有属性を持たせるのはサブタイプと同じですが、親テーブルと子テーブルを product_id で1対1の関係で結びます。
• 疎な列のサポート: 一部のデータベースシステム（Microsoft SQL Serverなど）は、多数のNULL値を持つ列を効率的に格納する「スパースカラム」機能をサポートしています。
• JSONやXMLデータ型の利用: 多くの現代的なデータベースは JSON や XML 型をサポートしています。柔軟性が必要な属性をこれらの型の単一の列に格納することで、リレーショナルモデルの利点を維持しつつ、スキーマレスの柔軟性を取り入れることができます。これは多くの場合、EAVよりも優れたトレードオフです。

7章：ポリモーフィック関連

一つの外部キーが、複数の親テーブルを参照できるようにするアンチパターン。

目的：複数の親テーブルを参照する

例えば、Comments テーブルがあり、そのコメントが Bugs に付けられたものか、FeatureRequests に付けられたものか、あるいは他の種類の課題に付けられたものかを区別したい、という状況です。

アンチパターン

Comments テーブルに、参照先のテーブル名を格納する issue_type 列と、そのテーブルでのIDを格納する issue_id 列を持たせます。これにより、Comments テーブルの外部キー（issue_type, issue_id）が、状況に応じて Bugs テーブルや FeatureRequests テーブルを "多形的に" 参照するように見せかけます。

しかし、この方法では標準的な外部キー制約が使えません。

• 参照整合性が保証されない: issue_id に存在しないIDを登録したり、issue_type に無効なテーブル名を入れたりできてしまいます。また、親テーブルのレコードが削除されても、コメントは孤立して残ってしまいます。
• クエリが複雑になる: コメントとその親を取得するクエリは、issue_type の値に応じて CASE 文を使ったり、複数の LEFT JOIN を書いたりする必要があり、非常に複雑で非効率になります。

アンチパターンの見つけ方

• テーブルに object_type や owner_type のような、他のテーブル名を格納する列がある。
• 外部キーのように見えるが、FOREIGN KEY 制約が定義されていない列がある。

アンチパターンを用いても良い場合

ありません。このパターンは常に避けるべきです。

解決策

• 交差テーブルを作成する: Comments テーブルと、各親テーブル（Bugs, FeatureRequestsなど）との間に、それぞれ交差テーブル（Bugs_Comments, FeatureRequests_Commentsなど）を作成します。これにより、標準的な外部キー制約が使え、参照整合性が保証されます。
• 共通の親テーブルを作成する（シングルテーブル継承）: すべての親テーブル（Bugs, FeatureRequests）が共通して持つ列をまとめた、Issues という親テーブルを作成します。Comments はこの Issues テーブルを単一の外部キーで参照します。各課題タイプに固有の属性は、Issues テーブルの追加の列として持ちます（NULLを許容）。
• 共通の親テーブルを作成する（具象クラス継承）: 上記と同様に Issues テーブルを作成しますが、各課題タイプ固有の属性は、それぞれ別のテーブル（Bugs, FeatureRequests）に格納し、Issues テーブルと1対1で関連付けます。

8章：マルチカラムアトリビュート(複数列属性)

一つの属性を表現するために、複数の列を使ってしまうアンチパターン。ジェイウォーク（信号無視）の逆バージョンです。

目的：複数の値を持つ属性を格納する

ジェイウォークと同様に、1つの製品に複数のタグを付けたい、といった目的です。

アンチパターン

タグを格納するために、tag1, tag2, tag3 のように、あらかじめ決められた数の列を用意します。

この方法の問題点は明らかです。

• 検索が困難: 特定のタグを持つ製品を検索するには、WHERE tag1 = '...' OR tag2 = '...' OR tag3 = '...' のように、すべてのタグ列を検索する必要があります。
• 追加・削除が困難: タグを追加・削除する際、空いている列を探したり、値をシフトさせたりする複雑なロジックが必要になります。
• 上限数の問題: タグの数が事前に用意した列の数を超えると、スキーマを変更（ALTER TABLE）する必要が出てきます。
• 一意性の保証が困難: 同じ製品に同じタグが複数登録されるのを防ぐのが難しくなります。

アンチパターンの見つけ方

• phone1, phone2 のように、数字で終わる名前の列が複数ある。
• 「タグは最大でいくつまでサポートすればいいですか？」という質問が出る。

アンチパターンを用いても良い場合

ありません。このパターンは常に避けるべきです。

解決策

ジェイウォークと同様、交差テーブルを作成します。Products テーブルと Tags テーブルの間に Product_Tags テーブルを作成し、多対多のリレーションシップを表現します。これにより、上記の問題はすべて解決します。

9章：メタデータトリプル(メタデータ大増殖)

テーブルや列といったデータベースのメタデータ（構造）そのものを、値として使ってしまうアンチパターン。

目的：パーティショニングによるパフォーマンス向上

大量のデータを扱うテーブルにおいて、クエリのパフォーマンスを向上させるために、データを分割して格納しようとします。

アンチパターン

年や月、地域など、特定の基準でテーブルを分割します。例えば、Bugs_2023, Bugs_2024 のように、年ごとにテーブルを作成します。

これにより、以下の問題が発生します。

• クエリが複雑になる: 複数の年にまたがるデータを検索するには、UNION ALL を使って多数のテーブルを結合する必要があり、クエリが冗長になります。
• スキーマ変更が困難: Bugs テーブルに列を追加する場合、すべてのパーティションテーブルに対して ALTER TABLE を実行する必要があり、メンテナンスが悪夢になります。
• 外部キー制約が使えない: 他のテーブルからこれらのパーティションテーブル全体を參照するような、単純な外部キー制約は定義できません。
• アプリケーションロジックの複雑化: アプリケーションは、どのパーティションテーブルにクエリを発行すべきかを判断するロジックを持つ必要があります。

アンチパターンの見つけ方

• Users_Europe, Users_Asia のように、同じ接頭辞を持つテーブルが多数存在する。
• アプリケーションコードに、現在の日付などに基づいてクエリ対象のテーブル名を動的に生成するロジックがある。

アンチパターンを用いても良い場合

手動でのパーティショニングは、ほとんどの場合避けるべきです。

解決策

多くのモダンなデータベースシステムは、宣言的パーティショニングという機能をサポートしています。

• Bugs という単一のテーブルを定義し、created_at 列などのパーティションキーに基づいて、データベースに自動的にデータを分割させます。
• アプリケーションは単一の Bugs テーブルに対してクエリを発行するだけでよく、データベースが裏側で適切なパーティションのみをスキャンしてくれます。

これにより、アプリケーションロジックをシンプルに保ちつつ、パーティショニングのパフォーマンス上の利点を享受できます。

物理設計のアンチパターン

10章：ラウンディングエラー(丸め誤差)

金融計算など、正確さが求められる場面で浮動小数点数型（FLOAT, DOUBLE PRECISION）を使ってしまうアンチパターン。

目的：非整数を格納する

価格や割合など、小数を含む数値を格納します。

アンチパターン

FLOAT や DOUBLE PRECISION 型は、数値を2進数形式で近似的に表現します。そのため、0.1 のような10進数の小数を正確に表現できず、ごくわずかな誤差（丸め誤差）が生じます。

この小さな誤差は、計算を繰り返すうちに蓄積され、大きな問題を引き起こす可能性があります。

• 不正確な計算結果: SUM や AVG などの集計関数が、期待と異なる結果を返すことがあります。
• 等価比較の失敗: WHERE price = 49.95 のような比較が、内部表現の誤差により失敗することがあります。

アンチパターンの見つけ方

• 金額を格納している列のデータ型が FLOAT になっている。
• 計算結果の小数点以下の桁が、...0000001 や ...9999999 のようになっている。

アンチパターンを用いても良い場合

科学技術計算など、非常に大きな値や非常に小さな値を扱う場面で、絶対的な精度よりも計算速度や値の範囲が重要な場合には FLOAT 型が適しています。しかし、金融計算には決して使ってはいけません。

解決策

正確な10進数の計算が必要な場合は、NUMERIC または DECIMAL 型を使いましょう。

• これらのデータ型は、数値を10進数のまま（内部的には文字列や特殊な形式で）格納するため、丸め誤差が発生しません。
• 精度（全体の桁数）とスケール（小数点以下の桁数）を指定できるため、要件に応じた正確な計算が可能です。
• 例: NUMERIC(10, 2) は、小数点以下2桁まで、全体で10桁までの数値を正確に格納します。

11章：サーティーワンフレーバー(31のフレーバー)

列の定義に、CHECK 制約や外部キー参照ではなく、ENUM 型やそれに類する独自の方法を使ってしまうアンチパターン。

目的：列が取りうる値を制限する

ステータス列に "OPEN", "IN_PROGRESS", "CLOSED" のいずれかの値のみを許可するなど、列の値を特定のリストに限定したい。

アンチパターン

ENUM 型は、一見すると便利に見えます。しかし、以下のような問題があります。

• 値の追加・変更が困難: ENUM 型に新しい値を追加するには、ALTER TABLE が必要となり、多くの場合、テーブルの再構築が発生し、コストのかかる操作になります。
• 移植性が低い: ENUM 型の構文や動作は、データベース製品によって大きく異なります。標準SQLには存在しません。
• 値に関する追加情報を持てない: "OPEN" というステータスが何を意味するのか、といった追加のメタデータを ENUM 型自体に持たせることはできません。

アンチパターンの見つけ方

• CREATE TABLE 文に ENUM(...) という構文が使われている。
• アプリケーションのコード内に、ステータス値をハードコーディングしたリストがある。

アンチパターンを用いても良い場合

値のリストが固定的で、将来にわたって変更される可能性が絶対にない、と断言できる稀なケースでは、ENUM 型も選択肢になるかもしれません。しかし、そのようなケースはほとんどありません。

解決策

• 参照テーブルを作成する: ステータスの値を格納する Statuses テーブルを作成し、Bugs テーブルからそのテーブルへの外部キー参照を設定します。
• CHECK 制約を使用する: CHECK (status IN ('OPEN', 'IN_PROGRESS', 'CLOSED')) のように、CHECK 制約を使って列の値を制限します。

参照テーブルを作成する方法が最も柔軟性が高く、推奨されます。これにより、

• 値の追加・削除が容易になる（Statuses テーブルに行を挿入・削除するだけ）。
• 各値に関する追加情報（例：ステータスの説明）を Statuses テーブルの列として持つことができる。
• 移植性が高まる。

これはやっちゃったことありますね・・・
確かに後で変更するのが大変になっちゃうんですよね。

サーティワンフレーバーというのは、フレーバーをENUMで定義しちゃうと変えるのが大変だよ。という意味合いの名前でしょうか。

12章：ファントムファイル(幻のファイル)

画像などのバイナリファイルをデータベース外のファイルシステムに保存し、データベースにはそのパスのみを格納するアンチパターン。

目的：ファイルとメタデータを一緒に管理する

製品画像とその製品情報など、ファイルとそれに関連するデータを一緒に管理したい。しかし、データベースに大きなファイルを格納することによるパフォーマンスの懸念から、ファイルを外部ストレージに置くことを選択します。

アンチパターン

ファイルをファイルシステムに保存し、データベースのテーブルにはそのファイルへのパスを VARCHAR 型の列として格納します。これは一見、データベースの肥大化を防ぎ、Webサーバーがファイルを効率的に配信できるため、合理的な設計に見えます。

しかし、この方法には「思考停止で採用するな」と本書は警鐘を鳴らします。なぜなら、データの管理がデータベースとファイルシステムの2つに分かれてしまうことで、新たな問題が発生するからです。

• データの不整合（ファントムファイル）: データベースのトランザクションとファイルシステムのファイル操作は、アトミック（不可分）ではありません。
  • レコードを登録したが、ファイルの保存に失敗する（DBにパスはあるが、実体がない）。
  • ファイルは保存したが、レコードの登録に失敗する（ファイルはあるが、DBにパスがない）。
  • レコードを削除したが、ファイルの削除に失敗する（孤立したファイルが残る）。
• バックアップとリストアの複雑化: データベースのバックアップと、ファイルシステムのバックアップを、完全に同じタイミングで取得することは困難です。リストアする際に、両者の整合性が取れなくなっている可能性があります。
• 参照整合性の欠如: データベースの外部キー制約のように、ファイルパスが指すファイルが実際に存在することを保証する仕組みがありません。

アンチパターンの見つけ方

• 「ファイルはあるのに、アプリケーションに表示されないのはなぜ？」
• 「削除したはずの画像が、URLを直接叩くとまだ見えてしまう」
• ファイルシステムに、どのデータベースレコードからも参照されていない孤立したファイルが大量に存在する。

アンチパターンを用いても良い場合

このパターンは、デメリットを十分に理解し、対策を講じた上で採用する必要があります。

• ファイルが非常に大きい場合: 数ギガバイトに及ぶ動画ファイルなど、現実的にデータベースに格納するのが不可能な場合は、このパターンを選択せざるを得ません。
• コンテンツ配信ネットワーク(CDN)を利用する場合: ファイルをCDNから配信することでパフォーマンスを向上させたい場合。

解決策

このアンチパターンが提起する問題は、整合性をどう担保するかです。解決策は一つではありません。

• ファイルをデータベースに格納する: 最もシンプルで整合性を保てる解決策は、ファイルを BLOB 型の列に格納することです。これにより、データベースのトランザクション機能によって、メタデータとファイル実体のアトミックな操作が保証されます。バックアップも容易になります。パフォーマンスの懸念はありますが、本当にそれがボトルネックになるかをまず測定するべきです。
• 不整合を検知・修正する仕組みを構築する: ファイルを外部に置くことを選択した場合、アプリケーション側で整合性を保つための仕組みを構築する必要があります。
  • 孤立したファイルを定期的にクリーンアップするバッチ処理を実装する。
  • 参照先のファイルが存在しない場合に、アプリケーションが適切にエラーハンドリングする（例：デフォルト画像を表示する）。
  • ファイルとレコードの登録・削除処理を、より堅牢にする（例：リトライ処理、失敗時の補償トランザクション）。

思考停止で「ファイルはDBの外」と決めるのではなく、ファイルをDBに格納した場合のデメリットと、外に置いた場合の整合性リスクを天秤にかけ、プロジェクトの要件に合った最適な方法を選択することが重要です。

これも全く同じ経験をしたことがありますね。画像ファイルはS3に、そのパスをテーブルのデータとして保持していました。
通常時は特に問題はないのですが、いざ削除・データ復旧・サービス移管などをやろうとすると途端に複雑性が増します。
きちんとDBの負荷を考えて場合によってはテーブルにバイナリデータを突っ込んだほうがいい場合もあるんですね。
まあ、とは言え、どちらかと言うと削除・データ復旧・サービス移管、等を考慮した設計を最初からしておいて、同様の設計で各種ファイルの扱いを実装するような設計をするほうが良いような気はします。

13章：インデックスショットガン(闇雲インデックス)

パフォーマンス問題を解決しようとして、手当たり次第にインデックスを作成してしまうアンチパターン。

目的：クエリのパフォーマンスを向上させる

特定のクエリが遅い場合に、その速度を改善しようとします。

アンチパターン

クエリの実行計画を分析せずに、WHERE 句に登場するすべての列や、考えられるすべての列の組み合わせに対してインデックスを作成します。

インデックスは読み取り（SELECT）のパフォーマンスを向上させますが、書き込み（INSERT, UPDATE, DELETE）のパフォーマンスを低下させます。

• 書き込み性能の悪化: 行を挿入・更新・削除するたびに、テーブルだけでなく、関連するすべてのインデックスも更新する必要があり、オーバーヘッドが増加します。
• ストレージの消費: インデックスはテーブルとは別にストレージ領域を消費します。インデックスが増えれば、それだけディスク容量が必要になります。
• オプティマイザの混乱: 不必要なインデックスが多すぎると、クエリオプティマイザが最適な実行計画を選択するのを妨げる可能性があります。

アンチパターンの見つけ方

• テーブルに大量のインデックスが定義されている。
• どのクエリで使われているか分からないインデックスがある。
• 開発者が「とりあえずインデックスを付けておいた」と言う。

アンチパターンを用いても良い場合

ありません。インデックスは慎重に設計・管理されるべきです。

解決策

M.E.S.S. (Measure, Explain, Synthesize, Seek) アプローチでインデックスを管理します。

1. Measure (測定): パフォーマンス測定ツール（EXPLAIN ANALYZE など）を使って、どのクエリが遅いのか、どこにボトルネックがあるのかを特定します。
2. Explain (説明): EXPLAIN を使って、クエリオプティマイザがどのような実行計画を立てているか（どのインデックスを使い、どのテーブルをどのように結合しているか）を理解します。
3. Synthesize (統合): 実行計画を分析し、クエリのパフォーマンスを向上させるために、どのようなインデックスが効果的かを考え、作成します。複合インデックスやカバリングインデックスが有効な場合も多いです。
4. Seek (探求): 不要なインデックスや重複したインデックスがないかを探し、定期的に削除します。多くのデータベースは、使用されていないインデックスを特定する機能を提供しています。

M.E.S.Sアプローチって初めて聞きました。
geminiの造語でしょうか？
geminiに聞いたところBill Karwinがその言葉を使っていると言っています。
本当でしょうか、原本にあたってみたいと思います。

クエリのアンチパターン

14章：フィア・オブ・ジ・アンノウン(恐怖のunknown)

NULL を他の値と同じように扱おうとして、SQLの3値論理（TRUE, FALSE, UNKNOWN）で混乱するアンチパターン。

目的：欠損値を扱う

必須ではない属性など、値が存在しない場合を表現する必要があります。

アンチパターン

NULL は「値がない」ことを示すマーカーであり、特定の値（0や空文字列など）ではありません。NULL を含む比較演算の結果は、TRUE や FALSE ではなく、UNKNOWN になります。

• NULL = NULL は UNKNOWN
• NULL <> NULL は UNKNOWN
• 1 + NULL は NULL

この UNKNOWN の振る舞いを理解していないと、予期せぬクエリ結果に繋がります。特に NOT IN 演算子と NULL の組み合わせは有名です。
SELECT * FROM ... WHERE status NOT IN ('OPEN', NULL)
このクエリは、status が NULL の行に対して status <> 'OPEN' と status <> NULL の両方を評価しようとします。後者が UNKNOWN になるため、WHERE 句全体の結果も UNKNOWN となり、一行も返しません。

アンチパターンの見つけ方

• WHERE col = NULL や WHERE col <> NULL という間違った構文を使っている（正しくは WHERE col IS NULL, WHERE col IS NOT NULL）。
• NOT IN を使ったクエリが、期待通りに動作しない。
• NULL を避けるために、数値列に 0 や -1、文字列型に '' や 'N/A' のような特別な値を代わりに使っている（これは別のアンチパターンに繋がる）。

アンチパターンを用いても良い場合

NULL の振る舞いを正しく理解し、適切に扱う必要があります。避けるべきものではありません。

解決策

• NULL の比較には IS NULL と IS NOT NULL を使うことを徹底します。
• NULL を他の値に変換したい場合は、COALESCE() 関数や IFNULL() 関数を使います。例: COALESCE(price, 0)
• NOT IN のサブクエリやリストに NULL が含まれる可能性がある場合は、代わりに NOT EXISTS を使うことを検討します。EXISTS は3値論理の影響を受けにくく、より安全で、多くの場合パフォーマンスも優れています。
• UNIQUE 制約は、NULL を複数許容する（データベース製品による）など、NULL の扱いを理解しておく必要があります。

あんまりUNKNOWNで困ったこと無いなと思っていたのですが、IDEでIS NULLを使え！と怒られたことは何度もありました。知らず知らずのうちにアンチパターンを避けられていたんですね。便利な世の中です 😃

15章：アンビギュアスグループ(曖昧なグループ)

GROUP BY 句に含めていない非集計列を SELECT リストに含めてしまうアンチパターン。

目的：グループごとの集計を行う

各製品カテゴリごとのバグの総数など、グループ化したデータに対して集計関数（COUNT, SUM, AVGなど）を適用します。

アンチパターン

標準SQLでは、GROUP BY を使う場合、SELECT リストに含めることができるのは、GROUP BY 句で指定した列か、集計関数で集計された列のみです。

しかし、MySQLの古いバージョンなど一部のデータベースでは、GROUP BY に含めていない非集計列を SELECT リストに指定することが許されていました。
SELECT product_name, category_name, COUNT(*) FROM Products GROUP BY category_name;
この場合、各カテゴリの product_name として何が返されるかは不定です。MySQLは、そのグループ内のいずれかの行の値を任意に選択して返します。これは非決定的な結果であり、クエリを実行するたびに結果が変わる可能性さえあります。

アンチパターンの見つけ方

• GROUP BY クエリの結果が、実行するたびに変わることがある。
• GROUP BY 句で指定されていないのに、SELECT リストに裸の列（非集計列）が存在する。

アンチパターンを用いても良い場合

ありません。この非標準的な振る舞いに依存するべきではありません。幸い、最近のMySQLのバージョンでは、デフォルトでこの構文はエラーになります。

解決策

• SELECT リストには、GROUP BY 句の列と集計関数のみを含めるというルールを徹底します。
• グループ内の特定の行の値を取得したい場合は、MIN() や MAX() を使って明示的に指定します。
• グループ内のすべての値を取得したい場合は、GROUP_CONCAT() (MySQL) や STRING_AGG() (PostgreSQL) のような集計関数を使います。
• ANY_VALUE() 関数（MySQL 5.7以降）を使うと、非決定的な結果になることを許容した上で、この種のエラーを抑制できますが、利用には注意が必要です。

そもそも GROUP BY に含めていないものを SELECT に指定することができる時代があったことすら知りませんでした。

16章：ランダムセレクション

テーブルからランダムに1行を取得しようとして、ORDER BY RAND() (またはそれに類する関数) を使ってしまうアンチパターン。

目的：ランダムな行を選択する

今日のオススメ商品など、テーブルから無作為に行を選択したい。

アンチパターン

SELECT * FROM Products ORDER BY RAND() LIMIT 1;
このクエリは、一見すると正しく動作するように見えます。しかし、データベースの内部では、以下の処理が行われています。

1. Products テーブルのすべての行に対して、RAND() 関数を実行し、ランダムな値を生成します。
2. そのランダムな値を、テーブルのすべての行に一時的な列として追加します。
3. その一時的な列を使って、テーブルのすべての行をソートします。
4. ソートされた結果の最初の1行を返します。

テーブルの行数が増えるほど、このソート処理のコストは爆発的に増加し、パフォーマンスを著しく低下させます。

アンチパターンの見つけ方

• ORDER BY 句で RAND() や RANDOM()、NEWID() のような関数が使われている。
• データが増えるにつれて、特定のクエリが急激に遅くなる。

アンチパターンを用いても良い場合

テーブルの行数が非常に少なく、将来も増える見込みがない場合に限定されます。

解決策

より効率的にランダムな行を取得するには、いくつかの方法があります。

• IDの範囲でランダムに選択する: テーブルの最大IDと最小IDを取得し、その範囲内のランダムなIDをアプリケーションで生成します。そして、WHERE id >= (ランダムなID) で検索し、最初の1行を取得します。ただし、IDに欠番があると、ランダム性に偏りが生じます。
• オフセットでランダムに選択する: テーブルの総行数を取得し、0 から (総行数 - 1) までのランダムなオフセットを生成します。そして LIMIT 1 OFFSET (ランダムなオフセット) を使って1行を取得します。ただし、OFFSET が大きいとパフォーマンスが低下する可能性があります。
• 2回のクエリに分ける: 上記の2つの方法を組み合わせ、IDの欠番問題を解決します。まずランダムなオフセットを取得し、そのオフセットにある行のIDを取得するクエリを実行し、次にそのIDで目的の行を取得します。
• データベース固有の機能を使う: TABLESAMPLE (PostgreSQL, SQL Server) のような、テーブルから近似的なランダムサンプリングを行うための、より効率的な機能が提供されている場合もあります。

こういう要望に出会ったことがありませんでした。今なら TABLESAMPLE を使うのが良いようですね。後はUUIDを使っている場合はドはuuidをラムダムに作成してWHEREする方法もあるようです。

SELECT id
FROM your_table
OFFSET floor(random() * (SELECT count(*) FROM your_table))
LIMIT 1;

17章：プアマンズ・サーチエンジン(貧者の検索エンジン)

LIKE 演算子のワイルドカード（%）を行の先頭に使ったパターンマッチングで、全文検索のような機能を実現しようとするアンチパターン。

目的：キーワード検索を行う

ブログ記事の本文など、長いテキストの中から特定のキーワードを含む行を検索したい。

アンチパターン

SELECT * FROM Comments WHERE comment_text LIKE '%searchword%';
このクエリの問題点は、インデックスが全く効かないことです。

• B-Treeインデックスは、文字列の先頭から順にソートされています。LIKE 検索のパターンがワイルドカードで始まっていると、インデックスのどこから検索を開始すればよいか分からず、結果としてテーブルのすべての行をスキャンするフルテーブルスキャンが発生します。

アンチパターンの見つけ方

• LIKE を使った検索で、パターンの先頭に % や _ が使われている。
• テキスト検索クエリが非常に遅い。

アンチパターンを用いても良い場合

検索対象のデータ量が非常に少なく、フルテーブルスキャンが問題にならない場合。しかし、これは一時的な解決策にしかなりません。

解決策

適切な全文検索技術を導入します。

• データベースの全文検索機能: 多くのデータベースシステム（PostgreSQL, MySQL, SQL Serverなど）は、LIKE よりもはるかに高機能で高性能な全文検索機能（転置インデックスなど）を組み込みで提供しています。これらを使うことで、自然言語の検索（ステミング、ストップワードなど）も可能になります。
• 専用の検索エンジン: より高度な検索要件（関連度スコアリング、ファセット検索、サジェスト機能など）が必要な場合は、Elasticsearch や OpenSearch (Solr) のような、専用の検索エンジンを導入することを検討します。これらは、データベースと連携し、高速でスケーラブルな検索機能を提供します。

18章：スパゲッティクエリ

一つのクエリで多くのことをやろうとしすぎて、過度に複雑で巨大なSQL文を作成してしまうアンチパターン。

目的：一度に多くの結果を取得する

アプリケーションが必要とするデータを、データベースへの1回のラウンドトリップでまとめて取得しようとします。

アンチパターン

多数の JOIN、サブクエリ、UNION、CASE 文などを駆使して、一つの巨大なSQLクエリを作成します。開発者は、ネットワークのオーバーヘッドを減らすことが常に最善だと信じているかもしれません。

しかし、このようなクエリは多くの問題を引き起こします。

• 可読性とメンテナンス性の低下: クエリが何をしようとしているのかを理解するのが非常に困難になり、デバッグや変更がほぼ不可能になります。
• パフォーマンスの低下: クエリオプティマイザは、複雑すぎるクエリに対して最適な実行計画を見つけられないことがあります。結果として、複数の単純なクエリを実行するよりも、はるかにパフォーマンスが低下することがあります。
• 再利用性の欠如: クエリが特定のユースケースに特化しすぎているため、他の場所で再利用することができません。

アンチパターンの見つけ方

• SQLクエリが画面に収まらないほど長い。
• WITH 句やサブクエリが何層にもネストしている。
• クエリの作成者本人でさえ、そのクエリが何をしているのかを説明できない。

アンチパターンを用いても良い場合

ありません。クエリは常に、人間が理解できるようにシンプルに保つべきです。

解決策

一つのクエリには一つのタスクという原則に従います。

• 複雑な処理は、複数の単純なクエリに分割します。
• データベースへのラウンドトリップが懸念される場合は、まず本当にそれがボトルネックになっているかを測定します。多くの場合、複雑なクエリを解析・実行するコストの方が、複数回のラウンドトリップのコストよりも大きくなります。
• ストアドプロシージャを使って、一連のSQL文をデータベース側で実行することもできますが、ビジネスロジックをデータベースに閉じ込めることによるデメリットも考慮する必要があります（25章を参照）。
• 重要なのは、バランスです。闇雲にクエリを分割するのではなく、論理的にまとまりのある単位で、可読性とパフォーマンスのバランスが取れたクエリを作成することを心がけます。

19章：インプリシットカラム(暗黙の列)

SELECT 文で、取得する列を明示的に指定せずに、アスタリスク（*）ワイルドカードを使ってしまうアンチパターン。INSERT 文で列リストを省略するのも同様です。

目的：テーブルのすべての列を取得する

開発中は、すべての列を素早く取得できるため便利に感じられます。

アンチパターン

SELECT * FROM Bugs;
INSERT INTO Bugs VALUES (123, '...', ...);

この方法は、一見するとタイプ量を減らせて効率的に見えますが、アプリケーションを脆弱にします。

• 予期せぬ列の取得: SELECT * を使っていると、後からテーブルに列が追加された場合（例：ALTER TABLE）、アプリケーションが予期せずその新しい列も取得してしまいます。これにより、アプリケーションがクラッシュしたり、意図しない振る舞いをしたりする可能性があります。
• 列の順序への依存: INSERT 文で列リストを省略すると、VALUES 句の値の順序が、CREATE TABLE で定義された列の順序に依存してしまいます。後からテーブルの列の順序が変更されると、この INSERT 文はエラーになるか、最悪の場合、間違った列にデータが挿入されてしまいます。
• パフォーマンスの低下: 実際に必要なのは数個の列だけなのに、SELECT * を使うと、テキストやBLOBなどの大きなデータを含むすべての列を取得してしまい、ネットワーク帯域やメモリを無駄に消費します。
• カバリングインデックスの妨げ: SELECT * は、クエリをインデックスだけで完結させるカバリングインデックスの利用を妨げます。

アンチパターンの見つけ方

• アプリケーションのコードに SELECT * が使われている。
• INSERT 文に列リストが指定されていない。

アンチパターンを用いても良い場合

• EXISTS 句の中など、行の存在チェックのみが目的の場合。
• アドホックなクエリ（対話的に実行する使い捨てのクエリ）の場合。
• アプリケーションのコードでは、決して使うべきではありません。

解決策

SELECT 文と INSERT 文では、必ず列を明示的に指定します。

• SELECT bug_id, summary FROM Bugs;
• INSERT INTO Bugs (bug_id, summary) VALUES (123, '...');

これにより、アプリケーションはテーブル構造の変更に対して堅牢になり、パフォーマンスも向上し、コードの意図も明確になります。

アプリケーション開発のアンチパターン

20章：リーダブルパスワード

ユーザーのパスワードを、平文（暗号化されていない状態）や、可逆な暗号化を施しただけでデータベースに格納してしまうアンチパターン。

目的：ユーザーを認証する

ユーザーが入力したパスワードが、登録されているパスワードと一致するかどうかを確認します。

アンチパターン

パスワードをそのまま VARCHAR 型の列に格納します。あるいは、簡単に元に戻せる暗号化（例：Base64エンコーディングや、鍵がコードにハードコーディングされた単純な暗号化）を施して格納します。

これは、セキュリティにおける最も重大な過ちの一つです。データベースの内容が漏洩した場合、攻撃者はすべてのユーザーのパスワードを直接知ることができてしまいます。

• アカウントの乗っ取り: 攻撃者は、漏洩したパスワードを使ってユーザーのアカウントに不正にログインできます。
• パスワードリスト攻撃: 多くのユーザーは複数のサービスで同じパスワードを使い回しています。攻撃者は、漏洩したパスワードのリストを使って、他のサービスへの不正ログインを試みます。

アンチパターンの見つけ方

• Passwords テーブルに、人間が読める文字列が格納されている。
• アプリケーションに「パスワードを忘れた場合」の機能があり、ユーザーに現在のパスワードをメールで送信している（これはパスワードが復元可能な形で保存されている証拠）。

アンチパターンを用いても良い場合

ありません。絶対にやってはいけません。

解決策

パスワードは、常に強力なハッシュ関数を使ってハッシュ化してから格納します。

1. ソルトの生成: パスワードごとに、ランダムでユニークな文字列（ソルト）を生成します。これにより、同じパスワードでもユーザーごとに異なるハッシュ値が生成され、レインボーテーブル攻撃を防ぎます。
2. ハッシュ化: パスワードとソルトを連結し、Bcrypt, Scrypt, Argon2 のような、意図的に計算コストが高く設計された「適応的な」ハッシュ関数を使ってハッシュ値を計算します。MD5やSHA-1のような高速なハッシュ関数は、現代のハードウェアでは簡単に破られてしまうため、パスワードのハッシュ化には絶対に使ってはいけません。
3. 格納: 生成されたハッシュ値と、使用したソルトの両方をデータベースに格納します。
4. 検証: ユーザーがログインする際は、入力されたパスワードと、データベースに保存されているそのユーザーのソルトを使って、再度ハッシュ値を計算します。その結果が、データベースに保存されているハッシュ値と一致すれば、認証成功です。

21章：SQLインジェクション

ユーザーからの入力を適切に処理（エスケープやプレースホルダ化）せずに、動的にSQLクエリを組み立ててしまう、最も有名で危険なアンチパターン。

目的：ユーザー入力に基づいてクエリを実行する

ユーザーが指定したIDで製品を検索するなど、ユーザーからの入力値を使って動的にSQL文を生成します。

アンチパターン

ユーザーが入力した文字列を、単純な文字列連結でSQLクエリに埋め込みます。
$sql = "SELECT * FROM Products WHERE product_id = " . $_GET['id'];

もしユーザーが 123 OR 1=1 のような文字列を入力すると、最終的なSQL文は以下のようになります。
SELECT * FROM Products WHERE product_id = 123 OR 1=1
OR 1=1 は常に真なので、このクエリはテーブルのすべての行を返してしまい、意図しない情報漏洩に繋がります。攻撃者は、UNION を使って他のテーブルの情報を盗んだり、DROP TABLE でデータベースを破壊したりすることさえ可能です。

アンチパターンの見つけ方

• アプリケーションのコードで、SQLクエリが文字列連結で組み立てられている。
• WebアプリケーションのURLに ?id=1' のようにシングルクォートを追加すると、SQLエラーが表示される。

アンチパターンを用いても良い場合

ありません。SQLインジェクションは、アプリケーションが持ちうる最も深刻な脆弱性の一つです。

解決策

プリペアドステートメント（Prepared Statements）とパラメータ束縛（Parameter Binding）を常に使用します。

1. 準備 (Prepare): ユーザー入力が入る部分をプレースホルダ（? や :name など）にしたSQL文のテンプレートを、先にデータベースに送信して解析・コンパイルさせます。
   $stmt = $pdo->prepare("SELECT * FROM Products WHERE product_id = ?");
2. 束縛 (Bind): ユーザーからの入力値を、プレースホルダに「値」として束縛（バインド）します。
   $stmt->bindParam(1, $_GET['id']);
3. 実行 (Execute): クエリを実行します。
   $stmt->execute();

この方法では、ユーザー入力は常に単なる「値」として扱われ、SQLの構文の一部として解釈されることはありません。これにより、SQLインジェクション攻撃を根本的に防ぐことができます。

22章：シュードキー・ニートフリーク(疑似キー潔癖症)

代理キー（サロゲートキー）の欠番を埋めようとしたり、キーの値に意味を持たせようとしたりするアンチパターン。

目的：代理キーの値を管理する

自動インクリメントされる主キーの値が、何らかの理由で連番でなくなった（例：行の削除、トランザクションのロールバック）場合に、それを「修正」しようとします。

アンチパターン

• 欠番を埋める: 削除された行のIDを再利用しようとする。
• 連番にリセットする: テーブルのすべての行のIDを、1から始まる連番に振り直そうとする。

これらの操作は、多くの問題を引き起こします。

• 外部キー制約の破壊: IDを振り直すと、他のテーブルからそのIDを参照している外部キーとの整合性が壊れてしまいます。
• データの喪失: IDの再利用は、誤ったデータを上書き・削除する原因になります。
• 意味のない作業: 代理キーの値自体には、何の意味もありません。それは単なるユニークな識別子であり、連番である必要は全くありません。

アンチパターンの見つけ方

• データベースのIDを振り直すための管理用スクリプトが存在する。
• 開発者が「IDが歯抜けになっているのが気持ち悪い」と言う。

アンチパターンを用いても良い場合

ありません。代理キーの値は、データベースに任せて、決して手動で操作するべきではありません。

解決策

代理キーは、単なる不透明な識別子であると受け入れます。

• 代理キーの値が連番であることや、特定の順序を持つことに依存するようなロジックを書いてはいけません。
• 行が作成された順序が必要な場合は、created_at のようなタイムスタンプ列を別途用意し、それを使ってソートします。
• 請求書番号のように、ビジネス上、連番であることが求められる値は、主キーとは別の列として管理します。その場合でも、アプリケーション側でシーケンスを管理し、トランザクションのロックなどを利用して一意性を保証する必要があります。

23章：シー・ノー・エビル（臭いものに蓋）

データベース操作で発生したエラーを無視したり、適切に処理しなかったりするアンチパターン。

目的：アプリケーションをクラッシュさせない

データベースのエラーが発生しても、プログラムの実行を継続させようとします。

アンチパターン

• try...catch ブロックでエラーを捕捉するが、catch ブロックの中が空っぽ。
• エラーが発生したかどうかを示す戻り値を確認しない。
• エラーメッセージをログに出力するだけで、ユーザーには何も通知せず、トランザクションもロールバックしない。

エラーを無視すると、アプリケーションは予期せぬ状態に陥ります。

• データの不整合: トランザクションの一部だけが成功し、残りが失敗した状態でコミットされてしまう可能性があります。
• サイレントな失敗: 問題が発生しているにもかかわらず、ユーザーや開発者はそれに気づかず、後になってより深刻な問題を引き起こします。
• デバッグの困難: エラーの原因を特定するための情報が何も残っていません。

アンチパターンの見つけ方

• コード内に空の catch ブロックがある。
• データベース操作関数の戻り値がチェックされていない。
• エラーが発生しても、アプリケーションが何事もなかったかのように動作を続ける。

アンチパターンを用いても良い場合

ありません。すべてのエラーは、適切に処理されるべきです。

解決策

データベース操作は常にエラーが発生する可能性があるものとして扱います。

1. エラーを検知する: 使用しているデータベースAPI（PDO, JDBCなど）が、どのようにエラーを通知するかを理解します。多くの場合、例外をスローするように設定するのが最善です。
2. エラーを処理する: try...catch ブロックを使ってエラーを捕捉します。
3. トランザクションをロールバックする: エラーが発生したら、進行中のトランザクションは必ずロールバックし、データベースを操作開始前の状態に戻します。
4. ユーザーにフィードバックする: 「エラーが発生しました。しばらくしてからもう一度お試しください」のような、適切なメッセージをユーザーに表示します。機密情報を含む可能性のある詳細なエラーメッセージを、決してユーザーに見せてはいけません。
5. ログを記録する: 開発者がデバッグできるように、エラーの詳細（スタックトレースなど）をサーバーのログファイルに記録します。

24章：ディプロマティック・イミュニティ(外交特権)

SQLは単なるデータアクセス言語だと軽視し、コードレビューやバージョン管理、テストといった、他のコードと同じ品質管理のプラクティスを適用しないアンチパターン。

目的：迅速な開発

SQLは「本物のコード」ではないと考え、厳格な開発プロセスを省略して素早く変更を加えようとします。

アンチパターン

• SQLクエリが、アプリケーションのソースコードの中に文字列リテラルとして埋め込まれている。
• データベースのスキーマ変更（CREATE TABLE, ALTER TABLEなど）が、バージョン管理されていない .sql ファイルや、開発者の手作業によって場当たり的に行われる。
• SQLコードに対する単体テストや統合テストが存在しない。

SQLコードもアプリケーションの重要な一部です。それを他のコードと区別して扱う（外交特権を与える）と、多くの問題が発生します。

• 品質の低下: レビューされていないコードは、バグやパフォーマンス問題、セキュリティ脆弱性を含みやすくなります。
• 再現性の欠如: ある開発者の環境では動くが、別の環境では動かない、といった問題が発生します。
• デプロイの失敗: 本番環境へのデプロイ時に、スキーマの不整合やクエリのエラーが発生します。

アンチパターンの見つけ方

• バージョン管理システムに .sql ファイルが含まれていない。
• 開発者が「DBAに頼んで、本番DBに直接インデックスを追加してもらった」と言う。

アンチパターンを用いても良い場合

ありません。SQLも第一級のコードとして扱うべきです。

解決策

SQLコードに、アプリケーションコードと同じ開発プラクティスを適用します。

• バージョン管理: すべてのSQLコード（スキーマ定義、クエリ、ストアドプロシージャなど）をGitなどのバージョン管理システムで管理します。
• コードレビュー: 他のコードと同様に、SQLコードもチームメンバーによるレビューを受けます。
• テスト: SQLクエリに対する単体テストや、データベースと連携した統合テストを作成します。
• データベースマイグレーションツール: FlywayやLiquibaseのようなツールを導入し、スキーマの変更を体系的、かつバージョン管理された形で適用できるようにします。これにより、どの環境でも同じスキーマバージョンを再現できるようになります。

25章：スタンダード・オペレーティング・プロシージャ(錆びついた開発標準)

「これまでずっとこのやり方でやってきたから」という理由だけで、時代遅れになったり、現在のプロジェクトには不適切だったりするプラクティスを使い続けてしまうアンチパターン。

目的：標準化による一貫性の確保

チーム内で開発標準を設けること自体は、コードの一貫性を保ち、予測可能性を高める上で良いことです。

アンチパターン

問題は、その標準が形骸化し、なぜその標準が作られたのかという理由が忘れ去られ、思考停止で適用されるようになってしまうことです。

• 技術の進化への不追従: かつては最善だったプラクティスが、新しい技術（例：ORM、データベースの新しい機能）の登場によって、もはや最適ではなくなっているかもしれません。
• コンテキストの無視: あるプロジェクトで有効だったプラクティスが、別の異なる要件を持つプロジェクトにもそのまま適用されてしまう。例えば、すべてのビジネスロジックをストアドプロシージャで実装するというルールは、特定の種類のアプリケーションでは有効かもしれませんが、一般的なWebアプリケーションでは、テストのしにくさや、バージョン管理の難しさ、特定のデータベースベンダーへのロックインといった多くのデメリットをもたらします。

アンチパターンの見つけ方

• 開発者が、特定のプラクティスを採用している理由を「それがルールだから」としか説明できない。
• チームが、新しい技術やアプローチの採用に抵抗する。
• 開発標準のドキュメントが、何年も更新されていない。

アンチパターンを用いても良い場合

ありません。標準は、定期的に見直され、その妥当性が再評価されるべきです。

解決策

開発標準を「生きたドキュメント」として扱います。

• 理由を文書化する: なぜその標準が選択されたのか、どのような問題を解決しようとしているのか、どのようなトレードオフがあるのかを明確に文書化します。
• 定期的に見直す: プロジェクトの節目や、新しい技術が登場した際に、現在の標準がまだ有効かどうかをチームで議論し、見直します。
• 柔軟性を持つ: 標準は絶対的な法律ではありません。特定の状況において、標準から逸脱する正当な理由がある場合は、それを許容し、その決定を記録します。
• チームのオーナーシップを育む: 開発標準は、一部のアーキテクトがトップダウンで決めるものではなく、チーム全員が議論に参加し、その策定と改善に責任を持つべきです。

ボーナス

26章：標準SQLにおける外部キーの誤った使い方

この章では、外部キー制約を定義する際に陥りがちな、微妙で気づきにくい間違いについて解説します。特に、MATCH句の振る舞いは重要です。

複合外部キーとNULL

複数の列からなる複合外部キーを定義した場合、その一部の列がNULLである行の扱いが問題になります。

標準SQLでは、外部キーの照合戦略としてMATCH句を定義できます。

• MATCH SIMPLE (デフォルト): 複合外部キーの列のいずれかがNULLの場合、その行は制約のチェック対象外となります。つまり、他の列が親テーブルに存在しない値であっても、NULLを含む行は挿入・更新できてしまいます。これは多くの場合、開発者が意図しない動作であり、データの不整合を招く可能性があります。
• MATCH FULL: 複合外部キーの列がすべてNULLであるか、すべてNULLでないかのどちらかであることが要求されます。すべてがNULLでない場合は、その組み合わせが親テーブルに存在しなければなりません。一部の列だけがNULLである行は許可されません。これにより、より厳密なデータ整合性が保証されます。
• MATCH PARTIAL: 一部の列がNULLであることを許可しますが、NULLでない列の組み合わせは、親テーブルのいずれかの行の対応する列と一致しなければなりません。このオプションは実装が複雑であるため、ほとんどのデータベース製品ではサポートされていません。

アンチパターン

• 複合外部キーを持つテーブルで、MATCH句を指定せずにデフォルトのMATCH SIMPLEの振る舞いに依存してしまうこと。
• これにより、外部キーの一部がNULLで、残りの部分が無効な値を持つ「半端な」参照が作られてしまい、データの整合性が損なわれる。

解決策

• 複合外部キーを定義する際は、その列がNULLを取りうるかどうかを慎重に検討します。
• もし、キーの構成要素が部分的にNULLになることを許容したくない場合は、MATCH FULLを指定することを検討します。
• あるいは、すべての外部キー列にNOT NULL制約を課すことで、MATCH SIMPLEの曖昧な振る舞いを回避し、MATCH FULLと似た効果を得ることもできます。

多くの開発者はMATCH句の存在自体を知らないことが多く、デフォルトの挙動によって意図しないデータ不整合を引き起こしているケースがあります。複合キーを使う際は、この点を意識することが重要です。

geminiが言っているように私は MATCH 句を知りませんでした！ MATCH FULL を使えば 一部NULL を含む複合外部キーを引いてしまうことはなくなるんですね。

27章：MySQLにおける外部キーの誤った使い方

この章では、特にMySQLのストレージエンジンに関連した、外部キーのよくある誤解や誤用について解説します。

ストレージエンジンと外部キー

MySQLには、InnoDやMyISAといった複数のストレージエンジンが存在します。重要なのは、外部キー制約をサポートしているのはInnoDBだけであるという点です。（MySQL 8.0時点）

アンチパターン

• 外部キーをサポートしないストレージエンジン（例: MyISAM）を使っているテーブルに対して、FOREIGN KEY句を含むCREATE TABLE文を実行してしまうこと。MySQLは、この場合にエラーを返しません。CREATE TABLE文は成功しますが、外部キー制約は黙って無視されます。テーブルは作成されますが、参照整合性は一切働きません。開発者は外部キーが機能していると思い込んでいるため、データの不整合が発生しても気づきにくい、という非常に厄介な状況に陥ります。

• データ型や照合順序の不一致。 InnoDBであっても、外部キーとして参照する列と、参照される親テーブルの主キー列とで、データ型、長さ、および照合順序（Collation）が完全に一致していなければ、外部キー制約は正しく作成されません。これもエラーにならず、単に制約が無視されることがあるため注意が必要です。特に、CHARSETやCOLLATEがテーブルのデフォルト設定に依存している場合に、意図しない不一致が生まれがちです。

アンチパターンの見つけ方

• SHOW CREATE TABLE <table_name>; を実行した結果、FOREIGN KEY句が定義されているはずなのに、表示されない。
• 親テーブルに存在しない値を子テーブルに挿入できたり、親テーブルの行を削除しても子テーブルの行が残ってしまったりする。
• アプリケーションが、本来であれば外部キー違反で失敗するはずの操作を、エラーなく完了してしまう。

解決策

• テーブルのストレージエンジンを必ず確認し、InnoDBを使用する事を徹底します。
• CREATE TABLE文を実行した後は、必ずSHOW CREATE TABLEを実行して、意図した通りに外部キー制約が作成されているかを確認します。
• 外部キーに関連する列は、データ型、長さ、符号（UNSIGNEDなど）、文字セット、照合順序が完全に一致するように明示的に定義します。

MySQLの「エラーを返さずに黙って無視する」という振る舞いは、非常に危険です。スキーマを定義する際は、常に確認作業を怠らないことが重要です。

MySQLは使ったことがなくて説明に関しては全く理解できませんでした 💦

付録：正規化のルール

t_wada さん曰く、

正規形についてちゃんと理解しようとするととても大変。そこについてとてもわかり易く、いい具合の正規形についての紹介がある。また、これまで紹介してきたアンチパターンを例に正規形の例を提示していてわかりやすい。
とのことです。ココはぜひ読みたいです。

ミニアンチパターン

上記のパアンチパターンのそれぞれに具体例としてミニアンチパターンの説明がされているそうです。
楽しみです。ここは予想が難しそうなので題名を眺めるだけにしておきます。

• 2.6 CSV列を複数の行に分割する
• 3.6「私のコンピューターでは動作しているのに」
• 4.6 BIGINTは十分に大きい？
• 8.6 価格の保存
• 11.6 予約語
• 13.6 すべての列にインデックスを作成する
• 14.6 NOT IN (NULL)
• 15.6 ポータブルSQL
• 16.6 クエリでランダムに複数行を取得する
• 20.6 ハッシュ文字列をVARCHAR型で格納する
• 21.6 引用符内のクエリパラメータ
• 22.6 グループごとの自動インクリメント
• 23.6 構文エラーメッセージ解読の進め
• 24.6 名前の変更
• 25.6 MySQLのストアドプロシージャ

減った章：

マジックビーンズ(ActiveRecordパターン)とは

書籍の初版には存在し、第2版で削除された章が「マジックビーンズ」です。ActiveRecordデザインパターンをアンチパターンとして紹介するものでした。

ActiveRecordパターンとは？

ActiveRecordは、多くのO/Rマッピング（ORM）フレームワーク（特にRuby on Railsのものが有名）で採用されているデザインパターンです。

• オブジェクトとテーブル行の対応: クラスがデータベースのテーブルに、オブジェクトのインスタンスがテーブルの1行に対応します。
• 永続化ロジックの保持: オブジェクト自身が、自身のデータをデータベースに保存（save）、更新（update）、削除（delete）するためのメソッドを持ちます。

# 例: ActiveRecordパターン
class User < ActiveRecord::Base
end

# 新しいユーザーを作成して保存
user = User.new(name: "Bill Karwin")
user.save

# ユーザーを検索して更新
user = User.find_by(name: "Bill Karwin")
user.update(name: "t-wada")

このように、SQLを直接書かなくても直感的にデータベース操作ができるため、開発効率を劇的に向上させます。

なぜアンチパターンと見なされたのか？

その強力さの一方で、ActiveRecordはいくつかの設計上の原則に反する側面を持っています。

• 単一責任の原則への違反: 本来、ビジネスロジックを表現するべきドメインオブジェクトが、永続化という全く異なる関心事まで責務として持ってしまっています。
• 密結合: ドメインモデルがデータベースのスキーマ構造と密接に結合してしまいます。データベースの列を追加・変更すると、対応するクラスの属性も変更する必要があり、柔軟性が損なわれます。
• テストの困難性: ビジネスロジックをテストしたいだけなのに、オブジェクトがデータベースへの依存を内包しているため、データベース接続なしでの単体テストが難しくなります。
• パフォーマンス問題の隠蔽（N+1問題）: ループ内で関連オブジェクトにアクセスすると、開発者が意図しない大量のSQLが発行される「N+1問題」が発生しやすくなります。ORMがSQLを隠蔽するため、問題に気づきにくいのです。

なぜ第2版で章が削除されたのか？

著者のBill Karwinは、この章を削除した理由を「ActiveRecordが必ずしもアンチパターンであるとは言えなくなったから」と説明しています。

• パターンの成熟: 現代のActiveRecord実装は非常に洗練されており、Eager Loading（includesなど）によるN+1問題への対策や、関心事を分離するための仕組みが提供されています。
• トレードオフの認識: ActiveRecordは、その設計上のデメリットと引き換えに、絶大な生産性向上というメリットを提供します。そのトレードオフを理解した上で使うのであれば、それはアンチパターンではなく、有効な選択肢の一つです。
• Data Mapperパターンの台頭: ActiveRecordとは対照的に、ドメインオブジェクトと永続化ロジックを完全に分離するData Mapperというパターンも広く使われるようになり、開発者はプロジェクトの要件に応じて適切なパターンを選択できるようになりました。

結論として、ActiveRecordは「銀の弾丸」ではありません。その魔法のような便利さの裏にある仕組みと代償を理解し、パフォーマンスや設計上の問題を意識しながら使うことが、現代の開発者には求められています。思考停止で使うことは、依然としてアンチパターンと言えるでしょう。

砂の城

データベースを使ったサービスを運用するための準備・心構えの解説。
出版元との契約の関係で第2版から削除されました。しかし原本が公開されています。
データベースのシステムを考える時に読むとしっかりとした設計になりそうです。

さいごに

t_wada さんの講演をベースに SQLアンチパターンの第2版の内容をgeminiに相談しながら想像して書いてみました。

あくまで想像です！本来の内容は書籍を購入してよんでみてください！
私も本を入手次第想像と比較しながら読んでみたいと思います。

参考書籍