はじめに

このスクラップではSQLアンチパターン本を購入し、社内で輪読会をしていくにあたり章ごとのメモをまとめていきます。
https://www.amazon.co.jp/SQLアンチパターン-Bill-Karwin/dp/4873115892

やり方

• 章ごとに内容を整理する
• 関連の技術トピックを調べたことをまとめる

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

多対多の関連を表現する交差テーブルの作成を避けるためにリスト区切りにすることをジェイウォーク(信号無視)という。

Productsテーブルとユーザーアカウントの関連ははじめ「多対1」の関係だったが、次第に製品が複数の連絡先を持つ場合がある。そんな時はProductsテーブルの１つの行が複数の連絡先をもたなきゃいけない。

CREATE TABLE Products (
     product_id SERIAL PRIMARY KEY,
     product_name VARCHAR(1000),
     account_id BIGINT UNSIGNED,
     FOREIGN KEY(account_id) REFERENCES Accounts(account_id)
);

アンチパターン：カンマ区切りのリスト

データベースの構造に最小限の変更にするために、account_id列を複数アカウントのIDをカンマ区切りにしちゃった。

CREATE TABLE Products (
     product_id SERIAL PRIMARY KEY,
     product_name VARCHAR(1000),
     account_id VARCHAR(100), --- カンマ区切りのリスト
     FOREIGN KEY(account_id) REFERENCES Accounts(account_id)
);

INSERT INTO Products(product_id, product_name, account_id) VALUES(DEFAULT, 'Virtual TurboBuider', '12,34');

一見、テーブルにカラムを追加しなくていいのでうまく行っているようになるがこの変更が原因でパフォーマンスの問題やデータ整合性の問題が生じる。

• 集約関数
• アカウントIDに不正なIDが入らないようなケア
• 入力値にもカンマが入る場合があるからよくない
• リストの長さの制限

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

ジェイウォークが発生しているかは次のような会話している時に怒る

• VARCHARの最大長を話している時
• 正規表現で文字列の一部で取得する時
• エントリの文字列で区別できる文字を探す時

アンチパターンを使っていいパターン

データベースの非正規化の時は使っていい場合がある。

解決策：交差テーブルを作る

１つのテーブルの中のデータ型を変えるんじゃなくて別テーブルを作る。これによってProductsとAccountsの間は多対多になる。

CREATE TABLE Contacts (
     product_id BIGINT UNSIGNED NOT NULL,
     account_id BIGINT UNSIGNED NOT NULL,
     PRIMARY KEY(product_id, account_id),
     FOREIGN KEY(product_id) REFERENCES Products(product_id)
     FOREIGN KEY(account_id) REFERENCES Accounts(account_id)
);

INSERT INTO Contacts(product_id,  account_id) VALUES(123,12),(123,34)・・・;

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

ツリー型のデータ構造でのアンチパターン。
Reddit のように各コメントに対してスレッド形式でコメントできるような掲示板を例として見ていく。

アンチパターン: 常に直近の親に依存する(ナイーブツリー)

直近の親のidをのみを持つことで階層構造を持つ形式。
隣接リスト形式と呼ばれる。

CREATE TABLE Comments (
comment_id      SERIAL PRIMARY KEY,
parent_id       BIGINT UNSIGNED,
bug_id          BIGINT UNSIGNED  NOT NULL,
author          BIGINT UNSIGNED NOT NULL,
comment_date    DATETIME NOT NULL,
comment         TEXT NOT NULL,
FOREIGN KEY(parent_id) REFERNCES Comments(comment_id),
FOREIGN KEY(bug_id) REFERNCES Bugs(bug_id),
FOREIGN KEY(author) REFERNCES Comments(account_id)
);

良くない点

• 親子の階層が増えるたびに JOIN の数が増える
  • 到達するまでparant_id を辿っていく必要がある
• そのためCOUNT などの集約関数が使いづらくなる
• サブツリー全体の削除が難しい
  • 子孫を特定するために複数回のクエリの発行が必要

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

• ツリーの深さを決めようとしている時
• ツリー型のデータ構造にかなりうんざりした様子の時
• ツリーからはぐれたノード(コメント)を削除するためのバッチ処理を必要としている時

アンチパターンを使っていいパターン

• 直近の親/子の参照, 列の挿入は容易なので必要な操作がそれらのみの時
• 隣接リストをサポートしているDBの時

解決策：隣接リストの代替になるモデルを利用する

代替になるモデル

• 経路列挙モデル
• 入れ子集合モデル
• 閉包テーブルモデル

経路列挙モデル

親から子までのパス全てを保存するpathカラムを容易する。
like句 でそのカラムを検索することで親子関係を取得できる。

pathカラムにはジェイウォークと同様のデメリットがある。

入れ子集合モデル

ノードの親子関係を left, right の二つの値を持たせることで表現する。

left => その子ノードのどれよりも小さい値
right => その子ノードのどれよりも大きい値

親のleft, right の間の大きさの left, rightをもつレコードが子供になる

# コメントID 1 の子孫を取得するクエリ
select c2.*
from Comments as c1
  inner join Comments as c2
    on c2.left between c1.left and c1.right
where c1.comment_id = 1

メリット

• 親も子も持つノードを削除しても、特にメンテナンス不要で削除したノードの子孫が削除したノードの親に紐づくこと。

デメリット

• 直近の親の取得が複雑になる
  • 外部結合で他のノードが間に挟まってないことを調べるクエリが必要
• ノードに挿入が複雑になる
  • 挿入の際にleft, right を計算し直す必要がある

サブツリーに対する操作が多い時が適している。

閉包テーブルモデル

ノード間の親子関係を全て保存する別のテーブルを用意する。

CREATE TABLE TreePaths (
ancestor          BIGINT UNSIGNED  NOT NULL,
descendant        BIGINT UNSIGNED NOT NULL,
PRIMARY KEY(ancestor, descendant)
FOREIGN KEY(ancestor)   REFERNCES Comments(comment_id),
FOREIGN KEY(descendant) REFERNCES Comments(comment_id),
);

メリット

• 先祖, 子孫へのクエリ実行が容易
• メンテナンスが簡単

デメリット

• レコード数が増えやすい
• 直近の親/子へのアクセスが複雑
  • TreePathsテーブルにノード間の関係の距離を表現するカラムを持たせるという解決策がある

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

とりあえずどんなテーブルにも、主キーとしてid列を付与してしまうアンチパターン。

アンチパターン: すべてのテーブルに「id」列を用いる

フレームワークなどの影響で、全てのテーブルは以下の特徴を持つ主キー列が存在していなくてはならないという考えが普及している。

• 列名はid
• データ型は32ビットまたは64ビットの整数
• 一意の値が自動的に生成される

しかし、全てのテーブルにid列を加えると、意図に反した影響が生じることがある。

良くない点

• 冗長な主キーが作成されてしまう

  CREATE TABLE Bugs (
      id     SERIAL PRIMARY KEY,
      bug_id VARCHAR(10) UNIQUE,
      .
      .
      .
  )
  

  上記Bugsテーブルの場合、bug_id列がuniqueになっており、より「自然な」主キー (自然キー) として使える。

  つまり、id列と同じように利用することができるため、id列は冗長である。

• 重複行を許可してしまう

  CREATE TABLE BugProducts (
      id          SERIAL PRIMARY KEY,
      bug_id      BIGINT UNSIGNED NOT NULL,
      product_id  BIGINT UNSIGNED NOT NULL,
      FOREIGN KEY (bug) PREFERENCES Bugs (bug_id),
      FOREIGN KEY (product_id) PREFERENCES Products (product_id)
  );
  

  上記のような中間テーブルにおいて、id列を主キーとしてしまうとbug_idとproduct_idの組み合わせが常に一意であることを保証しなくなってしまう。

  bug_idとproduct_idの2列にunique制約を追加することで解決できるが、そうするとid列はただの無駄になってしまう。

• キーの意味がわかりにくくなる

  idという名前は極めて一般的であり、明確に意味を持たないためクエリの明確化に役立たない

• USINGを使用する(使用できなくなること)

  SELECT * FROM Bugs AS b
      INNER JOIN BugsProducts AS bp
      ON b.bug_id = bp.bug_id;
  

  上記のように両方のテーブルに同じ名前の列がある場合、USINGを使って以下のように書き直せる。

  SELECT * FROM Bugs INNER JOIN BugsProducts USING (bug_id);
  

  しかし常に主キーがid列となっている場合、参照する列名が異なりUSINGは使えない。

• 複合キーは使いにくい(と思ってしまうこと)

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

• テーブルの主キーが「id」列であるとき

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

• ORMフレームワークで開発を行う場合

  一部のORMフレームワークでは、開発をシンプルにするために設定より規約の原則に従っている。
  つまり、全てのテーブルでid列を主キーとして定義する。
  このようなフレームワークでは、それらの便利な機能を使いやすくなるので、規約に従った方が良い場合もある。

• 自然キーがあまりに長すぎる場合

  ファイルシステムの上のファイルの属性を記録するテーブルでは、ファイルパスを自然キーとして選択できるが長すぎる。

解決策: 状況に応じて適切に調整する

• わかりやすい列名にする

  • 上記Bugsテーブルでは、bug_idが相応しい

• 規約に縛られない
  Railsの場合、以下のようにid列以外を主キーとして宣言することもできる

  class Bug < ActiveRecord::Base
    self.primary_key = "bug_id"
  end
  

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

  • 主キーとしてより自然な列名を使用する
  • 中間テーブルの場合は複合主キーとして定義することもできる

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

4.1

外部キーを使うのはデータベースのリレーションを維持すること
逆に外部キー制約が使わない場合の理由は次の通り

• データの更新が参照整合制約と衝突する
• データベース設計の柔軟性が高いため参照整合制約が必要ない
• データベースが外部キーのために作成するインデックスがパフォーマンスに影響する(ある？)
• 外部キーをサポートしてない製品を使っている(ある？)
• 外部キーを宣言する構文を調べなければならない

4.2 アンチパターン：外部キーを使わない

外部キー制約を使用しなくてもよくなればデータベース設計は柔軟が高まって、実行速度が早くなるかもと思っているかもしれないが代償がある

4.2.1 完璧なコードが前提

• レコードの追加、変更時に関連レコードを必ず事前チェックする処理を組み込む
  • レコードが知らないところで更新されるのを防ぐためにテーブルをロックする処理を組み込む
    • これをやると書き込みできなくなる

4.2.2 ミスを調べる必要がある

外部キーがない参照先のテーブルレコードを都度ちぇっくしなくてはいけずかなり煩わしい作業になる

SELECT b.bug_id, b.status
FROM Bugs b OUTER JOIN BugStatus s
   ON b.status = s.status
WHERE s.status IS NULL; <= チェック

4.2.3 必ず利用者のコードが正しく参照維持できるかはわからない

