読書メモ：SQL アンチパターン

November 16, 2022

以下の本を読んだ。記憶しておきたいところについてメモを残しておく。

• SQL アンチパターン
• https://www.oreilly.co.jp/books/9784873115894/

各章の構成 #

各章では、以下の構成でアンチパターンを解説していきます。

1. 目的
   • 解決すべきタスクです。アンチパターンとは、タスクの解決のために用いられたものの、逆に多くの問題を生じさせてしまうものなのです。
2. アンチパターン
   • アンチパターンそのものを解説します。タスクの解決策として用いられる方法が、その性質によって予期せぬ結果を招き、アンチパターンになってしまう経緯を説明します。
3. アンチパターンの見つけ方
   • プロジェクトでアンチパターンが使われているとき、それを察知するための手がかりがあります。直面している問題の種類や、メンバー間の会話での何気ない言葉が、そこにアンチパターンがあるかもしれないことに気づくヒントになります。
4. アンチパターンを用いてもよい場合
   • ルールには例外がつきものです。通常はアンチパターンであると思われるアプローチでも、状況によってはそれが適切である場合や、打てる手のなかでは一番ましなもの、という場合があります。
5. 解決策
   • アンチパターンに陥らないようにして、冒頭のタスクを解決するための望ましい方法を紹介します。

1 部：データベース論理設計のアンチパターン #

コードを書き始める前に、データベースに格納する情報を見極め、データの構成や関連付けを最善の方法で実現するための設計を行う必要があります。この作業には、データベースのテーブルや列、関連（リレーションシップ）の設計などがあります。

1 章：ジェイウォーク（信号無視） #

開発者はよく、「多対多」の関連を表現する交差テーブルの作成を避けるために、カンマ区切りのリストを使います。私はこのアンチパターンをジェイウォーク（信号無視）と名づけました。どちらも、“intersection”（交差点／交差テーブル）を避けようとする行為だからです。

• 目的：複数の値を持つ属性を格納する
• アンチパターン：カンマ区切りフォーマットのリストを格納する
• 解決策：交差テーブルを作成する

「ひとつひとつの値は個別の行と列に格納しましょう。」

2 章：ナイーブツリー（素朴な木） #

組織図やスレッド形式のコメント欄（掲示板）のようなツリー構造のデータを格納するための解決策として多くの書籍や記事で推奨されているのは、親の ID 列を加えるという単純な方法です。しかし、この方法は思慮が浅い、素朴（ナイーブ）な解決策であるとも言えるでしょう。

• 目的：階層構造を格納し、クエリを実行する
• アンチパターン：常に親のみに依存する
• 解決策：代替ツリーモデルを使用する

階層型データを格納するための設計として隣接リストの代替となるものには、経路列挙モデル（Path Enumeration）、入れ子集合モデル（Nested Set）、閉包テーブルモデル（Closure Table）などがあります。

どの設計を使うべきか

• 隣接クエリ
  • 従来最も一般的に用いられてきた設計で、ソフトウェア開発者の多くが知っている方法です。
• 再帰クエリ
  • WITH や CONNECT BY PRIOR 構文をサポートするデータベース製品では、隣接リスト設計であっても計算効率を高められます。
• 経路列挙
  • 「パンくず」ユーザーインターフェースとして効果的ですが、参照生合成が保証できず、冗長な情報を格納するために、脆弱な面を持っています。
• 入れ子集合
  • 巧妙な設計手法ですが、巧妙すぎるとも言えます。経路列挙と同じく、参照整合性を保証できません。ツリーの修正よりも、ツリーの検索の必要が多い場合に適しています。
• 閉包テーブル
  • この章で紹介した設計のなかで、最も用途が幅広く、また唯一、ノードが複数のツリーへ所属することができます。関連付けを格納するために、別個のテーブルが必要です。また、階層が深くなると多くの行数が必要になり、計算が楽になる分、スペースが消費されるというトレードオフが生じます。

「階層構造はエントリと関連（リレーションシップ）から成り立ちます。これを念頭に、行う作業に合わせて最適な設計手法を選択しましょう。」

3 章：ID リクワイアド（とりあえず ID） #

コラム：主キーが本当に必要か

「このテーブルには主キーは不要だ」という言葉を、何人かのソフトウェア開発者から聞いたことがあります。彼らは一意のインデックスでの管理を面倒だと考えていたり、主キーとして使える列がないテーブルを扱っていたりするようです。主キー制約が重要になるのは、次のような必要があるときです。

• 行の重複を避けたい
• クエリで個別の行を参照したい
• 外部キー参照をサポートしたい

主キー制約を使わないと、例えば以下のように重複行をチェックするという、煩わしい手間が生じます。

SELECT bug_id FROM Bugs GROUP BY bug_id HAVING COUNT (*) > 1;

主キー制約を設定することを避けたばかりに、この先このようなチェックを何度実行することになるのでしょうか。もし重複してしまったときはどのように対処できるでしょうか。主キーのないテーブルは、大量の MP3 の音楽ファイルを、曲タイトルなしで管理するようなものです。音楽は聴けますが、聴きたい曲を特定したり、同じ曲が重複しないようにすることはできません。

• 目的：主キーの規約を確立する
• アンチパターン：すべてのテーブルに「id」列を用いる
• 解決策：状況に応じて適切に調整する

主キーは制約であり、データ型ではありません。データ型がインデックスをサポートしている限り、単一の列や複数の列を主キーとして宣言できます。また、自動インクリメントする整数型の列を、主キーではない列としても定義できます。この 2 つは独立した概念です。

わかりやすい列名にしよう

主キーには、わかりやすい名前をつけましょう。主キーの名前は、主キーが識別する対象のエンティティを表すものにすべきです。例えば、Bugs テーブルの主キーの名前は bug_id が相応しいでしょう。可能ならば、外部キーの列名にも同じような命名規則を用いましょう。これはつまり、主キー名はスキーマ内で一意となるべきであることを意味します。片方の主キーがもう片方の主キーを参照する外部キーではない限り、複数テーブルで主キーに同じ名前を使うべきではありません。

-- 単に id と命名されていた場合
SELECT b.id, a.id
FROM Bugs b
INNER JOIN Accounts a ON b.assigned_to = a.id
WHERE b.status = 'OPEN'

