【備忘録】DB設計

#はじめに

設計は大きく以下の3レイヤ

• 論理設計
  • 正規化やER図 etc
• 物理設計
  • サーバーやストレージといった物理的なHWレベルの設計
• 実装設計
  • 特定のDB製品を前提に、具体的な構築手順や方法を検討
  • ex.「●●データベースサーバーのチューニング」

1.データベースを制する者はシステムを制する

##システムとデータベース

##データベースあれこれ

• データベースの代表的なモデル

  • RDB

  • XML DB

    • web上でやり取りされるデータの形式にxmlを使う機会が増えてきているなかで、このxml形式のデータを扱うDB
    • RDBが苦手とする階層構造のデータの扱いを得意とする

  • KVS(key value store)

    • 単純なデータへの高速問い合わせが可能
    • 反面、複雑なデータ操作が苦手

  • 階層型データベース

    • データを木構造で表現する

• 主なDbMS

  • RDBのDBMSにはいくつか種類がある
    • Oracle DB
    • SQL Server
    • DB2
    • PostgreSQL
    • MySQL

##システム開発の工程と設計

##設計工程とデータベース

• システム開発工程の中の設計工程、そのサブ工程である「データ設計」について説明していきます

• データ設計が重要な理由

  • システム(プログラム)とはデータベース上の値を扱い、サービスを提供する、そのデータフォーマットにそってプログラムが作られれるから
  • ひと昔前の「プロセス中心のアプローチ」だと、プロセス(業務)が変わるたびにデータの設計が必要となり、業務ごとにデータを持つという非効率な面が発生するから

• 3層スキーマ

  • 外部スキーマ
    • ビューの世界=ユーザーから見たデータベース
  • 概念スキーマ
    • テーブルの世界=開発者から見たデータベース
    • 概念スキーマの設計を論理設計と言う
  • 内部スキーマ
    • ファイルの世界=DBMSから見たデータベース
    • 論理データモデルを具体的にどのようにDBMS内部に格納するかを定義するスキーマ

• なんで3層？=概念スキーマいる？
  • 結論：データ独立性を高めるために必要
  • もし概念スキーマがなければ...
    • 例えばユーザーが入力画面(ビュー)を変更したい場合、直で内部スキーマ(DBMS)の変更を強いられる
    • 逆に内部スキーマを変更した場合、ユーザーが直接みることになる外部スキーマを変更する必要性がでてくる

★これ(方法や概念) が なかっ たら どういう 不都合 が 生じる だろ う か？と考えてみることで上記のような疑問やその有用性がわかるような学習となるでしょう

#2.論理設計と物理設計

• 論理設計が先、あとに物理設計(料理に例えると、料理を決めて、料理に合わせて器を用意する)
  • 逆はない。(料理が何かわからないのに、適切な器は選べない)

##2.1概念スキーマと論理設計
RDBの論理設計は以下の4ステップ

• エンティティ(実体)の抽出
  • 現実世界に存在するデータの集合体(顧客や、会社、注文履歴)
  • どのエンティティを抽出する？(←要件定義工程と一部被る)
    • 結論：システムに必要なエンティティ
• エンティティの定義
  • 属性(=列)を決めて、どうデータを保持するか決める
• 正規化
  • 目的：データ更新が整合的に行えるように
  • 正規化：フォーマットを整理すること (詳細は3章)
• ER図の作成
  • 背景：正規化を行うと、テーブルが分割され、テーブル数が増える
  • 目的：エンティティ同士の関係を把握するために

##2.2 内部スキーマと物理設計
物理設計には大きく以下5ステップがあります

• テーブル定義
  • 論理設計で定義された概念スキーマをもとに、DBMS内部に格納するための「テーブル」の単位に変換
• インデックス定義
  • インデックスはなくても機能的には問題はないが、パフォーマンス面で有効な方法
    • 要は、本の索引機能(詳細は6章)