ユーザーが作ったコードやスクリプトでデータベースのレコードを変更する時に参照の関係性が崩れてしまってしまうことがある

4.2.4 UPDATE が難しいパターン

外部キー制約を嫌うのは関連するテーブルを更新する時に外部キー制約が邪魔だと感じるためで、そのためのコード改修をする必要がある
ただ、解決できない問題もあり、子の行が依存する列をUPDATEする場合で親の行を更新するまで子の行は更新できず、親の更新も参照する子の値を更新する前には変更できない

UPDATE BugStatus SET status = 'INVALID' WHERE status = 'BOGUS'; 
UPDATE Bug SET status = 'INVALID' WHERE status = 'BOGUS'; 

4.3,4.4 アンチパターンの見つけ方とアンチパターンを使っていい場合

アンチパターンの兆候として次のような言葉が出てきたらキーレスエントリーのアンチパターンがでている

• 親が更新または削除されて子のレコードを特定ようしている時
• 親の行が存在していることを確認したい時
• 外部キー制約がパフォーマンスに影響するから使わないようにする時

逆にアンチパターンを使ってもいい場合として外部キー制約をサポートしていないデータベース製品や外部キー制約をつかった関連づけを行えない時は外部キー制約を使わない設計になる

4.5 解決策

外部キー制約を使おう
外部キー制約を使うことでデータの不整合を検出して修正するだけでなく、データベースのレコード登録時点でミスを防止できる
外部キー制約はアプリケーションコードでは実現できない機能にカスケード更新というものがある

CREATE TABLE Bugs(
    reported_by BIGINT UNSIGNED NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'NEW',
    FOREIGN KEY(reported_by) REFERENCES Accounts(account_id)
          ON UPDATE CASCADE
          ON DELETE RESTRICT,
    FOREIGN KEY(status) REFERENCES BugStatus(status)
           ON UPDATE CASCADE,
           ON DELETE SET DEFAULT
);

カスケード更新を使うと親の行の更新削除が可能で、子の行も適切に処理してくれる(便利)
カスケード処理の振る舞いは子テーブル側の外部キー上で定義するため、新しい子テーブルを加える場合でもアプリケーションコードの変更は不要かつ親テーブルの定義も不要

https://qiita.com/suin/items/21fe6c5a78c1505b19cb

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

カラムにスキーマを定義せず任意の可変な属性名、値を設定できるようにしているテーブルのこと。
オブジェクト指向でいう継承のように基底クラスがあり、それを元にして追加で属性が必要な子クラスたちを
まとめて一つのテーブルで永続化してしまうなどの際に現れるパターン

アンチパターン: 汎用的な属性テーブルを使用する

可変属性のサポートにより、柔軟性や列数の少なさを魅力的に感じるが
RDBの利点が損なわれてしまう。

良くない点

• 必須属性を設定できない
  • 属性の定義がレコード単位でバラバラになる/それらが同一カラムのため、特定の属性に必須属性の設定ができない
• 参照整合性を強制できない
  • 上と同じ理由で外部キーなどを設定できない
• データの型を定義できない
  • 値のカラムが柔軟さのため文字列型での適宜になるため
• 行の再構築が必要
  • 同じレコードとして認識すべきものの属性が複数カラムになるため、一レコードとして扱うには複雑なクエリが必要になる

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

• 動的に新しい属性を定義できると行っている時
• クエリの結合数がいくつまでか確認している時
• 外部システム導入時にクエリが複雑すぎて対応できず困っている時

アンチパターンを使っていいパターン

• Redis などの KVSを使う時

解決策

サブタイプの数が限られ、開発者が熟知している場合は解決策が機能する

シングルテーブル継承

• 可変な属性名を全てカラムとして定義する
  • サブタイプ(モデル)ごとに使用するカラム/使用しないカラムが分かれる
    • サブタイプごとに固有の属性があるため

メリット

• サブタイプ/サブタイプごとに固有の属性が少ないときは有用

デメリット

• どのサブタイプがどの固有の属性をもつのか管理できない

具象テーブル継承

• サブタイプごとにテーブルを作成する
• 全サブタイプをまとめて取得する頻度が低い時に有用

メリット

• サブタイプを表す列が不要
• 他のサブタイプがもつ属性をカラムとして定義する必要がない

デメリット

• 全サブタイプがもつ共通の属性が増えた際に大変
• 全サブタイプをまとめて取得するのが大変

クラステーブル継承

• 継承でいう基底クラスのようなテーブルを定義する。
  • 子クラスはその基底テーブルのidを主キーとして関連付けをもつ。
  • STI のサブタイプの部分のみをサブタイプごとに別テーブルにしたようなパターン

メリット

• 基底テーブルの属性の参照が多い場合は有用

半構造化データ

• XMLやJSONの形でサブタイプの属性/値を全て一つのカラムに格納する

メリット

• 拡張性が極めて高い

デメリット

• SQLでサブタイプの特定の属性にアクセスする手段がほぼないこと

後処理

• すでにあるEAVはどうしようもないから頑張る
• 同じレコードの属性を無理に一行にまとめるより複数行で取得した方が良い
  • アプリケーション側で for文などで処理する

ポリモーフィック関連

1つの子テーブルから複数の親テーブルを参照する関連

CREATE TABLE comments (
    comment_id   SERIAL PRIMARY KEY,
    issue_type   VARCHAR(20), -- "Bugs"または"FeatureRequests"が格納される
    issue_id     BIGINT UNSIGNED NOT NULL,
    author       BIGINT UNSIGNED NOT NULL,
    comment_date DATETIME,
    comment      TEXT,
    FOREIGN KEY (author) REFERENCES Accounts(account_id)
);

Commentsテーブルはissue_typeカラムによって、BugsかFeatureRequestsどちらかに紐づく

アンチパターン : 二重目的の外部キーを使用する

良くない点

• ポリモーフィック関連を定義する

  • issue_typeによってissue_idの紐付き先が変わるため、issue_idに外部キーを宣言できない
  • issue_typeが正しくテーブル名と対応していることの保証もできない

• ポリモーフィック関連へのクエリ実行
  子テーブルに親テーブルを結合する際、Comment.issue_typeの値によって行単位でjoinする先を切り替えることができないため、両方の親テーブルを外部結合する必要がある

  SELECT *
  FROM Comments AS c
      LEFT OUTER JOIN Bugs AS b
      ON b.issue_id = c.issue_id AND c.issue_type = "Bugs"
      LEFT OUTER JOIN FeatureRequests AS f
      ON f.issue_id = c.issue_id AND c.issue_type = "FeatureRequests";
  

• 非オブジェクト指向の例
  上記Bugs、FeatureRequestsの場合はどちらもissueに関係する親テーブルだった (同じモデルから継承したサブタイプ) が、親テーブル同士に全く関係が無い場合にも使用できてしまう (図６−３)。

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

以下のような言葉を耳にしたら

• あるテーブルから複数の「あらゆる」リソースへと関連付けを行っている時
• 外部キーを宣言できない時
• issue_typeのように、どのテーブルを参照しているのかを示すカラムが存在する時

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

• ポリモーフィック関連を意識的に選択する時
  ORMを使った設計を行う際に、子側のテーブルを設計/作成する時点ではまだ存在しない親テーブルとの関連を作成できる

解決策 : 関連を単純化する

1. 参照を逆にする

2. 交差テーブルの作成
   複数の親テーブルそれぞれに対応した以下のような交差テーブルを作成する (図6-4)

   CREATE TABLE BugsComments (
       issue_id    BIGINT UNSIGNED NOT NULL,
       comment_id  BIGINT UNSIGNED NOT NULL,
       PRIMARY KEY (issue_id, comment_id),
       FOREIGN KEY (issue_id) PREFERENCES Bugs(issue_id),
       FOREIGN KEY (comment_id) PREFERENCES Comments(comment_id),
   );
   
   CREATE TABLE FeaturesComments (
       issue_id    BIGINT UNSIGNED NOT NULL,
       comment_id  BIGINT UNSIGNED NOT NULL,
       PRIMARY KEY (issue_id, comment_id),
       FOREIGN KEY (issue_id) PREFERENCES FeatureRequests(issue_id),
       FOREIGN KEY (comment_id) PREFERENCES Comments(comment_id),
   );
   

   こうすれば、Comments.issue_typeのようなカラムは不要になる

3. 交差点に交通信号を設置する
   2の解決策の場合、コメントを複数のバグまたは機能要求と関連付けるなど、許可したくない関連付けも許可されてしまう。
   これを防ぐため、各交差テーブルのcomment_id列にUNIQUE制約を宣言する

4. 両方の「道」を見る
   交差テーブルを使えば、特定のバグまたは機能要求を指定したコメントへのクエリを実行できる

   SELECT *
   FROM BugsComments AS b
       INNER JOIN Comments AS c USING (comment_id)
   WHERE b.issue_id = 1234;
   

   また、ポリモーフィック関連とは異なり、参照整合性を保証できる。

   クエリ省略

5. 「道」を合流させる
   複数の親テーブルに対するクエリの結果を、親テーブルたちがあたかも1つのテーブルに格納しているかのように扱いたい場合の方法

   • UNIONで連結する
   • COALESCE関数を用いる

   クエリ省略

6. 共通の親テーブルの作成
   Bugs、FeatureRequestsのように同じモデルから継承したサブタイプについてポリモーフィック関連の場合、全ての親テーブルが継承する基底テーブルを作成することで対応できる(図6-5) (5章「EAV」の「クラステーブル継承」の項)

   クエリ省略

   Isuuesのような基底テーブルを使えば、外部キーによるデータ整合性制約に依存できる

7章 マルチカラムアトリビュート(複数列属性) : 1対多問題

7.1 複数の値を持つ属性を格納する

1章のようなジェイウォークと同じテーマの問題で、1つのテーブルに属するべきだと思える属性に複数の値がある場合、それをどう格納するかという問題が７章のテーマ
新しいアンチパターンの例として、バグデータベースにバグを分類するためのタグ付機能を追加するとした時にどうするかを考える。

• マルチカラムアトリビュートパターンは1対多関連を表現する時にでてくる
• マルチカラムアトリビュートパターンは、列を複数作成し値を格納する

7.2 アンチパターン：複数の列を定義する

バグにタグ(crasy,printing,performance)をつけるためにtagを使い、使っていないカラムはNULLになる。

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

このテーブルの設計には問題がある。特定のタグをつけられたバグを検索しようとすると、３列全てを取得しないといけず、冗長になってしまう。レコードの追加や削除する時もNULLが入ってないかを走査しなければならないため無駄がある。

SELECT * FROM Bugs
WHERE tag1 = 'perfmance'
        OR tag2 = 'perfmance'
        OR tag3 = 'perfmance'

また、複数の列に同じ値が入らないようにしたい場合にもマルチカラムアトリュビュートでは防ぐことができない。さらに増加する

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