-- 一意な ID 名がつけられている場合
SELECT b.bug_id, a.account_id
FROM Bugs b
INNER JOIN Accounts a ON b.assigned_to = a.account_id
WHERE b.status = 'OPEN'

規約に縛られない

ORM フレームワークの多くは、「id」という名前の擬似キーが使われることを規約としています。しかし、この規約を上書きして、別の名前を宣言することも可能です。

自然キーと複合キーの活用

一位であることが保証できて、NULL を許容しない、行の識別に使える属性がテーブルに含まれているのであれば、単に習慣に従うという理由のために、擬似キー（＝代理キー、サロゲートキー）を追加する義務を感じる必要はありません。

複合キーは、適切な場合に用いるようにしましょう。例えば交差テーブルのように、行を識別するために最適な方法が複数の属性値の組み合わせである場合に、複合主キーを使うとよいでしょう。

「規約は、役立つと思える場合のみ従いましょう。」

4 章：キーレスエントリ（外部キー嫌い） #

参照整合性制約を使わないように進める考え方もあります。外部キーを使用しない主な理由としては、以下のものが挙げられます。おそらく、あなたも見聞きしたことがあるのではないでしょうか。

• データの更新が、参照整合性制約と衝突してしまう。
• データベースが外部キーのために作成するインデックスが、パフォーマンスに影響すると考えている。

外部キー制約を省略すれば、データベース設計がシンプルになり、柔軟性が高まり、実行速度が速くなると思っている読者もいるかもしれません。しかし、そこには代償があり、代償は別の形で支払わなくてはならないのです。つまり、開発者であるあなたに、参照生合成を保証するためのコードを書く責任が生じるのです。

• 目的：データベースのアーキテクチャを単純化する
• アンチパターン：外部キー制約を使用しない
• 解決策：外部キー制約を宣言する

「データベースでのミスの発生を未然に防ぐために、外部キー制約を用いましょう。」

5 章：EAV（エンティティ・アトリビュート・バリュー） #

可変属性をサポートする必要があるとき、プログラマーにとって魅力的な解決策と思えるのは、もう１つ別のテーブルを作成して、属性を「行」に格納することです。属性テーブルの各行には、3 つの列があります。

• エンティティ：通常の場合、親テーブルに対応する外部キーです。親テーブルの方ではエンティティごとに１行が割り当てられています。
• 属性：従来型のテーブルでは属性は列の名前に相当しますが、この新しい設計では属性名が各行に入っていて、行ごとに識別する必要があります。
• 値：エンティティの属性の値です。

このような設計は、エンティティ・アトリビュート・バリュー（Entity-Attribute-Value）、略して「EAV」と呼ばれます。オープンスキーマ、スキーマレス、名前／値ペア、と呼ばれることもあります。

可変属性をサポートしたいがために EAV を用いた結果、リレーショナルデータベースの多くの長所が失われてしまいます。

• 目的：可変属性をサポートする
• アンチパターン：汎用的な属性テーブル（EAV）を使用する
• 解決策：サブタイプのモデリングを行う

EAV を使わずに、EAV が扱うようなデータを格納する方法はいくつかあります。どの解決策を採用するかは、データをどのように検索したいかによるので、状況に応じて適切な設計を選択しましょう。

シングルテーブル継承

最もシンプルな設計は、すべてのタイプの属性を個別の列に格納して、関連するすべてのサブタイプを１つのテーブルに格納することです。加えて１つの属性列を、その行がどのサブタイプであるかを定義するために使います。属性によっては、全てのサブタイプに共通のものがありますが、多くの属性はサブタイプ固有のものです。対応する属性を持たないオブジェクトを格納する行には、対応する属性列に NULL を入れなくてはなりません。このため、テーブルには非 NULL の値を持つ列がバラバラに点在することになります。

名称は、マーティン・ファウラーの著書「エンタープライズアプリケーションアーキテクチャパターン」に由来しています。

具象テーブル継承

サブタイプごとにテーブルを作成するというものです。すべてのテーブルは、基底型に共通する属性と、それぞれのサブタイプに固有の属性を含んでいます。

名称は、マーティン・ファウラーの著書「エンタープライズアプリケーションアーキテクチャパターン」に由来しています。

別々のテーブルに格納されるサブタイプをまたいでデータを検索したい場合は、テーブルを UNION したビューを定義知るという手段があります。

具象テーブル継承は、すべてのサブタイプをまたいだ検索を実行する頻度が低い場合に適切です。

クラステーブル継承

テーブルをオブジェクト指向のクラスであるかのように見なして、継承を模倣するという方法です。まず、すべてのサブタイプに共通する属性を含む基底型のテーブルを１つ作ります。次に、サブタイプごとに１つずつ追加のテーブルを作成し、基底型テーブルに対する外部キーの役割を持つ主キーを設定します。

名称は、マーティン・ファウラーの著書「エンタープライズアプリケーションアーキテクチャパターン」に由来しています。

サブタイプをまたいだ検索を実行する場合は基底型のテーブルに対して実行し、その後に各サブタイプテーブルに対してクエリを実行することで、サブタイプ固有の情報を取得できます。またはサブタイプの数が少ない限り、これらをすべて JOIN するクエリを書くこともできます。

クラステーブル継承は、すべてのサブタイプに共通する列を参照するクエリが頻繁に実行される時に適切です。

半構造化データ

サブタイプの数が多い場合や、頻繁に新しい属性を追加しなければならない場合は、LOB 列（BLOB 型、CLOB 型、TEXT 型など）を追加し、XML や JSON などの形式で属性名と値を共に格納することもできます。

マーティン・ファウラーは、著書でこのパターンをシリアライズ LOB（Serialized LOB）と呼んでいます。

「メタデータは、メタデータのために用いましょう。」

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

バグデータベースに、ユーザーがバグについてのコメントを書き込める機能を追加することにします。バグによっては、多くのコメントが書き込まれることがあるかもしれません。また、どのコメントも必ず、いずれか１つのバグへ関連付かなければなりません。つまり、Bugs と Comments の間には、１対多の関連が存在しています。しかし、コメントを記入できるテーブルは、2 つあるかもしれません。例えば、Bugs と FeatureRequests です。この 2 つは類似したエンティティですが、それぞれ別テーブルに格納されています（具象テーブル継承）。コメント自体は、それがどちらのタイプ（Bugs または FeatureRequests）に属するかにかかわらず、１つのテーブル（Comments）に格納したいところです。しかし、複数の親テーブルを参照する外部キーを宣言することはできません。