• HWサイジング
  • システムで使用するデータサイズを見積、十分なストレージを選定すること
  • また、ストレージI/Oを考慮してサーバーの性能も考慮する必要がある
  • 通常、システム開発では性能要件を2つの指標を使って定義しています
    • 処理時間...特定の処理についてどれくらい時間がかかったか
    • スループット...単位時間あたりにどれだけ処理をこなせたか
  • 一番安全なのでは、スケーラビリティの高い構成にあらかじめしておくこと
• ストレージの冗長構成決定
  • 複数のディスクを束ねて仮想的に1つのストレージにする技術(RAID)
  • 冗長性による信頼性向上だけでなく、RAIDは性能向上にも1役買っている
  • 構成を決めるのに、考えるべきポイントは以下

    • そのデータは信頼性と性能、どちらが求められるのか?

    • どのレベルのRAID?

      • RAID 0...データを異なるディスクに分散するが、冗長性なし
      • RAID 1 ... 2本のディスクに全く同じデータを持つ。ただし、性能はディスク1本のときと変わらない
      • RAID 5 ... 最低3本で構成し、データとともに「パリティ」と呼ばれる誤り符号訂正符号を分散配置。1本までならどのディスクが壊されてもパリティから実データを復元することができ、保全できる。また、データを分散させるため、I/O性能の向上も期待できる。ただ、パリティ演算のため、書き込み速度はそのほかRAIDに比べて遅い
      • RAID 10 ... RAID 0と1のいいとこどり。最低4ディスク必要なためお金がかかる。

    • 何本のディスクでRAIDを構成するか？

  • 結論：少なくともRAID 5で構成し、お金があればRAID 10。
• ファイルの物理配置決定
  • データベースのファイルをどのディスクに配置するか
  • ここでは対象を「データファイル」「インデックスファイル」の2つにします
  • 配置を決めるポイントは「サイズ」と「性能」
  • 例えば、最もファイルI/O量が多いデータファイルは他のファイル群とは独立したディスクに格納

##2.3 バックアップ設計
主要なバックアップ方式は以下の3つ

• 完全バックアップ
  • ある地点でのスナップショットを作成
  • デメリット
    • 他のバックアップ手法に比べて時間がかかる、HWリソースへの負荷が高い
    • バックアップリスタートのためにサービス停止が必要
• 差分バックアップ
  • DBNSはユーザーから受け付けた変更をすぐにデータファイルに反映するのではなく、いったんトランザクションログに溜め込む。したがってトランザクションログにはデータベースへの変更操作履歴が累積的に残ている
  • これをバックアップしておけｂ、データベースへの操作をもう一度リプレイ可能。
  • よって、バックアップ時は大本となるある地点でのフルバックアップデータと、最新のトランザクションログのバックの2つあればよい
  • メリット
    • フルバックアップに比べて、バックアップデータ量が少なくて済む
      -　デメリット
      -　フルバックアップに比べて、バックアップに時間がかかる
      -　2つの資材(フルバックアップと、最新のトランザクションログ)のうちどちらか一方でも不備があればバックアップできない
• 増分バックアップ
• 背景：差分バックアップには無駄があった
• 古いログファイルを内包しているので、リカバリの際、同じデータを何度もバックアップしていることになる
• 上記のような無駄を省いたのが増分バックアップであり、必要なのは、ある地点でのフルバックアップと、各日における変更分を記録したトランザクションログ
• メリット
  • バックアップ量が3つのバックアップ方法のうち、一番少ない
  • ゆえにバックアップ時間も一番短い
• デメリット
  • リカバリ手順が一番複雑
  • リカバリに必要なファイルが増えるため、完全にデータを復旧できる可能性が最も低くなる
• ではどのバックアップ方式を採用すべきか？
  • (俺的)一番は「完全にデータバックアップできる」可能性が一番高い
  • 以下4つのポイント
  • いつ時点の状態に復旧するか(そもそも復旧する必要があるか)
  • バックアップに使用できる時間
  • バックアップ用に活用できるストレージ容量
  • 選択肢は4つ
  • バックアップしない ... テーブル(出力)ではなく、入力データから復元する場合もある
  • フルバックアップ ...最新の状態に戻せない可能性あり
  • フルバックアップ + 差分バックアップ
  • フルバックアップ + 増分バックアップ

