sgykfjsm.github.com

mysql_study

データベース設計徹底指南

  • 正しいデータ型を使うのが大事。
  • テーブルが演算の単位となるので、ここの設計が非常に重要
  • テーブルを正しく定義しないとまともなクエリがかけない
  • データベースはリファクタリングが難しい
    • DDLに時間がかかる
    • appへの影響範囲が大きい
    • そもそものリファクタリングの方法がわからない
  • 負債が溜まりすぎて首がまわらない
  • なぜ技術的負債が溜まるか
    • データモデルに対する無理解
    • リレーショナルモデルを知らなくても便利に使える
  • 技術的負債が蓄積すると開発が難しくなる
    • テストが難しくなる
  • 負債を溜めないようにするためにデータモデルをちゃんと理解する

    • つまりリレーショナルモデルを理解する
  • リレーションとは

    • ある物事に対する事実の集合
    • テーブルはリレーションに近似する
    • 集合は
      • 重複が無い
      • NULLが無い
      • 要素間に順序が無い
    • リレーションは以下を持つ
      • 見出し
      • 本体
  • SQLと対応する概念だが、性質は異なる
  • リレーションはn次元空間にプロットされた点のようなものの集合
  • データ型とはドメイン
    • 属性が取りうる値の有限集合
    • コンピュータで表現できるものなら要素は何でもOK
    • NULLとポインタはNG

リレーションの演算 - 演算の入力も結果もリレーション - 集合操作に基づく演算 - 制限(要素を絞り込む)、射影(属性を絞り込む)、属性名変更(RENAME)、拡張(EXTEND、複数の要素を組み合わせて新しい要素を作り出す)、和(union)、積(intersect)、差(difference)、直積(product)、結合(join) - productとintersectはいずれもjoin - 特殊なケース - リレーショナルモデルにおいてはinnser joinがプリミティブ - Outer Joinはプリミティブではない - UNION相当

SQLと集合の演算 - select / 射影 - from / 直積 - where / 制限

リレーションの正規化 - 正規化はデータベースの論理的な整合性を保つ - 正規化は矛盾を防ぐ - 矛盾とは論理的な不整合 - リレーションから重複を排除

1NF - テーブルがリレーションであること - 繰り返しグループが発生していない - アトミックである - 主キーの一部に意味を持たせない - アトミックでなくなる - 値の一部に依存した処理が入り込む

2NF - 候補キーの真部分集合から非キー属性へのFDを取り除いた状態 - FDを解消する 必要な操作は射影 select ditinct 正規化によりテーブルが増えても問題ない。誤った手=ブル設計によりクエリがスパゲッティになる方が問題になる。 - BCNF - ボイスコッド正規形の略 - 全ての自明でないFDが取り除かれた状態 - 残る

グラフに対するクエリの問題点 - グラフと供養の問題を表現できない - 何度joinすれば良いのかわからない - そもそもRDBは向いていないので、グラフデータベースを使うか、ストアドで頑張る

ツリー - 閉路がなく、連結している - ツリーは特殊なグラフ - 多数の制約がある

隣接リストモデル - 子ノードが親ノードのIDを持つ - 根ノードの親はNULL - 再起クエリまたはストアドプロシジャー

パス列挙モデル - 寝ノードからのフルパスで経路を表現するモデル - Like句で検索

入れ子集合モデル

クロージャーテーブルモデル - 祖先、子孫の関係にあるノードを全てリストアップ - テーブルサイズは大 - 更新は容易

上に挙げたどのモデルも決定打にはならなくて、どれも妥協案。要件次第で使い分けるべき - データサイズを気にしなければクロージャーテーブルがおすすめ - コンパクトさを重視するなら隣接リストモデル

履歴データ - 実はリレーショナルモデルと非常に相性が悪い←結構意外 - 履歴データとは時系列で並んだデータ

問題点1. 時間軸との直交性 - リレーションとはある時点での事実の集合 - 履歴データは時間軸と直行していないので、クエリの実行タイミングによっては結果が異なる。 - 行の意味が異なる。 - リレーションとは事実の集合 - 特定の行だけに隠れた特別な意味がある - 現在有効な価格かどうか

履歴データ対策 - テーブルを分割(対策1) - 意味の異なるリレーションに分ける - 意味の違いが解消 - 時間の直交性を持つことができるようになる - 元のデータはUNIONで。 - 問題:外部キー制約が使えなくなる。 - 同じレコードが含まれていないという制約をつけるのが難しい。 - トリガーを使えば表現可能 - 重複したデータを持つ(対策2) - そもそも重複したデータがある。 - 直行しない - 更新異常(矛盾)に注意 - 擬似(pseudo)キーの利用 - 外部キーは使いたいけど、 - そもそも擬似キーは本質的に不要 - ディスクスペースが無駄 - 更新のオーバヘッドが増える - joinが増える - 履歴の過去データにフラグを立てる? - フラグは自動的に更新されない - 本質的に不要なカラムを持つことになる。

まとめ DB設計鉄則三箇条 - リレーショナルモデルを理解する - リレーションを正規化する - リレーショナルモデルの限界を知る

あえて非正規化することは無い - JOINの結果をキャッシュしておくことで代用できる。 - RDBでマスタを持っておいて、ドキュメント型NoSQLDBに流し込むというのはアリ - 集計結果を持つようなテーブル(データウェアハウス的なもの)であればそもそも集合演算とは違ってそれは集計演算なので、それであれば非正規化というのはアリ。 - ステータスを示すカラムもフラグなので、そうするぐらいならテーブルを分けたほうが良い。