• 目的：複数の親テーブルを参照する
• アンチパターン：二重目的の外部キーを使用する
• 解決策：関連（リレーションシップ）を単純化する

この問題の解決策の１つは、名前を付けられるほど有名なものです。ポリモーフィック関連（Polymorphic Associations）です。他に、プロミスキャス・アソシエーション（Promiscuous Asssociation：無差別な関連）と呼ばれることもあります。複数のテーブルを参照できるからです。

ポリモーフィック関連を機能させるには、文字列型の列を追加します。追加した列には、現在の行が参照する親テーブルの名前を格納します。

残念ながらポリモーフィック関連はアンチパターンです。

コラム：データへのメタデータの混入

ポリモーフィック関連アンチパターンには、EAV アンチパターンと似た特徴があると気付いた人もいるかもしれません。どちらのアンチパターンでも、メタデータオブジェクトの名前が文字列として格納されるのです。EAV では、属性の列名が文字列として値に格納されます。ポリモーフィック関連では、親テーブルの名前が文字列として値に格納されます。これは「データへのメタデータの混入」とでも呼ぶべきものです。このコンセプトは他のアンチパターンでも別の形で現れます。

データベースを再設計して、ポリモーフィック関連の弱点を避けつつ、必要なデータモデリング要件を満たさなければなりません。柔軟な関連付けを実現しつつも、メタデータを効果的に用いて整合性を維持する、数少ない方法を紹介します。

交差テーブルの作成

解決策の１つは、問題の本質が何かを考えるとすぐにわかります。すなわち、ポリモーフィック関連では「本来あるべき関連が、逆さまになっている」のです。

子テーブル側である Comments の外部キーでは、複数の親テーブルを参照できません。代わりに、複数の外部キーを Comments テーブルを参照するために使用しましょう。複数の親テーブルそれぞれに対応した交差テーブル（BugsComments、FeaturesComments）を作成し、各交差テーブルでは Comments への外部キーに加えて、各親テーブルへも同じく外部キーを定義します。

共通の親テーブルの作成

すべての親テーブルが継承する基底テーブルを作成することで対応できます（クラステーブル継承）。Comments テーブルに基底テーブルへの外部キーを持たせます。

「テーブル間の関連（リレーションシップ）には、参照元テーブルと参照先テーブルが常にそれぞれ１つしかないことを忘れないようにしましょう。」

7 章：マルチカラムアトリビュート（複数列属性） #

• 目的：複数の値を持つ属性を格納する
• アンチパターン：複数の列を定義する
• 解決策：従属テーブルを作成する

これは「ジェイウォーク」アンチパターンと同じテーマの問題です。すなわち、１つのテーブルに属するべきだと思える属性に複数の値がある場合、それをどう格納するかという問題です。ジェイウォークアンチパターンでは、複数の値をカンマ区切りフォーマットで連結し、１つの列に格納してしまいました。

ジェイウォークパターンで見たように、複数値をカンマ区切りで１列に格納すべきではありません。各列には、値を１つのみ格納すべきです。それならば、それぞれ１つのタグを格納する列を複数作成することが、自然な選択のように思えてきます。

CREATE TABLE Bugs (
  bug_id      PRIMARY KEY,
  description VARCHAR(1000),
  tag1        VARCHAR(20),
  tag2        VARCHAR(20),
  tag3        VARCHAR(20)
);

あるバグにタグを付けるには、３列用意したタグ列のどれか１つに値を入れます。未使用の列は NULL のままにします。しかし、この設計にも問題があります。従来の設計では簡単にできていたことが、複雑になってしまうのです。例えば、特定のタグが付けられたバグを検索しようとすると、３列すべてを取得しなければなりません。タグ文字列は３つの列のどれにでも格納される可能性があるためです。複数の列に対する値の追加や削除にも問題が生じます。どの列が空いているかを確認できないからです。一意性の保証にも問題があります。複数列に同じ値を格納したくはありませんが、それを防ぐ制約をかけることはできません。また将来的に列数が３列では足りなくなる可能性もあります。

コラム：アンチパターンに共通するパターン

ジェイウォークアンチパターンとマルチカラムアトリビュートアンチパターンには共通項があります。これら 2 つのアンチパターンは、「複数の値を持つ可能性がある属性を格納する」という同じ課題に立ち向かっているのです。先に見たジェイウォークアンチパターンの例は、多対多関連を扱うものでした。マルチカラムアトリビュートアンチパターンの例は、１対多関連に関するものです。

ジェイウォークで見たように、最善の解決策は、属性を格納する列を１つ持つ従属テーブルを作成し、属性を格納することです。属性値を複数の列ではなく、複数の行に格納するのです。従属テーブルで外部キーを定義し、親テーブルの行に値を関連づけます。

「同じ意味を持つ値は、１つの列に格納するようにしましょう。」

8 章：メタデータトリブル（メタデータ大増殖） #

営業部門では、取引が活発な顧客を知るために、年ごとに営業収益を分類する必要が生じました。年ごとのデータを格納する列を新たに追加するという解決策が採用されました。列名には、対象の年を用います。

しかし実際には、注目している一部の顧客の分だけしかデータは入力されず、ほとんどの行でこれらの年間営業就役の列は NULL のままとなりました。毎年この列を１つ追加する必要がありました。

テレビシリーズの「スタートトレック」には、トリブルと呼ばれる、全身を体毛に覆われた小さな動物が登場します。トリブルはとても可愛い動物でした。しかしすぐに、手に負えないほど繁殖力が強いことが明らかになります。次々に増加するトリブルの管理が深刻な問題になります。

コラム：メタデータへのデータの混入

テーブル名に年を追加するということは、データの値をメタデータ識別子と組み合わせるということです。このアンチパターンは EAV アンチパターンとポリモーフィック関連アンチパターンで見た、「データへのメタデータの混入」の逆です。