##2.4 リカバリ設計

• 障害復旧は3あり、バックアップファイルを戻す「リストア」と、そのファイルに対して増分/差分バックアップしていたトランザクションログを適用して障害発生直前の状態に戻す「リカバリ」、データベースサーバーに残っているトランザクションログを適用する「ロールフォワード」

#3.論理設計と正規化(なぜテーブルは分割する必要があるのか)

##3.1テーブルとは何か

##3.2テーブルの構成要素

• 外部キー

  • Excelでいうところの入力規則で、あるテーブルの列(外部キー)には、外部キーが記述されているテーブル内の「値しか」挿入できない = 「参照整合性制約」
  • このとき、外部キーが記述されているテーブルには自由にレコードを「追加」はできます
  • ただ、「削除」は自由にできません。そのキーを別テーブルが参照しているかもしれないからです。
  • では、削除/更新する場合はどうするか？
    • そのキーを参照している別テーブルのレコードごと削除する「カスケード」

• どの列をキーにするか？

  • 少なくとも可変長文字列が設定されているカラムはキーに選ばない
    • 理由：レコードを一致/結合させる際、空白ありなしだと、予期しないパターンが発生し、混乱を招く可能性があるから
    • 対策：少なくとも、何らのコードやIDといったフォーマットがきっちり決まっているデータを設定する

• 外部キー(参照整合性制約)以外の制約

  • Not Null 制約
  • 一意制約...主キーと似ているが、テーブル内で一意制約は何個でも設定できる
  • CHECK制約...ある列の取りうる値の範囲を制限

##3.3 正規化とは何か

• 一言でいうと、「データの冗長性をなくし、一貫性を保持するための方法」

##3.4 第1正規形

• 「一つのセルに一つの値しか含まない」

• 当たり前じゃんかと思うかもしれないが、例えば「子供」という列があったとき、複数の子供がいるレコードでかつ非正規系ならば、一つのセルに値が複数入ったデータが発生する

• なぜ一つのセルに複数の値が入っていてはダメなの？

  • レコードを一意に決定できないケースがあるから(=関数従属)
  • 関数従属
    • Y = f(x)...xを決定すれば、Yが一意に決まる。このとき、「Yはxに従属する」
    • 正規化とは全ての列に対して「関数従属を満たすように整理する」ことでもある

• 対策

  • 対策①：子の分、列を用意する
  • 対策②：親-子の関係レコードを複数用意する ←無駄が少ないので基本こちらを採用

社員ID	社員名	子1	子2	子3
A	加藤	太郎1	太郎2
B	藤本

社員ID	社員名	子
A	加藤	太郎1
A	加藤	太郎2
B	藤本

• 対策②の問題点

  • 主キーを決められない
    • 3列全部を主キーにすればいいじゃんと思うかもしれないが、例えば藤本さんのように子を持たない=「NULL」の場合、主キーはNULLを許容しないため、ダメです。※同性同名かつ子を持たないレコードがあった場合、重複してしまいます

• 対策②の解決方法

  • テーブルを分ける(現在だと、「社員」と「扶養者」の情報が1テーブルに混在していて、テーブルの意味やレコードの単位が理解しにくい)

##3.5 第2正規形

会社cd	会社名	社員id	社員名	年齢
C001	A社	000A	加藤	40

上記テーブルは、全カラムがスカラーなので、第1正規系ではある。

しかし、全カラムが主キーに対して関数従属でないからです。
このテーブルの主キーは、「会社cd」「社員id」であり、「会社名」以外はこの2カラムを主キーにアクセスできるが、「会社名」だけは、「会社cd」だけで事足りるテーブル構成です。(部分関数従属)

第2正規系では、部分関数従属をなくし、完全関数従属を満たすようにテーブルを成形します。