• タグのような複数の値を持つ属性のためにテーブルに列を幾つ定義すべきか悩む時
• 複数列から特定の値を検索しようとする場合

7.4 アンチパターンを使っていい場合

外部キー列を使うと良い
Bugs テーブルから Accouts テーブルへの複数のリレーションを持つ従属テーブルを作ることで各アカウントのバグに対して持つ役割がわかる

7.5 解決策

最善の解決策は属性を格納する列を1つ持つ従属テーブルを作って、属性を格納し、属性は複数の列ではなく行に入れて外部キーを定義して親の Bugs テーブルの行に値を関連づける

CREATE TABLE Tags (
     bug_id BIGINT UNSIGNED NOT NULL,
     tag VARCHAR(20),
     PRIMARY KEY(bug_id, tag),
     FOREIGN KEY(bug_id) REFERENCES Bugs(bug_id)
);

INSERT INTO Tags(bug_id,  tag) VALUES(123,'crash'),(3456,'performance')・・・;

ここのバグにつけられたタグは従属テーブルの1つの列に格納されるから特定タグをつけられたバグの検索が簡単になる。レコードの追加や削除する時も従属テーブルへ行を挿入するか削除すればよく、値を追加する場所を探すために開いている列を探さなくていい。

# 単一検索
SELECT * FROM Bugs INNER JOIN Tags USING(bug_i) WHERE tag = 'performance';

# 複数検索
SELECT * FROM Bugs 
INNER JOIN Tags AS t1 USING(bug_i) 
INNER JOIN Tags AS t2 USING(bug_i) 
WHERE t1.tag = 'printing'
AND t2.tag = 'performance';

PRIMARY KEY 製薬で重複排除できるし、従属テーブルを使うことで Bugs テーブルにタグを増やそうとしたときにも苦労したけど必要なタグを増やしやすくなる。

8章 メタデータトリブル(メタデータ大増殖)

区別のつきやすい値に基づいてカラムやテーブルを分割するというアンチパターン。
例えば年ごとにカラム/テーブルを分ける。
スケーラビリティを目的として行われることが多い。

良くない点

• 分けている条件が増えるにつれてテーブルやカラムが増える
  • 年の場合、年をまたぐ度にテーブルやカラムが増える
• 分けている条件を遵守してデータを追加しなければならない
  • 年の場合, 2020年用のテーブル(カラム)に2021年のデータが入らないように気をつける必要が発生する
  • データの修正も同様
• 分割されたテーブルで主キーがユニークである必要があるので採番用のテーブルが必要になる
• テーブルをまたいでクエリを実行するのが辛い
• テーブルを分割した場合、全てのテーブルに同じ列を追加するようにする必要がある。
• テーブルを分割した場合、従属テーブルで外部キー制約を定義できない。

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

• ~~ごとにテーブル(カラム)を追加する必要があるという発言が見られた時
• サポートしているテーブル/カラムの上限数がいくつまでか確認している時
• データの追加の失敗の理由が条件に従った新しいテーブルの作成がされていないという理由の時

アンチパターンを使っていいパターン

過去のデータを最新のデータから分離するようなケースの時。
(過去データはほとんどクエリを実行する必要がないケースの時)

解決策: パーティショニングと正規化

水平パーティショニング/シャーディング

水平パーティショニング/シャーディング という機能がSQL製品に実装されているのでそれを使う。
特定の条件でテーブルを分割する機能だがアンチパターンのように個別の分割テーブルを参照することなく、
一つのテーブルに対してクエリを実行できる。
また、手作業でデータを追加しても誤って違うテーブルにデータを追加するようなことが起きない。

垂直パーティショニング

水平パーティショニングはレコード単位で分割されるが、
垂直パーティショニングは列単位でテーブルを分割する。
列の一部のサイズが大きい場合や滅多に使用されない列の場合メリットがある。
型がBLOBやTEXTの場合、サイズは可変で大きいケースが多いので対象としやすい。

従属テーブルの導入

テーブルやカラムを分けている条件を列に分けて
従属テーブルとしてデータを保存できるケースもあり、その場合は従属テーブルで解決できる。

9章: ラウンディングエラー (丸め誤差)

少数の丸めによって生まれる誤差

アンチパターン: FLOATデータ型を使用する

SQLのFLOAT型は、他のプログラミング言語のfloat型と同じように、IEEE754標準に従って実数を2進数形式でエンコードする。

丸めが避けられない

10進数で記述できる全ての値を、2進数として格納できるわけではないため、やむを得ず近似値に丸められてしまう。
例えば、1/3は0.333...と無限に桁数が続くため、正確な値を少数で表すことはできない。

そのため、例えば0.333のような限りなくオリジナルに近い有限精度の値を使うことになり、意図していた値とは正確に一致しなくなる

1/3 + 1/3 + 1/3 + = 1.00
0.333 + 0.333 + 0.333 + = 0.999

加えて、IEEE754では、浮動小数点数を2進数形式で表現する。2進数形式で無限精度が必要な値は、10進数で表現される値とは異なる。例えば、10進数では有限精度で表せる59.95を2進数で正確に表すには無限精度が必要にな李、2進数形式で格納できる近似値を使用することになる。その近似値を10進数形式で表すと、59.950000762939になる。

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

FLOAT型などのデータ型を用いている場合

解決策: NUMERICデータ型を使用する

NUMERICまたは、DECIMELを用いて、固定精度の少数点数を表すようにする。

これらのデータ型は、列の定義に指定した精度で数値を格納する。精度とスケールを指定する。
精度に9を指定すると、123456789のような値は格納できるが、1234567890は格納できなくなる。
精度に9を指定し、スケールに2を指定した場合は、1234567.89は格納できるが、1234567.891や123456.789は格納できなくなる。

このように、無限精度が必要な値を格納することができなくなるため、有理数を丸めることなく格納できる。

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

連絡先情報のテーブルで値の種類が少ない列として敬称(salutation)カラムがあり、Mr., Mrs, Ms., Dr., Rev. を入れておけば全ての人の敬称に対応できそう。ただ、他にも敬称を入れなきゃいけない場合連絡先テーブルを変更しなきゃいけず、無停止ではテーブル修正も効かない。

CREATE TABLE PersonalContacts (
~他のカラム定義~
   salutation VARCHAR(4) CHECK(salutation IN('Mr.', 'Mrs.', 'Ms.', 'Dr.', 'Rev,')),
);

10.1 目的：列を特定の値に限定する

列に格納できる値を限定された値にすることは有効で、列をシンプルに扱える。
列に無効な値が入ることを排除できる。

INSERT INTO Bugs (status) VALUES('NEW');----OK

INSERT INTO Bugs (status) VALUES('MUSCLE'); ---NG

10.2 アンチパターン：限定する値を列定義で指定する

CHECK 製薬
ENUM は MySQL の実装では取りうる値を文字列として宣言されるが、列に格納されるのは定義されたリスト中の文字列の位置
ドメインやユーザー定義型を使い列に入力する値を特定の値の中のどれかに限定する方法もあるが、サポートするRDBMSが少ない
いずれも共通の短所がある
CHECK制約、ドメイン、ユーザー定義型のメタデータを扱うクエリは複雑化しやすく、メンテナンスコストがあがる

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

ENUM や CHECK 制約を使っていて問題に遭遇するのは値が固定されていないとき
ENUM の採用を検討するときは値の変更が予定されているか、変更の可能性がないかを検討し、変更の可能性がある場合は ENUM を使わないほうがいい

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

値セットが変わらない場合

10.5 解決策：限定する値をデータで指定する

列の値を制限するために参照テーブルを作成し、許可する値を1行に1つずつレコードを格納する
ENUM データ型、CHECK 制約、ドメインやユーザー定義型と異なり参照テーブルを用いた解決策は外部キー制約を用いた参照整合性の宣言という標準的な SQL 機能を利用したものであり移植も容易

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

画像など大容量メディアファイルを永続化させる場合にDB外に保存するというアンチパターン。
DBに保存する場合はBLOB列などに保存する。
DB外に保存する場合はその代わりに任意のファイルシステムに保存しファイルパスをDBに格納する。

良くない点

ファイルの削除の問題

画像のあるファイルパスを削除しても画像自体は削除されない

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

外部にある画像が削除や更新された場合、
他のクライアントはトランザクションがコミットされる前に変更が反映されてしまう

ロールバックの問題

DBへの変更はロールバックで戻るが、画像の削除はロールバックの対象ではないので戻らない。

バックアップツール使用時の問題

• DBのバックアップツールでは外部の画像はバックアップされない。
• 外部の画像もファイルシステム用のツールでバックアップするとしてもDBのバックアップとの同期を保証できない

SQLアクセス権限使用時の問題

GRANTやREVOKEなどのSQLのアクセス権限は外部の画像ファイルを対象にできない。

ファイルはSQLデータ型ではないという問題

DBに保存されているパスが正当なパス名であることの保証ができない。
また、画像がそのパスに保存されているという保証もできない。

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

下記の質問への適切な答えがない時

• データのバックアップの方法が問題なく行えるか
• 不要な画像ファイルを自動で削除することができるか
• 画像に対してのアクセス権限を適用できるか
• 画像に対しての変更をキャンセルし、ロールバックできるか

アンチパターンを使っていいパターン

• DBの容量が減らせる
• DBのバックアップが短時間で終わる
• DB外に画像がある場合、プレビューなどが容易

アンチパターンの見つけ方 の箇所に記載してある要件を満たさなくて良いなら
上記のメリットは大きいのでよく検討するのが良い。

解決策: BLOB列を使う

BLOB列に画像を保存するようにすれば、アンチパターンの見つけ方で問題とした全てを解決できる。

インデックスショットガン

インデックスについてのアンチパターン

アンチパターン: 闇雲にインデックスを使用する

1. インデックスをまったく定義しない

インデックスの更新 (INSERT, UPDATE, DELETE) によってデータベースにはオーバーヘッドが生じる。このことからオーバーヘッドを排除するために、インデックスそのものを使用しなければよいと考えてしまうことがある。しかし、基本的にテーブルに対するクエリ発行回数の方が、テーブルの更新回数よりもの何百倍も多く、インデックスにはオーバーヘッドが発生するだけのメリットがある。

2. インデックスを多く定義しすぎる

使用されないインデックスには作るメリットがない。
例えば、

1. 主キーへの明示的なインデックス
   ほとんどのデータベースは、主キーへのインデックスを自動的に作成するので、メリットが無く、追加のオーバーヘッドになる可能性がある
2. VARCHAR(80)などの長い文字列を格納するデータ列へのインデックス
   そもそもこういった列に対して検索やソートを実行することがほとんどない
3. (特定の場合の)複合インデックス
   複合インデックスにはメリットがあるが、多くの場合は冗長であったり、使用頻度が低くなりがち。また、複合インデックスは、検索条件・結合条件・ソート順において、列を定義した順 (左から右) に使わなければならない

