『達人に学ぶDB設計徹底指南書』を読みました。

皆様こんにちは、Amiaです。

私はここ何週間かでタイトルにもある通り『達人に学ぶDB設計徹底指南書』を読んでいました。
そのため今回は上記の書籍について読んだ感想等をまとめいきたいと思います。

よろしければ最後までご覧になっていただければと思います。

⚫︎良かった点
⚫︎学んだこと
⚫︎難しく感じた部分
⚫︎最後に

⚫︎良かった点

論理設計だけでなく物理設計についても記載されている。
バッドノウハウ、グレーノウハウの例が豊富に記載されていて、著者の実体験にも基づいておりイメージしやすい。

⚫︎学んだこと

「第1章データベースを制する者はシステムを制す」

DOA:システムを作る際にプログラムよりも前にデータの設計から始める方法論。
3層スキーマ
- 外部スキーマ
  - システムの利用者であるユーザーから見て、データベースがどのような機能とインタフェースを持っているかを定義するスキーマ。
  - テーブルやビュー。
- 概念スキーマ
  - データベースに保持するデータの要素及び、データ同士の関係を記述するスキーマ。
  - テーブル定義。
- 内部スキーマ
  - 概念スキーマで定義された論理データモデルを、具体的にどのようにDBMS内部に格納するかを定義するスキーマ。
  - データの物理的配置。
- 概念スキーマは何のためにあるか？
  - 外部スキーマと内部スキーマの間に位置することで、両者の変更が互いに影響し合わないようにするための緩衝材の役割を果たしている。

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

論理設計:概念スキーマを定義する設計のこと。
システムの世界での「論理」:「物理層の制約にとらわれない」という意味。
システム開発におけるデータベース設計は下記の手順で行われる。
- ①概念スキーマ(論理設計)
- ②内部スキーマ(物理設計)
論理設計の四つのタスク
- ①エンティティの抽出
  - システムのためにどのようなデータが必要になるかを抽出する。
  - このタスクの一部は「要件定義」と重なっている。
- ②エンティティの定義
  - 各エンティティがどのようなデータを保持するかを決める。
- ③正規化
  - エンティティについて、システムでの利用がスムーズに行えるように整理する。
- ④ER図の作成
  - エンティティ同士の関係を表現する図を作成する。
物理設計の五つのタスク
- ①テーブル定義
  - 論理設計で定義された概念スキーマをもとに、それをDBMS内部に格納するための「テーブル」の単位に変換していく作業。
- ②インデックス定義
- ③ハードウェアのサイジング
  - システムで利用するデータサイズを見積り、それに十分な容量の記憶装置を選定する。
  - システムが十分な性能を発揮できるだけのスペックのCPUやメモリを持ったサーバーを選定する。
- ④ストレージの冗長構成決定
  - どの程度冗長化して信頼性(可用性)・性能を確保するかでRAIDのレベルを決める。
- ⑤ファイルの物理配置決定
  - データベースのファイルをどのディスク(またはRAIDグループ)に配置するかを考える。
  - DBMSでは自動化が進んでいるが、基本的な考え方を押さえておくことが重要。
  - データベースに格納されるファイルは下記の5種類に大別される。このうち開発者が意識するのは❶と❷だけ。
  - ❶データファイル
    - ユーザーがデータベースに格納するデータを保持するためのファイル。
  - ❷インデックスファイル
    - データに作成されたインデックスが格納されるファイル。
  - ❸システムファイル
    - DBMSの内部管理用に使われるデータを格納する。
  - ❹一時ファイル
    - DBMS内部での一時的なデータを格納するために使われる。
  - ❺ログファイル
    - DBMSによって呼び方が異なる。
    - DBMSは、テーブルのデータに対する変更を受け付けた場合、即座にデータファイルを更新しているわけではない。一旦、このログファイルに変更分を溜め込んだ後に、一括してデータファイルに変更を反映している。
データベースにおいては、データの整合性とパフォーマンスの間に強いトレードオフが存在する。
三つのバックアップ方式
- ①フルバックアップ(完全バックアップ)
  - ある時点でそのシステムで保持されている全てのデータをバックアップする方式。
- ②差分バックアップ
  - 前回のフルバックアップ後に変更された部分のデータだけをバックアップする方式。
