達人に学ぶDB設計を読んだ

はじめに

今回はDB設計の学習の一環で達人に学ぶDB設計を読んだのでその感想について
記事にしたいと思います。

良かったところ

DB設計について各分野で深くまで学習できる書籍だったと思います。
設計の段階で使われる技術についての解説はもちろんのことですが
それを使う上でのメリット、デメリットの解説もあるので本当に実践で使える
知識を身につけるなら読んでおきたいものの一つなのではないでしょうか。
また、各章末に演習問題もついており身につけた知識をアウトプットする
場所もあるのでその点も読んで良かったと思ったところです。

学んだこと

第2章　論理設計と物理設計

• 論理設計における論理とはCPUパワーやストレージの格納場所、DBMSのデータ型やSQLの構文などの物理層の制約にとらわれないと言う意味で使われる。
• データベース設計は概念スキーマ（論理設計）→内部スキーマ（物理スキーマ）の順で行われるのが原則。
• 論理設計は以下1~4の手順で進める。
  • 1　エンティティの抽出
    • エンティティとは実態を意味しDBにおけるデータのこと。
    • 用件定義の段階で行われ、どのようなエンティティが必要かを抽出する。
  • 2　エンティティの定義
    • 各エンティティがどのようなデータを保存するかを決める。ここでのデータはDBの列を意味する。
    • 特定の列の値を決めるためのキーとなる列を定義することが重要。
  • 3　正規化
    • エンティティ（テーブル）についてシステムでの利用がスムーズに行えるように整理する作業を指す。
    • 単にテーブルと列を用意しただけではシステムの利用に耐えられずそこから正規化をする必要がある。
  • 4　ER図の作成
    • テーブル同士のつながりを図にしたもの。
• 物理設計は以下の1~5の手順で進める。
  • 1 テーブル定義
    • 論理設計で定義された概念スキーマをもとにそれをテーブルの単位に変換していく作業。
  • 2 インデックス定義
    • パフォーマンスの部分で重要な役割を果たす。
  • 3 ハードウェアのサイジング
    • 大きさを決める作業でありシステム開発では２種類の意味で使われる。
      • キャパシティという意味でシステムで利用するデータのサイズにキャパシティ的に見合うストレージを選定する。
      • パフォーマンスという意味でシステムが十分なパフォーマンスを発揮できるだけのサーバーやストレージのCPUやメモリを選定する。
    • データベースにおいて生合成とパフォーマンスは強いトレードオフの関係にある。
    • キャパシティのサイジング
      • データ量は物理的なテーブル設計とインデックス定義が終わらなければ算出できないため論理設計後でなければ作業の実施はできない。
      • 算出するデータ量はテーブル以外に様々な形式のファイルも加算する必要がある。
      • サービスが開始されてからデータがどの程度増えるというのは想定が難しい場合もあるので余裕をもっとサイジングを行うこと、後から簡単に記憶装置を追加できる構成（スケーラビリティが高い）にしておくことの二つのアプローチが考えられる。
    • パフォーマンスのサイジング
      • 性能用件
        • システム開発では性能用件を処理時間とスループットで定義する。「どれだけ早いか」と「どれだけ多いか」ということ。
  • 4 ストレージの冗長構成決定
    • RAID
      • 高い耐障害性を構築するための技術としてRAIDというものがあり、これの基本的な考え方として複数のディスクに同じデータを書き込んで冗長化することで特定のディスクが壊れても他のもので保存するというもの。
      • RAID0（ストレイピング）はデータを異なるディスクに保存してI/O性能を高めたものだが、一つのディスクが壊れたらデータが失われてしまう側面もある。
      • RAID1（ミラーリング）は二つのディスクに同じデータを保存するものでディスクが同時に壊れない限りはデータは守られるがディスク二つで一つのデータを保存するので使用効率が悪い。
      • RAID5はパリティ分散と呼ばれる方式でパリティという誤り符号訂正符号を分散して格納することによってディスクが一つまでなら壊れてもパリティから復元できる。またデータを分散できるためI/O性能も向上する。
      • RAID10（RAID1+0）はRAID1でRAID0を作る方式でRAID1の高信頼性とRAID0の高速性を両立させている。しかし、必要なディスクが多いためコストがかかる。
      • 採用するならば可能であるならRAID10が望ましい。RAID0は耐障害性の観点から絶対に採用すべきではない。
  • 5 ファイルの物理配置決定
    • データベースのファイルをどのディスクに配置するかを考える作業。