マルチカラムアトリビュートアンチパターンと、このメタデータトリブルアンチパターンは、データの値から列やテーブル名を作成します。つまり、「メタデータへのデータの混入」です。

• 目的：スケーラビリティを高める
• アンチパターン：テーブルや列をコピーする
• 解決策：パーティショニングと正規化を行う

テーブルサイズが巨大化した場合に、手作業でテーブルを分割せずに、パフォーマンスを改善するもっと良い方法があります。水平パーティショニング、垂直パーティショニング、従属テーブルの導入などです。

水平パーティショニングの使用

水平パーティショニングあるいはシャーディングと呼ばれる機能を用いることで、テーブル分割の決定に悩まされることなく、巨大なテーブルを分割するメリットを得られます。行を分割するいくつかのルールを定めて論理テーブルを定義すれば、あとはデータベースが必要な作業を行ってくれます。

垂直パーティショニングの使用

水平パーティショニングがテーブルを行で分割するのに対し、垂直パーティショニングは列でテーブルを分割します。列でのテーブル分割は、列の一部のサイズが大きい場合や、めったに使用されない場合にメリットがあります。

従属テーブルの導入

マルチカラムアトリビュートの解決策と同様、メタデータトリブル列の解決策は、従属テーブルの導入です。

プロジェクトごとに年別の列を持たせて強引に１行に収めるのではなく、プロジェクトと年の組み合わせごとに１行となるようにテーブルを定義しましょう。

「データにメタデータを増殖させないように気をつけましょう。」

2 部：データベース物理設計のアンチパターン #

格納するデータを決定した後は、使用する RDBMS の機能を最大限に活かして、データ管理の実装を行います。この作業には、テーブルやインデックスの定義、データ型の決定などがあります。

9 章：ラウンディングエラー（丸め誤差） #

• 目的：整数の代わりに少数値を利用する
• アンチパターン：FLOAT データ型を利用する
• 解決策：NUMERIC データ型を利用する

ほとんどのプログラミング言語には float や double と呼ばれる、実数を表すデータ型があります。SQL にも同じ名前で類似のデータ型があります。float 型を用いたプログラミングに慣れているプログラマーの多くは、少数値のデータが必要な場合には当然のように SQL の FLOAT データ型を選択します。SQL の FLOAT データ型は、他のプログラミング言語の float 型と同じように、IEEE 754 標準に従って実数を 2 進数形式でエンコードします。FLOAT 型を効果的に使用するには、その浮動小数点数の特徴を理解しておく必要があります。

多くのプログラマーは、「10 進数で記述できるすべての値を、2 進数として格納できるわけではない」という浮動小数点数の特性を、あまり意識していません。浮動小数点数における一部の数値は、やむを得ず近似値に丸めなくてはならないのです。

3 分の 1 のような有理数を 0.333… のような循環小数で表す場合、正確な値は小数では表現できません。この問題への妥協策は、可能な限りオリジナルに近い値、例えば 0.333 などの有限精度の値を使うことです。このため、意図していた値と正確には一致しなくなります。

「どうせ実際に無限の桁数を入力することはできないのだから、入力する数字には有限精度のものしかないことになる。入力できる値は有限精度として正確に格納されるので、問題はないはずだ」と考える人もいるかもしれません。しかし、残念ながらそうではありません。

IEEE754 では、浮動小数点を 2 進数形式で表現します。2 進数形式で無限精度が必要な値は、10 進数で表現される値とは異なります。10 進数では有限精度で表せる値、例えば 59.95 を 2 進数で正確に表すには、無限精度が必要です。FLOAT データ型で無限精度を扱うことはできないため、2 進数で正確に表すには近似値を使います。その近似値を 10 進数形式で表すと 59.950000762939 になります。

データベースの FLOAT 列にある値が、すべて有限精度で表せるとは保証できません。このため、アプリケーションでは、対象の列にあるすべての値は丸められている可能性を想定しなければなりません。

実質上、FLOAT、REAL、DOUBLE、PRECISION などのデータ型を用いると、「完全に正確な値」は期待できないと考えるべきです。つまり、「ラウンディングエラー（丸め誤差）」アンチパターンに陥っている可能性があります。

SQL で FLOAT データ型を使うことは、自然なことだと見なされます。ほとんどのプログラミング言語に見られるデータ型と同じ名前を持っているからです。しかし、このデータ型よりもふさわしい選択肢があるのです。

FLOAT やその類似したデータ側の代わりに、SQL データ型の NUMERIC または DECIMAL を用いて、固定精度の小数点数を表すようにしましょう。

NUMERIC および DECIMAL 型の長所は、FLOAT データ型と異なり、有理数を丸めることなく格納できる点です。値 59.95 を格納するとき、その値が正確に格納されていることを信頼できるのです。格納された値とリテラル値 59.95 との等価性を比較すると、比較は成功します。

これらのデータ型を使用しても、3 分の 1 のような、無限精度が必要な値を格納することはできません。しかし、少なくとも私たちは、10 進数形式におけるこのような無限小数の取り扱いには慣れています。正確な 10 進数値が必要な場合は、NUMERIC データ型を用いましょう。FLOAT データ型では、10 進法の有理数の多くを表現できません。つまり、FLOAT は概数として扱われるべきなのです。

「できる限り、FLOAT 型は使わないようにしましょう。」

10 章：サーティワンフレーバー（31 のフレーバー） #

列に格納できる値を限定された値に制限することは、とても有用です。列に無効な値が含まれていないことを保証できるので、列をシンプルに使うことができます。

コラム：バスキン・ロビンス社のサーティワンアイスクリーム

1953 年、バスキン・ロビンス社は、毎日、日替わりで違う種類のアイスクリームが食べられるアイスクリームパーラーを始めました。現在も「サーティワンアイスクリーム」として有名なこのアイスクリームパーラーは、「31 のフレーバー」というキャッチフレーズを何年もつかいつづけました。

それから 60 年ほど経過した現在、同社は 21 種類の定番フレーバー、12 種類のシーズン限定フレーバー、16 種類の地域限定フレーバー、そしてさまざまな「今月のフレーバー」を提供しています。当初、日替わりで毎日 31 種類という不変のアイスクリームフレーバーを提供していた同社は、選択肢を拡張し、それらを組み換え可能な可変のセットにしたのです。