- ③増分バックアップ
  - 前回のフルバックアップ後にその日に変更された部分のデータだけをバックアップする方式。
バックアップ方式にもトレードオフがある。
リストア及びリカバリの手順
- ①リストア:フルバックアップのファイルをデータベースに戻す。
- ②リカバリ:差分(または増分)バックアップしていたトランザクションログを適用する。
- ③ロールフォワード:データベースサーバーに残っているトランザクションログを適用する。

「第3章論理設計と正規化 ~ なぜテーブルは分割する必要があるのか？」

正規化
- 正規化とは更新時の不都合/不整合を排除するために行う。
- 正規化は従属性を見抜くことで可能になる。
- 正規形はいつでも非正規形に戻せる。
- 正規化は常にするべきか？
  - 第3正規形までは原則として行う。
  - 関連エンティティが存在する場合は関連とエンティティが1対1に対応するように注意する。
正規形:データベースで保持するデータの冗長性を排除し、一貫性と効率性を保持するためのデータ形式。
第1正規形
- 1つのセルの中には1つの値しか含まない状態にする。
第2正規形
- テーブル内で部分関数従属を解消し、完全関数従属のみのテーブルを作る。
第3正規形
- テーブル内での推移的関数従属を解消する。
ボイス-コッド正規形
- 非キーからキーへの関数従属をなくす。
第4正規形
- 独立な多値従属性が複数存在するテーブルを分割することで作られる。
第5正規形
- 関連がある場合は、それに対応する関連エティティを作る。

「第4章 ER図 ~ 複数のテーブルの関係を表現する」

IDEF1X
- 角の尖った四角で表記したエンティティ
  - 独立エンティティ:他のテーブルのデータに依存することなく、データを保持することのできるエンティティ。
- 角の丸い四角で表記したエンティティ
  - 従属エンティティ:他のテーブルにデータが存在しなければ、データを保持することのできないエンティティ。
- エンティティ間の関連:カーディナリティの「多」が黒丸(⚫︎)で表現される。

「第5章論理設計とパフォーマンス ~ 正規化の欠点と非正規化」

正規化と検索SQLのパフォーマンスは強いトレードオフの関係にある。
非正規化は最後の手段。
正規化の次数は高ければ高いほど良い。
冗長性排除によって引き起こされる性能問題
- サマリデータの冗長性排除
  - サマリデータを冗長に保持すると正規形に違反するが、検索を高速化できる。
- 選択条件の冗長性排除
  - 選択条件を冗長にすると第2正規形ではなくなる。
  - 選択条件を冗長に保持すると正規形に違反するが、検索を高速化できる。
非正規化のリスク
- ①非正規化は検索のパフォーマンスは向上させるが、更新のパフォーマンスを低下させる。
- ②データのリアルタイム性(鮮度)を低下させる。
- ③後続の工程で設計変更すると手戻りが大きい。
論理設計には物理設計の知識が必要である。

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

インデックス設計:SQLのパフォーマンス改善のための非常にポピュラーな手段。
- アプリケーション透過的
  - インデックスを使うかどうかはDBMSが自動的に判断するため、アプリケーションのコードに影響を与えない。
- データ透過的
  - テーブルに格納されているデータの中身が影響を受けることはない。
  - テーブルの構造が変化することはない。
- 大きな性能改善効果
- B-treeインデックス:最も頻繁に利用するインデックス。
  - B-treeインデックスの長所:親ソート性、均一性、持続性、処理汎用性、非等値性が平均的に優れている。
  - B-treeインデックスの設計指針
    - ①大規模なテーブルに対して作成する。
      - データ量が少ない場合はインデックスの効果はない。
    - ②カーディナリティの高い列に作成する。
      - カーディナリティが高い列ほどインデックスの効果が高い。ただし、値が平均的に分散しているほうが良い。
        ③SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成する。
  - B-treeインデックスを利用できていないSQL
    - インデックス列に演算を行なっている。
    - 索引列に対してSQL関数を適用している。
    - IS NULL述語を使っている。
    - 否定形(<>)を用いている。
    - ORを用いている。
    - 後方一致、または中間一致のLIKE述語を用いている。
    - 暗黙の型変換を行なっている。
