『理論から学ぶデータベース実践入門』読書録

参考

理論から学ぶデータベース実践入門 ~リレーショナルモデルによる効率的なSQL

SQLとリレーショナルモデル

リレーショナルモデル

• リレーショナルモデルとは、現実世界のデータを「リレーション」と呼ばれる概念を用いて表現するデータモデル
• データモデルとは、データをどのように表現するかという概念

リレーションの定義

• SQLにおいてリレーションに相当するものは、テーブル
• リレーショナルモデルにおけるリレーションの定義は、見出し(heading)と本体(body)のペアで構成された集合(タプルの集合)
  • 見出し: n個の属性(attribute)の集合。名前とデータ型のペア
  • 本体: 属性値の集合である組(タプルの集合)。タプルの属性値は、名称とデータ型で指定されたものと一致している
• タプルと属性は、SQLではそれぞれ行(ロー)と列(カラム)に相当している

集合とリレーショナルモデル

• 集合とは、物の集まりを表す概念
• 集合が満たすべき要件
  • その要素が集合に含まれているかどうかを不確定要素がなく判別できる(未知のものは集合に含められない)
  • 集合の要素が重複してはならない
  • 集合の要素はそれ以上分解できない

リレーションの演算

下記演算を用いることで、リレーションから必要な情報を抽出するのがクエリの本質。その原理を応用して作られたのがRDB。

• 制限
• 射影
• 拡張
• 属性名変更
• 和
• 積
• 差
• 直積
• 結合

クロージャという性質

• リレーショナルモデルで大切なことは、リレーションを用いた演算結果がリレーションになること
• 演算の入力も出力も同じデータ構造を持ったものになる性質をクロージャ(閉包)と言う

SQLにおけるリレーション操作

SELECTの基本形

• シンプルなSELECTは、3つのリレーションの演算を同時に行う操作

SELECT カラムのリスト
FROM テーブルのリスト
WHERE 検索条件

• テーブルのリスト(直積)
• 検索条件(制限)
• カラムのリスト(射影)

INSERT(挿入)

• 和集合
• リレーションは値だから、リレーショナルモデルに更新という概念は存在しない
• しかしINSERTの場合は、行を追加することによって、テーブルの値=リレーションが変化しているように見える
• この矛盾は、テーブルが値と変数の両方の役割を持つことに起因する
  • リレーショナルモデルにおけるリレーションを格納する変数はRelvar(Relation Variable, 関係変数)と呼ぶ
  • SQLにおけるテーブルの更新処理とは、Relvarとしてテーブルに割り当てられた、リレーションの値を変更すること
  • 変数の中身は変化するが、値の持つ意味そのものは変化していない
• INSERTは、Relvarの値(=リレーション)を、そのリレーションに対して新しくINSERTするタプル(=行)を追加したリレーションと置き換えるという操作
  • R := R ∪ {T}

DELETE(削除)

• 差集合
• 元のリレーション(=Relvarに代入されている値)から、リレーションとの差集合を、Relvarに代入するのと等価
• 要は、RelvarをWHERE句の条件を満たさないタプルからなるリレーションで置き換えることと等価
  • R := R - {T}

UPDATE(更新)

• UPDATEは、DELETEとINSERTの組み合わせで表現できる
  • R := (R - {T1}) ∪ {T2}

SQLにあってリレーショナルモデルにないもの

SQLを効果的に使うためのコツは、リレーショナルモデルに沿って使うこと。そのために、SQLとリレーショナルモデルの違いについてよく理解しておく。

• 要素の重複
• 要素間の順序
• リレーションの更新
• トランザクション
• ストアドプロシージャ
• NULL

正規化理論1 -関数従属性-

正規化

• 正規化理論は、RDBを使いこなすために必要なテクニックであり、リレーショナルモデルを前提として構築されたDB設計理論
  • リレーショナルモデルを補完する理論
• 正規化することのメリット
  • 矛盾を防ぐことができる → 異常を防ぐことができる
• 矛盾(異常)の原因は重複
  • 重複を排除する上で役に立つのが正規化理論

###　正規形

第1正規形(1NF)

• 1NFの要件: リレーションであること
  • 行が上から下に順序付けされていない
  • 列が左から右に順序付けされていない
  • 重複する行は存在しない
  • それぞれの行と列の交差点(列の値)は、ドメイン(データ型)に属する要素の値をちょうど1つだけ含んでいる
  • すべての列の値は定義されたものだけであり、かつそれぞれの行において常に存在する

候補キーとスーパーキー

• キーは1NFから正規化を進めるための基本的な考え方となる概念
• 候補キーとは、そのリレーションに含まれるタプルの値を一意に決められる属性の集合で、なおかつ、既約(Irreducible)であるもの
  • 既約とは、それ以上属性を減らすことができないことで、余分な属性がない状態
  • 候補キーに含まれない属性は、非キー属性と呼ぶ
  • リレーションには、重複したタプルが含まれないため、最低でも1つの候補キーが存在する
• スーパーキーとは、候補キーのスーパーセット、つまり、余分な属性を含むもの
  • 見出しの全体には、すべての属性が含まれることから、それは必ずスーパーキーになる