• バックアップ設計
  • データベースのデータが失われるような事態を防ぐためにはデータを失わないような設計をするためのRAID設計、データが失われた時に復旧できるようにするバックアップとリカバリの二つの方針がある。
  • データベースのバックアップ設計は以下の主要な３つのバックアップを組み合わせる。
    • フルバックアップ：ある時点でのデータ全てのバックアップを取る方式。
    • 差分バックアップ：フルバックアップのファイルとその差分を取ったバックアップファイルを取る方式。
    • 増分バックアップ：フルバックアップのファイルとその時点での変更分のみのバックアップファイルを取る方式。
  • バックアップ方式は「フルバックアップ＋差分バックアップ」か「フルバックアップ＋増分バックアップ」が一般的。
• リカバリ設計
  • 「バックアップファイルを戻す」作業をリストア、そのファイルに対してトランザクションログを適用して変更文を反映する作業を「リカバリ」と呼ぶ。
  • リストアおよびリカバリの手順
    • 1　リストア
    • 2　リカバリ
    • 3　ロールフォワード

第3章　論理設計と正規化

• 正規化とはデータの冗長性をなくしていく作業でデータ更新時の不整合を防止することを目的としている。
• 作成したテーブルは全て英語の複数形や複数名詞で表現できる。できなければテーブル設計に問題がある。
• 正規化には第１正規形〜第５正規形まであるが第３正規形まで覚えれば十分。
  • 第１正規形：レコードの各列に入る値を一つにする。テーブルの分割が有効。
  • 第2正規形：完全関数従属のテーブルになるようにする。テーブルの分割が有効。
  • 第3正規形：二段階の関数従属（推移的関数従属）を解消する。テーブルの分割が有効。

第4章　ER図

• ER図の代表的なフォーマットとしてIE（Information Engineering）表記法とIDEFIXがある。
• リレーショナルデータベースにおいて「多対多」の関係はNG。解決には該当のテーブルの間にデータを特定するための第3のテーブルを作成する関連実態を用いる。

第5章　論理設計とパフォーマンス

• テーブル結合の操作は結合するテーブル、テーブルのレコードが増えるほど処理時間がかかる。
• 正規化と検索SQLは強いトレードオフの関係にあり、厳しい正規化はパフォーマンスの低下を招くが、非正規化すればデータ不整合が発生しやすくなる。
• 非正規化というのは切羽詰まった時に取る最終手段。
• 正規化による起こる２パターンの性能問題
  • サマリデータの冗長性排除によるパターン：集計結果などのサマリデータをテーブルに保持すると非正規化するが、検索結果を高速化できる。
  • 選択条件の冗長性とパフォーマンス：選択条件を冗長に保持すると正規形に違反するが、検索結果を高速化できる。
• 非正規化の３つのリスク
  • 非正規化は検索パフォーマンスは向上させるが更新のパフォーマンスを低下させる。
  • データのリアルタイム性（鮮度）を低下させる。
  • 後続の工程で設計変更すると、手戻りが大きい。

第6章　データベースとパフォーマンス

• インデックス
  • SQLチューニングとしてポピュラーな手段。以下がその理由。
    • アプリケーションのコードに影響を与えない。
    • テーブルのデータに影響を与えない。
    • 以上を理由を踏まえても性能改善の効果が大きい。
  • B-treeインデックス
    • インデックスにはいくつか種類があるが基本的に利用されるのはB-treeインデックス。
    • 場合によっては他のインデックスが優れている場面もあるが、平均点の高く汎用性に優れているところが長所にある。
    • どの列にB-treeインデックスを作ればいいかには以下のような指針が挙げられる。
      • 大規模なテーブルに対して作成する。しかし、データ量が少ない場合（目安としてレコード件数１万以下）は効果がない。
      • カーディナリティの高い列（一意の値の種類が多い列）に作成する。目安として全体レコードの5％程度に絞り込めること。また値が平均して分散しているのが望ましい。
      • SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成する。
