2013-11-28

@nippondanji 氏の「データベース設計徹底指南！！」は神プレゼン！脅威の主義主張の一貫性保証は DB エンジニアの鏡だった件！

RDB conference lifelog

の回から良コンテンツを提供しまくりなエンバカデロ・テクノロジーズさん主催の第 3 回 DB エンジニアのための勉強会に参加してきました。
今回は漢(オトコ)のコンピュータ道で有名な漢の中の漢、 @nippondanji 氏がデータベース設計を徹底指南してくれるということで、元々 DB エンジニアがバックグランドのわたしとしてはいかないわけにはいかんだろう、と喜び勇んでいってきました！
内容はというと下記の概要をカバーする内容でした。

リレーショナルデータベース（以下RDB）は登場してからかなりの時間が経っています。その名が示すように、RDBはリレーショナルモデルをベースに考案されたソフトウェアです。しかしながら、未だに現場ではRDBが使いこなされているとは言いがたく、リレーショナルモデルへの理解も進まず、誤った常識が跋扈しているのが現状です。改めてリレーショナルモデルについて解説することで、間違った「常識」を徹底的に破壊し尽くし、本来DB設計をどうすべきかについて奥野氏より解説、そして議論したいと思います。

実際、わたしの経験上からも残念ながら開発の現場においては RDB が使いこなされていないというのは事実だと思います。

わたしはデータモデリング（リレーショナルモデルの理解）超重要！
データモデリングに特化した仕事をする人がいてもいいくらいだ！
という意見なんですが、あまりこの意見は賛同を得られなかったりしています。

データモデリング軽視の風潮は NoSQL があれば RDBMS いらないみたいなことも言ってる人も生みだしていることにはもはや言葉もありません.. ( 実際には両者は目的によって使い分けるものですし、いくら非構造を NoSQL が許容するからといってリレーショナルモデルの理解を軽視していい理由にはならないのにもかかわらず... 残念極まりないことです... )
(2013-12-01 22:53 追記) 上述のわたしの意見についても漢(オトコ)のコンピュータ道: その選択、ちょっと待った！NoSQLデータベースへ乗り換える前に検討すべき3つのポイントにてさらにすばらしいフォローアップがされているので、こちらもお読みいただきたい！

ちょっとでもそういうことに心当たりがある方には是非今回のスライドは読んでほしいと思います！おそらく、 @nippondanji 氏のことなので、このあとスライドを公開してくださると思うんですが、わたしとしてはできるだけ多くの人に今回のスライドは読んでみてもらいたい！

(2013-11-29 1:15 追記) そして、 @nippondanji が資料を公開してくれました！！！しかもわたしのブログにコメントを入れてくれる親切ぶり、感動！
データベース設計徹底指南
さあ、みなさん、読みましょう！

きっとこのスライドを読んだあとには、データモデリング (リレーショナルモデルの理解) 超重要を認識し、 NoSQL と RDBMS は使い分けだということを理解してくれる人が増えるんじゃないかな、と思います！

ちなみに普段から @nippondanji 氏の漢のコンピュータ道を読んでる方も、ニンマリする脅威の主張の一貫性を発揮したプレゼンでもありました。わたしの場合はというと、漢(オトコ)のコンピュータ道: 書評：「7つのデータベース 7つの世界」と漢(オトコ)のコンピュータ道: RDBMSに関する典型的な誤解が絶えないという現実がスライド中にある

クイズ (1)
「リレーションモデルにおけるリレーションとはテーブル間のリレーションである (Yes/No) 」
クイズ (2)
「リレーションは 2 次元な構造をもつ (Yes/No) 」

がリンクしていたところでニヤニヤが止まりませんでしたｗ

漢のコンピュータ道に馴染みの方も馴染みのない方も今回の神プレゼンを是非是非、読みましょう！そして、より多くの方がデータモデリング超重要に賛同してくれるようになることを願っています！

(2013-11-29 1:53追記) ご本家の漢(オトコ)のコンピュータ道: DBエンジニアのための技術勉強会で発表したスライドを公開しました。でも、このポストのことを書いてくださりました。光栄極まりなしです！

では、以降にいつもどおりブログを書くまでが勉強会ということで、今回の勉強会に参加してとってきたメモを公開しておこうと思います。
※スライドの公開等あれば、随時追加更新していきます。

データベース設計徹底指南！！ (2013-11-29 1:15 にスライド埋込)

データベース設計徹底指南 from Mikiya Okuno

RDB の上でデータベース設計をどのようにやるかを徹底指南！
今日は個人としてきてくれた漢！

