Open
6

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

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

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

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

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の最大長を話している時
  • 正規表現で文字列の一部で取得する時
  • エントリの文字列で区別できる文字を探す時

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

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

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

1つのテーブルの中のデータ型を変えるんじゃなくて別テーブルを作る。これによって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_idproduct_idの組み合わせが常に一意であることを保証しなくなってしまう。

    bug_idproduct_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文などで処理する
ログインするとコメントできます