3. インデックスが役立たないとき

インデックスにおけるもうひとつの誤りは、インデックスを使えないクエリを実行すること

例えば、last_name, first_nameからなる複合インデックスがつけられたテーブルに対して実行される以下のクエリはインデックスが役に立たない

select * from Accounts order by first_name, last_name;

上のように、インデックスを定義していてもクエリの掛け方によってはその恩恵が得られない

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

以下のような言葉を耳にしたとき

• 「このクエリを高速化するにはどうすればいい？」
• 「フィールド全部にインデックスを定義したのに、なぜ実行速度が速くならないのだろう？」
• 「データベースを遅くすると書いてあるのを何かで読んだことがある。それ以来、インデックスは使わないようにしている」

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

汎用的なデータベースを設計する必要がある場合。ただし、できる限り、十分な情報に基づいた検討をすべき。

解決策: 「MENTOR」の原則に基づいて効果的なインデックス管理を行う

最適なインデックス作成を行うためのチェックリストとして「MENTOR」というものがある

Measure (測定)

ほとんどのデータベースには、SQLのクエリ実行時間を記録する方法がある。これによって、最大のコストがかかっている操作を識別できる。
- MySQLでは、スロークエリログと呼ばれる機能

本番環境に配備したアプリケーションに対してプロファイリングを行うことで、より正確な情報を取得できる。

Explain (解析)

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

表12-2

Nominate (指名)

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

Test (テスト)

インデックスを見直し、作成後、再びクエリのプロファイリングを行う。変更が効果をもたらしたことを確認する。

Optimize (最適化)

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

使用頻度の高いインデックスをあらかじめキャッシュメモリにロードしておくことでメリットが得られる場合もある。

Rebuild (再構築)

長期にわたって行の更新や削除を行うことで、インデックスは次第に不均衡になっていく。できる限りインデックスの効率を高めたいのであれば、定期的にメンテナンスを実施する価値がある。

MySQLでは、インデックスのメンテナンスコマンド ANALYZE TABLEまたはOPTIMIZE TABLEがある。

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

この章の目的はNULLを含む列に体してクエリを書くこと
バグデータベースの Accounts テーブルには、 first_nameとlast_name列がある。
このテーブルに対してミドルネームのイニシャルをテーブルに追加するよう変更依頼を受けたので次のように対応したが、アプリケーションではフルネームを全く表示されなくなった。
ミドルネームのイニシャルを登録したユーザーは正常に表示されたが、それ以外が表示されなくなった。

ALTER TABLE Accounts ADD COLUMN middle_initial CHAR(2);

UPDATE Accounts SET middle_initial = 'J.' WHERE account_id = 123;
UPDATE Accounts SET middle_initial = 'C.' WHERE account_id = 321;

SELECT first_name || ' ' || middle_initial || ' ' || last_name AS full_name FROM Accounts;

13.1 目的：かけている値を区別する

データベースのデータに値がないような状況は、生じるためSQLでは値が無や不明、適用不能にする相当する値として予約語のNULLがある。

• 行の作成時点では不明な値にNULLを使用できる。
• 特定行において適用可能な値が他にない場合にNULLを使える
• 無効な値が入力された場合に関数はNULLを返せる
• 外部結合は一致しないテーブルの列のためにNULLをプレースホルダーとして使用する

13.2 アンチパターン：NULLを一般値として使う、または一般値をNULLとして使う

• SQLでのNULLの振る舞いに困惑する開発者は少なくない。
  • SQLはNULLをゼロ、FALSE、空文字列とは異なる特殊な値として扱う
  • NULLを含む列や式で計算する場合が挙げられる
• NULLを許容する列の検索
  • NULLを用いた比較はTRUEやFLASEではなく、unknownを返す
  • NULLを検索しようとすると以下のような間違いが起こる
    • SELECT * FROM Bugss WHERE assinged_to = NULL;
    • SELECT * FROM Bugss WHERE assinged_to <> NULL;
    • WHERE句の条件は式がTRUEであるときにのみ満たされる
• プリペアドステートメントでNULLを返す
  • SELECT * FROM Bugs WHERE assigned_to = ?;
  • パラメータに一般的な整数値を返すときには予期する結果を返す
• NULLの使用を避ける
  • 下記のクエリではunknownな値を表すために、-1を使う場合
    • hours で使えるのは数値のみ

CREATE TABLE Bugs (
    bug_id SERIAL PRIMARIY KEY,
    --他の列--
    assigned_to BIGINT UNSIGNED NOT NULL,
    hours NUMERIC(9,2) NOT NULL,
    FOREIGN KEY(assigned_to)REFERENCES Accounts(account_id)

);

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

• データにNULLが含まれる可能性がある場合は何かしらの問題が発生すると想定をするべき
• アプリケーションのテスト時にテストデータの設計でNULLを含むエッジケースを想定してテストを実施すべき

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

• NULL を使うこと自体はアンチパターンではない
• NULLを一般値として使ったり、一般値をNULLに相当するものとして使うのがよくない

13.5 解決策：NULLを一意な値として使う

• NULLの問題のほとんどはSQLの３値論理の振る舞いについてのよくある誤解に基づいている
  • プログラマーは2値論理(真偽)に慣れているから
• スカラー式でのNULL
  • プログラマーが期待する結果と予想に反する実際の結果の例

• 論理式でのNULL
  • NULL の論理式での 振る舞いを理解するための鍵は「NULLはTRUEでもFALSEでもない」という概念
  • NULLはTRUEでもFALSEでもない

• NULLの検索
  • IS NULLでは対象データがNULLならTRUEを返す
  • IS NOT NULLは対象データがNULLでない場合にTRUEを返す
  • IS DISTINCT NULLはリテラル値またはNULLを渡したいプリペアドステートメントでも使うことができる

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

• NOT NULL制約を宣言する
  • NULLがアプリケーション のポリシーに反する場合やその列においてNULLが意味をなさない場合にNOT NULL制約を宣言するのがいい
  • アプリケーション ではなくデータベースで一貫した制約を強制する方が良い良い
• 動的なデフォルト
  • COALESCE関数が便利
    • 可変長引数を取り、最初の非NULLの引数を返す
    • COALESCE関数を使えばミドルネームのイニシャルの代わりに空白文字を使う式を書ける
      • イニシャルがNULLの場合でも式全体の結果はNULLにはならない

SELECT  first_name || COALESCE(' ' || middle_initial || ' ' , ' ' ) || last_name
   AS full_name
FROM Accounts;

データ型を問わず、欠けている値にはNULLを使う

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

GROUP BYでグループ化する際に一意に定まらない値を持つ列を出力しようとしてしまい
意図した値を得られないというアンチパターン。

目的:グループ内で最大値を持つ行を取得する

上記のようなデータの場合、下記のようにクエリを書くと
エラーになるか正しく値を取得できないケースがある。

select product_id
     , max(date_reported) as latest
     , bug_id
from bugs
         inner join bugsproducts using (bug_id)
group by product_id

アンチパターン: 非グループ化列を参照する

単一値の原則
group byでグループ化した場合、グループごとに単一の行となる必要がある。
前述したクエリではproduct_idでグループ化しているが
product_idに複数のbug_idが紐づくため、単一値の原則に反する。

SQLがクエリの意図を汲んでくれるとは限らない
単一値の原則に反している場合、SQLはどの値を返せばいいのか判断がつかないため
エラーが起きたり意図しない値が返る。

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

SQLiteとMySQLではエラーが発生しない。
(MySQLでは ONLY_FULL_GROUP_BY をオプションで指定していない場合)

アンチパターンを使って良い場合

関数従属性のある列のみにクエリを実行する

関数従属性... 一方の値が決まると他の値も一意に定まる関係
ex.) 1対1の関係のテーブルなど

下記のように max(date_reported)のみであれば関数従属性があるので正しい結果が得られる。

select product_id
     , max(date_reported) as latest
from bugs
         inner join bugsproducts using (bug_id)
group by product_id

相関サブクエリを実行する

同じ製品でより新しい日付をもつもののみ検索するサブクエリを書く

導出テーブルを使用する

サブクエリの中身をJOINして導出テーブルとする。

• product_id ごとに1行のみの結果が得たい時に使う
• サブクエリよりはパフォーマンスが良い

JOINを使用する

外部結合で差集合を求める。
今回のケースでのクエリの差集合の場合

• product_id が同じ
• date_reported が新しい
• bug_id が大きい
  上記のものがない差集合を求めるクエリ

他の列に対しても集約関数を使用する

bug_id が最大の時に最新の日付を持つのであれば
bug_idにもMAX関数を使用すれば良い

グループごとに全ての値を連結する

GROUP_CONCAT関数でグループ化した際に複数レコードの値を返すカラムをまとめる。
この場合、最新日付に対応するbug_idは取得できない.
かつSQL標準には準拠していないため使用できないDBもある。

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

ランダムな結果を返すSQLクエリが必要になる場面でのアンチパターン

アンチパターン: データをランダムにソートする

ランダムな行を取得する最も一般的な方法は、ランダムにソートを行い、最初の行を取得するというもの

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

よく用いられる方法だが、以下の問題点がある

• ランダムにソートを行うため、インデックスのメリットを得られない
• 必要な行が最初の1行のみであるにも関わらず、データセット全体をソートしてしまう

データ量が増えていくにつれて、パフォーマンスの問題が生じる

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

以下のような言葉を耳にしたとき

• 「SQLは、行をランダムに返そうとすると本当に遅くなる」
• 「アプリケーションへのメモリ割り当てを増やしたいんだけど。いったん全ての行をアプリケーション上にフェッチしないと、ランダムに1行を選択できないんだ」
• 「一部のエントリは、他に比べて表示される頻度が高いように見える。この乱数発生器は均等に乱数を生成しているのだろうか？」

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

• データセットが小さい場合
• アメリカ50州のリストからランダムに州を選択したい時など、リストサイズが今後増えていく可能性が低い場合

解決策: 特定の順番に依存しない

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

SELECT b1.*
FROM Bugs AS b1
INNER JOIN (
    SELECT CEIL(RAND() * (SELECT MAX(bug_id) FROM Bugs)) AS rand_id
) AS b2 ON b1.bug_id = b2.rand_id;

• 使用できるケース
  • 1から主キーの最大値までの間の全ての値が使用されていることが確実な場合

欠番の穴の後にあるキー値を選択する

SELECT b1.*
FROM Bugs AS b1
INNER JOIN (
    SELECT CEIL(RAND() * (SELECT MAX(bug_id) FROM Bugs)) AS rand_id
) AS b2 ON b1.bug_id >= b2.rand_id
ORDER BY b1.bug_id
LIMIT 1;

乱数に一致するキー値がない老婆の問題を解決できる。ただし、欠番の1つ上のキー値が選択されやすくなる