＊＊＊＊＊＊

みなさん、正しい DB 設計をしていますか？
正しい DB 設計とはなんぞが今日はわかって帰れるぞ

＊＊＊＊＊＊

鉄則1：正しいデータ型を使う
〜カラムの場合〜
- プログラム全般において大事なこと
〜配列操作の場合〜
- Ruby の例
- カンマ区切りで配列を定義しようとするとわけのわからんことに
- 急いで書いたので実は間違ってるｗｗｗｗｗ
〜テーブルの場合〜
- DB においては演算のためのデータ型はテーブル
- 演算はクエリ
- テーブルを正しく定義しないとまともなクエリは書けない！
  ↓正しくないままクエリを書くとどうなるか
  - 遅い
  - SQL ムダに長い
  - JOIN に異常が
  - 難解になる
  - ロジックが間違ってる

＊＊＊＊＊＊

DB 設計の欠陥は技術的負債になる
- DB はリファクタリングが難しい
- 変更による影響が大きい
- 方法分からない（実はあまり方法論は確立されていない）
- 負債のたまりすぎで首が回らない
  ↓
放置された負債がさらに足かせに！

＊＊＊＊＊＊

技術的負債がたまる原因
- データモデルに対する無理解
- リレーショナルモデルをしらない
- そもそも正規化しない
  →結果としてセオリーを無視した DB 設計に
  ↓そういった状況をつくりだしてしまうのは
リレーショナルモデルをしらなくてもじつは RDBMS は便利…
- index による高速検索
- Transaction を解決してくれる、すごい

＊＊＊＊＊＊

技術的負債が蓄積すると
- 開発を難しくする
- 検索の要件が難解に
- テスト項目が増える
- 負債がたまるほどリファクタリングが困難に！

＊＊＊＊＊＊

RDBMS を使いこなすためには
- データモデルは超重要！！！！

＊＊＊＊＊＊

RDBMS におけるデータモデルは、リレーショナルモデル

＊＊＊＊＊＊

リレーションとは、ある物事に対する事実の集合
- テーブル≒リレーション

＊＊＊＊＊＊

集合の性質
- 重複がない、 NULL がない、要素間で順序もない (たとえ数字でも)

＊＊＊＊＊＊

リレーションの構成部品 (是非資料を参照されたい！)
リレーション→ 見出し + 本体
- 見出し
- 属性
- 属性値
- 組
- 本体

＊＊＊＊＊＊

リレーションのイメージ

＊＊＊＊＊＊

SQL との対応 (対応する概念だが性質は異なる)
- リレーショナルモデル - SQL
- リレーション - テーブル
- 属性値 - カラム
- 組 - 行

＊＊＊＊＊＊

リレーションの実体
- n 個のアトリビュートをもつリレーションは n 次元空間にプロットされた点の集合
- タプル = 点
- 2 次元にみえるのは縦横の軸がある表として表現するからなだけ

＊＊＊＊＊＊

データ型 = ドメイン
- 属性がとりうる値の有限集合 (重要！)
- 無限でないのはコンピュータで扱う値だから
- 32 bit 整数なら 2^32 通り

＊＊＊

ドメインの要素
- コンピュータで表現できるものなら何でも可
- ただし pointer と NULL は NG
- リレーショナルモデルは pointer を排除するために考えられたから
- NULL がだめなのは集合だから

＊＊＊＊＊＊

リレーションの演算
- 演算の入力も結果もリレーション
- 整数と整数の足し算の結果が整数なのと同じ

＊＊＊

集合操作にもとづく演算
- 和、差、直積、射影、制限、結合 etc...

＊＊＊＊＊＊

演算の例
- 1 つのリレーションに対して
  - 制限 (RESTRICT) 組の絞込
  - 射影 (PROJECT) 属性を絞り込む
  - 属性名変更 (RENAME)
  - 拡張 (EXTEND) 既存の属性から新しい属性
- 2 つのリレーションに対して
  - 和 (UNION)
  - 積 (INTERSECT)
  - 差 (DIFFERENCE)
  - 直積 (PRODUCT)
  - 結合 (JOIN)

＊＊＊＊＊＊

豆知識
- 直積と積はいずれも結合 (特殊なケース)
  - 直積：共通する属性がひとつも存在しないケース
  - 積：すべての属性がまったく同じケース
- リレーショナルモデルに存在する JOIN は INNER JOIN だけ

＊＊＊＊＊＊