同じことは、あなたがデータベースの設計を担当するプロジェクトでも起こる可能性があります。むしろ、そうなるものだと想定しておいた方がよいくらいです。

• 目的：列を特定の値に限定する
• アンチパターン：限定する値を列定義で指定する
• 解決策：限定する値をデータで指定する

多くの人が、有効なデータ値を定義時に指定するという方法をとります。列の定義はメタデータの一部です。つまり、テーブル構造それ自体の定義の一部です。

例えば、列には CHECK 制約を定義できます。CHECK 制約は、制約条件が FALSE になるような値の挿入や更新を拒否します。MySQL は、列の取りうる値を指定された値セットの中に制限する、ENUM と呼ばれる非標準のデータ型をサポートしています。ドメイン（DOMAIN）やユーザ定義型（UDT）を使い、列に入力する値を特定の値の中のどれかに制限する方法もあります。

しかし、ここまで見てきた解決策には、共通の短所があります。

（詳細は省略。定義内容の確認に手間がかかる点、固定値の追加・削除に手間がかかる点などが問題としてあげられている。）

ここで列の値を制限するための、もっと良い方法があります。

まず参照テーブル BugStatus を作成し、許可する値を１行に１つずつ status 列に格納します。次に、Bugs.status に外部キー制約を宣言し、いま作成した新しいテーブルを参照させます。

CREATE TABLE BugStatus(
  status VARCHAR(20) PRIMARY KEY
);

INSERT INTO BugStatus (status) VALUES ('NEW'), ('IN PROGRESS'), ('FIXED');

CREATE TABLE Bugs(
  -- 他の列 ...
  status VARCHAR(20),
  FOREIGN KEY (status) REFERENCES BugStatus(status)
    ON UPDATE CASCADE
);

これで、Bugs テーブルに行の挿入や更新を行うときは、BugStatus テーブルに存在する status 値を使わなくてはならないようにできました。このようなステータス値の矯正は ENUM や CHECK 制約でもできますが、この解決策では、より柔軟な運用ができます。

「列に入力する値を限定するときは、値セットが固定されている場合はメタデータを、流動的な場合はデータを用いましょう。」

11 章：ファントムファイル（幻のファイル） #

• 目的：画像をはじめとする大容量メディアファイルを格納する
• アンチパターン：物理ファイルの使用を必須と思い込む
• 解決策：必要に応じて BLOB 型を採用する

画像のバイナリデータは、BLOB データ型に格納できます。しかし、BLOB を用いず、画像をファイルシステムにファイルとして格納し、ファイルパスを VARCHAR としてデータベースに格納する人も多くいます。

この問題は、ソフトウェア開発者の間で熱い議論の的になっています。どちらの解決策にも、長所があります。しかし、大多数のプログラマーの間では、ファイルを常にデータベースの外部に格納すべきであるという考えで意見が一致しています。つまり、私はかなりの少数派の方になってしまうかもしれません。しかし、外部ファイルに格納するという設計には、いくつもの大きなリスクがあります。

次のいずれかが懸念となる場合は、画像を外部ファイルではなく、データベースの内部に格納することを考えるべきです。ほぼすべてのデータベース製品は、あらゆるバイナリデータを格納できる BLOB データ型をサポートしています。

ファイルの削除時における問題

画像がデータベースの外にある場合、データベースで画像のパスを含む行を削除しても、そのパスの指定先のファイルは自動的には削除されません。対応する画像ファイルも自動的に削除するようにアプリケーションを設計する必要があります。

トランザクション分離の問題

通常、データの更新や削除においては、トランザクションを COMMIT するまで変更は他のクライアントには見えません。しかし、ファイルがデータベースの外部にある場合、状況が異なります。

ロールバック時における問題

一般的な設計では、エラーが発生した際にはトランザクションをロールバックします。ロールバックは、アプリケーションロジックが変更のキャンセルを要求した場合にも行われます。データベース外のファイルはロールバック対象とはなりません。

データベースのバックアップツール使用時における問題

データベースのバックアップに用いたトランザクションと同期していることは保証できません。アプリケーションでは、画像ファイルの追加や変更が随時行われます。データベースのバックアップ開始直後に、画像ファイルの追加や変更が行われる可能性もあるのです。

SQL アクセス権限使用時における問題

外部ファイルには、データベースのアクセス権限が適用されません。

「データベース外部のリソースは、データベースでは管理できないことに注意しましょう。」

12 章：インデックスショットガン（闇雲インデックス） #

• 目的：パフォーマンスを最適化する
• アンチパターン：闇雲にインデックスを使用する
• 解決策：「MENTOR」の原則に基づいて効果的なインデックス管理を行う

「インデックスショットガン」アンチパターンとは、十分な根拠なくインデックスを作成したり、作成しなかったりすることだと言えます。データベースを分析し、インデックスを作成するための、あるいは作成しないための、正当な理由を見つけましょう。

MENTOR とは、Measure（測定）、Explain（解析）、Nominate（指名）、Test（テスト）、Optimize（最適化）、Rebuild（再構築）の頭文字をとったものです。

Measure（測定）

情報がなければ、情報に基づく判断はできません。ほとんどのデータベースには、SQL のクエリ実行時間を記録する方法があります。これによって、最大のコストがかかっている操作を識別できます。

Explain（解析）

最もコストがかかるクエリを特定した後は、クエリの処理が遅くなっている原因を解析します。データベースは、クエリ実行計画（Query Execution Plan: QEP）と呼ばれるクエリ最適化機能によって、クエリ実行にどのインデックスを使うかを判断しています。この QEP の分析結果のレポートを取得します。

Nominate（指名）

クエリの QEP を読んで、クエリがインデックスを使わないでテーブルにアクセスしている箇所を探しましょう。

Test（テスト）

インデックスの作成後、再びクエリのプロファイリングを行うのです。大事なのは、変更が効果をもたらしたことを確認してから作業を終了することです。

Optimize（最適化）

インデックスはコンパクトで、使用頻度の高いデータ構造であるため、キャッシュメモリに格納されやすくなります。メモリ上のインデックスにアクセスすることによって、ディスク I/O を伴う読み込みよりもはるかにパフォーマンスを改善できます。データベースサーバーでは、キャッシュに割り当てるシステムメモリの量を設定できます。