関数従属性(Functional Dependency, FD)

• 2NF~BCNFの正規化を進めるための基本的な考え方となる概念
• 関数従属性とは、Aの値がわかれば、Bの値が求められるということ
• 2NF~BCNFにおける正規化は、自明ではない関数従属性を取り除く作業

第2正規形(2NF)

• 2NFは、候補キーの真部分集合から非キー属性への関数従属性を取り除く作業
  • 真部分集合とは、部分集合のうち、もとの集合自身以外のもの
• リレーションが1NFで、かつ部分関数従属性が含まれない場合、そのリレーションは2NFとなる
• 関数従属性を解決するためには、1つのリレーションを複数のリレーションへと分解する(射影)
  • 無損失分解できるように(分解後のリレーションに含まれる情報を使って、元のリレーションを再構築できること)

第3正規形(3NF)

• 3NFは、推移関数従属性(Transitive Dependency)と呼ばれる関数従属性を取り除く作業
  • 推移関数従属性とは、非キー属性間の関数従属性のこと

ボイスコッド正規形(BCNF)

• BCNGは、3NFから、非キー属性から候補キーの真部分集合への関数従属性を取り除く作業
  • 候補キーになりうる属性の組み合わせが複数存在している
  • 候補キーがほかにないかと探す
• BCNFでは、自明ではない関数従属性がすべて取り除かれた状態
  • これ以上は関数従属性による無損失分解はできない

正規化理論2 -結合従属性-

結合従属性(JD)

• 4NF以降は、結合従属性に関する正規化
• 結合従属性は、キー自身に冗長性が含まれている場合に生じる重複
• リレーション内に自明ではない結合従属性が存在すると、そのリレーションは4NF~6NFによる正規化の対象となる
  • 非キー属性が存在しないリレーションだけが対象
  • 候補キーに複数の属性が含まれる場合
• 例えば、{氏名}という共通の候補キーを含む2つのリレーションに分けた場合

結合従属性による正規化 (4NF~6NF)

第4正規形(4NF)

• 多値従属性による正規化
• 非キー属性を含まないリレーションを、結合従属性によって共通の属性を含む、2つのリレーションに無損失分解できるもの

第5正規形(5NF)

• 自明ではない結合従属性が全て取り除かれた状態
• リレーションRの部分集合A, B, Cがあるとき、5NFでは{AB, BC, CA}のような結合従属性が取り除かれる

第6正規形(6NF)

• 自明な結合従属性(そのリレーション自身を含む結合従属性)しか存在しないようになるまで、すべての結合従属性を排除した状態の正規形
• 5NFであって6NFでないリレーションとは、非キー属性が複数存在するリレーション
• {A, B, C}という属性からなるリレーションRにおいて{A}が候補キーである場合、{AB, AC}という結合従属性が存在する
• 6NFまで分解したリレーションは無駄な結合が多く、実用的ではない
  • DB設計としては、通常は5NFにまでとどめる

リレーションの直交性

リレーションの直交性と重複

• 正規化は、1つのリレーションの内部から重複をなくすことに絞った作業
  • 直交性とは、複数のリレーション間の重複に関する概念
• 直交性とは、一言で言うと、同じ値を含まないということ

同じ値を含むリレーションの例

• レプリカ
• 同じ型のリレーション
  • 2つのリレーションを結合してみて、空集合にならなければ同じ値を含んでいる
• 見出しの一部だけが同じリレーション
  • すべてのリレーションを6NFになるまで無損失分解してから、タプルを比較して、重複が無いことを確認すれば、直交性を保証できる

リレーション直交化のための戦略

• 正規化
• 属性(カラム)の名前を統一する
  • 命名規則を統一する
  • 何のプロパティかを示すために、主語を含める(e.g. name → student_name)
• アプリケーションの整合性
  • 異なる2つの機能で同じ意味のデータが必要な場合、共通のコンポーネントを設計する代わりに、独自にそれぞれDBにデータを登録すると、直行しないDBができあがる
• ただし、2つのテーブルが表す条件の意味が完全に独立したものであれば、直交していなくても設計上の問題はない

重複を解消することのメリット

• 異常を防げる
• 必要なデータがどこにあるかが明確になる
• クエリの記述が宣言的になる
• 不要な無損失分解が必要ない
• 複雑な制約が必要ない
• アプリケーションのコードに無駄がなくなる
• 性能が向上する

ドメインの設計戦略

ドメイン

• ドメインとは、リレーショナルモデルにおけるデータ型のこと
• ドメインとは、属性が取りうる値の集合

ドメインの設計戦略の概要

• アプリケーションが必要とするデータから生まれる
• アプリケーションの設計手法のおすすめは、ドメイン駆動設計
• DBは本質的なデータを扱うようにする

IDを設計するという考え方

• IDをナチュラルキー(自然キー)にすべきか、サロゲートキー(代理キー)にすべきか
  • ナチュラルキーとは、すでにこの世界に存在する何らかの言葉をキーとして使う
  • サロゲートキーとは、この世界には存在せず、DBあるいはそれを利用するアプリケーションの内部だけで通用するID