じゃあ、 OUTER JOIN とはなんなんだ？
- OUTER JOIN はプリミティブな演算ではない
- OUTER JOIN は UNION 相当
- (INNER JOIN) U (駆動表にマッチしないものの集合)

＊＊＊＊＊＊

リレーションの演算はわかったけど、 SQL との関係は？？？？
- SELECT の実態: 3つの集合演算を同時に行うのが SELECT だ！
  - select (射影)
  - from (直積)
  - where (制限)
その評価には順番がある
- 直積→制限→射影
  - SELECT 3
  - FROM 1
  - WHERE 2

＊＊＊

その他の操作
和
- SELECT ... UNION DISTINCT SELECT ...
  - DISTINCT 必要
差
- SELECT .... MINUS SELECT ...
- SELECT ... WHERE NOT EXISTS (SELECT ...) ...

＊＊＊＊＊＊

本日のメインディッシュ、リレーションの正規化
なぜ正規化が必要なのか
- データベースの論理的整合性を保つ
- 論理的な正しさを保証する
- 論理演算によって正しい結果が得られる
- リレーショナルモデルは集合演算だけでなく、論理学にも根ざした学問なのだ！
  ↓
- つまり矛盾を防ぐ
  - 集合に含まれる命題に矛盾がないことを目指す

＊＊＊＊＊＊

論理的に矛盾しているデータは、どちらが正しいかいくら考えてもわからない！！
正しい答えが得られない DB は無価値！！！！！！！！！！
矛盾をなくすための定石、それが正規化！！！！！！！！！！

＊＊＊＊＊＊

では矛盾とはなんぞ！？
不整合 = 矛盾

＊＊＊＊＊＊

正規化理論
- リレーションから重複を排除するためのデータベース設計
- 重複が矛盾を引き起こす、だから重複を排除する
- 第一正規形から第六正規形
- 3NF と 4NF の間に BCNF というものがある超重要
- 最終的には 5NF を目指す

＊＊＊＊＊＊

用語
- 論理学の用語を知ろう
  - 命題 e.g. ポチは犬である
  - 述語 e.g. x は犬である
  - 命題関数 e.g. F(x)
  - 閉世界仮説：リレーションは事実の集合 (事実＝真となる命題)
  - リレーションに含まれる組の属性値を代入→真
  - それ以外の属性値を代入→すべて偽
    ↓
- なんで大事？
  - 演算によって事実だけが画一的に得られるのがリレーショナルモデルの真骨頂！

＊＊＊＊＊＊

1NF
- 要件：テーブルがリレーションであること
- リレーションの条件を5つ列挙

＊＊＊

繰り返しグループ
同じ性質のものが繰り返し出現、 1NF になってない典型例

＊＊＊

アトミックな属性でなければならない
それ以上分解できないような属性、ひとつのカラムに複数の属性をもたせるようなのはダメ (属性の中に繰り返しグループがある)

＊＊＊

データ型 = ドメイン
属性の値は、ドメインという有限集合に含まえる要素のひとつ

＊＊＊＊＊＊

NG な設計の例：こんなのにはご用心
- 主キーの一部に意味を持たせる e.g. 1234-567890
問題点：
- アトミックでなくなってしまう
- つまり値の一部に依存した処理ができるようになってしまう

＊＊＊＊＊＊

1NF の例
- 繰り返しグループなし
- 重複なし
- NULL なし

＊＊＊＊＊＊

正規形を考える上での大事な概念
用語 2
- 候補キーとスーパーキー
- すべての属性を含む集合は常にスーパーキー
- 関数従属性
- スーパーキー→任意の属性の集合
- 自明な FD
- 2NF 以降は FD をとりのぞいていく作業

＊＊＊＊＊＊

2NF
- 候補キーの真部分集合 (それ自身は含まないもの)からから非キー属性への FD を取り除いた状態

＊＊＊＊＊＊

FD を解消するとは？
- 無損失分解すること
  - 必要な操作は射影 SELECT DISTICT
    ↓
- 2NF の例

＊＊＊＊＊＊

テーブル数が増えるのは問題なのか？
- それは問題ではない
- むしろ、間違った DB 設計をするほうが問題

＊＊＊＊＊＊

2NF であっても 3NF ではない例

＊＊＊＊＊＊

BCNF
- ボイスコッド正規形
- すべての自明ではない FD が取り除かれた状態、だから超重要
- 3NF でおしまいとかよくいうが、そんなことはない

＊＊＊

3 NF ではあっても BCNF でない例

＊＊＊

候補キー内部には FD がない
- もし仮に候補キーに FD があると
- {A,B} が候補キー
- A -> B
- B の値は A によってきまる