Rebuild（再構築）

長期にわたって行の更新や削除をおこなうことで、インデックスは次第に不均衡になっていきます。これは、ファイルシステムが次第に断片化していくのと似ています。実際には、最適化されたインデックスと、不均衡なインデックスの間には、大きな違いがない場合もあります。しかし、できる限りインデックスの効率を高めたいのであれば、定期的にメンテナンスを実施する価値はあります。

「データとクエリについての理解を深め、MENTOR の原則に基づいてインデックスを管理しましょう。」

3 部：クエリのアンチパターン #

データベースでは、データの追加や検索などを行います。

13 章：フィア・オブ・ジ・アンノウン（恐怖の unknown） #

• 目的：欠けている値を区別する
• アンチパターン：NULL を一般値として使う、または一般値を NULL として使う
• 解決策：NULL を一意な値として使う

SQL での NULL の振る舞いに困惑するソフトウェア開発者は少なくありません。多くのプログラム言語とは異なり、SQL は NULL をゼロ、FALSE、空文字とは異なる特殊な値として扱います。これは SQL 標準および主要なデータベース製品のすべてに当てはまります。

以下の場合、多くのプログラマーは、hours 列に時間が入っていないバグに対しては結果が 10 になると予想するでしょう。しかし、クエリは NULL を返します。

SELECT hours + 10 FROM Bugs;

以下のクエリは、assigned_to 列に値 123 を持つ行のみを返します。assigned_to 列に他の値または NULL を持つ行は返されません。

SELECT * FROM Bugs WHERE assigned_to = 123;

ということは、以下のクエリは先ほどのクエリの補集合、つまり上のクエリで返されなかった行がすべて返されるのではないかと思えます。

SELECT * FROM Bugs WHERE NOT (assigned_to = 123);

しかし、このクエリも assigned_to 列に NULL が割り当てられた行を返しません。NULL を用いた比較は、TRUE や FALSE ではなく、すべて不明（unknown）を返します。NULL の否定ですらも NULL のままです。NULL または非 NULL を検索しようとして、いかのような間違いをしてしまうことは珍しくありません。

SELECT * FROM Bugs WHERE assigned_to = NULL;
SELECT * FROM Bugs WHERE assigned_to <> NULL;

NULL の扱いを嫌って、NULL を一切使わないようにデータベースを設計するソフトウェア開発者がたくさんいます。NULL の代わりに、不明（unknown）または適用不能（inapplicable）を意味する値を新たに定義するのです。この NULL に対する態度は正しいとは言えません。例えば、ゼロ除算エラーを防止するためのコードを書くのが嫌だからといって、ゼロを扱うことをすべて禁止するのが得策ではないのと同じことです。

NULL を用いることそれ自体がアンチパターンなのではありません。NULL を一般値として使用したり、一般値を NULL に相当するものとして扱うことが「フィア・オブ・ジ・アンノウン（恐怖の unknown）」アンチパターンなのです。

NULL の問題のほとんどは、SQL の 3 値論理の振る舞いについてのよくある誤解に基づいています。他のほとんどのプログラム言語に実装されている従来型の 2 値論理（真偽のロジック）になれているプログラマーにとって、3 値論理はやっかいな存在です。この仕組みを学ぶことで、SQL クエリでの NULL をうまく取り扱えるようになります。

スカラー式での NULL

スタンという 30 歳の男性がいるとします。もう１人の男性オリバーの年齢は不明（unknown）です。スタンがオリバーよりも年上かと尋ねられた場合、唯一の可能な答えは「わかりません」です。同じく、スタンとオリバーが同い年であるかどうか、スタンとオリバーの年齢の合計はいくつかを尋ねられた場合も、答えは「わかりません」としか答えようがありません。

次に、同じく年齢が不明（unknown）なチャーリーという男性がいるとします。オリバーとチャーリーが同い年であるかどうかを尋ねた場合、答えはやはり「わかりません」です。これが、NULL = NULL のような比較の結果が NULL になる理由です。

プログラマーが期待する結果と、予想に反する実際の結果のいくつかの例を示します。

論理式での NULL

NULL の論理式での振る舞いを理解するためのカギは、「NULL は TRUE でも FALSE でもない」という概念です。プログラマーが期待する結果と、予想に反する実際の結果のいくつかの例を示します。

NULL の検索

ある値と NULL を比較するとき、等式も不等式も NULL を返さないため、NULL の検索には他の手段が必要になります。IS NULL 述語を使用します。あるいは IS DISTINCT FROM も使用できます。この述語は、一般的な非等価演算子 <> のように機能します。かつ、対象データが NULL の場合でも、常に TRUE または FALSE を返します。IS DISTINCT FROM によって、値の比較前に IS NULL で比較しなければならない面倒な式を書かずに済みます。

SELECT * FROM Bugs WHERE assigned_to IS NULL;
SELECT * FROM Bugs WHERE assigned_to IS NOT NULL;

SELECT * FROM Bugs WHERE assigned_to IS NULL OR assigned_to <> 1;
SELECT * FROM Bugs WHERE assigned_to IS DISTINCT FROM 1;

「データ型を問わず、欠けている値には NULL を用いるようにしましょう。」

14 章：アンビギュアスグループ（曖昧なグループ） #

• 目的：グループ内で最大値を持つ行を取得する
• アンチパターン：非グループ化列を参照する
• 解決策：曖昧でない列を使用する

「曖昧なクエリ結果を避けるために、単一値の原則に従いましょう。」

15 章：ランダムセレクション #

• 目的：サンプル行をフェッチする
• アンチパターン：データをランダムにソートする
• 解決策：特定の順番に依存しない

ランダムな結果を返す SQL クエリが必要になる場面は、驚くほど頻繁にやってきます。例えばニュースのハイライトや広告のような、コンテンツのローテーション表示などです。

いったんアプリケーション側にすべてのデータを取得して、その中からサンプルを選ぶのではなく、直接データベースからクエリでサンプルを抽出する方が良い方法だと言えます。

SQL でランダムな行を取得するための最も一般的な方法は、ランダムにソートを行い、最初の行を取得するというものです。この技法はわかりやすく、実装も簡単です。