• ナチュラルキーの使いどころと問題点
  • 用いる値がIDとして機能しうるなら問題ない
  • 1:1で対応していないものをIDとして用いてはならない
    • e.g. 人を識別するIDとしてemailを使う
• サロゲートキーの使いどころと問題点
  • IDが存在しない場合、サロゲートキーを用いる
    • e.g. オーダーの識別子
  • すでにナチュラルキーが存在するにもかかわらず、サロゲートキーを新たに作成してはならない
    • 関数従属性が生じる
• リレーショナルモデルにおけるキーは、候補キーとスーパーキーだけ
  • ナチュラルキーとサロゲートキーは、ドメイン設計にとってのテーマであり、リレーショナルモデルのテーマではない

SQLにおけるドメインの表現

• 適切なデータ型を選ぶ
• 述語を制約で表現する
• ドメインをテーブルとして表現する

NULLとの戦い

NULL

• NULL = 値が存在しない、または値が不明

3値論理

• TRUE, FALSE, Unknownという3つの論理値によって判定を行う論理システムを3値論理(3VL)という
• 問題
  • NULLは演算を台無しにする
  • 検索結果が意図しないものになる
  • 最大の問題は、3値論理になることで、論理形態が複雑になること→閉世界仮説(すべての問がリレーションの演算だけで解決する)というリレーショナルモデルの前提を覆してしまう
  • オプティマイザへの弊害

NULL対策

• 対策
  • テーブルを正規化する
• 誤ったNULL対策
  • NOT NULLとして定義する代わりに、デフォルト値にNULLと同じような意味を持たせること
• NULLを使っても良いケース
  • リレーショナルモデルに適合しないデータをテーブルを用いて格納する場合

SELECTを攻略する

SELECTはSQLの心臓部

• データを取得する唯一の手段がSELECT
• 基本構造
  • テーブルのリスト(直積)
  • 検索条件(制限)
  • カラムのリスト(射影)

SELECT七変化

集約関数

• COUNT
• GROUP BY

サブクエリ

• テーブルサブクエリ
• スカラサブクエリ
• 行サブクエリ

リレーションではない操作

• リレーションではない操作
  • ソート
  • 明示的に定義されていないカラム
  • ストアドファンクション(ユーザ定義関数)
• リレーショナルモデルから逸脱する操作は危険であり、取り扱いに注意が必要
  • リレーショナルモデルの範疇でできることは、リレーショナルではない操作で実装しない
  • リレーショナルモデルの範疇でうまく記述できないと思ったら、DB設計を見直す
  • リレーショナルモデルではない操作がどうしても必要な場合は、リレーショナルモデルに関するロジックを必ず先に行う

履歴データとうまく付き合う

履歴データの問題点

• リレーションには各要素同士に順序はないのに対して、履歴にはどちらが古いのか、新しいのかという順序が存在する
• 履歴データは時間軸とリレーションが直交していない = 時間によってクエリの結果が変わる
• それぞれの行の意味が均一でない

履歴データに対する解決策

• リレーションを分割する
  • 現在の価格と過去の価格を含む2つのリレーションに分割する設計 ⇔ 外部キーが使用できず、またデータの不整合が起きる
  • 全ての価格を1つのリレーション内に格納し、現在の価格だけを別のテーブル上に重複して持つ ⇔ 行が重複する
  • サロゲートキーを用い、price_id_master, price_list, price_list_historyテーブルに分ける ⇔ JOINが増えてしまう

履歴データのアンチパターン

• フラグを立てる
  • flgカラムはカーディナリティが低いので効率が良くない
  • {start_date, end_date}→{flag}という関数従属性が存在し、リレーションが3NFになっていない
  • バッチなどでflagの値を定期的に書き換える必要がある
• 手続き型として実装する
  • 手続き型(ストアドプロシージャやファンクション)で実装すると、リレーショナルモデルによる強固なデータの整合性を手放すことになる

リファクタリングの最適解

リファクタリング

リファクタリングの手順

1. 作業前後のバックアップ
2. スキーマの変更
3. データの移行
4. アプリケーション移行のためのトリガーの作成
5. アプリケーションのデプロイ
6. 移行のためのトリガーの削除
7. 移行のためのカラムの削除

反復的なリファクタリング

• 日々アプリケーションのコードのリファクタリングを行う傍らで、DBのリファクタリングも反復的に行う必要がある
• 機能が損なわれていないことを確かめるために、回帰テストを行う
• パフォーマンスが低下していないことを確かめるために、ベンチマークテストを行う
• バージョン管理のためにマイグレーションを利用する

リファクタリングの種類

• インデックスの追加・削除
• カラム名の変更
• NOT NULL制約の導入
• 主キーの定義変更
• 無損失分解
• テーブルの垂直分割と統合

リファクタリングに強いベストプラクティス

• 正規化と直交性
• カラムではなくテーブルを追加する
  • カラムの初期値はあるか
  • 主キーに対して関数従属しているか
• SELECT * を使わない
  • 具体的に参照するカラム名を列挙する
• アプリケーションを疎結合に