＊＊＊＊＊＊

用語 3
- さらに正規化をすすめるための用語
  - 結合従属性： JOIN するともとに戻るような無損失分解
  - 自明な JD
- 4NF と 5NF は自明でない JD を取り除いていく作業

＊＊＊＊＊＊

4NF/5NF のヒント
- 非キー属性 BCNF に自明でない JD はない
- 非キー属性が BCNF は自動的に 5NF
- なのでなかなか 4,5NF はなかなかする機会がない

＊＊＊＊＊＊

自明でない JD の本質
- 非キー属性がないリレーションを JOIN したリレーション
  = 見出し全体が候補キーであるようなリレーション

＊＊＊＊＊＊

一般的な 4NF の解説
- MVD を取り除いたパターンというのは忘れよう
- MVD は JD の特殊なパターン
  ↓
- 共通の属性を含む 2 つのリレーションに無損失分解可能な JD を解消した状態

＊＊＊＊＊＊

5NF
- すべての自明でない JD が取り除かれた状態

＊＊＊＊＊＊

4NF であって 5NF でない例
- コネクショントラップなんてものは実際にない→調べてみて

＊＊＊＊＊＊

6NF
- 自明なものを含めてすべての JD を取り除く
- 非キー属性は最大でひとつ

＊＊＊＊＊＊

リレーションの直交性
- 正規化は個々のリレーションの内部の重複をテーマにしたもの
- リレーション同士の重複に焦点をあてたのが直交性

＊＊＊＊＊＊

用語 4
- 直交したリレーションとは、互いに重複したタプルを含まないものを指す
- 直交していないと片方だけを更新することで不整合が生じる
- 見出しが同じ型のタプルは単に重複がなければ Ok
問題
- 見出しが異なる場合には単純に比較できない
- 直交性を確認するためには 6NF まで分解して比較する
- 重複があれば DB 設計を見直そう→リレーションの統合と再編

＊＊＊＊＊＊

リレーショナルモデルには限界がある
↓
現実世界との境界線がある
→リレーショナルモデル以外の領域は創意工夫が必要
リレーショナルモデル≒ SQL だからこそ
- SQL ならなんとかできるものもある
  → GROUP BY, ORDER BY

＊＊＊＊＊＊

リレーショナルモデルでは扱いが難しいテーマ
- グラフ＊
- ツリー＊
- 行列
- 履歴データ＊
- 全文検索
- 正規表現
- ソート
- 集計
- 空間データ
＊は今回の勉強会で取り扱われたもの

＊＊＊＊＊＊

グラフ：数学におけるグラフ構造のこと
- グラフ = ノードの集合 + エッジの集合
- 表現は簡単にできるがクエリを書くのが難しい

＊＊＊

グラフに対するクエリの問題点
- グラフ特有の問題を表現できない
- 何度 JOIN すればよいか事前にわからない
解決策
- ストアドプロシージャ
- グラフデータベース (RDB を使う意味がそもそもない)

＊＊＊＊＊＊

ツリー
- ツリーも RDB と相性が悪い
- なぜならこれもグラフの一種だから
- 閉路がなく連結している
- 任意の 2 つのノードを結ぶパスはただひとつである
- 隣接してないどの 2 つのノードを結んでも閉路ができる
  ↓
- よく扱うツリーの特徴
- 親子関係がある有向グラフ
- あるノードへ向かうエッジはひとつのみ
- 根あるいは root
- 根からの距離が深さとして表される (階層構造になっている)

＊＊＊

ツリーの表現方法
- 隣接リストモデル
- パス列挙モデル
- 入れ子集合モデル
- クロージャーテーブルモデル

＊＊＊＊＊＊

隣接リストモデル
- 再帰クエリあるいはストアドプロシージャで解決

＊＊＊

パス列挙モデル
- 根ノードからのフルパスで経路を表現する
- LIKE で検索
- 深さを求めるのは簡単
- 非正規化されたデータそのもの

＊＊＊

入れ子集合モデル
- Ift と rgt による表現

＊＊＊

クロージャーテーブルモデル
- 祖先、子孫の関係にあるノードをすべてリストアップ

＊＊＊＊＊＊

ツリーについてはどのモデルを使えばよいのか？
- 根本的にツリーとリレーショナルモデルは相容れないから
- 残念ながら決定版はない！
- 要件次第で使い分けるべき
- データサイズを気にしないならクロージャーモデルがおすすめ
- コンパクトさを重視するなら隣接リストモデル

＊＊＊＊＊＊