SELECT * FROM Bugs ORDER BY RAND() LIMIT 1;

これはよく用いられる解決策ですが、時間の経過とともに弱点が現れます。

ソートの基準を行ごとにランダムな値を返す関数にすると、ある行の値が他の行の値より大きいか小さいかにかかわらず、行はランダムにソートされます。この方法で求めている結果が得られます。

非決定性を持つ式（RAND 関数）によってソートを行うということは、インデックスからメリットを得られないことを意味します。これはクエリのパフォーマンスにとっては問題となります。

インデックスを使えない場合には、データベースはクエリ結果を「手作業で」ソートしなければならなくなります。これはテーブルスキャンと呼ばれる手法で、多くの場合、全ての結果を一時的なテーブルとして保存し、物理的に行を入れ替える処理を行います。テーブルスキャンによるソートは、インデックスを用いたソートよりはるかに遅くなります。データセットのサイズが大きくなるにつれて、このパフォーマンスの違いはさらに大きくなります。

ランダムなソートのもう１つの弱点は、必要な行が最初の１行のみであるため、データセット全体をソートする労力のほとんどが無駄になってしまう点です。１行をランダムに取得するためだけに毎回大量の行をソートするのは、極めて非効率です。

これらの弱点は、少量のデータに対してクエリを実行するときには特に目立ちません。しかし、本番稼働後にデータベースのデータ量が増えていくにつれ、問題が現れ始めます。

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

データセットが特に大きくはなく、また増えていくこともない場合にはランダムなソートを用いてもよいでしょう。

１と最大値の間のランダムなキー値を選択する

主キーが１から開始され、かつ連続している場合は、１から最大値までの間の値をランダムに選択することで取得できます。

また、１と最大キー値の間に欠番がありうる場合は、欠番の穴の後にあるキー値を用いるようにします。

すべてのキー値のリストを受け取り、ランダムに１つを選択する

アプリケーションコードを用いて、結果セットの主キーから値を１つ選択する技法もあります。選択したら、その主キー値を用いてデータベースから該当する行全体を取得します。

この解決策には他の弱点があることに注意してください。リストのサイズが非常に大きい場合はアプリケーションメモリのリソースを越えてエラーになります。クエリを合計２回実行する必要があります。

この解決策は、単純なクエリを用いて、程々のサイズの結果セットからランダムな行を選択するときに使用しましょう。特に、非連続なリストからランダムに行を選択したい場合に便利な技法です。

オフセットを用いてランダムに行を選択する

データセットの行数をカウントし、0 と行数までの間の乱数を返す技法もあります。データセットにクエリを実行する際、その数をオフセット値として使用します。

ベンダー依存の解決策

ほとんどのデータベース製品には、この章の目的を実現するための独自の解決策が実装されています。例えば、Microsoft SQL Server 2005 では TABLESAMPLE 句が、Oracle では SAMPLE 句が使用できます。

「クエリについては最適化できないものもあります。最適化できない場合は、別のアプローチを採用しましょう。」

16 章：プアマンズ・サーチエンジン（貧者のサーチエンジン） #

• 目的：全文検索を行う
• アンチパターン：パターンマッチ述語を使用する
• 解決策：適切なツールを使用する

SQL には、文字列比較のためのパターンマッチ述語があります。ほとんどのプログラマーは、キーワード検索を実装する際に、まずパターンマッチ述語を使うことを考えます。最も一般的なのは、LIKE 述語です。多くのデータベース製品は、正規表現も独自の方法でサポートしています。

パターンマッチ述語の最も大きな問題点は、パフォーマンスの低下です。パターンマッチ術後は従来型のインデックスのメリットを得られないため、テーブルのすべての行をスキャンしなければなりません。文字列型の列に対するパターンマッチ処理は、例えば 2 つの整数の透過性比較よりもはるかに大きなコストがかかるため、検索時のテーブルスキャンには全体としてかなりの処理量が必要になります。

2 番目の問題点は、LIKE や正規表現を用いた単純なパターンマッチでは、意図しないマッチが生じてしまうことです。

SELECT * FROM Bugs WHERE description LIKE '%one%';

このクエリは単語「one」を含むテキストとマッチしますが、「money」、「phone」、「lonely」などもマッチしてしまいます。前後をスペースで区切ったキーワードで検索すると、今度は前後に句読点がある語や、文の冒頭または末尾になる語とマッチしなくなります。

このように、パフォーマンス、スケーラビリティ、処理の煩雑さなどの問題を考えると、キーワード検索のために単純なパターンマッチングを使用するのは、決して良い方法とは言えないのです。

最善の方法は、SQL の代わりに専用の全文検索エンジンを使うことです。主要なデータベース製品は、全文検索というありふれた要件に対する独自の解決策を用意しています。各種データベース製品に拡張機能として組み込まれている機能を使用します。ただしこれら独自機能は標準化されておらず、ベンダー間での互換性もありません。その場合は独立したプロジェクトとして提供されている技術を使用します。

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

アンチパターンの節で見たクエリは、れっきとして SQL クエリです。シンプルな用途に対して正しく使用すれば、大きな価値を得ることができます。パフォーマンスはデータベースにおいて常に悩ましい問題ですが、使用頻度が極めて低いので最適化に労力をかける意味がないクエリもあります。めったに使用しないクエリのためにインデックスを更新するのは、その非効率なクエリをインデックスなしで実行するのと同じくらいのコストがかかります。

単純なケースのためにパターンマッチを行う場合には、弱点を最小限に抑えつつ、十分な結果を得ることができるでしょう。

「問題を解決するために、必ずしも SQL を使う必要はありません。」

17 章：スパゲッティクエリ #

• 目的：SQL クエリの数を減らす
• アンチパターン：複雑な問題をワンステップで解決しようとする
• 解決策：分割統治を行う

SQL は非常に表現力に優れた言語であり、１つのクエリやステートメントで多くのことを実現できます。しかし、だからといって SQL は１つのクエリですべてのタスクを処理することを強制するものではありませんし、ときにはそれが良くないアイデアである場合もあります。おそらくあなたが他のプログラミング言語でコードを書くときには、１つのプログラムですべての仕事をこなすような習慣はないはずです。