- B-treeインデックスの注意事項
  - 主キー及び一位制約の列には作成不要。
  - B-treeインデックスは更新性能を劣化させる。
  - 定期的なメンテナンスを行うことが望ましい。
統計情報
- テーブルやインデックス等「データ」についてのデータ。(メタデータ)
- DBMSは上記のメタデータを頼りにSQLのアクセスパスを決定する。
- SQL文によってテーブルにアクセスする流れ
  - ①ユーザーからSQL文がDBMSへ発行される。
  - ②DBMS内の「パーサ」で構文チェックを行い、「オプティマイザ」というモジュールへ送る。
  - ③オプティマイザは「カタログマネージャ」というモジュールに、統計情報の照会をかける。
  - ④オプティマイザが統計情報から最短経路を選択してSQLを手続きに変換する。その後、テーブルへアクセスを行う。
- 統計情報の収集タイミング
  - データが更新された後、なるべく早く。
  - 統計情報収集は原則、夜間帯に実施する。

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

バッドノウハウ(アンチパターン)のダメな点
- ①システムの品質に左右する。
- ②後から変更することが容易ではない。
論理設計の代表的なバッドノウハウ
- 非スカラ値
  - 1つのセル内で複数の値を持つことができるデータ型や構造。
  - かつては標準機能で「配列型」があり、非スカラ値を含むテーブルを作ることができた。
  - 情報は可能な限り分割して保存するのが良いが、意味を壊してはいけない。
- ダブルミーニング
  - 同一の列だが、途中から格納するデータが変わっている。(同一の列が2つの意味を持つ。)
  - テーブルや列の意味は、一度決めたら容易に変更してはならない。
- 単一参照テーブル
  - あらゆるタイプのマスタテーブルを1つにまとめる。
  - 利点
    - マスタテーブルの数が減るため、ER図やスキーマがシンプルになる。
    - コード検索のSQLを共通化できる。
  - 欠点
    - 大きめの可変長文字列型で宣言する必要がある。
    - レコード数が多くなり、検索のパフォーマンスが悪化する。
    - コードタイプやコード値を間違えて指定してもエラーになることがないため、バグに気づきにくい。
    - ER図の可読性を下げる。
  - テーブルにポリモフィズムはいらない。
- テーブル分割
  - 水平分割:レコード単位でテーブルを分割する手段。
    - テーブルを分割することでパフォーマンスを改善できる。
    - 分割する意味的な理由がない。
    - 拡張性に乏しい。
    - 他の代替手段がある。
  - 垂直分割:列単位でテーブルを分割する手段。
    - SQL文のパフォーマンス改善が可能。
    - 分割する意味的な理由がない。
    - 「集約」で代替可能。
  - 集約:テーブル分割の代替案に位置づけられる方法。下記の2種類に分類される。
    - 列の絞り込み
      - 元のテーブルから必要な列だけを持った新しいテーブルを追加作成する。
      - 定期的に元のテーブルとの同期が必要。
    - サマリテーブル
      - 集約関数によってレコードを集約した状態で保持する。
      - 事前に集約を行ったテーブルを作っておく。
      - 更新のタイムラグによってデータの整合性がとれない時間帯が生じるデータ同期の問題は列の絞り込みと同じ。
- 不適切なキー
  - 主キー、外部キー、結合キーについては
    - 可変長文字列は不変性がないため不向き。
    - 同じデータを意味するキーは同じデータ型にする。
    - キーには固定長文字列の「コード」列が望ましい。
- ダブルマスタ:同じ役割を果たすはずのマスタテーブルが2つ存在するようなケース。
  - SQLを複雑にして、パフォーマンスを悪化させる。
  - システム統廃合によって生じることが多い。
バッドノウハウのどこが悪いか?
- 運用にかかるコストが高くなる。
- エンジニアやプログラマの設計に対する理解を妨げる。
- 設計変更が難しい。

「第8章論理設計のグレーノウハウ」