履歴データ
- 履歴データ≒時系列で並んだデータ
- 実はリレーショナルモデルで扱うのは難しい

＊＊＊

履歴データの問題点 (1)
- 時間軸との直交性
- リレーションとはある時点での事実の集合
- 履歴データは時間軸と直交していない→時刻が変わるとクエリ結果が変わる e.g. NOW の結果でクエリの結果が変わる

＊＊＊

履歴データの問題点 (2)
- 行の意味が異なる
- リレーションは事実の集合
- しかし、履歴では特定の行だけ特別な意味が含まれてしまう
- e.g. 現在有効な価格、現在有効でない価格

＊＊＊

履歴データ対策 (1)
- テーブルを分割する
- 意味のあるリレーションに分ける
- 日付はあるがクエリの結果に影響は与えない
- もとのテーブルは UNION で得られる
- その問題点
- 外部キー制約が使えない
- 同じレコードが含まれていないという制約をつけるのが難しい→トリガーを使えば表現は可能

＊＊＊

履歴データ対策 (2)
- 重複したデータをもつ
- 外部キーが利用可能
- UNION が不要
- その問題点
- 重複したデータがあること
- 直交しない
- 間違って片っ方だけ更新してしまうといったことが起こりうる

＊＊＊

履歴データ対策 (3)
- 擬似 (pseudo) キーの利用
- 外部キーは利用したいけどデータの重複は避けたい場合
- その問題点
- 擬似キーは本質的に不要
- JOIN が増える

＊＊＊

どれが一番いいの？
- やっぱり答えがない

＊＊＊＊＊＊

NG!! こんな設計はダメ
- 有効、無効を判断するフラグをたてたら？
- フラグは自動的には更新されない (バッチで更新？？？んなバカな)
- フラグだらけになってしまう

＊＊＊＊＊＊

リレーショナルモデルで表現することができないテーブルは正規化する意味が無い

＊＊＊＊＊＊

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

質疑応答

Q. 入れ子集合モデルを適用した例はあるのか？
A. ツリー構造はいろんなところででてくる。掲示板とか。親子関係がツリー。入れ子は好みで使う。

＊＊＊＊＊＊

Q. 履歴データについて
A. テーブルをわけるということは、バッチでデータ移動するということ

＊＊＊＊＊＊

Q. ドメインとカラムの数はおなじくらいになる気がするが？
A. 実際そういうもの。きっちりきっちりやったらそうなる。どこで妥協するかが勘所だったりする。

＊＊＊＊＊＊

Q. NULL について
A. リレーショナルモデルに該当するモデルについては NULL は使わない (えない)
SQL では必要に応じて NULL は使ったっていい

＊＊＊＊＊＊

Q. FROM が直積とは？
A. SQL の文法的には JOIN だが。

＊＊＊＊＊＊

Q. リレーショナルモデルで表現できるテーブルをあえて非正規化することはあるか？
A. ひとつだけ許容出来るのは、キャッシュするときだけだと思っている。

＊＊＊＊＊＊

Q. 外部キーについて。更新時のパフォーマンス劣化についてどう思う？
A. アプリケーションで参照整合性を保つより、全然マシだと思ってる。
そこをスキップしちゃって全然いいって思ってるなら、いいんじゃないか。
性能に問題が起こることはケース・バイ・ケースでしょう。

＊＊＊＊＊＊

Q. 分析系についてはリレーショナルモデルの外にあるものか？
A. そういったところで非正規化はありだ

＊＊＊＊＊＊

Q. 開発者がモデリングはやるべきか？実例はあるのか？
A. 友人の話をきいて書いたエントリ。むしろそういう考え方を浸透させたくて書いた。

＊＊＊＊＊＊

Q. リレーショナルモデルの限界
A. NoSQL が適する場面はいくらでもある。ランキングだったり、全文検索。
大事なのは併用すること。リレーショナルモデルを使ったほうが表現が簡単にできる。

＊＊＊＊＊＊

Q. 履歴データについて
A. 時間軸と直交していないことを問題であるということを伝えたかった。
DB デザインですべての制限を表現することができる
ステータスが含まれるようなテーブルは分割することを考えるとうまくいくことが多い
(ひとつのテーブルに無理にまとめようとした結果、 NULL を許容するようなデータ設計になっていないか)

＊＊＊＊＊＊

Q. DB を分割するという問題
A. すぐに答えをこの場では出せない
マッチョな DB サーバをたてるとか、何をキーにして分割するとか考えられることがたくさんある

＊＊＊＊＊＊

では、今日はこんなところで。