• 使用できるケース
  • 欠番が滅多にない場合
  • キー値が均等に選ばれることがさほど重要ではない場合

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

アプリケーション側で処理する

<?php
$bug_id_list =
    $pdo->query("SELECT bug_id FROM Bugs")->fetchALL(PDO::FETCH_ASSOC);

$rand = rand( 0, count($bug_id_list) - 1 );
$rand_bug_id = intval($bug_id_list[$rand]['bug_id']);

$stmt = $pdo->prepare("SELECT * FROM Bugs WHERE bug_id = ?");
$stmt->bindValue(1, $rand_bug_id, PDO::PARAM_INT);
$stmt->execute();
$rand_bug = $stmt->fetch;

ただし以下の弱点がある

• リストのサイズが非常に大きくなってしまう可能性がある。

• クエリを2回実行する必要がある。

• 使用できるケース

  • 単純なクエリを用いて、ほどほどのサイズの結果セットからランダムな行を選択する場合

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

データセットの行数をカウントし、0と行数までの間の乱数を返す技法

<?php
$rand_sql = "SELECT FLOOR(
    RAND() * (SELECT COUNT(*) FROM Bugs)
) AS id_offset";
$result = $pdo->query($rand_sql)->fetchALL(PDO::FETCH_ASSOC);
$offset = intval($result['id_offset']);

$sql = "SELECT * FROM Bugs LIMIT 1 OFFSET :offset";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$rand_bug = $stmt->fetch;

• 使用できるケース
  • キー値が連続していることを前提にできず、かつ各行が平等に選択される必要がある場合

ベンダー依存の解決策

ほとんどのデータベース製品には、この章の目的を実現するための独自の解決策が実装されている。

16章 プアマンズ・サーチエンジン(貧者のサーチエンジン)

検索に合致するテキストを高速に取得することが求められているが、パフォーマンスとスケーラビリティに優れた技術が必要。
SQLにおいて文字列の部分一致による比較は非効率性や不正確さにつながる
=> 例えば、crashという単語で検索すると、crashed,crashes,crashingなどもヒットする。SQLを使ってこのような問題を解決するためにどうするか。

アンチパターン：パターンマッチ述語を使う

SQLには文字列比較のためのパターンマッチがある。キーワード検索等で一般的に使用されているLIKE演算子である。

SELECT * FROM Bugs WHERE description LIKE %crash%;

LIKE演算子は0個以上の文字列と一致するワイルドカードを(正規表現も)扱うことができる。前後に％をつけることで文字列中に部分一致するワードとマッチすることができる。

パターンマッチ述語はキーワード検索等で一般的だが問題点がある。

• パターンマッチはインデックスのメリットを受けることができない
• LIKE演算子を用いたパターンマッチでは意図しないマッチが生じる可能性がある
  • one と検索して、money/prone/lonelyなどもマッチする
  • キーワード検索のためのパターンマッチを使うのは決していい方法ではない

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

• LIKE述語の2つのワイルドカードの間に変数を挿入する方法を調べた時
• 正規表現で解決するには難易度の高い課題に当たっている時
• 検索でパフォーマンスが劣化している

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

• 用途がシンプルな場合（検索したいケースが単純）
• パフォーマンスを考慮しなくても良いケース

解決策：適切なツールを使用する

• SQLの代わりに専用の全文検索エンジンを使用する
• SQL標準に準拠しつつも部分文字列マッチングより効果的な転置インデックスを使用する

ベンダー拡張

主要なデータベース製品は、全文検索の要件に対する独自の解決策を用意している。これらの独自機能は標準化されておらず、ベンダー間での互換性もない。

使用するデータベース製品が1つである場合にはSQLクエリと親和性が高く、高パフォーマンスなテキスト検索を行うための最善策とも言える。

MySQLのフルインデックス

MySQLではMyISAMストレージエンジンのみがサポートするフルテキストインデックスが提供されている。
フルテキストインデックスを定義できるのはCHAR、VARCHAR、TEXT型の列。

Bugs テーブルの summary,description列の内容を含むインデックスからキーワード検索するには MATCH関数を使う。MATCH関数を使うことでMySQL4,1以降でより詳細に結果を絞り込むためにパターンにシンプルな論理式記法を使える。

SELECT * FROM Bugs WHERE MATCH(summary, description) AGAINST ('crash');

https://dev.mysql.com/doc/refman/5.6/ja/fulltext-search.html

Oracleでのテキストインデックス

CONTEXT

• 単一のテキストの列に対してインデックスを作成
  • インデックスを用いた検索にはCONTAINSを使用
  • このインデックスはデータ変更に対する一貫性が維持されない