• 対策
• テーブル分割
• 会社名を別テーブルに外だしする(関数従属する会社cdとともに)
• そもそも第2正規系でないと何が悪いのか？
  • 会社cdと会社名の対応関係が間違ったままレコードが登録される可能性がある
  • 新しい会社を登録しようとしたとき、主キーに社員idがふくまれているので、社員がいないと会社を登録できないといった運用になってしまう(主キーなのでNULLを設定することもできず、ダミー社員を用意する方法もあるが根本的解決になっていません)

##3.6第3正規形

• 第2正規系から隠れた関数従属を見つける
• 例えば、段階的な従属関係(推移的関数従属)がある

会社cd	部署cd	部署名	社員id	社員名	年齢
C001	AAA	広報	000A	加藤	40

上記の例では、、「部署cd→部署名」の関数従属があり、全体としても「会社cd,社員id(主キー)→部署cd」という関数従属がある(推移的関数従属)

• 推移的関数従属があるとなんでダメなの？

  • 本当は部署「人事」は存在するが、該当する社員がいないと登録できない(主キーは社員idでNot Nullのため)

• 対策

  • テーブルを分割
  • 推移的関数従属の一番低レイヤ(上記の例だと、部署cd→部署名)の関係を外だしする
  • これにより、非キー列はキー列に対してのみ従属するようになり、非キー列間の従属がなくなりました

##ボイス-コッド正規形
★あとでやる

##第4正規形
★あとでやる

##第5正規形
★あとでやる

##正規化についてのまとめ

• 正規形はいつでも非正規形に戻せること

#4.ER図～複数のテーブルの関係を表現する

##4.1 テーブルが多すぎる

##4.2 テーブル同士の関係を見抜く

• 1対1

  • 2つのテーブルの主キーが完全一致。正規化すると起こりえないケース。

• 1対多

  • 正規化で主に発生する関係

• 多対多

  • 現実世界に存在するエンティティをそのまま持ってきた状態が主(例えば、「学生」エンティティと「講義」エンティティ)
  • 多対多がRDBで問題となる理由は、両者のエンティティが共通のキーとなる列を持っておらず、両エンティティを結合した情報を得ることができない
    • もし、この多対多のエンティティ同士を無理矢理にでも関連づけようとすれば、
      「学生」テーブルに「講義コード」列を持つようにするしかありません。
      しかし、それでは講義を複数受講している学生がいれば「学生」テーブルに、一人の学生が複数行含まれるようになりますし、
      講義を未登録の学生は、「学生」テーブルにも登録できなくなってしまいます。

##4.3 ER図の書き方

##4.4 多対多と関連実体

• 多対多の関係に対応するための対策
  • (俺だったら)「学生」「講義」エンティティとは別に、「受講状況」エンティティを用意する(要は知りたいのは、「学生はどの講義を受講しているか」/「この講義を受講している学生はだれか」)
  • 関連実体
    • 上記の例だと、「学生」「講義」それぞれの主キーを組みわせて、主キーとする「受講」エンティティを用意する
    • すると、「学生」と「受講」は1対多の関係、「受講」と「講義」は1対多の関係になる

#5.論理設計とパフォーマンス～正規化の欠点と非正規化～

正規化がSQLのパフォーマンス問題を引き起こすのは、
正規化すると結合が必要になるからです。
この対策は2つあり、1つは「SQL分そのもののチューニング」もう一つは「非正規化」

##正規化の功罪

##非正規化とパフォーマンス

##冗長性とパフォーマンスのトレードオフ

#6.データベースとパフォーマンス
##パフォーマンスを決める要因

##インデックス設計

##B-treeインデックスの設計方針

##統計情報

#7.論理設計のバッドノウハウ
##論理設計のやってはいけない
##非スカラ値
##ダブルミーニング
##単一参照テーブル
##テーブル分割
##不適切なキー
##ダブルマスタ

#論理設計のグレーノウハウ
##ライン上に位置する設計
##主キーが役にたたないとき
##列持ちテーブル
##アドホックな集計キー
##多段ビュー

##データレイジングの重要性

#一歩すすんだ論理設計～SQLで木構造を扱う～