hirapi's blog

ちゃんとしたふりをする

『SQLアンチパターン』を読んだ①「I部 データベース論理設計のアンチパターン」

前の職場で読書会があったものの何回かしか参加できず通読できなかった『SQLアンチパターン』を読んでいる。
毎日1〜2個読んで、本文や調べたことをnotionにまとめていたのだけど、せっかくなのでその一部をこちらにも転記する。
引用の域を超えてしまうので本の内容をまとめた部分は端折って、感想とその周辺の最低限の内容だけを転載した(ら元のメモの2割くらいになった)。

www.amazon.co.jp

1〜8章で共通していた視点

  • メタデータとデータを分けて考える
    • データにメタデータを混入させない:テーブル名やカラム名を値として格納しない
    • メタデータにデータを混入させない:属性の値を元にテーブルやカラムを作らない
  • 参照整合性・一意性・データ型はデータベース側で保証する
    • 外部キー制約やユニーク制約をちゃんと貼る、貼れるように設計する
    • インデックス等のオーバーヘッドより、余計なSELECTやアプリケーション上の操作のほうが無駄
    • 常にアプリケーションコードを介してデータ操作が行われるとは限らない
  • 将来的に増えるもの・変わりうるものを想定して決める
    • テーブルやカラムの数には上限がある
    • 個別のテーブル名やカラム名を列挙するようなクエリは保守がしにくい

各章

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

  • 交差テーブル、solidusで慣れっこだぜ
  • 「ベンダー中立」データベースによって挙動や構文が異なるとそのDBベンダーにロックインされてしまうのでよくない、なるほど

2. ナイーブツリー(素朴な木)

  • 経路列挙モデル WHERE '1/4/6/7/' LIKE c.path || '%' で親を取得
  • 閉包テーブル:1つのパスを表現するテーブルを作る
    • 先祖IDカラムと子孫IDカラムを持つ
      • 自己参照(先祖IDと子孫IDが同一)のレコードも入れる
      • 「ノード1が親、ノード2が子」のパスは 先祖ID = 1 子孫ID = 2
    • けっきょく交差テーブルということか
    • 関係性も1つのオブジェクト、みたいにとらえるとイメージしやすいかも
    • solidusでやたら中間テーブルあって大変な思いをしたけど、その分柔軟だったな

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

  • アンチパターン:元のモデルに、主キーになりうる意味のある値(自然キー)が存在する場合にも、全てのテーブルの主キーに id 列を用いる
    • Railsだとそうするしな〜 🤔
    • ↓言われてみれば確かにそう

      主キーは「あるテーブル上の 1 つの行」を識別するためのも のなので、主キーの列名でもそのテーブルの種類が分かるようにしておくべきです。そうすれば「bug_id = 1」という情報だけで、データベース上の 1 行を特定することができます。

  • 主キーの概念とデータ型の概念を切り離し、適切なカラムを主キーにする
    • 主キーは数値型じゃないと駄目、インクリメンタルじゃないと駄目、ということはない
    • なるほどたしかに〜〜〜
  • 解決策:主キーにはわかりやすい名前をつけること
    そういえばRailsでも変えられる……。ただ bug.bug_id はちょっと気持ち悪い気もする
    class Bug < ActiveRecord::Base
        self.primary_key = 'bug_id'
    end

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

  • MyISAM、外部キー制約無いんだ……
  • カスケード更新 のオプション(MySQL
    • 外部キーを指定する際に、参照先のテーブル(のレコード)の更新時・削除時の挙動が指定できる
    • MySQLリファレンスより
    • RESTRICT ← 参照オプションが指定されていない場合のデフォルト
      • 親テーブルの更新・削除操作を拒否する
    • CASCADE
      • ON UPDATE CASCADE
        親テーブルの該当レコーとの該当カラムが更新された場合、子テーブルのレコードのカラムも同じ値に更新する
      • ON DELETE CASCADE
        親テーブルの該当レコードが削除された場合、子テーブルのレコードも削除する
    • SET NULL
      • 親テーブルの該当レコードが更新または削除された場合、子テーブルのレコードの当該カラムをNULLにする
      • 子テーブル側のカラムがNULLを許容している必要がある
    • NO ACTION
      • MySQLでは RESTRICT と同じ
      • 遅延チェックをするデータベースシステムでは、遅延チェックを指す
      • MySQLでは即時チェックのみ
    • SET DEFAULT
      • SET NULL のカラムデフォルト値版?
      • MySQLの記法では存在するが、InnoDBでは使えない(テーブル定義時にエラーになる)

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

  • アンチパターン:属性テーブル
    • IssuesテーブルとIssuesAttributesテーブルを作る
      • IssuesテーブルはIDだけを持つ
      • IssuesAttributesはIssuesテーブルのIDと、属性名と、値を持つ
      • Issue has many IssuesAttributes の関係
        • IssuesAttributesの1レコードはIssueの1属性を表す
    • やばそう
    • へー

      導入時の初期設定が不要で、裏でデータベースを使っているようなソフトウェアパッケージの多くは、 カスタマイズしやすいように、EAV 設計を採用しています

7. マルチカラムアトリビュート(複数列属性)

  • 解決策の従属テーブル、solidusのaddressesが近かったかも

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

  • レコードが大きくなると必ずパフォーマンスが低下すると思いこみ、とにかくレコードが増えないようにメタデータを設計してしまう

    • しかし実際は、毎年データも増えカラムも追加されるテーブルではいずれパフォーマンスが低下する
    • たしかに
  • MySQL水平パーティショニング(シャーディング)

  • 垂直パーティショニング
    • MySQLは垂直パーティショニングやってくれない? 謎

      MySQL 8.0 does not support vertical partitioning, in which different columns of a table are assigned to different physical partitions. There are no plans at this time to introduce vertical partitioning into MySQL.

    • 「LOB型のカラムは多くのデータベース製品が自動でパーティショニングする」そうなのか
      ただし * でSELECTすると、全カラムを取るために↑のパーティショニングが無駄になるとのこと