• PARAMETER(’’SYNC(ON COMMIT)') を追加すると自動で内容を同期

CTXCAT

• 短いテキストに特化したインデックス
• 同じテーブルの他の列と組み合わせて構造化
• インデックス化対象のデータがトランザクショによって更新されてもインデックスの一貫性は維持

CTXXPATH

• XMLドキュメントをexistsNode関数を用いて検索する用途に特化

CTXRULE

• CTXRULE型のインデックスは大量の文書解析ルールを設計し、分類結果を確認できる

Microsoft SQL Serverでの全文検索

• SQL Server2000以降では全文検索がサポートされている
  • CONTAINS述語などが提供されている
• 言語、シソーラス、データ変更時の同期など複雑な構成オプションが使用可能

PostgreSQLでのテキスト検索

• パフォーマンスを最適化するにはテキスト検索可能なTSVECTORを用いてコンテンツを格納
  • TSVECTORは検索対象のテキスト列の内容と常に同期する必要がある
  • TSVECTOR列に対してGIN(汎用転置インデックス)を作らないといけない
• PostgreSQLのテキスト検索演算子(@@)を用いてフルテキストインデックスを用い全文検索が可能

SQLiteでの全文検索

• SQLiteの標準テーブルは効率的な全文検索をサポートしていない
• SQLiteの拡張機能のFTS拡張を用いることで効率的な全文検索が利用できる

サードパーティーのサーチエンジン

• データベースに依存せずにテキストの全文検索をしたい場合はデータベースから独立して動作する検索エンジンが必要
  • Sphinx Search:OSSの検索エンジンで、MySQLやPostgreSQLをうまく連携する
  • Apache Lucene: Javaアプリケーション向けの検索エンジンであるが、他の言語向けのプロジェクトもある
• 転置インデックスの自作
  • データベースベンダー依存の検索機能や個別の検索エンジン製品のインストールを行わず、データベース中立かつ効率的なテキスト検索を行いたい場合、転置インデックスとの設計を行う
    • 転置インデックスとは検索対象になりうる可能性がある全ての語のリストで、語と語を含むテキストとの多対多の関係を作る

まとめ

SQLを使ってこのような問題を解決するためにどうするか。

上記の問いに対して問題を解決するためには必ずしもSQLを使わないといけないわけではない

17章 スパゲッティクエリ

複雑な要件を一つのクエリで満たそうとしてしまい 正しくないまたはメンテナンス性に乏しいクエリになってしまうというアンチパターン。

以下は取り扱っている製品の数、
バグを修正した開発者の数、開発者一人当たりの平均バグ修正数、
修正したバグのうち顧客から報告された数を出力するクエリだが 結果が誤っている。

SELECT COUNT(bp.product_id) AS how many _products,
COUNT(dev.account_id) AS how_many_developers,
COUNT(b.bug_id)/COUNT(dev.account_id) AS avg_bugs_per_developers
COUNT(cust.account_id) AS how_many_customers
FROM Bugs b INNER JOIN BugsProducts bp
ON b.bug_id = bp.bugid
    INNER JOIN Accounts dev ON b.assigned_to = dev.account id
    INNER JOIN Accounts cust ON b.reported_by = cust.account id
WHERE cust.email NOT LIKE '%@example.com'
    CROUP BY bp.product_id;

目的:クエリの数を減らす

減らせないタスク自体の複雑性に対して解決策をシンプルにしようとして一つのクエリで済ませてしまおうとする。

アンチパターン: 複雑な問題をワンステップで解決しようとする

SQLが表現力に優れているがゆえに一つのクエリで済ませてしまおうとする。
プログラミングをしている時のように分割して対処すべきである。

意図に反した結果

一つのクエリで処理しようとしてしばしばデカルト積が生じる。 これは二つのテーブルを結合する時に関連を制限する条件がないときに生まれるもので
結合時に全ての行をペア同士にして結果が予測よりはるかに膨大になってしまう。 修正済みのバグが11件,未修正のバグが7件 と知っている時でも下記のようなクエリを書くと

SELECT p.product_id,
       COUNT(f.bug_id) AS count_fixed,
       COUNT(o.bug_id) AS count_open
FROM BugsProducts p
         INNER JOIN Bugs f ON p.bug_id = f.bug_id AND f.status = 'FIXED'
         INNER JOIN BugsProducts p2 USING (product_id)
         INNER JOIN Bugs o ON p2.bug_id = o.bug_id AND o.status = 'OPEN'
WHERE p.product id = 1
GROUP BY p.product_id;

このようになる。

この例ではBugsProductsテーブルがBugsテーブルの二つの部分集合と結合され 11件の修正済みのバグがと7件の未修正のバグが全てペアになってしまっている。 GROUP BY を外すとどのようになっているかよくわかる。

さらなる弊害

複雑なクエリにしてしまうと、作成/修正に多くの労力がかかり
また、最適化や高速な実行が難しくなるため、シンプルなクエリを複数実行するようにした方が良い場合がある。

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

• SUMやCOUNTの結果がありえないほど大きくなっている
• SQLが複雑すぎてかなりの時間がかかっている場合
• やたらとDISTINCTで重複を防ごうとしてしまう場合

アンチパターンを使って良い場合

単一のクエリをデータソースとしてアプリケーションに表示する
ライブラリやフレームワーク,BIツールを使っていると一つのクエリで済ませたくなってしまうが
基本的には要件の調整をした方が良い。
(複数のレポート出力でも見てもらえるようにするなど)

解決策: 分割統治を行う

結果が同じなら単純な方を選ぼう

ワンステップずつ

意図しないデカルト咳が生じているテーブル間に論理的な結合条件が見つからない場合
そもそも条件が存在していない可能性がある。 その場合は分割する必要がある。 デカルト積の例でのクエリの場合、以下のようにシンプルな二つのクエリに分けられる。

SELECT p.product id, COUNT(f.bug_id) AS count_fixed
FROM BugsProducts p
         LEFT OUTER JOIN Bugs f ON p.bug_id = f.bug_id AND f.status = 'FIXED'
WHERE p.product_id = 1
GROUP BY p.product_id;

SELECT p.product_id, COUNT(o.bug_id) AS count_open
FROM BugsProducts p
         LEFT OUTER JOIN Bugs o ON p.bug_id = o.bug_id AND o.status = 'OPEN'
WHERE p.product_id = 1
GROUP BY p.product_id;

UNIONを用いる

結果をソートする必要がある場合などどうしても一つのクエリにまとめたい場合
UNIONが使用できる。

(SELECT p.product id, 'FIXED' AS status, COUNT(f.bug_id) AS bug_count
 FROM BugsProducts p
          LEFT OUTER JOIN Bugs f ON p.bug_id = f.bug_id AND f.status = 'FIXED'
 WHERE p.product_id = 1 CROUP BY P.product_id)
UNION ALL
(SELECT p.product_id, 'OPEN' AS status, COUNT(o.bug_id) AS bug_count
 FROM BugsProducts p
          LEFT OUTER JOIN Bugs o ON p.bug_id = o.bug_id AND o.status = 'OPEN'
 WHERE p.product_id = 1
 GROUP BY p.product_id)
ORDER BY bug_count DESC;

結合する場合は行がどのクエリでの結果なのかわかるようにしなければならないことに注意
また、bugcount_or_customerid_or_nullのようは汎用的すぎる列名を用意してしまう場合は
互換性のないクエリをまとめようとしていると考えるべき。

CASE式とSUM関数を組み合わせる

条件ごとに集約を行いたい場合はCASE式とSUM関数を組み合わせることでも実現できる。

SELECT p.product_id
     ,
     , SUM(CASE b.status WHEN 'FIXED' THEN 1 ELSE O END) AS count_fixed
     , SUM(CASE b.status WHEN 'OPEN' THEN 1 ELSE O END)  AS count_open
FROM BugsProducts p
         INNER JOIN Bugs b USING (bug_id)
WHERE p.product_id = 1
GROUP BY p.product_id;

上司の問題を解決する

冒頭のエピソードのような場合、それぞれを分割したクエリで出力するのが最善の解決策

課が取り振っている製品の数:

SELECT COUNT(*) AS how_many_products
FROM Products;

バグを修正した開発者の数:

SELECT COUNT(DISTINCT assigned_to) AS how_many_developers
FROM Bugs
WHERE status = 'FIXED';

開発者1人当たりの平均バグ修正数:

SELECT AVG(bugs_per_developer) AS average_bugs_per_developer
FROM (SELECT dev.account_id, COUNT(*) AS bugs_per_developer
      FROM Bugs b
               INNER JOIN Accounts dev
                          ON b.assigned_to = dev.account id
      WHERE b.status."FIXED"
      GROUP BY dev.account_id) t; 

修正したバグの中で顧客から報告されたバグの数:

SELECT COUNT(*) AS how_many_customer_bugs
FROM Bugs b
         INNER JOIN Accounts cust
                    ON b.reported by = cust.account_id
WHERE b.status = 'FIXED'
  AND cust.email NOT LIKE '%example.com'

SQLを用いたSQLの自動生成

複雑なクエリを分割すると似たようなクエリをいくつも書くようなケースがある
そのような場合はコード生成を行うと良い

SELECT CONCAT('UPDATE Inventory ',
'SET last_used = ''', MAX(u.usage_date),''''
'WHERE inventory_id = ', u.inventory_id, ';') AS update statement
FROM ComputerUsage u
GROUP BY u.inventory_id;

すると以下のようなクエリが作成される

UPDATE Inventory SET last_used = '2002-04-19' WHERE inventory_id = 1234;
UPDATE Inventory SET last_used = '2002-03-12' WHERE inventory_id = 2345;
UPDATE Inventory SET last_used = '2002-04-30' WHERE inventory_id = 3456;
UPDATE Inventory SET last_used = '2002-04-04' WHERE inventory_id = 4567;

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

ワイルドカードや暗黙的な列の指定によって生じるアンチパターン

アンチパターン: ショートカットの罠に陥る

ワイルドカード (SELECT *) を使用することによって、タイプ数を減らすことができる

SELECT * FROM Bugs;

INSERT文においても、以下のように列を明示的に指定せずに書くことで、より簡潔になる

INSERT INTO Accounts VALUES(DEFAULT, 'bkarwin', 'Bill', 'Karwin' ...#省略);

しかし、この方法には以下のような弊害がある。

リファクタリングにおける問題

例えば、Bugsテーブルに新規にdate_due列を加えた場合、列を明示的に指定せずに書かれたINSERT文はエラーを返すようになる。
これは、テーブルに追加されたdate_due列によって、クエリで指定されている列数とテーブルに列数が異なってしまうため。

INSERT INTO Accounts VALUES(DEFAULT, 'bkarwin', 'Bill', 'Karwin' ...#省略);

--> SQLSTATE 21S01: Column count doesn't match value coutn at row 1

暗黙的な列を使ったINSERT文では、テーブルに列が定義されている順番と同じ順番で、全ての列に値を与えなければならない。列定義に変更があった場合には、クエリがエラーを返すか、さらに悪い場合には、謝った列に値が格納されてしまうことになる。

また、列名を知らずにSELECT *を実行する場合、列は定義順に基づいて参照される。

<?php
$stmt = $pdo->query("SELECT * FROM Bugs WHERE bug_id = 1234");
$row = $stmt->fetch();
$hours = $row[10];
>

しかし、例えば列が削除された場合、Bugsテーブルにhours列は添字10の位置に存在しなくなったため、以下のコードは目的と異なる値を格納してしまうことになる。

$hours = $row[10];

ワイルドカードを使用している場合、列の追加、削除、名前変更などを行うと、クエリ結果に生じた変化をコードがうまく扱えなくなる場合がある。

隠れた代償

ワイルドカードを使うのは便利だが、パフォーマンスとスケーラビリティに悪い影響を及ぼす場合がある。クエリが多くの列をフェッチするようになるため、多くのデータがアプリケーションとデータベースサーバーの間を行き来しなくてはならない。

求めなければ得られない

SQLでは「不要な列以外のすべての列」を意味する構文はサポートされていない。ワイルドカードで暗黙的に全ての列を対象にするか、明示的に列を指定するかのどちらかしかない。

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

プロジェクトが以下のような状況になった時

• アプリケーションに障害が発生した。データベースの結果セットを古い列名で列を参照していたことが原因だ。コードを全て修正したつもりだったが、修正漏れがあるかもしれない
• 何日もかけて、ようやくネットワークのボトルネックを突き止めた。原因は、データベースサーバーへのトラフィックが多いことだった。統計値によれば、クエリは平均2MB以上のデータをフェッチしているが、実際に表示しているのはその10分の1だった。

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

アドほっくなSQLを素早く書きたい場合。例えば、ある解決法を試してみたいときや、現行システムのデータを診断したいときなど、1回しか使用しないクエリでは、保守性の低さはそれほど問題にならない。

解決策: 列名を明示的に指定する

ワイルドカードや暗黙的な列指定を使わずに、必要な列名は明示的に指定するようにする。

誤りの防止

列を明示的に指定することで、上述したエラーや混乱が発生しにくくなる。

• テーブル定義の列の順番が変更された場合でも、クエリ結果の列の位置は変わらない。
• テーブルに列が加えられた場合でも、クエリ結果に影響はない。
• テーブルから列が削除された場合、クエリはエラーを返す。ただし、これは良いエラー(修正すべきコードをすぐに特定できるため)。

それは多分、必要ない (YAGNI: You Ain't Gonna Need It)

SQLでワイルドカードを使用しないようにすると、使うかどうか分からない列をなるべく作らないようにしようという意識が高まる、という効果もある。

ワイルドカードを使えない局面はいずれ訪れる

列エイリアスや関数を利用したり、選択リストから特定の列を除外する場合など、クエリで各列を個別に扱わなければならない時が、いつか必ず訪れる。初めからワイルドカードを使っていなければ、後でクエリ変更が楽になる。
必要な列だけ指定するようにしましょう。

19章 リーダブルパスワード(読み取り可能パスワード)

目的

パスワードを使用するアプリケーションでは、ユーザーがパスワードを忘れることがつきもの
こうしたユーザーのためにパスワードのリカバリーとリセットを行えるようにしておく

アンチパターン

パスワードを平文で格納する。
絶対ダメ。攻撃された場合はもちろん、悪意のある内部の人間が絶対にいないとは言い切れない。

• アプリケーションクライアントからデータベースに送信されたSQLを傍受する
• データベースサーバー上のSQLクエリログを探す

また、認証時にも平文同士で比較することになり、たとえユーザーが間違ったパスワードを入力していたとしても非常に危険となる。
平文のパスワードをメールで送るのも非常に危険。

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

パスワードのリカバリーを行ってユーザーへ送信できるアプリケーションはパスワードを平文または復元可能な暗号化によって格納する
これこそがアンチパターンで、アプリケーションが読み取れるってことは攻撃者も不当にパスワードを読み取れる

アンチパターンの解決策

解決策はパスワードのハッシュにソルトを加えて格納すること(ソルトはハッシュ関数に渡す前にパスワードを連結する適当な文字列のこと)
パスワード文字列を不可逆な暗号化文字列に変換して比較するようにすれば良い

不可逆な変換の簡単なものは剰余の計算である
(適当な数値) % 3 = 2であるとき、この適当な数値が何かわからない。5でもいいし8でもいい。「結果から初期値がわからない」のが不可逆な変換。

ユーザーのパスワードに適当な文字列を付与してからハッシュ化することで元の文字列がさらに判別しにくくなると言う寸法

また、ハッシュ化する処理をSQLで行う場合、平文で扱う瞬間が存在してしまうため、アプリ側でハッシュ化する処理を実装したい
また、パスワードがわからなくなった場合はリカバリーするのではなくリセットするのが絶対

暗号化技術は日々進化していて、PBKDF2やBcryptみたいな技術の採用検討を行う

20章 SQLインジェクション

動的にSQLを組み立てようとして、値を柔軟にSQLに組み込むものの
受け入れる値の検証が不十分なためにデータに意図しない操作がされてしまうというアンチパターン。

目的:動的SQLを記述する

クエリをアプリケーションデータによって変化させるために
アプリケーションでSQLを組み立てる。

アンチパターン: 未検証の入力をコードとして実行する

クエリ文字列に動的に挿入された文字列が
開発者の意図しない方向でクエリを改変したことによって
SQLインジェクションが生じる。

<?php
$sql = "SELECT * FROM Bugs WHERE bug_id = $bug_id";
$stmt = $pdo->query($sql);

この例であれば
$sqlの変数に1234; DELETE FROM Bugsと入力されている場合
DELETE FROM Bugsも実行されてしまう。

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

DBに接続されている、ほぼ全てのアプリケーションは
動的にSQL文を構築するし、文の一部を文字列連結や変数の挿入によって構築する場合
SQLインジェクションの攻撃を受けるリスクにさらされる。
そのためDBに接続されているアプリケーションの開発に携わる場合
多かれ少なかれ、SQLインジェクションの可能性を考えた方が良い。

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

ない

解決策

入力値のフィルタリング

ユーザーからの入力に危険な文字列が含まれているかどうか探るより
その入力で無効な部分を初めから取り除くようにすべき。
つまり、クエリの構築に整数が必要な場合は入力内容から整数のみ使用するようにする。

PHPではfilter拡張が使える

<?php
$bugid = filter_input(INPUT_GET, "bugid", FILTER_SANITIZE_NUMBER_INT);
$sql = "SELECT * FROM Bugs WHERE bug_id = $bugid";
$stmt = $pdo->query($sql);

数値のような単純なケースでは型キャスト関数を使う。

<?php
$bugid = intval($_GET["bugid"]);
$sql = "SELECT * FROM BugS WHERE bug_id = $bugid"
$stmt = $pdo->query($sql);

正規表現でもって、安全な部分文字列を取得する方法もある。

<?php
$sortorder = "date_reported"; // default

if (preg_match("/([_[:alnum:]]+)/", $_GET["order"], $matches)) {
  $sortorder = $matches[1];
}
$sql = "SELECT * FROM Bugs ORDER BY $sortorder";
$stmt = $pdo->query($sql);

動的値のパラメータ化

動的な部分がシンプルな値で構成されているときはプリペアドステートメントを用いることができる。

<?php
$sql = "UPDATE Accounts
SET password_hash = SHA2(?, 256)
WHERE account_id = ?";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(1, $_REQUEST["password"],PDO: :PARAM_STR);
$stmt->bindValue(2, intval($_REQUEST["userid"]), PDO: :PARAM_ INT);
$stmt->execute();

上記のようにしていれば悪意のある値が渡されてもエラーが起きるだけで
任意のクエリが実行される状態にならない

UPDATE Accounts
SET password_hash = SHA2('xyzzy', 256)
WHERE account_id = '123 OR TRUE';

動的値を引用符で囲む

対策としてほぼ全ての場合でプリペアドステートメントが有効だが
パラメータで置き換えられているクエリに関して
オプティマイザがおかしな判断をして非効率なクエリの実行をしてしまうケースがある。

まれなケースだがこのような場合は
SQL文の中に十分注意して文字列を引用符で囲んで
直接値を埋め込む方が良い場合もある。

<?php
$quoted_status = $pdo->quote($_REQUEST["status"]);
$sql = "SELECT * FROM Accounts WHERE account_status = $quoted status";
$stmt = $pdo->query($sql);

こうした場合はデータアクセスライブラリの枯れた関数を用いるのが良く
PHPであればPDO::quateにあたる。
こういったセキュリティの問題を完全に理解するまでは絶対に自分で実装してはいけない。

ユーザーの入力をコードから隔離する

プリペアドステートメントではテーブル/列の識別子や予約後には対応できないため
それらの部分を動的にするには別の解決策が必要になる。

例えばユーザーがソートしたいときに

<?php
$sortorder = $_REQUEST["order"];
$direction = $_REQUEST["dir"];
$sql = "SELECT * FROM Bugs ORDER BY $sortorder $direction";
$stmt = $pdo->query($sql);

このようにしてしまうとどのようなパラメーターでも遅れてしまう。
その代わりにリクエストの値を用いて、すでにアプリケーションで定義済みの値に置き換える方法がある。

1. ユーザーの入力値をキー、SQL列名を値にマッピングする$sortorder配列と
   ユーザーの入力値をキー、SQL予約語ASC、DESCを値にマッピングする $directions配列を宣言する

$sortorders = array( "status" => "status", "date" => "date_reported" );
$directions = array( "up" => "ASC", "down" => "DESC" );

2.ユーザーの入力値が配列内に存在しない場合のデフォルト値を設定。

$sortorder = "bug_id";
$direction = "ASC";

3. ユーザーの入力値が、宣言した配列のキーと一致する場合、対応する値を使用する。

このようにして危険のないように予約語を動的に埋め込める。

この方法には以下のようなメリットがある。

• ユーザーの入力内容が直接連結されないためSQLインジェクションのリスクが減る
• SQL文のどの部分でも動的にできる
• 入力値の妥当性の検証を簡単に行える
• UIからDBの詳細を隠蔽できる

コードレビューしてもらう

以下のガイドラインに従うのが良い

1. 変数や文字列連結、文字列置換によって構築されているSQL文を特定する
2. SQLステートメントで使われている、すべての動的な要素を特定する
   (ユーザー入力、外部ファイル、環境まわり、外部のウェブサービス、サードパーティーのコード、データベースから取得した文字列など)
3. フィルター、バリデーター、マッピング配列などを用いて、これら変換する。
4. プリペアドステートメントまたは検証済みのエスケープ関数を用いて、SQLステートメントと外部データを組み合わせる。
5. 他にもストアドプロシージャなどの、動的SQLステートメントが隠れている場所がないか確認する。

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

疑似キーの欠番を埋めようとしてしまうアンチパターン

アンチパターン: 隙間を埋める

欠番を埋めようと考えた場合、たいてい、次の2つの方法が用いられる

欠番を割り当てる

疑似キーの自動裁判メカニズムを用いて、新しい主キーを割り当てる代わりに、新しい行に検出した最も値の小さい欠番番号を割り当てる方法
しかし、この方法では以下のような不要なクエリを実行する必要がある

SELECT b1.bug_id + 1 AS max_bug_id
FROM Bugs b1
LWFT OUTER JOIN Bugs AS b2 ON b1.bug_id + 1 = b2.bug_id
where b2.bug_id IS NULL
ORDER BY bi1.bug_id LIMIT 1;

3章 IDリクワイアドのコラム: シーケンスの特殊なスコープでも説明されている通り、上記の方法では2つのアプリケーションが同時に値を探そうとすると、平行処理の問題が生じ、片方は成功するが、もう一方はエラーになる。非効率かつエラーを招きやすい。

既存行に番号を振り直す

欠番を埋めて、全ての値が連続するように、既存行のキーを更新する方法
既存行に番号を振り直すためには、

• 欠番のキー値を特定する必要がある
• UPDATEステートメントを実行する必要がある
• 欠番が多い場合には、何度も繰り返さなければならない
• その行を参照していた全てのレコードにも反映する必要がある
  • 外部キー制約を一時的に無効化し、全ての子レコードを更新し、再び外部キー制約を有効化する必要がある
• 仮にこの作業を完了させたとしても、次にデフォルトの擬似キージェネレーターで生成される値は、最後に生成した値より1つ大きな値であるため、容易に新しい欠番が生まれる

上記のように、大変な労力を伴う上に、間違いの起こりやすいプロセスである、当然このような作業は避けるべき。

データ不一致の元

主キー値を再利用するのは、良い考えではない。欠番は、正当な理由による行の削除やロールバックの結果だから。
使用されていないからといって、疑似キーを再利用してはならない

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

以下のような発言を耳にした時

• 挿入をロールバックした後で、自動生成されたID値を再利用するにはどうすればいい？
• bug_idの4番に何が起こったの？
• 使用されていない最初のIDを取得するクエリはどうやって書いたらいい？
• 番号が足らなくなったらどうしよう？

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

疑似キー値の変更を正当化する理由はない。なぜなら、疑似キーの値それ自体が重要な意味を持つべきではないから。

解決策: 疑似キーの欠番は埋めない

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

行のナンバリング

行番号と主キーを混同してはいけない
主キーがテーブルにおける1行を識別するのに対し、行番号は結果セットにおける行の順序番号を示す。

GUIDの使用

同じ数を複数回使用しないために、ランダムな疑似キー値を生成する方法もある
一部のデータベースはこの目的のために、グローバル一意識別子 (Globally Unique IDentifier: GUID) をサポートしている
GUIDhは128ビット (32個の16進数) の疑似乱数であり、同じ識別子が生成される可能性が極めて低いために、事実上一意であると見なされる

GUIDは、従来型の疑似キージェネレーターと比べて、少なくとも以下2つの利点をもたらす

• 複数のデータベースサーバー間で、重複した値を生成することなく、並行して疑似キーを生成できる
• 欠番に関する不満を誰も口にしなくなる

ただし、2つ目の利点はデメリットにもなる。

• 値が長いため、タイプしづらくなる
• 値がランダムなので、値からパターンを推測したり、値の代償から生成された順番を推測したりすることができない
• GUIDの格納には16バイトが必要であり、一般的な4バイト整数の疑似キーと比べて多くのスペースが必要になり、実行時間も長くなる

最も重要な問題

疑似キーの欠番を埋めろという上司からの要求を、どうやって断るのかという問題

• 技術について説明する
• コスト見積もりを提示する
• 自然キー (ナチュラルキー) を使用する

22章 シー・ノー・エビル(臭いものに蓋)

目的：簡潔なコードを書く

仕事がクールであるということはコードの量が少ないということであり、よりエレガントであるというわけ
簡潔なコードにはより合理的な幾つかの理由があるから不要なコードをできるだけ削除する

• より短い時間でアプリケーションのコーディングを行える
• テスト、文書化、ピアレビューの対象となるコードの量が減る
• コードが少ないからバグが混入する可能性も少なくなる

アンチパターン：肝心な部分を見逃す

2つのパターンがある
①データベース API の戻り値を無視すること
②アプリケーションコード内に点在するSQL しか読まないこと
2つのパターンで、開発者は簡単に入手できる情報を見逃してしまってエラーに気づけない

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

IDEで関数の戻り値を無視していたり、関数呼び出しの際にチェック例外の処理を強調してくれるのでそういうところでチェック

アンチパターンの兆候例

• データベースにクエリを発行後にプログラムがクラッシュする
• SQL を作るコードから解析しようとしている(SQL 自体を見ない)
• エラー処理を減らそうとする

アンチパターンを使っていい場合

エラーに対して何もしなくていい時はエラーチェックを省略できる
オブジェクト指向言語の例外の仕組みは処理の責任のない例外を呼び出し元にもどせる

解決策：エラーから優雅に回復する

• ミスの原因に気づくための仕組みといかに早く回復する仕組みを入れる
• データベース API 呼び出しの戻り値と例外のチェックを入れる
• デバッグに SQL クエリを構築するコードではなく実際に構築された SQL クエリを使用することで、単純なミスを簡単に見つけられる(SQL をログに出してクエリを監視するなど)

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

ベストプラクティスに従ったほうがいいがDB周りは見過ごされがちであるというアンチパターン。

目的:ベストプラクティスを採用する

VCSを用いたバージョン管理や自動テスト、ドキュメンテーションなどのベストプラクティスに従うのが良い
そうでなければプロジェクトが失敗しやすいため。

アンチパターン: SQLを特別扱いする

アプリの開発ではベストプラクティスに従っていても
データベース開発においては従わないケースが多い。
このような特別扱いをディプロマティック・イミュニティ(外交特権)と名付けた。

以下のような理由が考えられる。

• DBAが特別扱いされる
• SQLの他のプログラミング言語との性質の違い
• SQLではアプリ開発言語のようにIDEの使用が一般的でない
• アクセス権付与の問題

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

• 今までとは軽量なエンジニアリングプロセスの導入
• DBAにはツールの説明を省く
• DBのドキュメントの不在
• DBスキーマの差分検知のツールを欲しがる時

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

確実にアドホックな用途である時。
使用後に直ちに削除できるかどうかで判断する。

解決策: 包括的に品質管理問題に取り組む

DB開発における品質保証は文書化、バージョン管理、テスティングのベストプラクティスに従うのが良い

文書化

以下のものは文書化しておいてほうが良い

• ER図
• テーブル, 列, ビューが何を表すものかの説明
• 関連の言語化
• トリガー
• ストアド
• SQLセキュリティ
  • ユーザーのアクセス権
  • システムレベルセキュリティ対策
• DBインフラ構成
• ORM
  • どのようなビジネスルールが実装されるか

バージョン管理

DBサーバーが完璧に故障した際の復旧のしやすさのため。
以下のようなファイルをバージョン管理するのが望ましい。

• データ定義スクリプト
  • CREATE TABLE ~~ など
• トリガーとプロシージャ
• ブートストラップデータ(初期データ)
• ER図とドキュメント
• DB管理スクリプト

テスティング

アイソレーションテストはDBにも適用できる。
構造と振る舞いをアプリケーションコードとは独立させて確認するのが良い。

• テーブル, 列, ビュー
  • 存在すべきオブジェクトが存在するか
  • 削除されているべきオブジェクトが削除されているか
• 制約
  • 否定テスト
  • 制約に対して違反するクエリを実行しエラーが返ることを確認
• トリガー
  • トリガーが正常な動作をするかのテスト
• ストアド
  • アプリのユニットテスト同様にテストする
• ブートストラップデータ(初期データ)
  • 存在確認のテスト
• クエリ
  • 返ってくる結果が正しいかのテスト
• ORMを使用したクラス
  • DBを抽象化したテストで入力に対して想定通りの振る舞いをするかどうか

複数のブランチを扱う

開発/テスト/ステージ/デプロイ の環境(ブランチ)ごとにDBを用意できれば
開発者は本番と同一の環境で開発とテストを行える

24章 マジックビーンズ (魔法の豆)

アクティブレコードについて、モデル＝アクティブレコードとして利用してしまうアンチパターン

アンチパターン: モデルがアクティブレコードそのもの

アクティブレコードはモデルをデータベーススキーマに強く依存させてしまう

簡素なアクティブレコードクラスは、1つのテーブルまたはビューを表現する
これはつまり、

• データベースをリファクタリングして新たなデータ構造を表す場合には、モデルクラスだけでなく、そのモデルクラスを使うアプリケーションのコードも変更する必要がある
• アプリケーションで新たな画面を処理するコントローラを加える場合、モデルとのやりとりを行うために、既存のコードによくにコードをコントローラに書く必要がある

アクティブレコードはCRUD機能を公開してしまう

各モデルはアクティブレコードの基底クラスから派生しており、基底クラスにあるCRUDメソッドを公開してしまう。
つまり、CRUD操作メソッドを通じて直接データを更新してしまえるため、不適切な操作が実装される可能性がある。

アクティブレコードはドメインモデル貧血症をもたらす

モデルを基本的なCRUDメソッド以外の振る舞いを持たないシンプルなデータアクセスオブジェクトとして扱ってしまうと、モデルの外部でビジネスロジックのコーディングが必要になる。結果として複数のコントローラクラスにロジックが書かれ、モデルの凝集度が低下する (ドメインモデル貧血症)。

例えば、Bugs、Accounts、Productsなどの複数のアクティブレコードとそれらを扱う複数コントローラクラスがある場合、図24-2のように各コントローラクラスには同じようなロジックが書かれることになり、仕様変更の際にはコードの多くの場所を変更しなければならない。

マジックビーンズのユニットテストは困難

モデルのテスト

• モデル自身の振る舞いのテストをデータベースアクセスから分離できない。
• テスト用のデータベースの準備と後始末は複雑であり、時間がかかる。

ビューのテスト

• モデルが関係する動的なHTML要素のテストをするとき、フレームワークは複雑で時間のかかるコードを常に実行しなければならない。

コントローラのテスト

• 単なるデータアクセスオブジェクトでしかない設計のモデルは、複数のコントローラにおけるコードの重複を招くため、テストも複雑になる。
• ビジネスロジックのテストを行いたいだけなのに、HTTPリクエストを作成する必要が出てしまう。

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

以下のような発言を耳にしたとき

• モデルにカスタムSQLクエリを渡すにはどうすればいい？
• 複雑なモデル操作を全てのコントローラにコピーすればいいのだろうか、それとも、親の抽象コントローラクラスに1回だけコーディングすればいいのだろうか
• モデルのユニットテストを行うために、データベースフィクスちゃをもっと書かなければならない

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

シンプルなアプリケーションやプロトタイプ作成においては、アクティブレコードパターンは単純で素早くコードを書くことができる点で優れている。
ただし、テスト可能性や保守性が下がり、技術的不負債が生じるため、リファクタリングは必須。

解決策: モデルがアクティブレコードを「持つ」ようにする

モデルを理解する

以下に示すガイドラインは、特にモデルをデータアクセスオブジェクトから分離する際の設計判断において参考になる

情報エキスパート

• 操作の責任を持つオブジェクトは、その操作を果たすために必要な全てのデータを持つべき。
• アクティブレコードは1回につき1つのテーブルを扱うことに特化しているため、複数のデータベースアクセスオブジェクトを集約して、複合的な操作を行うクラスを別に作成する必要がある。

生産者

• モデルがデータベース内のデータを扱う方法は、外部に公開されない、内部実装の詳細であるべき。
• コントローラとビューは、ドメインモデルのインターフェースを使用すべきで、データ取得や格納の方法を知るべきではない。

疎結合

• 論理的に独立しているコードは、分離して疎結合化を行うことが重要。

高凝集性

• ドメインモデルクラスのインタフェースは、CRUD操作などではなく、その意図を示すべき。
• モデルクラスを、モデルが使用するデータアクセスオブジェクトから分離すると、同じデータアクセスオブジェクトを使う複数のモデルクラスを設計でき、さらにテーブルに対する操作全てをアクティブレコードのサブクラスに詰め込むよりも良い凝集性を得られる。

ドメインモデルの使用

MVC本来の意味合いにおけるモデルとは、オブジェクト指向によって対象ドメインをアプリケーションの中に表現すること。つまりモデルとは、アプリケーションのビジネスロジックを実装する場所で、データベースとのやりとりはモデルの内部的な実装の詳細。

データベースの構造ではなく、アプリケーションの概念に基づいてモデルを設計すると、データベースの操作をモデルクラスに完全に隠蔽して実装できるようになる (図24-3)。

プレーンなオブジェクトのテスト

• モデルからデータアクセスオブジェクトを分離させると、データアクセスオブジェクトのスタブやモックを作成できるようになり、モデルのユニットテストをデータベースから独立しておこうなことができる。
• コントローラのテストも、コントローラのコードがシンプルになるため、多くの分岐をテストする必要がなくなる。

25章 砂の城

IT におけるインシデントからシステムを守るためのテクニックが存在しており、その話を扱う

目的

基幹系システムやウェブサービスでも無停止が当たり前になっている => 停止が収益の減少に直結したり、信頼性を損ねる

アンチパターン: 想定不足

• サービスを安定稼働させるためにはトラブルは突然起きるものとして想定し、そのための対応をどう撮るかの検討も必要
• 事象に応じて自動的にサービスを継続させる仕組みや対応手順を決めておくことで被害が最小限に抑えられる
  • 意外と対応がないのが障害発生時の対処をどうするかのポリシー策定
  • ポリシー策定にはデータの収集が必要で、問題の対処もできる
  • データの収集ができない時は障害の原因究明ではなく回避策に注力

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

• データサイズが1ヶ月で3倍になった
  • 想定以上の速さでデータが増加することはよくあるが、想定外の事象も起きやすいため、対処を検討する必要あり
• データベースへの更新でデッドロックが起きる。製品のバクじゃないか？
  • 適切な知識を身につけ、例外処理を実装せよ
• 問題を解決するためにベンダーが要求しているデータは本番の負荷が高すぎるので採取できない
  • 問題解決のためのデータの採取にはシステムのオーバーヘッドがつきものであるため、RDBMS製品ではどのようなデータ採取ができ、どの程度のオーバーヘッドがあるか事前に調べておく
  • 予め準備しておけばいざという時に慌てない
• 最近マシンをアップグレードした。だから性能の問題とは無縁だろう
  • マシンを変えたからと言って性能の問題とは無縁ではない
  • 非効率なクエリや突発的なスパイクにインパクトがないということはなく、安心できるものではない
  • 将来的なキャパプラも考える

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

• サービス規模に応じて適切なアーキテクチャを考えたり、仕組みや体制、対策を講じていく
  • そのためにシステムが停止することでの損失、事業計画への影響、停止時間の許容できるかと言ったことを考慮して検討する

解決策

• どのようなトラブルが起こりうるかを可能な限り想定しておくこと
• サービスの運用を始めるにあたって実施・想定しておくべき代表的な対策について紹介する
• ベンチマーク
  • 大きなトラフィックが予想されるシステムでは事前にどの程度まで処理が可能なのかということをベンチマークしておく
  • ベンチマークをやるのに必要なこと3つ
    • 現実に即したシナリオを用いること
    • 実際のアプリケーション で使うデータと同サイズのデータでやること
    • 実際のシステムと同じハードウェアやOSを使う
• テスト環境の構築
  • 本番環境で利用しているシステムと同じ構成のテスト環境を1つ用意するのが理想
• 例外処理
  • データベース 管理システムを用いたアプリケーション では適切な例外処理を実装することが必須
    • データベース の接続が途切れたり、DBMSの固有なエラーへの対処が必要
• バックアップ
  • データが破壊されなければサービスは止まらないからバックアップを万全にする
• 高可用性
  • マシンが故障すること考慮してマシンを冗長化する仕組みを考えておく必要がある
    • コストと停止時間を天秤にかけて、高可用な構成にする
• ディザスタリカバリー
  • マシンの冗長化だけでなく災害が起きたときのデータが継続的に複製し続けるレプリケーションなどのディザスタリカバリーも考慮すると良い
• 運用ポリシーの策定
  • システムの限界を超えた事象も起こりうる
    • 高可用性の限界を超えた障害
      • 限界を超えた時、クラスター運用ではなく単一マシンで動かすなどの対処も考えておく
    • 問題の調査
      • 再現性がないものは調査が難航になる
      • 調査がどの程度の本番影響許容するかを確認する
    • 性能劣化