複数の SQL クエリやステートメントを実行するのは、効率の面からは最善の方法ではない場合もあるかもしれません。しかし状況に応じて、効率とタスク実現のどちらを優先するかを的確に判断することが大切なのです。

「SQL では、１行のコードで複雑な問題を解決できると思える場合があります。しかし、状況に応じてクエリを分割することも検討するようにしましょう。」

18 章：インプリシットカラム（暗黙の列） #

• 目的：タイプ数を減らす
• アンチパターン：ショートカットの罠に陥る
• 解決策：列名を明示的に指定する

ワイルドカードを使用することで、後々のリファクタリングにおいてバグを生み出す原因となったり、パフォーマンスとスケーラビリティに悪い影響を及ぼす場合があります。クエリが多くの列をフェッチするようになるため、多くのデータがアプリケーションとデータベースサーバの間を行き来しなくてはなりません。

本書のサンプルでもワイルドカードを使用していますが、それは紙面を節約したり、重要な部分を強調したりすることが目的です。私は実際のアプリケーションコードではめったにワイルドカードを使いません。

ワイルドカードや暗黙的な列指定を使わずに、必要な列名は明示的に指定するようにしましょう。列名を全て入力するのは手間がかかると思うかもしれませんが、それだけの価値はあるのです。

「必要な列だけを指定するようにしましょう。」

4 部：アプリケーション開発のアンチパターン #

SQL はたいてい、他の言語（C++、Java、PHP、Python、Ruby など）で開発されたアプリケーションと共に使用されます。アプリケーションで SQL を用いる方法には、適切なものとそうでないものがあります。

19 章：リーダブルパスワード（読み取り可能パスワード） #

• 目的：パスワードのリカバリーとリセットを行う
• アンチパターン：パスワードを平文で格納する
• 解決策：ソルトを付けてパスワードハッシュを格納する

パスワードを、一方向性の暗号学的ハッシュ関数を用いて暗号化しましょう。またハッシュを格納していても、攻撃者にデータベースへのアクセスを許してしまえば、トライアンドエラーでパスワードを解読しようとするでしょう。解読には長時間を要するかもしれませんが、攻撃者はよくあるパスワードとそのハッシュ値のリストを格納した自前のデータベースを準備しておくこともできます。この種の辞書攻撃を防ぐ方法の１つは、暗号化前のパスワードへの「ソルト」の不可です。ソルトとは、ハッシュ関数に渡す前にパスワードに連結する無意味な文字列のことです。ソフトを付加されたパスワードから生成されたハッシュは、攻撃者のハッシュデータベースのハッシュとは一致しにくくなります。

「あなたが読み取れるものは、攻撃者にも読み取れます。」

20 章：SQL インジェクション #

• 目的：動的 SQL を記述する
• アンチパターン：未検証の入力をコードとして実行する
• 解決策：誰も信用してはならない

コラム：ママの SQL インジェクション

A 「お宅の息子さんが通われている学校の者です。実は学校のコンピュータに問題が生じまして……」 B 「あら、うちの息子が何かしでかしてしまったのかしら？」 A 「まあ、ある意味そうです……。お伺いしますが、お宅の息子さんの名前は、本当に『Robert’); DROP TABLE Students:–』なのですか？」 B 「そうよ。うちでは省略して BOBBY TABLES」って呼んでるの。 A 「その名前のおかげで、今年の生徒のレコードがすべて消えてしまいました。まったく、とんでもない名前をつけたものですね……」

SQL インジェクションのようなセキュリティ上の脆弱性を正当化する理由はありません。ソフトウェア開発社には、SQL インジェクションを防ぐために防御的なプログラミングを進んで行う責任があります。

「ユーザーには、値の入力は許可しても、コードの入力を許可指定はいけません。」

21 章：シュードキー・ニートフリーク（擬似キー潔癖症） #

• 目的：欠番を詰める
• アンチパターン：隙間を埋める
• 解決策：擬似キーの欠番を埋めない

番号が連番になっていないことを、特に気にしない人もいます。

一方、欠番を気にする人がいるのも理解できます。開発者はこの問題を解決しようとして「シュードキー・ニートフリーク」アンチパターンに陥ってしまいます。

欠番を詰めるための作業には大変なリスクが伴います。また持続可能なものでもありません。

主キーの値は、一意で非 NULL の値でなければなりません。各行を識別できなくてはならないからです。しかし、主キーのルールはそれだけです。行の識別のために連続している必要はないのです。

擬似キージェネレーターによって生成される値は、一般に単調増加するため、行番号と同等に見えます。行番号と主キーを混同してはいけません。主キーがテーブルにおける１行を識別するのに対し、行番号は結果セットにおける行の順序番号を示します。ページネーションなど、整った行番号が欲しいときは行番号を求める関数を使用しましょう。

ランダムな擬似キー値を割り当てる方法もあります。一部のデータベースは、この目的のためにグローバル一意識別子（GUID）をサポートしています。

「擬似キーは、行を一意に識別するためにあります。行番号と混同しないようにしましょう。」

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

• 目的：簡潔なコードを書く
• アンチパターン：肝心な部分を見逃す
• 解決策：エラーから優雅に回復する

「コードのトラブルシューティングは、それだけで十分に大変な作業です。闇雲に進めても、作業を遅らせるだけです。」

23 章：ディプロマティック・イミュニティ（外交特権） #

• 目的：ベストプラクティスを採用する
• アンチパターン：SQL を特別扱いする
• 解決策：包括的に品質問題に取り組む

アプリケーションコードの開発ではベストプラクティスを受け入れる開発者であっても、データベースコードではこれらの慣行が免除されると考える傾向があります。

「アプリケーション開発のルールはデータベース開発には当てはまらない」

このような、データベースには特権的な何かがあるという考えを、私は「ディプロマティック・イミュニティ」アンチパターンと名付けました。

「アプリケーションと同じく、データベースに対しても、ソフトウェア開発のベストプラクティスを適用し、文書化、テスト、バージョン管理を行いましょう。」

24 章：マジックビーンズ（魔法の豆） #

• 目的：MVC の M（モデル）を単純化する
• アンチパターン：モデルがアクティブレコードそのもの（ドメインモデル貧血症）
• 解決策：モデルがアクティブレコードを「持つ」ようにする

「モデルはテーブルから分離させましょう。」