• 統計情報
  • 統計情報がSQLのアクセスパスを決める最大の要因であるため重要。
  • 統計情報の収集はデータが大きく更新された後、なるべく早く行うべき。また、作業には多くのリソースを消費するのでシステムの利用者が少ない夜間帯に行うのが原則。

第7章　論理設計のバッドノウハウ

• SQLは一つの列に配列型のデータを入れることができるが、第１正規形を優先すべき。
• 意味が崩れない限り情報は分割してスカラ値を作成するべき。
• 一度体重を格納する列と決めた後に年齢を格納するみたいに後から列の意味を変更してはいけない。
• 様々なタイプのマスタテーブルを合わせた単一参照テーブルはポリモルフィズムであり、テーブルには必要ない。
• テーブル分割の手法である垂直分割と水平分割は論理的な意味を持たないので原則利用すべきではない。
• 主キーや外部キーなどのキーとなる列のデータ型には可変長文字列（VARCHAR）は不変性を備えていない点と固定長文字列（CHAR）との混同という理由から使うべきではない。

第８章　論理設計のグレーノウハウ

• 主キーが決められない、または主キーとして不十分なケース
  • 1.そもそも入力データに主キーにできるような一意キーが存在しない。
  • 2.一意キーはあるが、サイクリックに使いまわされる。
  • 3.一意キーはあるが、途中で指す対象が変わる。
• 以上の問題を解決するために代理キーを利用するという方法が考えられるが、それは極力避けて自然キーのみで解決すべき。
• 上記2,3のケースではタイムスタンプ、インターバル（有効期限）の列を追加することで解決が可能。
• ビューの背後にはテーブルが存在していることを意識しなければパフォーマンスの悪化につながる。
• システムの世界には「KISS（Keep It Simple ,Stupid）の原則」という過度に複雑な作りはシステムをダメにするという思想からきているスローガンがあり、そのことからも多段ビューの使用は避けるべき。
• データクレンジング
  • それまで業務で使用されていたデータをデータベースに登録できる状態にする作業をデータクレンジングと呼ぶ。
  • データクレンジングは設計に先立って行う。
  • 一意キーのないデータは不適切なキーを生むバッドノウハウの原因となる。
  • 名寄せはデータの表記揺れを解消して名称を統一する作業で、これをサボるとバッドノウハウであるダブルマスタの原因となる。

第９章　一歩進んだ論理設計

• 木構造はリレーショナルデータベースと相性が悪く、専用のデータベースも研究されている。
  • リレーショナルデータベースにおける木構造を扱う方法論
    • 隣接リストモデル：最も古典的なモデルで検索、更新で複雑な処理を必要とする。
    • 入れ子集合モデル：木を円の包含関係によって表現するモデル。検索は簡単だが、更新のパフォーマンスに問題がある。
    • 入れ子区間モデル：入れ子集合モデルの拡張版。更新のパフォーマンスを克服できるが実数型有効桁数が十分に確保されていなければ実用的でない。
    • 経路列挙モデル：ルートからノードまでの経路を保持するモデル。検索に強く更新に弱い。

難しかったこと

データベースを扱う書籍ですので当然SQLが登場するのですが
SQLについてはある程度分かっている前提ですので細かな解説はありません。
ですのでSQL初学者はSQLが出てきて読むのが詰まるということがあると思います。
本書籍を読む前にはSQL専門の書籍を一つ読むことをお勧めします。

さいごに

深くまで解説してくれる書籍でしたが、その分なかなか理解が追いつかない場所も
多分にあり理解がなかなか理解できない箇所もありました。
そこらへんは再度読み返して知識をゆっくり身につけるくらいの意識で
行きたいと思います。