グレーノウハウ:バッドノウハウとははっきり断定することができないが、無神経に使うと開発や運用に支障をきたすような設計のこと。
- 代理キー
  - 下記のような主キーが決められない、または主キーとして不十分ケースに利用される。
    - そもそも入力データに主キーにできるような一意キーが存在しない。
    - 一意キーはあるが、サイクリックに使いまわされる。
    - 一意キーはあるが、途中で指す対象が変化する。
  - 極力代理キーの使用は避けて、自然キーによる解決を図るべきである。
    - 代理キーがそもそも論理的に不要なキーのため。
    - 論理モデルをわかりにくくしてしまうため。
  - 代理キーを使わず、自然キーだけで解決する場合
    - そもそも入力データに主キーにできるような一意キーが存在しない。
      - データベース側で打つてはない。
        
        業務使用を調整する。
        
        データベースに投入される前のアプリケーションでデータが一意になるように整形する。
    - 一意キーはあるが、サイクリックに使いまわされるか途中で指す対象が変化する。
      - 時点や期間を表す列を持つ。
- 列持ちテーブル
  - 利点
    - シンプルな設計。
    - 入出力のフォーマットと合わせやすい。
  - 欠点
    - 列の増減が難しい。
    - 無用のNULLを使わなくてはならない。
  - 特殊な状況でない限り、原則として列持ちテーブルは使うべきではない。基本的には「行持ち」テーブル構成を採用するべき。
  - 行持ち⇔列持ち間のデータ移行はSQLで簡単に変更できる。
- アドホックな集計キー:都道府県テーブルに対してつける地方コード列等のような場当たり的につけるキーのこと。
  - テーブルに場当たり的にキーを追加していくとパフォーマンスを劣化させる。
  - 解決策
    - キーを別テーブルに分離する。
      - SQLでは統合処理が必要になるため、パフォーマンス問題の解決にはならない。
    - ビューを使う。
    - GROUP BY句の中でアドホックキーを「アドホック」に作る。
      - CASE式で割り振る。
- 多段ビュー
- ビューは物理的にはSELECT文が書かれたファイルにすぎない。
- ビュー定義のSELECT文を実行して、オリジナルのテーブルにアクセスしてデータを取り出している。
- ビューの背後にあるテーブルの存在を常に意識する。
- データクレンジング:業務で利用されていたデータをデータベースに登録できる状態にすること。
  - データクレンジングはデータベース設計に先立って行う必要がある。
  - 代表的なデータクレンジングの内容
    - 一意キーの特定
      - 一意キーの存在しないデータは、バッドノウハウ「不適切なキー」をも生み出す。
    - 名寄せ:人名や企業名の表記揺れを解消して名称を統一する。
      - 名寄せをサボるとバッドノウハウ「ダブルマスタ」を生み出す。

「第9章一歩進んだ論理設計 ~ SQLで木構造を扱う」

リレーショナルデータベースで木構造を扱う方法
- 隣接リストモデル
  - 最も古くから知られている方法。
  - ノードのレコードに親レコードの情報(ポインタ)も持たせようとするもの。
  - 更新や検索のクエリが極めて複雑になり、パフォーマンスも悪いという欠点がある。
- 入れ子集合モデル
  - ノードを点ではなく面積を持った「円」としてとらえる。
  - ノード間の階層関係を縁の包含関係によって表す。
  - 更新対象以外のレコードも連動して更新しなければいけいないため、更新時のパフォーマンスが悪い。
- 経路列挙モデル
  - ノードをディレクトリ(フォルダ)と見なし、各ノードまでの経路(path)を記述する。
  - 検索が簡単で更新が複雑。

⚫︎難しく感じた部分

イメージしづらい部分があり全体的に難しいと感じた。
第6章は内部的な内容で特に難しいと感じた。

⚫︎最後に

今回読んだ書籍は全体的に難しく、イメージしづらい部分が多くあるように感じました。
まだまだ理解不足だということを痛感しました...。
これからアウトプットしたり経験を積んだりして慣れていきたいと思います。
気になった方は是非購入を検討してみてはいかがでしょうか。

⚫︎良かった点

⚫︎学んだこと

「第1章 データベースを制する者はシステムを制す」

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

「第3章 論理設計と正規化 ~ なぜテーブルは分割する必要があるのか？」

「第4章 ER図 ~ 複数のテーブルの関係を表現する」

「第5章 論理設計とパフォーマンス ~ 正規化の欠点と非正規化」

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

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

「第8章 論理設計のグレーノウハウ」

「第9章 一歩進んだ論理設計 ~ SQLで木構造を扱う」